Calling .DLL Function vs. Visual Basic Worksheet Function (126806)

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 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.
2. 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.
3. 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.
4. 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.
5. 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.