In VBA you can create a UDF that returns any VBA datatype, but there are really only 2 kinds of UDFs: Volatile and Non-Volatile. In the C XLL UDF world there are several other types that you can create.
Features in the XLL Plus Function Wizard
The features tab in the XLL Plus function wizard shows you many of the function types you can create just by clicking a checkbox or an option button:
A function can either be a worksheet function (UDF) or a Command Macro (think VBA Sub or XLM command macro, cannot be called as a UDF) or a Hidden Function (not shown in the Excel Function Wizard but still callable as a UDF).
Hidden functions are useful for testing purposes.
These checkboxes give you several interesting options:
One peculiarity of the Excel smart recalc engine is that it flags formulas whose precedents have been recalculated as needing recalculation even when the values of the arguments have not actually changed.
If you have a UDF that takes a long time to calculate this can be very inefficient.
Checking Cache Results stores both the values of the functions arguments and the results of the function in an application-level cache. Each time the function is called the values of the rguments are checked against the cache and if they have not changed then the previous results are returned.
This sounds wonderful, and it is, but there is a tradeoff: it takes time to store the values of the arguments and to check them. So if your function arguments contain large ranges of data (for example Lookups) then it may take longer to check the argument values than to calculate the result!
Create Asynchronous Version
This option creates an additional add-in function which runs the core function asynchronously, in a worker thread. The function will be have the same name as the core function, plus the suffix “Async”.
Create Queued Version
This option is only available in Excel 2010 and later. When you use the option this Excel calculation thread does not get blocked but can continue whilst the queued function(s) are executing. When the queued functions complete they call back and Excel 2010 has function to handle this callback within the calculation process.
Do not call in Formula Wizard
When you are entering a function and its arguments using the Excel Function Wizard the function gets executed many times. If your function takes a long time to execute this can be annoying, so this option stops the function being executed (it just returns #Value to the Function Wizard). In VBA your function can do a similar thing by using
If (Not Application.CommandBars("Standard").Controls(1).Enabled) Then Exit Function
This registers the function as a thread-safe function. In Excel 2007 and later on a multi-core PC this can speed up execution significantly. VBA functions cannot be made thread-safe.
There are certain rules you have to follow when writing thread-safe functions. The most obvious ones are:
- You can’t call other functions that are not thread-safe.
- You can’t store data that is static or is accessible from outside the function itself (unless you use a thread lock)
Debugging thread-safe functions can be disconcerting since you get jumped from one thread to another: its best to start by debugging on a single thread (switch Excel’s multi-threaded calculation feature off).
This registers the function as a volatile function, in a somewhat similar way to using Application.Volatile in a VBA UDF.
Other Function Types
In the Details tab of the XLL Function wizard you can also use some more function types:
Can Defer Recalculation
This option flags the UDF as a Macro Command Equivalent function. This means that function has slightly greater permissions than a standard UDF. In this example the PREVIOUS function returns the value that the calling cell had at the previous recalculation.
This enhanced capability comes at a price: the function cannot be Multi-threaded and will be volatile if using Reference type arguments.
Using this option you can offload calculation of your functions to a High Performance Computing (HPC) clusters.
XLL Functions can be flagged as a number of different types, mainly to handle special situations such as the need for queued functions or to support HPC clusters.
By far the most useful type is Multi-Threaded, which can produce calculation speed improvements that often scale almost linearly with the number of cores in the PC.