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

This entry was posted in Uncategorized. Bookmark the permalink.

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

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.

3. fastexcel says:

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

5. Nick says:

I am interested in the situation of detecting uncalculated cells (particularly in multi-cell ranges), and appreciate that Excel may well have changed somewhat since you wrote this blog. I note that you suggest using .HasFormula (with its Null value for mixed content). Previously, I had happily used .HasFormula for single cell arguments, but was concerned that, for large arrays, looping and checking each range member individually would be very slow.

Obviously your suggestion of using the .HasFormula multi-cell property is fast, as is COUNTA, and, if there are ANY cells with formulae, COUNTA should always return greater than 0. However, my impression is that most (if not all) of the built-in Excel worksheet functions (including COUNTA) actually (now?) return Empty if there are any uncalculated cells in the range, thus avoiding the need to test .HasFormula at all (and should work on all types of range arguments). This should be about as fast as the test can get in VBA!

Incidentally, it turns out that not only .Value (and .Value2) but also .Formula (and .FormulaLocal) can be read into variants as arrays in single relatively rapid operations, allowing a pretty fast FOR loop to check through the value and formula arrays for the uncalculated condition pair of Empty AND Formula (and aborting when the first is found). I used this method to reliably detect when uncalculated arguments were present and then tested a few built-in functions to see if they also returned Empty when provided with the same argument, eg,
If IsEmpty(WorksheetFunction.COUNTA(Arg) Then . . .
All those I tried (Count, Sum, Max, etc) seemed to work OK when used ‘inline’. However, I could not get them to work in a Function wrapper for some reason, eg,
Function NotCalc(Arg) As Boolean
NotCalc = IsEmpty(WorksheetFunction.COUNTA(Arg)
End Function
hoping to use
If NotCalc(Arg) Then Exit Function
but the value was never TRUE.

• fastexcel says:

@Nick – Interesting – I have not tried testing for Empty

6. Richard Berendsen says:

Dear FastExcel,

I use this function to calculate the number of cells in a range which have a green colorindex.
For some reason this function executes every time I check a range. This really takes up a lot of extra time when running VBA code. I would like to avoid this from happening, but I do not know how I can avoid this. Would you be able to help out or point me in the right direction?

This UDF was adjusted by myself and based on the below UDF:
https://docs.microsoft.com/en-us/previous-versions/office/troubleshoot/office-developer/count-cells-number-with-color-using-vba

Function CountCcolorGreen(range_data As range) As Long
Dim datax As range
Application.ScreenUpdating = False
For Each datax In range_data
If datax.Interior.ColorIndex = 14 Then
CountCcolorGreen = CountCcolorGreen + 1
End If
Next datax
Application.ScreenUpdating = True
End Function

Kind regards,
Richard

• fastexcel says:

This UDF should only recalc when something (but not the formatting of a cell) is changed in range_data.

Screenupdating does not do anything in a VBA UDF but would not cause it to recalc.
The problem with your UDF is actually the reverse: it will not recalculate when you change a color in the range so the color count will be wrong. Usually this is solved by adding Application.Volatile to make it recalc at every workbook calculation.