How VBA declared Volatility works

Question

I have a situation where I would like a function to be "partially" volatile - that is, volatile until it no longer returns an error, at which point it would no longer be calculated unless one of its arguments changed (i.e. standard non-dirty volatility).

Thus far I've tried three approaches, none of which work. Can anyone suggest anything else?

Approach 1: Application.Volatile in an IF condition

Public Function VolTest() As Long
    Static lngCounter as Long
    If lngCounter < 5 Then
        Application.Volatile
    End If
    lngCounter = lngCounter + 1
    VolTest = lngCounter
End Function

Result: Counter keeps counting beyond 5 (I believe it should stop at 5)

Approach 2: Application.Volatile in separate called function that isn't always called

Public Function VolTest() As Long
    Static lngCounter as Long
    If lngCounter < 5 Then
        lngCounter = VolTest_VolatileIncrememnt(lngCounter)
    Else
        lngCounter = VolTest_NonVolatileIncrememnt(lngCounter)
    End If
    VolTest = lngCounter
End Function

Public Function VolTest_NonVolatileIncrememnt(lngCounter As Long) As Long
    VolTest_NonVolatileIncrememnt = lngCounter + 1
End Function

Public Function VolTest_VolatileIncrememnt(lngCounter As Long) As Long
    Application.Volatile
    VolTest_VolatileIncrememnt = lngCounter + 1
End Function

Result: As approach 1

Approach 3: Pass in current cell and set dirty if not yet reached

Public Function VolTest(rngThis as Excel.Range) As Long
    Static lngCounter as Long
    If lngCounter < 5 Then
        rngThis.Dirty
    End If
    lngCounter = lngCounter + 1
    VolTest = lngCounter
End Function

Result: Excel hangs in an infinite loop

I've also tried keeping track of rngThis, where the argument is a string instead of a range (range via Range(strThisCell)), in a dictionary stored as a property of ThisWorkbook, and only setting dirty if not already in the function, which breaks the infinite loop, but also returns with #VALUE! as soon as rngThis.Dirty is called.

I have a horrible report that is build on `CubeValue` formulae. These are calculated by reference to `CubeMember`s and `CubeSet`s that are looked up on a sheet-by-sheet basis to determine parameters for `CubeValue`s. As per a question I asked yesterday, there's a set of syntax for `CubeSet` that is supported in `CubeSet` but not in `CubeValue`. I'd like to be able to write a UDF that will (volatilely) keep calculating the `CubeSet` using the syntax I want to use, then once calculated, work out what the syntax should be using the supported version, and return that (and then stop being volatile) — tobriand, Apr 28 '15 at 10:55
I don't think you can do it with `Volatile`. That is, as far as I know, only evaluated when the function is compiled, not each time it runs. — Rory, Apr 28 '15 at 12:15
That's what I thought. I'm open to other approaches though - although I have to say if @L42 has something acting in the right way then maybe there's a setting that I'm using differently that determines how `Application.Volatile` is treated. It always struck me as weird that what is apparently a subroutine call nevertheless has a compile-time effect. — tobriand, Apr 28 '15 at 13:39
I stand corrected. I just tried the second function @L42 posted, and it does behave as he suggests. Just be aware that `Application.Volatile False` is not quite the same as leaving out the `Application.Volatile` statement altogether: https://fastexcel.wordpress.com/2011/09/05/false-volatility-is-this-a-bug/ — Rory, Apr 28 '15 at 14:51
Right, so that means it works in two separate cases, but not on mine... hmm, wonder why...? Not that it *should* have changed for 2013, but are either of you using Excel 2010? — tobriand, Apr 28 '15 at 15:04

L42 · Accepted Answer · 2015-04-28T10:02:41.870

1

The first code works at my end but you need to put the counter inside the If Statement like this:

Public Function VolTest() As Long
    Static lngCounter As Long
    If lngCounter < 5 Then
        Application.Volatile
        lngCounter = lngCounter + 1
    End If
    VolTest = lngCounter
End Function

It is still volatile but the value changes until 5 only. It is the same if you declare Application.Volatile on top but then again put the counter inside the If Statement.

Edit1: Turn Off Volatile

Public Function VolTest() As Long
    Static lngCounter As Long
    If lngCounter < 5 Then
        Application.Volatile (True)
        lngCounter = lngCounter + 1
    Else
        Application.Volatile (False)
    End If
    VolTest = lngCounter
    MsgBox "Called" ' to test if it is turned off
End Function

edited Apr 28 '15 at 10:02

answered Apr 28 '15 at 09:39

L42

19,427
11
44
68

Unfortunately, this is a toy example - what I'm going to be USING this for is to avoid executing around 100k other expensive formulae if I don't need to. In other words, it's the volatility (and consequential execution or non-execution of the function) I care about, not the counter (that's just there as a test). – tobriand Apr 28 '15 at 09:46
This doesn't seem to work, at least on Excel 2010. The counter still keeps going indefinitely. Same goes when I split out the volatility declaration into a separate function (so in theory it only ever has Application.Volatile with one value present at any one time). Only explanation I can think of is it is working more like a compile-time setting and less like a runtime one. Which is rather annoying! – tobriand Apr 28 '15 at 09:59
Hmm... wonder if it's to do with individual calculation settings or something. I have to say, I'm getting some odd behavior even with setting volatile explicitly to false and instead flagging dirty in a dictionary after the calculation event fires in ThisWorkbook (only flagging where <5). I figured it might be because of automatic calculation, but I've turned that off too, so somewhat stumped... – tobriand Apr 28 '15 at 10:17

How VBA declared Volatility works

1 Answers1

Linked