|
The information in this article applies to:
- Microsoft Excel for Windows 95, versions 7.0, 7.0a
- Microsoft Excel for Windows, versions 5.0, 5.0c
SUMMARY
The following information discusses the relative speed and efficiency of
calling a function from a dynamic link library (DLL) file versus calling a
worksheet function in a Visual Basic, Applications Edition module. The
performance issues described below make the assumption that both the Visual
Basic function and the DLL function are written equally well.
MORE INFORMATION
Microsoft provides examples of Visual Basic for Applications procedures
for illustration only, without warranty either expressed or implied,
including, but not limited to the implied warranties of merchantability
and/or
fitness for a particular purpose. The Visual Basic procedures in this
article are
provided 'as is' and Microsoft does not guarantee that they can be used in
all situations. While Microsoft support engineers can help explain the
functionality of a particular macro, they will not modify these examples
to provide added functionality, nor will they help you construct macros to
meet your specific needs. If you have limited programming experience, you
may want to consult one of the Microsoft Solution Providers. Solution
Providers offer a wide range of fee-based services, including creating
custom macros. For more information about Microsoft Solution Providers,
call Microsoft Customer Information Service at (800) 426-9400.
The performance you receive calling a Visual Basic function versus
calling a DLL function in a module in Microsoft Excel depends on the
following factors:
- When you call a function in a DLL, there is an initial overhead involved
in loading the DLL (which only happens once but can be slow). Once the
DLL is loaded, however, the DLL call is quite fast. In terms of pure
call overhead, a DLL call is probably faster than calling an
Application.WorksheetFunction() because the DLL call doesn't go through
IDispatch. Note that the memory consumed by the DLL is in addition to
the memory already used by Microsoft Excel.
- If the function that you are calling from the DLL is performing
operations on a Range, then a built-in worksheet function will probably
perform faster than the external function. The reason for this is the
overhead that is involved in transferring the contents of the range
out of Microsoft Excel and into the external DLL. The built-in functions
in Microsoft Excel can access the cell table much more efficiently than
a DLL.
For example, the following Visual Basic command
Application.Sum(Range("A1:T100"))
is actually two calls; one to construct a range, and one to call the Sum
function. Internally, the Sum function adds up the cell contents
directly without allocating memory. By contrast, the following call to a
function in a DLL
MyDllBasedSum(Range("A1:T100"))
is one IDispatch call, and one DLL call (faster). However, within the
DLL call, there is at least one IDispatch call to fetch the contents of
the range as an array, an operation that allocates a lot of memory and
performs a lot of type conversion and copying (much slower), only to
have it all immediately freed once the sum is taken.
NOTE: IDispatch is an OLE Automation function that exposes methods and
properties through a dispatch (DISPID) mechanism as well as
"collections." IDispatch provides mechanisms to access and retrieve
information about an object's methods and properties.
- Calculations that aren't bound by Range contents may be faster in an
external DLL.
Note, however, that the algorithms used by the built-in functions in
Microsoft Excel are very efficient. Unless you can come up with a
dramatic speed difference, it probably isn't worth the extra time and
complexity to work with a DLL.
- A function written in Visual Basic will usually perform more slowly than
(or approximately even with) a DLL-based function or a worksheet
function, depending on what the function does.
- For functions that simply make a number of IDispatch calls to other
objects, there will probably be little difference between a DLL
function and a Visual Basic function because the overhead of
IDispatch and the Microsoft Excel-object is constant regardless of
who is making the call.
- Pure math and memory computations are probably faster in a DLL;
however, Visual Basic is pretty good at these, and you should weigh
the complexity and cost of a DLL before deciding on this.
- If there is a Visual Basic run-time function that performs the same
action as a Microsoft Excel worksheet formula or a Microsoft Excel
formula-expression, generally the run-time function calculates faster
than the Microsoft Excel formula. The exception to this rule is factor 2
above. For example, the following Visual Basic function
Application.Sum(5, 6)
calculates slower than a Visual Basic expression of 5 + 6. Note that
there is not a lot of overlap between these functions because the
Microsoft Excel worksheet functions that overlapped with Visual Basic
runtime functions were intentionally not made accessible from Visual
Basic.
|