Quick Jump
Introduction | Example | Tutorial | Applications | Comments

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

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)

Application Volatile VBA

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.

Application Volatile VBA

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 VolatileDemo(rng1 As Range)
VolatileDemo = Time()
End Function

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

Application Volatile VBA

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

Application Volatile VBA

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.

Application Volatile VBA

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 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.


Comments

Now you know everything you should need to know about Application.Volatile. I hope you found this VBA tutorial useful! Leave a comment below, subscribe to my email list, share this article on social media and follow me on Google+ and Twitter for more great VBA content.


wellsrPRO

Coming Fall 2017

Auto-Import Macros Directly from wellsr.com

Absolutely FREE when you join the waitlist


About Ryan Wells


Ryan Wells

Ryan Wells is a Nuclear Engineer and professional VBA Developer. He is the lead developer of several VBA applications, including PDF VBA - a leading Excel Add-in for exporting Excel Objects, like charts and tables, to PDFs. Discover more of his popular Excel Add-ins, including Mouse To Macro and CF Shapes, at his dedicated Excel Add-ins page.