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.
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.
Application.Volatile
CurrDate = Now()
End Function
This Link strongly suggests Application.Volatile be at the top of the procedure
Charles Williams also has some good information here: Decision Models Calculation Secrets
Points out that INFO() is also volatile.
Includes other actions that trigger recalc.
Quick question:
Everyone on the web can tell me about how to CREATE volatile functions, but I’ve just run into a case I hadn’t known about before. I have a standard, non-volatile spreadsheet with a ton of calculation going on (lots of array statements, GETPIVOTDATA statements, logic, etc.). I was then on the phone consulting with someone and wanted to test the NOW function and its response values. To test it locally, I picked an empty cell in my calc-intensive sheet and typed in =TEXT(NOW(), “mm/dd/yyyy”). Which worked fine and dandy, and was volatile (as I expected). So fine, no big deal, I go ahead and delete the value in that cell. Now here’s the kicker: Excel acts as if I STILL have a volatile function in the workbook, recalculating every time I change even the littlest thing. Needless to say, this is driving me crazy. Any ideas on how I can get my workbook to be non-volatile again (without changing my calculation settings)? Oy vey,
Caleb
Caleb,
I suggest you do a dependency tree rebuild:
From XL2002, CTRL + SHIFT + ALT + F9
Rob
Rob, you’re a genius. That worked like a charm. Thanks so much.
Caleb
Following Excel User-Defined Functions with WinDbg: A Debugging Odyssey
Is it possible to apply
Application.Volatile(False)
to a volatile public function like INDEX and make it not volatile?
Alec,
Actually INDEX is not a volatile function, even though some MSoft documentation says it is.
Anyway no its not possible to apply Application.Volatile(false) to a built-in Excel function except by duplicating what the built-in function does inside a UDF.
I am trying to write a macro – if cell a = Yes, then write the name of the worksheet (“country name” on this case) this Yes is coming from. How can I write a macro for this?
This is what I did so far,
Function GetCountryNames(Cell As Range) As String
Application.Volatile
Dim Countrys As String
Dim ws As Worksheet
Countrys = “”
Row = Cell.Row
Dim OCell As String
OCell = “O” & Row
For Each ws In Worksheets
If (ws.Name Worksheets(1).Name) Then
If (ws.Range(OCell).Value = “Yes”) Then
Countrys = Countrys & ” ” & ws.Name
End If
End If
Next ws
GetCountryNames = Countrys
End Function
I don’t know what wrong, but it isn’t working. Any help. Thanks
I had a problem with manual calculation in a large worksheet back at my old job. I had set the calculation to manual in the normal way, nothing tricky, and no matter how many times I changed the calculation back to automatic, it wouldn’t stick. Sometimes even opening it after several other sheets would trigger a change to manual calculation. Any idea what was going on and how I could have fixed it?