False Volatility – Is this a bug?

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.

This entry was posted in Calculation, UDF, VBA, XLL. Bookmark the permalink.

5 Responses to False Volatility – Is this a bug?

  1. Jon says:

    I just wanted to see peoples comments on this topic. Is there a way to do this without posting a comment?

  2. fastexcel says:

    Yours is the first comment on this post!

  3. shg says:

    Thanks for the research, Charles. I think it is most definitely a bug, but considering what you have to do to manifest it, not one I’m concerned about.

    I have several UDFs that generate random variates with various distributions. They all include an optional argument to make the function volatile:

    If bVolatile Then Application.Volatile

    … rather than …

    Application.Volatile bVolatile

    … so it is never explicitly set False

    That’s worked consistently and reliably for me.

    Kind regards,

  4. fastexcel says:

    Stephen, that looks like an excellent bypass for (I think) the only VBA situation where this bug could be a problem.

  5. David Z says:

    Here’s another interesting situation… it is not *my* question but I was trying to help answer it. In this case, it seems that any time *any* cell value is changed on the worksheet (even if that cell is *not* referenced in the UDF), the UDF is recalculating regardless of whether Application.Volatile False (or omitted altogether).



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s