Search code examples
exceludfvba

Re-evaluate non volatile UDF automatically

Say, we have the following non-volatile UDF:

Function Twice(ByRef x As Double) As Double
    Application.Volatile False
    Twice = 2 * x
End Function

which we enter in a cell A1 with reference to cell A2, i.e. in A1 we put: 

= Twice(A2)

Furthermore, in A2 we put the random function, i.e. in A2 we put:

=RAND()

Now, if we re-calculate the sheet (say, by pressing Shift + F9), the value in the cell A2 will change, but the UDF will not update, even though its reference has changed.

How can we make this UDF update automatically (i.e. without pressing Ctrl+Alt+F9) when its reference value changes?

Solution

  • There is what I consider a bug when you use Application.Volatile False with a Double argument that refers to a volatile formula.

    If you remove the Application.Volatile False statement it works OK.

    See https://fastexcel.wordpress.com/2011/09/05/false-volatility-is-this-a-bug/ for a more detailed discussion of this and speculation as to the cause.