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.
