VBA - opening a new workbook causes #VALUE! error.

[CosmoPants]

Afternoon,

I've been trying to write a function in VBA to find the last value in a column, but return an offset value relative to it. In the function, the user can specify a worksheet and a column, but also an "offset" value as an integer, so that "0" will return the last value in the column, "1" will return the last-but-one, "2" will return the value two rows before the last value, and so on.

It works ok, but whenever I open a new workbook and switch back to the workbook with this function in it, all the cells containing the function now display a #VALUE! error.

I'm sure I must be missing something pretty obvious. Any help or suggestions greatly appreciated!

The variable "Lock_Worksheet" is a worksheet name, "x" is a column letter, and "y" is the number of rows from the last one you'd like returned.


Code below the line -
The variable "Lock_Worksheet" is a worksheet name, x is a column letter, and y is the number of rows from the last one you'd like returned.

--------------------------------

Option Explicit
Option Base 1

Public Function FIND_RECENT2(Lock_Worksheet As String, x As String, y As Integer)
Application.Volatile

Dim k As String
Dim LastRow

k = Lock_Worksheet & "!" & x & "1000"
LastRow = Lock_Worksheet & "!" & range(k).End(xlUp).Address

FIND_RECENT2 = range(LastRow).Offset(-y, 0)

End Function

----------------------------------

Any thoughts?