What are Volatile Functions in Excel


Excel uses a three stage process to recalculate cells. It first draws a dependency tree, and then lists a calculation chain and then recalculates the cells. Check this link if you are interested in learning more about the recalculation process. Excel determines if a cell’s value depends on another, and decides the order in which they should be recalculated.

When asked to recalculate, Excel also decides to not recalculate a cell if the value of all of its “precedents” remain unchanged. Declaring a function as Volatile would force excel to recalculate the cell every time it recalculates. Consequently, any cell that is dependent on the volatile cell is also recalculated. Thus, using a large number of volatile functions increases the recalculation time of a spreadsheet.

Volatile functions are mostly functions that do not accept any arguments, for instance Today(), Now(), etc. Another flavour is the functions that use properties of any Visual Basic Object in their calculation: Indirect(), Offset(), etc. You can make a function volatile by adding ‘Application.Volatile’ to your code.

 

 

Advertisements

If you liked it, let me know. If you didn't make sure you let me know!

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