...
...
One question though regarding this topic (looking up values from closed
workbooks): Have you tried using SQL.REQUEST to fetch values based on
conditions from closed workbooks as 4th alternative. Just curious as
this functions seems to achieve the desired result (though not tested
on my side)
First, SQL.REQUEST uses SQL and ODBC to pull data. It's my understanding (I
could be wrong) that when using it to pull data from Excel workbooks the range
accessed must appear like a database table and it must be a named range
(including field names in the top row). If your data in the other workbooks has
those characteristics, then SQL.REQUEST can pull that data even when that
workbook is closed. If your data doesn't look like that, then SQL.REQUEST can't.
Second, SQL.REQUEST is implemented as an .XLA file, specifically,
<office dir>\Library\MSQuery\XLODBC.XLA
containing wrapper functions that call routines in a DLL, specifically,
<office dir>\Library\MSQuery\XLODBC32.DLL
Since SQL.REQUEST is either written in VBA or XLM (I don't recall which, and it
may have changed in some recent version), you're stuck with the same sort of
performance hit using it as you would be using any other udf. So no basis for
assuming the call interface required by SQL.REQUEST would be any faster than the
call interface required by any other udf. Once you're in the the udf, what it
actually does could affect performance. SQL.REQUEST calls functions in a DLL.
It's possible XLODBC32.DLL does most of the work of extracting data from Excel
workbooks, but its file size (at least in Excel 97 SR-2) is only 67,584 bytes,
which makes it appear that it too is little more than a collection of wrapper
functions calling functions in other DLLs. While one or two DLL calls may be
fast, possibly dozens of DLL calls may not be.
The main alternative udf appoach involves creating a separate instance of the
Excel application, and using it to call its ExecuteExcel4Macro method with an
external reference as its argument. The drag here, over and above the udf call
interface, is creating the second Excel application instance and executing the
ExecuteExcel4Macro method. Creating the second Excel application instance isn't
quick, though that could mitigated by using code to leave it open until the
calling workbook is closed. That done, only the initial call to such a udf and
closing the workbook containing the last formula calling such a udf would incur
a performance penalty. That leaves the ExecuteExcel4Macro method call. While I
haven't tested it against SQL.REQUEST, it seems to me that ExecuteExcel4Macro
would make direct use of Excel itself to pull data, whereas SQL.REQUEST would
need to use a circuitous chain of DLL function calls to achieve the same result.
While only performance testing would prove this, I have to believe the
ExecuteExcel4Macro method call would run circles around the various DLL calls
made by SQL.REQUEST.
FWIW, MOREFUNC.XLL's INDIRECT.EXT also uses a separate Excel application
instance to do its magic, so whatever drag incurred by udfs using such is shared
by INDIRECT.EXT.
If you want to test the performance of SQL.REQUEST to, say, my pull() udf, feel
free. As long as you test them on ranges both with *AND* without field names in
the top row. One method that works all the time, even if it's slow, will tend to
beat out multiple quicker but more specialized alternatives.
