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:

- Set Calculation to Manual so that its easier to see whats happening.
- 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

- Show the Immediate Window (Ctrl G)
- Enter 1 in Cell A1
- Enter =Tracer(A1)+1 in cell A2
- 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.

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?

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.

@Kadir,

Try changing to Manual Calculation mode

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

Filtering and sorting flags cells as dirty and so triggers a recalc. If I remember correctly this behaviour was introduced when the Subtotal function was extended to be able to subtotal only visible cells. I do not believe there is a sensible bypass other than switching to manual calc mode whilst doing these operations.

see http://www.decisionmodels.com/calcsecretsb.htm and http://www.decisionmodels.com/calcsecretsi.htm