Protecting certain cells against volatile recalculation

[Schizoid Man]

Hi,

If I have a volatile UDF used in two different cells in a spreadsheet, is it
possible for me to 'protect' one of those cells against an F9 recalculation?

In other words, is there a way to block the volatile behavior at the
application level?

Thanks,
Schiz

 

[Bernie Deitrick]

Schiz,

You could store the value somewhere (using the calc event, every time you actually calc it), and
then where ever you want to use the value, you use the stored value instead of the UDF value. The
calc event needs to have a call to a cell where you write a key value to disallow storing the new
calc.

If Range("Blocker").Value = "Block" Then Exit Sub
Range("Storage").Value = Range("CellwithUDF").Value

HTH,
Bernie
MS Excel MVP