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
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
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


=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
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.


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.

This entry was posted in Uncategorized. Bookmark the permalink.

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

  1. Kadir says:

    I have just had a bizarre observation and I am struggling to understand the reason behind it. Sort and filter, if used in a table of 400 rows by 20 columns, in which each row containes the same exact UDF getting its arguments on the same row, it just causes system hang-up as the CPU becomes extremely busy calculating function results even though no function argument changes. Can anyone tell me why that is and how to get around of it?

  2. Kadir says:

    Hi again guys,

    I am quite novice in this site. If you feel the topic I have just brought deserves a seperate thread please let me know how to do this. This efficiency issue is something I have been suffering from for quite a long time and I truly need to get rid of it with your help only.

  3. fastexcel says:

    Try changing to Manual Calculation mode

  4. Kadir says:

    Thank you @fastexcel for your prompt return.

    I have to rely on Automatic Calculation, so Manual mode is unfortunately not an option.Even ScreenUpdate nor suspending system events are not options. This issue, first needs to be understood why both sorting and filtering cause system hang-up in the first place, then the remedy will come soon, I am sure. I just need true Excel geniuses in this never-discussed issue. Shall I (or can I) open a seperate thread on the matter here or any suggeted forums? This forum should not be an ordinary one though. Here I have seen very very upper level discussions and issues and therefore I think I am in the right place actually. Waiting for suggestions. Many thanks guys….

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s