0

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?

Community
  • 1
  • 1
Confounded
  • 446
  • 6
  • 19
  • Remove the line`Application.Volatile False` as @pnuts suggested and set the calculation of the workbook as automatic instead of manual – Siddharth Rout Sep 15 '15 at 08:27
  • @pnuts Not stating explicitly `Application.Volatile False` is the same as stating it as it is the default setting. I just put it here to be explicit in that I do not want the function to be volatile. – Confounded Sep 15 '15 at 08:29
  • 1
    Define x as `Variant`. – BrakNicku Sep 15 '15 at 08:43
  • Please do not change the edit. Your Question title should not have the description. The Post area is for that. – Siddharth Rout Sep 15 '15 at 08:43
  • @pnuts I want UDF to change only when the value in the cell to which it refers (in the example cell A2) changes. I do not want it to change when, say a value in cell A3 changes, which what would happen if the function is volatile. – Confounded Sep 15 '15 at 08:54
  • @user3964075 That seems to do the trick. Thanks. Would you care to expand your comment into an answer with perhaps some explanation on why this works? – Confounded Sep 15 '15 at 08:56
  • @SiddharthRout There are already some questions on re-calculation of UDFs - wanted to make clear that mine is not addressed by them. Also, why do you keep capitalising almost every word in the title? – Confounded Sep 15 '15 at 08:59
  • There you go.. I have uncapitalized them. :) You can make it clear in the post itself. The title area is not for descriptive text. – Siddharth Rout Sep 15 '15 at 09:01
  • @Confounded sorry, but I can't give you detailed explanation. I think it's because you need the parameter to be of type `Range` to make it work. If you pass `A2+1` as the parameter it won't recalculate. Maybe someone else will find more universal solution. – BrakNicku Sep 15 '15 at 09:03
  • @pnuts No. In my example, x depends on A2. When a value (returned by a function) in A2 changes, I want UDF to re-evaluate. If I make UDF volatile, it will revaluate when any value in any cell on the sheet changes. – Confounded Sep 15 '15 at 09:04
  • @user3964075 OK, I up-voted your comment. Thanks again. – Confounded Sep 15 '15 at 09:06
  • @user3964075 Strange, by defining parameters as `Variant` seems to turn some functions into Volatile. Need to dig deeper. – Confounded Sep 15 '15 at 09:22

1 Answers1

0

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.

Charles Williams
  • 23,121
  • 5
  • 38
  • 38
  • Thank you for your answer. This solution (as well as defining parameters as variant) has been offered in the comments to the OP, but I will accept you answer in order to close this question and since it offers further details and acknowledges this “strange” behaviour. – Confounded Sep 15 '15 at 12:04