Vlookups with the 4th argument FALSE are always slow, because Excel
"searches" line by line instead of using fast algorithms. If your data is
sorted ascending, you'll probably get an order of magnitude faster
calculation if you omit that argument and check yourself that it is an exact
match.
Or use MATCH and INDEX, in which case you can also have a descending sorted
table.

Be aware that your function calls will not be recalculated automatically if
the tables change. If you require that, include the ranges in your argument
list.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel


"Nick Earl" wrote in message
...
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