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

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

Thanks Niek,
I wasn't aware of the penalty for the 4th argument and will try index/match
method for comparison.

And, Thanks for your comment about not recalculating if the external tables
change. These tables are fairly static and I am aware that I will need to
keep the two workbooks in synch - this is a concern but not a problem right
now.

However, I still have the same questions about whether I can improve the
function by pre-loading the table values rather than re-reading them each
time they are called. Basically, I don't know how Excel behaves when using
the same table reference repetitively and if I can improve the processing by
anticipating the loading of the range objects.

Additionally, can I extract the other workbook data without opening them?

Regards

Nick Earl

"Niek Otten" wrote in message
...
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

This should be a bit more efficient, but I'm not sure it'll add up to
much:

Const R1_Sheet As String = "'C:\Documents and Settings\Owner\" & _
"My Documents\TableData1.xls'!A_Tables"
Const R2_Sheet As String = "'C:\Documents and Settings\Owner\" & _
"My Documents\TableData2.xls'!B_Tables"
Static A_Tables As Range
Static B_Tables As Range

If A_Tables Is Nothing Then _
Set A_Tables = Range(R1_Sheet)
If B_Tables Is Nothing Then _
Set B_Tables = Range(R2_Sheet)

In article ,
"Nick Earl" wrote:

I wasn't aware of the penalty for the 4th argument and will try index/match
method for comparison.

And, Thanks for your comment about not recalculating if the external tables
change. These tables are fairly static and I am aware that I will need to
keep the two workbooks in synch - this is a concern but not a problem right
now.

However, I still have the same questions about whether I can improve the
function by pre-loading the table values rather than re-reading them each
time they are called. Basically, I don't know how Excel behaves when using
the same table reference repetitively and if I can improve the processing by
anticipating the loading of the range objects.

Additionally, can I extract the other workbook data without opening them?