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!