Search code examples
vbaexcelfunctionworksheet

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!!

Solution

  • I would use

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

    cells alone refers to the current sheet