Finding out if a function is Volatile or Multithreaded using VBA: UDFs for UDFs

Part of my new Profiling Formulas and Functions command requires the code to determine whether a Function is  a native built-in Excel function, or an XLL function, or some other kind (VBA, Automation). And I also want to know if its multi-threaded or volatile because that can have a significant effect on calculation performance.

So here is how to do it!

Built-in Native Functions

You can get a list of the functions that are built-in to Excel from the Excel XLL SDK. I added 2 columns to the list showing whether the functions are Multi-Threaded or Volatile:

Then I can use VLOOKUP to find out if the function is a built-in Excel function, and if so whether it is Volatile or Multi-threaded.

XLL Functions

XLL functions have to be registered using the REGISTER C-API command. When registering the function you have to include a typestring that declares what type each of the function arguments is, and whether the function is multi-threaded or volatile.

And it turns out there is a VBA method to get the typestrings for all registered XLL Functions.
Application.RegisteredFunctions returns a 3-column array containing the name of the XLL file, the Function name and the function typestring.

But of course it is not quite so easy as that. The Function name it returns is the internal function name in the XLL code, which is usually not the same as the name of the UDF function as used by Excel!
So for example a UDF name like REVERSE.TEXT could have an XLL internal name of f1.

Using Register.ID to match up the Names

The way to find out which XLL function name (if any) corresponds to the Excel UDF Name is to find the internal number of the function (its Register ID).

For an XLL UDF this is the number returned if you enter the Excel UDF name in a formula without any () after it (for example =REVERSE.TEXT ). And you can get this in VBA using EVALUATE:

vExcelFuncID = Evaluate(strFunc)

Getting the Register Id number of the internal XLL function name requires calling an XLM Macro command called REGISTER.ID using VBA Application.ExecuteExcel4Macro.

Then if both Register.IDs match you have an XLL function and you can look in the typestring to see if its multi-threaded (the typestring contains $) or volatile (typestring contains #).

Other UDF Types (VBA, Automation …)

If the UDF is not built-in and not a registered XLL function it must be either a VBA or an Automation UDF (or an XLM UDF!). None of these can be multi-threaded.

But they can be volatile and I have not found a straight-forward way of determining this programmatically. The simplest way to find out is to put the UDF in a formula and see if the formula recalculates with every F9: but you can’t do that from a VBA UDF!

The VBA Code

You can download the code as an XLAM from here.

There are 2 Subroutines to get the Native function lists and registered XLL functions into module level arrays:

Option Explicit
Option Base 1
Dim vFuncRegister As Variant
Dim vNative As Variant

Private Sub GetNative()
'
' get the list of native built-in functions and their attributes
'
If IsEmpty(vNative) Then
vNative = ThisWorkbook.Worksheets("NativeFuncs").Range("A2").Resize(ThisWorkbook.Worksheets("NativeFuncs").Range("A1000").End(xlUp).Row - 1, 3)
End If
End Sub
Private Sub GetFuncRegister()
'
' get list of registered functions and their funcIDs
'
Dim sCmd As String
Dim vRes As Variant
Dim j As Long
'
If Not IsEmpty(vFuncRegister) Then Exit Sub
'
vFuncRegister = Application.RegisteredFunctions     ''' get data on XLL registered functions
'
' add column for funcid
'
ReDim Preserve vFuncRegister(LBound(vFuncRegister) To UBound(vFuncRegister), 1 To 4) As Variant
'
For j = LBound(vFuncRegister) To UBound(vFuncRegister)
'
' get funcids
'
If vFuncRegister(j, 1) Like "*xll" Then
sCmd = "REGISTER.ID(""" & CStr(vFuncRegister(j, 1)) & """,""" & CStr(vFuncRegister(j, 2)) & """)"
vRes = Application.ExecuteExcel4Macro(sCmd)
If Not IsError(vRes) Then vFuncRegister(j, 4) = vRes
End If
Next j
End Sub

The main function checks the function name against the native functions array, then if not found, checks it against the registered XLL functions array and if not found assumes it must be a VBA or Automation (or XLM) UDF.

Private Function CheckFunc(strFunc As String, blMulti As Boolean, blVolatile As Variant) As String
'
' returns
' type = B if built-in, X if XLL else O for Other (VBA or Automation)
' blMulti is true if multithreaded
' BlVolatile is True if Volatile, False if not volatile and ? if don't know
'
Dim strType As String
Dim vFound As Variant
Dim j As Long
Dim strTypeString As String
Dim vExcelFuncID As Variant
'
blMulti = True
blVolatile = False
'
' check for native xl function
'
On Error Resume Next
vFound = Application.VLookup(strFunc, vNative, 1, False)
On Error GoTo 0
If Not IsError(vFound) Then
strType = "B"
If Application.VLookup(strFunc, vNative, 3, False) = "V" Then blVolatile = True
If Application.VLookup(strFunc, vNative, 2, False) = "S" Or Val(Application.Version) < 12 Then blMulti = False
End If
'
If Len(strType) = 0 Then
'
' get xlfuncid - if not error then its an XLL func
'
vExcelFuncID = Evaluate(strFunc)
If Not IsError(vExcelFuncID) Then
strType = "X"
For j = LBound(vFuncRegister) To UBound(vFuncRegister)
If strFunc = vFuncRegister(j, 2) Or vExcelFuncID = vFuncRegister(j, 4) Then
strTypeString = vFuncRegister(j, 3)
If InStr(strTypeString, "!") > 0 Or _
(InStr(strTypeString, "#") > 0 And (InStr(strTypeString, "R") > 0 Or InStr(strTypeString, "U") > 0)) _
Then blVolatile = True
If InStr(strTypeString, "$") = 0 Or Val(Application.Version) < 12 Then blMulti = False
Exit For
End If
Next j
End If
End If
'
If Len(strType) = 0 Then
'
' else its Other (VBA or Automation)
'
strType = "O"
blMulti = False     ''' cant be multi
blVolatile = "?"    ''' don't know if volatile
End If
'
CheckFunc = strType
End Function

Then there are 3 UDFs to find out if the UDF is Volatile, is Multi-threaded and what type of UDF it is.

Public Function IsMultiThreaded(strFuncName As String) As Variant
'
' check if a function is Multi-Threaded
' Returns true or false
'
Dim blMulti As Boolean
Dim blVolatile As Variant
Dim strType As String
'
GetNative
GetFuncRegister
strType = CheckFunc(strFuncName, blMulti, blVolatile)
'
IsMultiThreaded = blMulti
End Function
Public Function IsVolatile(strFuncName As String) As Variant
'
' check if a function is volatile
' returns True or False or ? if don't know
'
Dim blMulti As Boolean
Dim blVolatile As Variant
Dim strType As String
'
GetNative
GetFuncRegister
strType = CheckFunc(strFuncName, blMulti, blVolatile)
'
IsVolatile = blVolatile
End Function
Public Function FuncType(strFuncName As String) As Variant
'
' get type of function: B for built-in Excel, X for XLL, O for Other

Dim blMulti As Boolean
Dim blVolatile As Variant
Dim strType As String
'
GetNative
GetFuncRegister
FuncType = CheckFunc(strFuncName, blMulti, blVolatile)
End Function

Note: Yes – the production version of this code is more optimised for speed, but this version is easier to understand!

Summary

When you are trying to optimise Excel calculation speed its important to know which functions are multi-threaded or volatile.

This post demonstrates a way of doing this programmatically.

Limitations:

There are some limitations of this method:

• Cannot determine if VBA and Automation UDFs are volatile.
• Will not detect XLL functions that internally change volatility programmatically.

Any ideas on how to determine the volatility of VBA UDFs will be gratefully received!

Advertisements