ID: Q126806
The information in this article applies to:
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.
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 professionals 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:
1. 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.
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.
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.
(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.
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.
Keywords : xlwin
Version : 5.0 5.0c
Platform : WINDOWSLast Reviewed: May 17, 1999