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