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.

About these ads
This entry was posted in Calculation, UDF, VBA. Bookmark the permalink.

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

  1. Hi Charles
    Maybe it’s just through my browser (Google Chrome), but the whole line of code is not showing in the fits 2 functions. It ends with Offset(0 in each case.

    However, the final function does not appear correct to me
    Function Depends(theCell1 as range, theCell2 as range)
    Depends=theCell1+theCell2+the Cell.Offset(0,1)
    End Function

    First there is a space in between “the and Cell”, and theCell (without the space has not been defined.

    Correcting it to get rid of the space, and making it theCell1.Offset(0,1) still does not work.

    I don’;t understand why
    =Depends(A1:B2,Z9).
    has A1:B2 as theCell1 range?

  2. Harlan Grove says:

    Perhaps a bit off-topic, but it seems a shame the volatile functions NOW, TODAY and RAND at least don’t have optional parameters to make them nonvolatile. That is, if their argument were TRUE or missing, they’re volatile; but when their argument were False, they’d be nonvolatile. Excel would then need a new setting to force full recalculation before saving AND printing.

    • fastexcel says:

      I agree, except that the new calculation mode sounds complicated.
      How about just having static versions which are non-volatile, but with an argument to trigger a refresh? So you would get the value when entered but it would not change on a recalc unless you changed the triggering argument (or did a full calculation).

  3. Pingback: Handle Volatile Functions like they are dynamite | Chandoo.org - Learn Microsoft Excel Online

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