vbaexcel

How VBA declared Volatility works


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.


Solution

  • 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