Volatile Excel Functions

Volatile Functions.

A Volatile Function in a formula in a cell makes that cell be always recalculated at each recalculation even if it does not appear to have any changed precedents.
Avoid volatile functions wherever possible.
You can make a User-Defined Function volatile by including Application.Volatile in the function code.

FastExcel measures Workbook Volatility by comparing the time for a Recalculation to the time for a full calculation, and allows you to measure worksheet volatility by comparing the sheet recalculate time with the sheet full calculate time.

Excel’s Volatile Functions.

Some of Excel’s functions are obviously volatile: RAND(), NOW(), TODAY()

Others are less obviously volatile: OFFSET(), CELL(), INDIRECT(), INFO()

Some are volatile in some versions of Excel but not in others: INDEX()became non-volatile in Excel 97.

A number of functions that are documented by Microsoft as volatile do not actually seem to be volatile when tested:

INDEX(), ROWS(), COLUMNS(), AREAS()

and CELL("Filename") IS volatile although a MSKBN article says its not.

Using a volatile function in a formula will flag the cell containing the formula as volatile, even if the volatile function never gets executed:

=IF(1<2,99,NOW()) will always return 99 and the volatile NOW() function will never be called, but the cell containing the formula will be treated as volatile, (thanks to Stephen Bullen for pointing this out).

You can download volatileFuncs.zip for a test workbook that you can use to test if a function or formula is volatile.

Conditional Formats are volatile.

Because conditional formats need to be evaluated at each calculation any formulae used in a conditional format is effectively volatile. Actually conditional formats seem to be super-volatile: they are evaluated each time the cell that contains them is repainted on the screen, even in Manual calculation mode, although VBA functions used in conditional formats will not trigger breakpoints when executed by the repaint.

Volatile Actions: Actions that trigger calculation

Autofilter

Selecting any filtering criteria when using Autofilter will flag ALL the formulae in the autofilter range as uncalculated, even if none of their precedents have changed and even if you select exactly the same filter criteria as before. This can cause Autofilter calculation to be extremely slow.

Clicking Row or Column Divider

If calculation is set to Automatic clicking or doubleclicking a row or column divider will trigger a recalculation. But manually changing the height or width of a column or row will NOT trigger a recalculation.

In Manual mode these actions do not flag the workbook as requiring calculation.

Inserting or Deleteing Rows, Columns or Cells

Insert or Delete Rows or Columns or Cells anywhere on a Sheet, even to the right or below the Used Range.
AND –
   The Worksheet contains formulae which:

EITHER -
    Contain Names referring to this Worksheet or other Worksheets
OR –
    Refer to other Worksheets
OR –
    Are referred to by other Worksheets

Then these formulae become flagged as uncalculated.

Adding, Changing or Deleting Defined Names

Any action taken to add, delete, change or alter a defined name or its refersto property will trigger a recalculation.

Renaming Worksheets and Changing Worksheet Position

Changing the name of a worksheet or moving it will trigger a recalculation in Automatic mode.
In Manual calculation mode, the statusbar will show "Calculate" ONLY if any formulae on any other sheet refer to the sheet with the changed name/position.

Deleteing Worksheets, but not Adding Worksheets

In Automatic mode deleting a worksheet will trigger a recalculation, but adding a worksheet will not.

Hiding or Unhiding Rows in Excel 2003

In Excel 2003 hiding or unhiding rows will flag the selected rows as uncalculated, even if no rows were actually hidden or unhidden. If calculation is automatic this will trigger a recalculation. This behaviour is a change from previous versions. Hiding or unhiding columns does NOT flag the column as uncalculated.

Probably the reason for the change is that the SUBTOTAL function in Excel 2003 has an option to include or ignore hidden rows, so Excel needs to be able to trigger a dependency recalculation by dirtying the cells when a row is hidden or unhidden.

Opening a .CSV file

Whenever you open a .CSV file, either through VBA or File-->Open, a recalculation of all open workbooks will be triggered, even if calculation is in Manual. Switching off .EnableEvents does not stop this recalculation. The only way I have found to prevent this recalculation from happening is to use VBA to switch Worksheet.EnableCalculation to False for each worksheet that you do NOT wabt to be calculated.

See evaluation circumstances for the circumstances that will cause a formula to be evaluated.