Excel User-Defined Functions
|Smart Recalculation||Evaluation Circumstances||Calculation Process||Dependency Trees|
|Controlling Calculation||"CALCULATE" in Status Bar||Calculation Methods||Calculating from VBA|
|Volatile Functions & Actions||User-Defined Functions||Repetitive Calculation||Version Timing Comparison|
|Action Ignored||Not Calculating||Error Handling, #value, calculated twice|
|Uncalculated cells, False dependencies||Performance||Function Wizard|
User Defined Functions can provide great power and convenience and appear very simple to write. But there are some problem areas that may need special attention in your UDF coding:
Excel will not allow a UDF written in VBA to alter anything except
the value of the cell in which it is entered.
Dependency Sequence Problems
Excel depends on analysis of the input arguments of a Function to
determine when a Function needs to be evaluated by a recalculation.
What this means is that the function will only be flagged as needing
evaluation when one or more of the input arguments change (unless
the function is volatile).
Function mySum1(SheetName as string) as Variant
This function will not automatically recalculate if Data!A2 changes: it will only automatically recalculate when Sheetname changes.
Note that putting all the references in the argument list does not
control the initial sequence which Excel uses to calculate the UDF,
and it may well be calculated more than once per recalculation cycle.
If you put a false dependency in the argument list
(a reference which is not actually used inside the function) Excel
will execute the function when the dependency changes, but not neccessarily
in dependency sequence.
User-Defined Volatile Functions
Modifying UDFs and Sheet.calculate
Whenever you modify the code in a UDF Excel flags all instances of the UDF in all open workbooks as being uncalculated. If you then do a sheet.calculate or shift/F9 Excel will not only calculate the currently selected worksheet(s), but will also evaluate all the instances of the UDF that are not on the currently selected worksheets.
UDFs may be evaluated more than once per workbook calculation
You should make sure that you have an On Error handler in your UDF to handle both real errors and errors that are caused by out of sequence execution. Un-trapped errors can halt the calculation process before it is complete.
Function mySum4(theFirstCell as range,theSecondCell
as range) as Variant
Using UDFs in Conditional Formats
UDFs referenced in conditional formats get executed each time the screen is refreshed (even if Application.screenupdating=false), and breakpoints in these UDFs will be ignored. Make sure you have an On Error handler in all UDFs referenced in conditional formats, since unhandled errors during a UDF execution caused by a screen refresh may cause VBA to silently stop running without any error message being issued.
Because UDFs referenced by conditional formatting will be executed more frequently than you would expect it is not a good idea to reference slow-running UDFs from conditional formats.
Unhandled UDF Errors, and debugging your UDF's
If you don't have an On Error handler in your UDF you may need to be aware of the differences in the way that different Excel versions react.
If you start the calculation process using Shift-F9 or F9 this problem does not occur: all cells are calculated and debug mode is not entered.
For the Microsoft view on the Excel 97 error handling problem see MSKB 244466
Using the Visual Basic ISEMPTY function on a UDF Range argument will return TRUE if either the input cell is not yet calculated or it contains nothing.
To distinguish between uncalculated cells and cells without any data check that the length of the formula is greater than 0 (suggested by David Cuin) or use Cell.HasFormula:
=ISEMPTY(Cell.Value) AND Len(Cell.formula)>0 or =ISEMPTY(Cell.Value) and Cell.HasFormula
These expressions will only return true if the cell is both uncalculated and contains a formula.
To check whether any of the cells in a range or function input parameter are uncalculated use the following function:
Public Function IsCalced(theParameter As Variant)
IsCalced = True
I recommend that ALL UDFs should include both an On Error handler and where possible check for empty cells. UDFs written using the C API can check for xlCoerce returning xlretUncalced.
In this example the Function will not return a recalculated answer until both the input arguments have been recalculated and are not empty.
Function mySum5(theFirstCell as Variant,theSecondCell
as variant) as Variant
If you have a function that you want to calculate in dependency sequence
for an argument that does not affect the result of the function (false
dependency), you also need to check the input arguments with ISEMPTY.
Function mySumTimes2(theFirstCell as range,theSecondCell
as range) as Variant
If you are using Excel 97 make sure you have installed the SR1 and SR2 updates.
There are several problems with UDFs in Excel97 which are fixed by
these two service releases, which are available from:
UDF error with multi-area range argument.
Howar Kaikow http://www.standards.com has discovered a bug in Excel's processing of UDFs using non-contiguous multi-area ranges as input arguments. See AreasBugBypass2.zip for a download containing two workbooks that illustrates the problem and a way of bypassing it..
The problem occurs when:
In these circumstances Excel/VBA incorrectly treats the multi-area range as referring to the active sheet. This means that the UDF may give incorrect answers without warning.
I recommend that you do not attempt to program a UDF to handle multi-area ranges as arguments: use multiple (optional if there are a varying number) range arguments instead.
If required you can bypass the problem as demonstrated in in the download, or by ensuring that there are no instances of the UDF where the multi-area range refers to the sheet with the UDF formula.
The bug exists in Excel 97, Excel 2000, Excel 2002 and partially in Excel 2003. It has been fixed in Excel 2007.
Referencing cell formatting properties
If your UDF references cell properties other than .value or .formula (ie .Bold) there are some occasions when these properties may be undefined when your UDF is evaluated. One such occasion is renaming a worksheet in automatic mode. If this happens you may need to explicitly recalculate your function.
For optimum performance UDFs should be coded in C and use the C API.
Make sure you use an IsEmpty routine at the top of your UDF to check for uncalculated cells so that you can avoid unneccessary calculations of your UDF. IsEmpty returns True if the variable being checked has not been initialised or has been set to empty. When used on UDF input range arguments IsEmpty returns True if the range either contains nothing or has not yet been calculated. Combining an ISEMPTY test with a test that the formula length is >0 will detect only uncalculated cells but not cells that are empty because they contain nothing.
Also since executing UDF's is usually slower than other Excel calculations, try to put the reference to your UDF in a place in your formulae where it will be calculated as late as possible (towards the end of the formulae and inside the brackets).
Consider if it makes sense only to abandon calculation not only if ALL the input arguments are uncalculated/empty but also if ANY of them are uncalculated/empty.
Automatic and Function key Calculation slower than VBA calculation
UDFs calculate significantly slower when the calculation is started
automatically by Excel or by pressing F9 than when the calculation
is started by a vba calculation statement like Application.calculate.
These timings are for 16000 very simple UDFs, using Excel 2002 on an AMD 1200MHZ with Windows XP:
So if you are using a lot of UDFs it really pays to be in manual calculation mode and have a calculate button that uses VBA to initiate an Excel calculation (Application.Calculate or Application.Calculatefull).
Automation Addin UDFs
UDFs in Automation addins created using VB6 do not use the VBE, so do not suffer from the VBE overhead as above, but otherwise give very similar performance to VBA. In theory compiled VB6 with array bounds checking disabled should be faster for intensive arithmetic calculations on arrays, but I have not yet managed to detect any speed improvement.
UDF's can be written as multicell array formulae that can be entered
using Ctrl-Shift-Enter. The results of the array calculation are returned
to the cells by assigning an array to the function.
Transferring information from Excel Ranges to the UDF.
You should also try to minimise the performance overhead of transferring
information from Excel to VBA and back to Excel:
It is faster (15-20%) to use the Range.Value2 property rather than the (default) Range.Value property. The Range.Value property attempts to convert cells formatted as Dates to a variant containg a VBA date type, and cells formatted as currency to a variant containing a VBA Currency type. Range.value2 attempts to convert date and Currency formatted cells into Variants containing Doubles.
Function mySum6(theFirstRange As Range, theSecondRange As Range) As Variant
Dim dblMySum As Double
On Error GoTo FuncFail:
If not IsCalced(theFirstRange) or not IsCalced(theSecondRange)
then exit Function
Using Excel functions inside your UDF.
If you are going to process the input ranges using Excel functions called from VBA, then keep the input ranges as Range object variables, so that the function does not have to transfer all the cell information to VBA : you are then just manipulating the object variable pointers.
Note that functions which reference ranges such as VLOOKUP
and INDEX can return Empty without raising an error if the
range being referenced contains uncalculated cells.
Note that using Application.WorksheetFunction.Match is generally about 20% faster than using Application.Match (similar results expected for other functions). The other difference is that Application.WorksheetFunction raises an error if, for instance, no match is found, but Application.Match returns an error value without raising an error.
Note that because there is a bug in the way Excel handles ranges containing multiple areas I do not recommend that UDF's are programmed to handle multiple areas.
Function mySum7(theFirstRange As Range, theSecondRange
As Range) As Variant
UDFs with a variable number of input arguments.
This example shows how to handle a variable number of arguments.
Function mySum8(ParamArray varArgs() As Variant) As Variant
Dim varArg As Variant
On Error GoTo FuncFail:
Your end-users can use the Function Wizard to enter your UDFs (usually from the User Defined Category) into a worksheet.
If your UDF takes a long time to execute you will soon discover that
the Function wizard executes your UDF several times.
Axel König has suggested adding the following code:
If (Not Application.CommandBars("Standard").Controls(1).Enabled) Then Exit Function
This code depends on the fact that when using the function wizard most icons in the toolbars are disabled. I have tested Axel's solution in Excel 97, Excel 2000, Excel 2002 and Excel 2003, and it works well.
A solution is also possible by using the Windows API to check if the Function Wizard window is showing and has the same process ID as the current Excel process.