Writing efficient VBA UDFs (Part 7) – UDFs calculated multiple times

There are several circumstances where Excel will calculate a UDF multiple times when you would expect it to only be calculated once. This can be a significant problem if your UDF takes a long time to execute.
The previous posts on writing efficient VBA UDfs (Part1,Part2,Part3,Part4,Part5 Part6 ) have not discussed this problem so I will show how to get around it.

Multiple UDF recalcs caused by uncalculated cells

When Excel recalcs a workbook after changes have been made the calculation engine starts by calculating the most recently changed formulas, and then uses the most recent calculation sequence for the remaining formulas.
If the calculation engine finds a formula that depends on a cell that has been dirtied/changed (or is volatile) but has not yet been calculated, it reschedules the formula to the end of the calculation chain so that it can be recalculated again after the uncalculated cell.
The problem is that the calculation engine only does this rescheduling after the formula/UDF has been calculated, so a formula containing a UDF can be calculated many times in each recalculation.

Here is a very simple example:

1. Set Calculation to Manual so that its easier to see whats happening.
2. Enter this UDF into a standard Module in the VBE.

   Public Function Tracer(theCell As Range)
   Tracer = theCell.Value
   Debug.Print Application.Caller.Address & "-" & Tracer
   End Function
   

3. Show the Immediate Window (Ctrl G)
4. Enter 1 in Cell A1
5. Enter =Tracer(A1)+1 in cell A2
6. Enter =Tracer(A2)+1 in cell A3

The Immediate window shows
$A$2-1
$A$3-2
because the formulas were calculated as they were entered.
Now clear the immediate window, return to Excel and press F9 to recalculate. The immediate Window now shows
$A$3-
$A$2-1
$A$3-2
Which shows that cell A3 was calculated first (with the value of its parameter range A2 showing as empty), followed by A2, followed by A3 again, this time with the correct value for its parameter A2.
Now if you clear the Immediate window and calculate the formulas again without changing anything (use Ctrl/Alt/F9). This time A3 only gets recalculated once, because Excel is reusing the final calculation sequence from the previous recalculation.

Handling uncalculated cells

Fortunately its fairly easy for the UDF to detect when its being passed an uncalculated cell because the cell will be empty:

Public Function Tracer2(theCell As Range)
If IsEmpty(theCell) Then Exit Function
Tracer2 = theCell.Value
Debug.Print Application.Caller.Address & "-" & Tracer2
End Function

This version of the UDF checks if the cell is empty and exits immediately. If you need to distinguish between genuinely empty cells and uncalculated cells you can check that the cell contains a formula useing:

=IsEmpty(theCell.Value) and Len(theCell.formula)>0 Then Exit Function
or

=IsEmpty(theCell.Value) and theCell.HasFormula Then Exit Function

If the parameter is a range of cells containing formulae then you need something a bit more complex:

Public Function IsCalced(theParameter As Variant) As Boolean
'
' Charles Williams 9/Jan/2009
'
' Return False if the parameter refers to as-yet uncalculated cells
'
Dim vHasFormula As Variant
IsCalced = True
On Error GoTo Fail
If TypeOf theParameter Is Excel.Range Then
vHasFormula = theParameter.HasFormula
'
' HasFormula can be True, False or Null:
' Null if the range contains a mix of Formulas and data
'
If IsNull(vHasFormula) Then vHasFormula = True
If vHasFormula Then
'
' CountA returns 0 if any of the cells are not yet calculated
'
If Application.WorksheetFunction.CountA(theParameter) = 0 Then IsCalced = False
End If
ElseIf VarType(theParameter) = vbEmpty Then
'
' a calculated parameter is Empty if it references uncalculated cells
'
IsCalced = False
End If
Exit Function
Fail:
IsCalced = False
End Function

This function handles both range references and calculated ranges (array formula expressions etc) and checks if ALL the cells in the parameter contain formulas and ANY of the cells are uncalculated.

Only Variant and Range Parameters can be uncalculated.

Only a UDF parameter defined as a Range or a Variant can be uncalculated. If all your paramters are defined as, for instance, Double then Excel will attempt to coerce the parameter to Double before passing it to the UDF, and if the Paramter actually refers to an uncalculated cell then the UDF will not be called.

Multiple UDF Recalcs caused by the Function Wizard

Whenever you use the Function Wizard with a UDF the UDF gets called lots of times, because the Function Wizard uses Evaluate to dynamically show you the result of the function as you enter the parameters for the function. This is not good if your UDF is slow to execute!
You can detect that the UDF has been called by the function wizard by checking if the Standard commandbar is enabled (this works in Excel 2007 and Excel 2010 even though the commandbars are not visible).

If Not Application.CommandBars("Standard").Controls(1).Enabled Then Exit Function


Multiple UDF Recalcs with multi-cell Array Formula UDFs

Useing an array UDF that returns results to multiple cells can be a very good way of speeding up UDF execution (see Part 5 – Array UDFs go faster), but there is a nasty slowdown bug you should be aware of:
When a multi-cell UDF is entered or modified and depends on a volatile formula: the UDF is evaluated once for each cell it occupies. This does not happen when the UDF is recalculated, only when it is entered or changed.

UDFs in Conditional Formatting formulas.

Formulas in Conditional Formatting rules get evaluated each time the portion of the screen containing the conditional format gets redrawn or recalculated (you can demonstrate this by using a Debug.Print statement in a UDF being used in a conditional formatting rule). So on the whole using UDFs in Conditional formats is probably not a great idea.

Conclusion

If you have UDFs which take a long time to execute it makes sense to add code to check for both uncalculated cells and the UDF being called by the function wizard.