CODEWITHSUNDEEP
vbaexcelfunctionworksheet
user7704036
user7704036

Reputation: 13

VBA - Function doesn't work using inputs from other worksheets

I want to create a function in VBA that returns the variation between two prices within a user-specified interval. For that I created the following function:

Public Function ret(p, i)

    ret = (p / Cells((p.Row - i), p.Column)) - 1

End Function

Where the input 'p' represents the last observation of a vector of prices, and the input 'i' refers to the number of periods I want to move up in the price vector.

The function works fine when I use inputs that are in the same worksheet where the function is defined. However, when employ inputs from other worksheets the function returns '#VALUE!'.

What is the function missing to work "globally"?

Thanks!!

Upvotes: 0

Views: 245

Answers (3)

Tim Williams
Tim Williams

Reputation: 166790

Avoiding Cells:

Public Function ret(p, i)
    If p.Row > i Then
        ret = (p / p.Offset(- i, 0)) - 1
    Else
        ret = "???"
    End If
End Function

Upvotes: 3

Shai Rado
Shai Rado

Reputation: 33692

The proper place to put your UDF code is not inside a Worksheet_Module, these are usually for Worksheet related events, such as Worksheet_Change , etc.

If you want to use a UDF from all worksheets, put it in a regular Module, so it's available to all worksheets.

Also, you should define your UDF parameters properly, and not leave them empty.

In your case use the code below inside a regular Module:

Public Function ret(p As Range, i As Long)

    ret = (p.Value / Cells(p.Row - 1, p.Column)) - 1

End Function

Screen-shot of VBAProject Explorer

enter image description here

Upvotes: 0

h2so4
h2so4

Reputation: 1577

I would use

ret = (p / p.parent.Cells((p.Row - i), p.Column)) - 1

cells alone refers to the current sheet

Upvotes: 2

Related Questions