User Defined Function - efficiencies.

[Nick Earl]

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

[Niek Otten]

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" <[email protected]> wrote in message
news:%[email protected]...
>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
>

[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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:%[email protected]...
>>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
>>
>
>

[JE McGimpsey]

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 <[email protected]>,
"Nick Earl" <[email protected]> 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?