Volatile User Defined Functions

Excel has the concept of volatile and non-volatile functions.

Microsoft define a volatile function as one that is recalculated every time calculation occurs in any cell in the worksheet. There's actually a bit more to it than that but for the sake of keeping it simple we'll go with that definition.

A non-volatile function is one that only recalculates when its inputs (arguments) change.

NOW() is a volatile function. Every time the sheet recalculates, NOW() is executed and gives you the current date and time. So when you change any cell on the sheet, NOW() is executed.

SUM() is not volatile. If we write

    =sum(A1:A5)

This will only be recalculated if its arguments, any cell in the range A1 to A5, changes.

Volatile User Defined Functions

By default, a UDF is non-volatile, but you can use the Application.Volatile method to make it volatile.

However, making a UDF volatile is to be avoided, and it's well known that UDF's execute more slowly than built in functions, so having lots of volatile UDF's in your workbook could slow down your Excel noticeably.

If you want to use the Application.Volatile method, ask yourself if that is really necessary. Let's look at an example.

If I write this function into C1

 
Function AddUp(MyCell As Range)

    AddUp = MyCell.Value + Range("A1").Value

End Function

and pass in B1 as the argument, the function will only execute when the argument, cell B1, changes. But this isn't what we want, we want the sum of A1 and B1.

Excel watches the arguments to functions and if they change, that function is marked as requiring a recalculation so its output can be refreshed on the sheet.

Rather than using the Application.Volatile method in this function, we should rewrite it so that all cells we are using in the calculation are passed in as arguments

Function AddUp(MyRange As Range)

    AddUp = WorksheetFunction.Sum(MyRange)

End Function

NOTE : I know this function is just replicating the SUM function, but this is just a demonstration of how you should pass all cells you want included in your calculation as arguments to your UDF.

When we change a cell not passed into our UDF, the AddUp function is not executed. You can double check this by adding a Debug.Print line to it and watching the Immediate Window in the VBA editor. You will only see the current date and time when the function is executed.

Function AddUp(MyRange As Range)

    AddUp = WorksheetFunction.Sum(MyRange)
    Debug.Print Now()

End Function

When a Non-Volatile Function is Volatile

Now we get to the 'interesting' part. There are some actions that make non-volatile UDF's, which means all UDF's, volatile.

Despite what Microsoft say, there are times when a UDF is executed even though its arguments are not changed. Even if you use Application.Volatile(False) to explicitly mark your UDF as non-volatile, it won't matter.

Like a number of 'features' in Excel, this problem has been around for years, and has been written about before. I noticed some great comments by Jeff Weir on Charles Williams blog.

In my testing I found that these actions made the UDF recalculate:

• Deleting a column, row or cell
• Deleting a sheet
• CTRL+ALT+SHIFT+F9 - Workbook tree rebuild and a forced recalculation
• CTRL+ALT+F9 - All open workbook tree rebuild and a forced recalculation

That may not be all the scenarios that cause a von-volatile function to execute, just the ones I've come across so far.

I don't know why Microsoft continue to claim that UDF's are non-volatile when this issue remains. If you've written some UDF's, and are calling them a lot, this can have very detrimental effects on the speed at which your Excel works.

The bottom line is, use UDF's sparingly.