Ensuring your formula are recalculated on File-Open
One problem we came across with Excel 2007 is that it does not automatically re-calculate the spreadsheet when it is re-opened – even when the Calculate option set to automatic! This is because the existing cells in the template have both a formula and a value in the cell. So Excel just assumes the value is correct and does not attempt to re-compute the formula. Of course, we have just added twenty rows of data and updated the formula references in the XML – but Excel has no why of knowing this, so assumes the values must be right!
The only way to force the recalculation is to ensure the cell has no value – just a formula. So the RemoveValue()
method becomes very useful for all formula in the worksheet. Hence:
Because of this phenomenon, we changed the ExcelCell.Formula
property assignment code so that it removes the cell’s value when you assign the cell a formula.