Volatile functions are a type of function that will always recalculate.
That means whenever Excel needs to calculate any part of the workshet, those cells containing volatile functions will also calculate.
There are a limited set of volatile functions – these are:
RAND, NOW, TODAY, OFFSET, CELL, INDIRECT
To test their behaviour, I’ve created a User Defined Function which returns the current time.
Function CurrDate() As Date
CurrDate = Now()
End Function
My worksheet contains formulas in column A and the formula description (escaped with a single quote) in column B
Notice which cells in column A get updated after I write something in cell B11
Only the cells containing volatile functions were recalculated.
I’ve seen very big worksheets where all of the cells refer to cell A1 – the formula being =NOW()
Users then wonder why there are noticable delays while performing data entry! This can be even more noticable when the dependent cells are XLL functions querying external data sources – requerying each time the user enters some more data.
In this case you could either manually enter the current time (or by a macro button) to save from volatile hell
-or-
ensure Excel has Calculation turned off – Tools > Options > Calculation > Manual
Calculation mode is saved with the workbook.
Be aware, Excel has a rule that it follows with regard to the workbook calculation mode:
If the workbook is the first workbook opened in that application, calculation mode is set to that saved with the workbook, otherwise it is ignored.
User Defined Functions can also be made Volatile by including the line: Application.Volatile
I read somewhere that it must be the first line of your User Defined Function. I don’t know if that’s true, but the first line is nice and visible so it’s a good idea.
Function CurrDate() As Date
Application.Volatile
CurrDate = Now()
End Function