Application Volatile VBA to Recalculate Function

Introduction - Application.Volatile

Placing Application.Volatile at the top of your custom VBA function makes your function recalculate each time a calculation occurs in another cell on your worksheet. By default, this does not happen. By default, user-defined functions (UDFs) are only recalculated when one of the arguments of the UDF is changed.

Let’s walk through an example of Application Volatility. We’ll make a function that returns the current time and we’ll see how it behaves differently when we change the Application.Volatile method.

Example - Application.Volatile

Function VolatileDemo(rng1 As Range)
Application.Volatile
VolatileDemo = Time()
End Function

Make powerful macros with our free VBA Developer Kit

This is actually pretty neat. If you have trouble understanding or remembering it, our free VBA Developer Kit can help. It’s loaded with VBA shortcuts to help you make your own macros like this one - we’ll send a copy, along with our Big Book of Excel VBA Macros, to your email address below.

Tutorial - Application.Volatile

Notice the Application.Volatile at the top of the function. After pasting this function in a module in your VBA Editor, go to cell C2 and type =VolatileDemo(A1)

If formatted to time, the current time will show up in cell C2. Cell A1 is never used in our function, but it will help us understand what VBA volatility means.

Once you’ve done that, type something into cell A2. It doesn’t matter what you type.

What happened? A new time appeared in cell C2, even though we entered a value into a cell that had nothing to do with our custom function. That’s what happens when Application.Volatile is turned on (set to True). Whatever cell you change on your sheet will trigger your calculation to run again.

Let’s run an experiment. Delete the Application.Volatile line from your VBA function and run it one time. You’ll have to run your function once so the VBA compiler knows it’s no longer volatile.

Your macro will look like this:

Function VolatileDemo2(rng1 As Range)
VolatileDemo2 = Time()
End Function

and your spreadsheet should look something like this once you run your function again:

Now, I want you to change the value in cell A2, just like we did earlier.

The time in cell C2 did not change! Like I said, when Application.Volatile is set to false, the default, your function only recalculates when one of the arguments change.

To drive this point home, change the value in cell A1.

Recall, cell A1 is the cell that is fed into our custom VBA function. In other words, it’s an argument of our function. That’s why the time reported in cell C2 was updated once we changed the value in cell C2.

Application Ideas - Application.Volatile

Most UDFs have no reason to be volatile. Volatile functions take more calculation time and can produce answers that you didn’t really want. Instead, you should design your UDFs to accept the arguments that are crucial to the calculation and leave application volatility set to false.

However, if you still want to use Application.Volatile in your VBA functions, you know how it behaves and what you can expect.

Now you know everything you should need to know about Application.Volatile. When you’re ready to take your VBA to the next level, subscribe using the form below.