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.



Categories: User Defined FunctionTags: , ,

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: