I have written a UDF to vlookup cell values from other workbooks. The lookup
ranges in the external sheets can be several thousand rows and the function
must be executed for several hundred rows on a recalculation.

Two questions:
1. If the other workbooks are not open at recalc, the cell values are
#VALUE. Is it possible to extract the reference ranges without having the
workbooks open (or opening then)?

2. I am not sure if the code reacquires the ranges each time the code
executes. Should I (or can I) create Global objects to hold the lookup
ranges to improve efficiency? The calculation time is only a few seconds but
'instantaneous' would be better.

Here is a sample of the function code - in my current function definition
there are currently 5 lookups in ranges from 1000 to 12000 rows:

Public Function GetScript(Recname)
Dim A_Tables, B_Tables
Dim A_Value, B_Value

R1_sheet = "'C:\Documents and Settings\Owner\My Documents\TableData1.xls'"
R2_sheet = "'C:\Documents and Settings\Owner\My Documents\TableData2.xls'"

Set A_Tables = Range(R1_sheet + "!A_Tables")
Set B_Tables = Range(R2_sheet + "!B_Tables")

On Error Resume Next
B_Value = WorksheetFunction.VLookup(Recname, B_Tables, 4, False)
If B_Value < "" Then
GetScript = B_Value
Else
A_Value = WorksheetFunction.VLookup(Recname, A_Tables, 2, False)
If A_Value < "" Then
GetScript = A_Value
Else
GetScript = ""
End If
End If

End Function

Thanks
Nick Earl