There was an interesting discussion here about using Application.Volatile False in a VBA UDF.
It seems that if you create a VBA UDF like this one:
Function functest2(x As Double) As Double Application.Volatile (False) Dim Z As Double Z = x + 2 functest2 = Z End Function
and call it from a cell when x refers to another cell that contains a volatile function or has a precedent cell with a volatile function, then the function is not recalculated when the volatile precedent cell changes because of a recalc. But of course it should!
If you change the function by either:
- Changing x as double to x as variant
- Removing the Application Volatile False statement
then the function is recalculated as expected. It also works as expected if the precedent cell is not volatile, or is changed, or a full calculation (ctrl-alt-F9) is done.
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. But this unexpected behaviour started me digging.
In the old XLM Macro language it turns out there was a use for Volatile False. If you created an XLM UDF using ARGUMENT(8) to make the argument remain as a reference rather than be coerced to a value the UDF was made Volatile, so you could use Volatile False if you did not want this behaviour.
Also (and probably more relevant) in a similar way when creating XLL functions if you register the function as a macro-equivalent function by adding a # to the end of the registration type string, and you are using type R or U arguments (reference) then the function will be made volatile, (the usual way is to add ! to the end of the type string). And in a similar way to the XLM macros you can use the C API function xlfVolatile to switch this volatility to False.
If you have not flagged the XLL UDF as a macro-equivalent function then using xlfVolatile has no effect
Macro-equivalent XLL functions can be useful because they have increased permissions compared to normal UDFs:
- They can get the previous value of an uncalculated cell
- They can use information functions that ordinary functions cannot
But these macro-equivalent XLL functions using R or U with Volatile set to False also do not recalculate when they have a volatile precedent.
It seems to me that this is definitely a bug with the VBA UDF, and that there is no reason to use Application.Volatile False with VBA (maybe unless you want to have a conditionally volatile function?).
With an XLL function (and an XLM function) at least there is a use for Volatile False, but the XLL behaviour with Volatile precedents also looks buggy to me.
So what do you think? Bug or by design?
Can you see another use for explicitly setting Volatile to False?
Anyone want to see if the same bug exists with an XLM function?
For more information on Volatile Functions and Actions see my Volatility page.