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.

14 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

  4. excelfeast says:

    Hi Charles. Question re this:
    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.

    Do you know if this tree is saved along with the file? Or does Excel save no info on dependencies, and instead construct the tree from scratch on file open?

  5. fastexcel says:

    The point is that Excel has to do some iterations during calculation to find a valid calculation sequence. This can be time-consuming so at the next calculation it uses the calculation sequence found during the last calculation : this is called the calculation chain. So to optimise calc performance the calc chain is stored with the workbook as a starting point for the next calc.

    • Jeff Weir says:

      Yeah, I get the point of Excel reusing the calculation chain to optimise it’s calculation sequence (and of course updating in in response to changes). But what I don’t get is the point of saving that calc chain with the file given that – as you say above – Excel has to rebuild the dependency tree when it next opens that workbook anyhows, given some other program may have modified the xml while the file was closed.

      That’s what I mean by ‘seems to be an exercise in bloating the file’.

      • fastexcel says:

        The dependency tree is not the same thing as the calculation chain. If a calculation chain exists it makes sense to save it and reuse it as a starting point for the calculation even if dependencies have changed.

  6. Jeff Weir says:

    Hi Charles. I’ve noticed that UDFs recalculate whenever you delete cells. This can cause massive delays when deleting entire columns, because the UDF gets called for each and every cell it is used in. So if you’re using thousands of UDFS – say your great FLOOKUP UDF – then deleting a column or cell will call it once for every instance of i.

    By way of example, put the following UDF in a module, then call it from the worksheet a bunch of times with =HelloWorld()

    Function HelloWorld()
    HelloWorld = “HelloWorld”
    Debug.Print Now()
    End Function

    Then delete a row. If your experience is like mine, you’ll see it gets called once for every instance of use.

    Anyone have any ideas whether this behavior can be stopped? I’d also be interested why it should get called. Seems like a flaw in Excel’s dependency tree to me, but there may well be a good reason.

    I’ve posted this as a Stack Overflow question, but thought here might be a good place too.
    http://stackoverflow.com/questions/29815150/need-to-stop-udfs-recalculating-when-unrelated-cells-deleted

  7. Jeff Weir says:

    After experimentation, I’ve found more actions that trigger UDFS:

    Any change to the number of columns that a ListObject (i.e. Excel Table) spans through resizing (but not rows). Even if the UDFs themselves aren’t in the ListObject concerned, or in fact in any ListObject at all.
    Adding new cells or columns anywhere in the sheet (but not rows).

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