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?