Forum Discussion
Idea needed to stop cell from calculating to preserve its value
In case anyone ever finds here, I tidied and tightened up the earlier VBA code. Test sheet attached.
Wondering if this could be a request to MS to add a self reference to a formula without having to turn on iterative calculations - e.g. a function called PREV() which would return the value of the current cell before the calculation. Then
=IF(F4,F5,F6)
becomes
=IF(F4,PREV(),F6)
Incidentally, it is quite weird. The earlier ifCache works perfectly when, on reflection, it should not. In a larger production environment it transpires - unsurprisingly, really - that the formula
=ifLive(F4,F5,F6)
never extinguishes the "Calculate" indicator when F6 is updated by sweepDictionary. I had to modify the function signature to
=ifLive(F4,F5,CELL("address",F6)) (ie take elseAddr, not elseRange)
to work around this issue. Code changes are minuscule. Update attached.
Update:
Yesterday, a rather naughty thought crossed my mind. Upon reflection, every cell in Excel is capable of holding TWO values: The cell value itself (inside the calculation tree) PLUS a Note (outside the calculation tree). A UDF, it turns out, is permitted to manipulate the Note attaching to ThisCell. I can hence simplify ifLive materially by using that Note to cache ThisCell's previous value (avoiding the need for an extra cell inside the Worksheet and an event handler to manipulate it).
- I had thought that =IF(F4;;F6) would be perfect for the purpose. The current behaviour of this illegal/meaningless statement - it produces TRUE (when F4 is TRUE) - is not intuitive.