Calling XLAM/XLL/Automation UDFs from VBA: Evaluate, Run, or Reference?

I have this nagging feeling that I do not know the best way to call UDFs from VBA.

So after a couple of days of research here is what I have found out so far.

The Available Methods

Application.ExecuteExcel4Macro

This method requires converting your UDF call and its parameters to strings. I have not explored it further.

Dim FuncString as String

FuncString="XLLFunction(" & FirstParam
FuncString=FuncString & "," & SecondParam

FuncString=FuncString & ")"

Result=Application.ExecuteExcel4Macro(FuncString)

Declare

You can use Declare to make functions in an external DLL available to VBA. Declare works well with XLL UDFs that only require simple parameters but I don’t know how to use Declare if you want to pass something like a variant array as a parameter to an XLL.

Declare Function DoNothingX_12 Lib "MarshalTest.xll" () As Long

dNothing = DoNothingX_12()

References

You can add references (VBE->Tools->References) to any COM based UDF (XLAM, Automation …) to your VBA project and then CALL the UDFs from VBA as though they were included in your project. This is very efficient, but does not work with XLL UDFs.

Here I have added references to 2 files – TestProj.xlam and AutoProj.dll (a VB6 automation addin).

Evaluate

You can use Application.Evaluate or Worksheet.Evaluate to get the result of an Excel formula string. Like ExecuteExcel4Macro you have to convert your UDF call and its parameters to strings. This makes it difficult to pass something like a variant array but easy to pass the address of a range using Excel formula syntax.
Evaluate has some quirks that you need to be aware of: see this post for details.

Sub TimeEval2()
Dim dTime As Double
Dim j As Long
Dim dNothing As Double
Dim str As String
str = "=DoSomething(A1:F10301)"
dTime = microtimer()
For j = 1 To 100000
dNothing = ActiveSheet.Evaluate(str)
Next j
dTime = microtimer - dTime
End Sub

Application.Run

This method is the easiest one to use and works with both COM-based and XLL-based UDFs. It will convert your function parameters to an appropriate type as required.
The only major drawback to Application.Run is that the parameters are handled By Value as opposed to By Reference, which means that each parameter is copied before being passed to the function. This is fine for scalar values and objects such as a Range object but is slow for arrays (objects get passed as pointers so your UDF has to know how to handle whatever data structure or object the pointer points to).

Sub TimeRunVarrX2()
Dim dTime As Double
Dim j As Long
Dim dNothing As Double
Dim str As String
Dim rng As Range
Dim varr As Variant
Dim jFunc As Long

str = "DoSomethingVarrX"
Set rng = Range("A1:F10301")
' get the register ID of the XLL function
jFunc = Evaluate(str)

dTime = microtimer()
dTime = microtimer()
For j = 1 To 100
' pass range object to be converted
dNothing = Application.Run(jFunc, rng)
Next j
dTime = microtimer - dTime

End Sub

You can pass Application.Run either the name of the function/sub as a string or as a Register ID if its an XLL function. And you can use Evaluate to convert the name of the XLL function to its Register ID, by calling Evaluate(“FunctionName”) rather than Evaluate(“FunctionName()”).

If you are repeatedly calling the function using Application.Run its much faster to use the Register ID rather than the function name.

Performance

I ran a series of performance tests comparing Application.Run, Call (using References or Declare) and Evaluate for VBA XLAM UDFs, C++ XLL UDFs and VB6 Automation addin UDFs.

Each function was called 100000 times.

The DoNothing UDF

This UDF takes no parameters and returns a single digit. I used this to measure the calling overhead. The VBA version looks like this:

Public Function DoNothing() As Double
DoNothing = 1#
End Function

Here are the timings:
Application.Run shows 2 timings for both the XLL and the VB6 Automation UDFs

• The first XLL time is using the Name of the function, the second (nearly 50 times faster) is using the Register ID
• The first Automation time is using the simple name of the function, the second is using the fully qualified name ( Application.Run(“AutoProj.TestFuncs.DoNothingA”) )

Considering that these timings are for 100K calls all the times are fast, but Evaluate is definitely the slowest.

The DoSomething UDF

This UDF takes a single parameter of a range object and just returns the number of rows in the range. The objective is to findout how the various methods handle an object.

Public Function DoSomething(theRange As Range) As Long
DoSomething = theRange.Rows.Count
End Function

The timings for passing a Range object (which is just a pointer) or a reference by value are very similar to the Do Nothing case.

The DoSomethingVArr UDF

This UDF takes a single parameter as a variant and can handle either a Range object or a variant array. If passed a Range object it coerces it to values.
The UDF returns the number of rows in the range or array.

The objective is to compare the time taken to pass a copy of a variant array with the time taken to pass a Range Object and then coerce it.

I ran the tests using a range which is 10301 rows by 6 columns conatining a mixture of text and numbers.

Public Function DoSomethingVArr(theArray As Variant) As Long
Dim theArray2 As Variant
If IsObject(theArray) Then
theArray2 = theArray.Value2
DoSomethingVArr = UBound(theArray2)
Else
DoSomethingVArr = UBound(theArray)
End If
End Function

Note that because Application.Run passes parameters by value the UDF has to coerce the range object to a local Variant array:  the code below does not work because you cannot change theArray: theArray does not get coerced to its values.

theArray = theArray.Value2

There are 3 sets of timings for RUN and CALL:

• Variant Array – this shows the time to pass the large variant array as a parameter.
• Range – coerce in function – this shows the time tp pass the range as an aobject or reference and then coerce it to its values inside the function.
• Get Variant Array and Pass – this shows the time taken by VBA to convert the range to values and then to pass the resulting array to the function.

The results seem to show that it takes about 9.5 milliseconds (or 950 seconds for 100K calls) to coerce the range to its values or to pass the resulting array of values to the function.

But using Application.Run to pass the variant array to an XLL function takes 3 times as long as to pass it to a VBA or VB6 function (2700 versus 957). Presumably this is because the variant array needs to be converted to an xlOper.

If you just pass the range object/reference using Application.Run and then coerce inside the function the times are nearly the same (983 versus 1023).

Conclusions

•  By far the most efficient method of calling UDFs is by using a Reference or Declare.
• The least efficient method is generally EVALUATE.
• Application.Run automatically handles converting UDF parameters between VBA and XLL, but this conversion can be relatively expensive for large arrays.
• Application.Run passes parameters to the UDF by Value. This means that the UDF is not allowed to modify the parameters even locally within the scope of the UDF.
• Objects are passed by Application.Run as read-only pointers by Value.
• All these methods have acceptable performance unless they are used a large number of times.

OK – so what have I missed?