Avoid rand() recalculation when opening a woorkbook

Topics: Developer Forum, User Forum
Aug 27, 2014 at 11:50 AM
Hi all,

I apologize in advance if the question had already been asked.

I'd like to know if it's possible to "block" the evaluation/calculation of the rand formula when opening a workbook with PHPExcel.
Today, when I open a workbook, the rand() formula is recalculated; I'd like to block this so that I get always the same figures when I open and re-open the workbook.

Thank you so much in advance for your help!
Aug 27, 2014 at 12:35 PM
Edited Aug 27, 2014 at 12:39 PM
At this point, no it isn't.
Excel flags certain functions such as RAND() as volatile and changes the behaviour of whether or not to recalculate cells in a spreadsheet on load based on that, and the (somewhat limited) documentation suggests that volatile formulae will always be recalculated.

When loading files into PHPExcel, PHPExcel will not recalculate any formulae unless you explicitly request getCalculatedValue() or getFormattedValue() for a cell
If you want to retrieve the result of a formula calculation without recalculating (e.g. the value that was last calculated in MS Excel itself) in PHPExcel, then getOldCalculatedValue() can be used.
Aug 28, 2014 at 1:46 PM
Edited Aug 28, 2014 at 1:47 PM
Hi MarkBaker,

Thank you so much for your detailed answer.
I did not know the function getOldCalculatedValue, it was really useful to me.

However, I know it's possible to configure a workbook so that volatile functions like RAND are not re-calculated when a workbook is opened (by Excel), except if the user explicit requires it. I think this feature is called "mode", and can have values such as "manual" or "automatic".
So, according to you, it's not possible to leverage this setting with PHPExcel?

Thank you again.