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:

worksheet.Cell(22, 5).RemoveValue();


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.

Last edited Jan 3, 2007 at 4:47 PM by JohnTunnicliffe, version 1

Comments

bikethis Mar 24, 2011 at 7:03 PM 
I have some forumlae that have a dependancy on other formulaic cells. Despite calling RemoveValue() on these cells, they are not calculated on file open. Any one else?

vcr2 Jul 8, 2009 at 12:38 PM 
I do have this problem of recalculation even using ExcelCell.Formula set.