Get value of cell containing UDF without circular reference

Hello,

I have a simple UDF that performs a http request to return a value to a cell. Whenever the cell recalculates I'd like to compare the old and new values in the cell to see whether there has been a change, the code looks roughly something like this:

Please Login or Register  to view this content.

The issue I'm having is that requesting the Value of a Range that contains a UDF will trigger a recalculation, and if this is placed inside the UDF (as above) then it will result in a circular reference which will crash Excel.

I've noticed that the same thing happens when using Application.Caller.Value2. Only Application.Caller.Text seems to work, although it's less useful because I need the pre-formatted values.

Is there a way to get the value of a cell without a recalculation? Excel must store this somewhere because if you reformat a value returned from a UDF then it doesn't require a recalculation (e.g increasing the number of decimal point 22.45 -> 22.445).

You can't get the value from the function that returns the value. Text is your only option other than storing the old value in a variable.