By default, the spreadsheet recalculates formulas in the spreadsheet when the contents of dependent cells change. You can turn this recalculation off. You can also recalculate an individual cell.

Also by default, the spreadsheet updates formulas when you add, insert, or remove columns or rows or when you move or swap blocks of cells. You can turn off these automatic formula updates, but generally you probably want the spreadsheet to update formulas in these cases. Keep in mind how turning off automatic formula updating might impact the spreadsheet if the user moves data, adds rows or columns, or performs other actions that affect the location of data.

When automatic formula updating is on, the spreadsheet updates absolute and relative cell references, as follows:

• When the spreadsheet is updating formulas, it updates absolute cell references when the cell referenced by the formula is part of the block that has changed.

  For example, if you have a formula in cell C3 that references cell A1, which uses an absolute reference, and then add a row to the top of the spreadsheet, you now want the formula to reference cell A2, because cell A1 is empty. If the spreadsheet did not update the formula, your formula would be referencing different data.

• When the spreadsheet is updating formulas, it updates relative cell references when the cell referenced by the formula is not part of the block that has changed.

  For example, if you have a formula in cell C3 that references cell C1 as a relative reference, it references cell C1 as the cell that is two cells above it. If you add a row between row 2 and row 3, cell C3 is now C4, and the relative address references cell C2, the cell two cells above it. Therefore, to use the same data in the formula, the spreadsheet updates the cell reference to the cell three cells above it, C1.

Use the AutoCalculation property to turn on or off the automatic recalculation of formulas. Use the Recalculate and RecalculateAll methods for recalculating formulas.

Using Code

1. If needed, set the reference style for the sheet with the ReferenceStyle property.
2. Add a formula to the cell with the SetFormula method.
3. Set the RecalculateAll method to recalculate.

Example

This example recalculates all the formulas.

fpSpread1.ActiveSheet.SetValue(0, 0, 20);
fpSpread1.ActiveSheet.SetValue(0, 1, 10);
fpSpread1.ActiveSheet.SetFormula(3, 0, "SUM(A1,B1)");
fpSpread1.ActiveSheet.SetFormula(4, 0, "A1*B1");
fpSpread1.ActiveSheet.SetValue(0, 1, 100);
fpSpread1.ActiveSheet.RecalculateAll();

FpSpread1.ActiveSheet.SetValue(0, 0, 20)
FpSpread1.ActiveSheet.SetValue(0, 1, 10)
FpSpread1.ActiveSheet.SetFormula(3, 0, "SUM(A1,B1)")
FpSpread1.ActiveSheet.SetFormula(4, 0, "A1*B1")
FpSpread1.ActiveSheet.SetValue(0, 1, 100)
FpSpread1.ActiveSheet.RecalculateAll()

Copyright © GrapeCity, inc. All rights reserved.