Writing efficient VBA UDFs Part 10 – Volatile Functions and Function Arguments

I just realised that none of my previous 9 posts on writing efficient VBA UDFs has discussed when and why you should make Functions Volatile. Since that’s fairly fundamental I really should have covered the topic early in the series … but anyway here goes.

What does Volatile mean?

Normally Excel’s smart recalculation engine only recalculates formulas that either have been changed/entered or depend on a cell or formula that has been changed somewhere higher up the chain of precedents for the formula.

This makes for very efficient calculation speed since in a typical workbook only a small faction of the formulas will be dependent on any particular cell or piece of data.

But some functions need to recalculate at every recalculation. For example NOW() should always give you the current time at the last calculation, and RAND() should give you a different random number each time it is calculated. These functions are called Volatile Functions, and any formula that uses one of them is a Volatile formula.

You can see more discussion of Excel’s built-in volatile functions and the volatile actions that trigger a recalculation at http://www.decisionmodels.com/calcsecretsi.htm.

How does Excel’s smart recalc engine know when to recalculate a function or a formula?

Excel maintains its dependency trees by looking at what other cells a function or a formula refers to, and the smart recalc engine uses these dependency trees to work out which formulas to recalculate.

For Functions Excel only looks at the arguments to the function to determine what the function depends on. So if you write a function like this:


Function Depends(theCell as range)
Depends=ActiveSheet.range("Z9")+theCell + _
theCell.Offset(0,1)
End Function

and call it in a formula =Depends(“A1”)
then Excel will only recalculate your function when A1 changes, and not when B1 or Z9 changes.

This could give you incorrect results.

Note: During a recalculation if Excel does evaluate the UDF it determines which cell references are actually being used inside the function to affect the function result, and if those cells have not yet been finally calculated it will reschedule the Function for later calculation. This is required to make the UDF be finally calculated in the correct dependency sequence.

How to fix this problem

There are several ways to fix this problem, but only one good one!

Make the function Volatile

If you add Application.Volatile to the function it will always recalculate:

Function Depends(theCell as range)
Application.Volatile
Depends=ActiveSheet.range("Z9")+theCell+ _
theCell.Offset(0,1)
End Function

But this will slow down the calculation, so generally its a bad idea unless, like RAND() or NOW() the function really needs to be Volatile.

Use Ctrl/Alt/F9 to trigger a full calculation

If you press Ctrl/Alt/F9 then Excel will recalculate every single formula in all the open workbooks, regardless of what has changed or is volatile.
Of course this can be very slow.

Make sure the Arguments to the UDF refers to ALL the cells the UDF uses.

Change the UDF to

Function Depends(theCell1 as range, theCell2 as range)
Depends = theCell1.Resize(1, 1)+ _
theCell1.Resize(1, 1).Offset(0, 1) + theCell2
End Function

This is the best solution.

Call it using =Depends(A1:B1,Z9) so that Excel knows that B1 is being referenced by theCell1.Offset(0,1).

Now Excel knows all the cells that the function depends on and it will be recalculated correctly and efficiently.

Detecting whether a Function or Formula is Volatile

You can download VolatileFuncs.zip from http://www.DecisionModels.com/Downloads/VolatileFuncs.zip

This contains tests for the volatile Excel built-in functions, using a function to increment a counter each time the referenced cell changes.
Public jCalcSeq As Long ''' calculation sequence counter
'
Public Function CalcSeqCountRef(theRange As Range) As Variant
'
' COPYRIGHT © DECISION MODELS LIMITED 2000. All rights reserved
'
' increment calculation sequence counter at Full Recalc or when theRange changes
' fixed for false dependency
'
jCalcSeq = jCalcSeq + 1
CalcSeqCountRef = jCalcSeq + (theRange = theRange) + 1
End Function

Summary

Make sure that the arguments to your UDF always directly refer to ALL the cells that the UDF uses.