Using Volatile Functions

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:

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
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
    CurrDate = Now()
End Function
Posted in Uncategorized

10 thoughts on “Using Volatile Functions

  1. 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,


  2. Is it possible to apply


    to a volatile public function like INDEX and make it not volatile?

  3. 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.

  4. 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
    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

  5. 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?

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.