Non-volatile UDF always recalculating

Question

I am trying to make a non-volatile UDF but it seems not possible. So here is a my very simple test-UDF:

Option Explicit
Dim i As Integer

Sub Main()

i = 0
[A1] = "zyy"
MsgBox i

End Sub

Function Test(rng As Range)
Application.Volatile (False)

Test = rng.Value
i = i + 1

End Function

I got a otherwise empty worksheet that uses this function a couple of times, and every time I call Main() and change any cell on the sheet with the UDFs all of them recalculate. How can I make this (any) UDF no-volatile? The Application.Volatile (False) should have that effect but obviously doesn't work.

Edit: If I change a cell manually it works like intended, it only recalculates when I change a cell via VBA. Is this normal behaviour or can I change it?

Answer 1

I am posting a new answer instead of trying to salvage my previous answer, even though I think they point to the same thing, it will be better to start fresh.

Background:

Previously I had tested your code and the results were exactly as I would expect them to be if you simply omit the False from that statement. I have never seen any reason to explicitly do Application.Volatile (False), because that is equivalent to simply omitting the statement entirely.

• If omitted, the function is non-volatile and the UDF evaluates only when a reference changes (i.e., not when other, non-referent cells change)
• If included as Application.Volatile (or Application.Volatile (True), the UDF becomes volatile and any change to the worksheet will force re-evaluation.

Continuing investigation

You commented that you still observed otherwise. So I made some changes to my code and tested again. All of a sudden weird stuff was happening. No matter what I did with the Application.Volatile function, any change to the worksheet was re-evaluating the UDF.

This didn't make sense, so I started googling and doing a little more testing.

In my tests I created three functions.

1. The first one is explicitly Volatile:
2. The second one is explicitly not volatile.
3. The third omits any statement of volatility.

I put one instance of each formula on a worksheet. Each referenced a different range.

I tested each of these by making changes to the worksheet (manually), and through a named subroutine. I used a Print statement and monitored the Immediate window in the VBE to confirm that in all cases, the functions evaluated (or not) only as expected. The first one always evaluates, while 2 and 3 only evaluate if reference range changed.

Function f_appvol(rng As Range)

Application.Volatile
Debug.Print "f_appvol"

f_appvol = rng.Value
End Function

Function f_appNOTvol(rng As Range)
Application.Volatile (False)
Debug.Print "f_appNOTvol"

f_appNOTvol = rng.Value
End Function

Function f_omit(rng As Range)

Debug.Print "f_omit"
f_omit = rng.Value

End Function

Then it got weird...

I started making changes within these functions and they start to behave wonky.

Specifically I got lucky and noticed that if I changed my non-volatile function to a volatile one, then all functions started acting as if they were volatile -- even the f_omit. I believe this may be the condition you are experiencing.

Somehow, we have managed to "confuse" Excel

I saved the workbook and tried again... back to normal!

Then I changed the argument in the volatile statement, and the strange behavior happened again.

This appears to be a bug

I don't see anything in the documentation that suggests this is normal/expected behavior, and it sure as hell is not desirable behavior from a debugging standpoint. This is the sort of thing that makes you pull out your hair in frustration!

I am using Excel 2010, Win 7 64b.

Resolution

The cause of the error seems to be making change to the volatility of a UDF.

In order to restore expected behavior, it seems necessary to save the workbook. Again, I don't think this is normal but it seems to solve your problem (or at least a very similar problem that I was able to replicate while troubleshooting yours).

On a possibly related note

There appears to be at least one bug related to volatility, as mentioned here. I link to it mainly because this writer echos my own sentiment: there is no reason to do Application.Volatile (False) because that is (or should be) the "normal" state of a UDF.

I have to admit that I had never seen the point of using Application.Volatile False since thats supposed to be what you get if you omit the Application.Volatile statement altogether.

Answer 2

I found the solution, and it is indeed a very simple one but also made this hard to debug: If you make any change to your VBA code all the UDF get flagged for recalculation! I modified the degug code of David:

Sub main()

'nothing depends on the Value in [A13]
[A13] = "" 
[A13] = "hgdg"
[A13] = ""
i = 46

End Sub

Function f_appvol(rng As Range)

Application.Volatile
Debug.Print "f_appvol"

f_appvol = rng.Value
End Function

Function f_appNOTvol(rng As Range)
Application.Volatile (False)
Debug.Print "f_appNOTvol"

f_appNOTvol = rng.Value
End Function

Function f_omit(rng As Range)

Debug.Print "f_omit"
f_omit = rng.Value

End Function

After entering the code and running it for the first time, only f_appvol is recalculated. If you now change i=46 to i=47 and execute it, all the UDF get recalculated. All subsequent runs after that first run after the change give the expected behaviour.