What are volatile Excel functions?
A volatile function is a worksheet function that can be used within an Excel formula just like many other functions that you may use on a regular basis. The key difference is that a volatile function will be recalculated whenever a calculation occurs on the worksheet regardless of whether the formula containing the volatile function needs to be recalculated. This behavior exists to account for the fact that the volatile function may return different results even if none of its predecessors (inputs) have changed. A couple of examples would be RAND() and NOW(). For example, NOW() returns the current date and time which is constantly changing. Since the function is identified as volatile, whenever any calculation occurs on the worksheet, any formula containing the NOW() function will be recalculated regardless of whether the proceeding calculation had any impact on the formula containing the NOW() function.Use of Volatile Functions
Use of volatile functions should be limited. Particularly, basing other calculations on the result of formulas containing volatile functions can lead to a high number of unnecessary recalculations.Affects of Volatile Functions
- Performance - Since volatile functions cause additional calculations to occur, the use (and particularly the over-use) of volatile functions can severely affect performance. For example, if the result of a formula containing a volatile function is used in other formulas which, in turn, feed other formulas, the effect can produce thousands of additional recalculations that are not necessary.
- Stability - There are known cases where an Excel crash can occur as a result of using volatile functions. For example, opening a CSV file will trigger the recalculation of volatile functions in a workbook that is already open. This scenario has been known to trigger a crash.
* Function is documented as volatile by Microsoft but may not be volatileWork Arounds
- Option 1 - Use static values in place of volatile functions. If the result of a volatile function is needed for another calculation, place the volatile function in its own cell and then use a macro to copy its value into a cell that will be referenced by the other formula.
- Option 2 - Using Visual Basic, create a non-volatile equivalent of the volatile function.
- Option 3 - Import a macro code module containing non-volatile equivalent functions.
- Click here to download basNonVolatile.zip
- Extract the basNonVolatile.bas file from this zip file.
- In your Excel workbook press Ctrl+F11 to open the Visual Basic editor.
- Click the File menu and click Import File
- Select the basNonVolatile.bas file and click Open.
- Press Ctrl+F11 to return to the Excel window.
- In place of Excel's volatile functions, use the non-volatile versions. The non-volatile versions have the same name but are prefixed with "NV". Example:
Note - The last argument of each function is an optional argument named DependentCell. Since the function is non-volatile this exists so that you can pass a reference to a cell that should trigger a recalculation of the formula.
Note - The basNonVolatile.bas function contains a non-volatile version of each of the functions listed above except for CELL and INFO.