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
Example - Application.Volatile
Function VolatileDemo(rng1 As Range) Application.Volatile VolatileDemo = Time() End Function
Tutorial - Application.Volatile
Application.Volatile at the top of the function. After pasting this function in a module in your VBA Editor, go to cell
If formatted to time, the current time will show up in cell C2. Cell
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 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. 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.
Get these macros delivered right to your spreadsheet for free
Access tutorials and import my macros without ever leaving ExcelShow me - It's free
About 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.Follow