Value changed of a cell with formula

[Fabrice]

Apparently the worksheet's Cells.CellPropertyChanged event is not raised when a cell with a formula is recalculated because one of the source cells was changed. Is there any way of getting notified in this situation?

 

[Nikolay Demirev]

Hello,

The CellPropertyChanged event is used only for notifying if some of the cell properties are changed. As the value of the cell is a formula value, when the formula is recalculated the actual cell value is not changed because the formula remains the same, only the result of its evaluation is changed.

When a cell has a formula value it is stored in the FormulaCellValue object as a FormulaExpression. When some of the cells, which the formula refers are changed the FormulaExpression is invalidated and UI is notified for that invalidation. When the UI is updated all invalidated formulas are evaluated and the new result is presented in the UI.

You could use the following code snippet to track if some FormulaExpression is invalidated. Please bear in mind that you have to detach from the events to prevent memory leaks or use weak events.

Worksheet worksheet = this.radSpreadsheet.ActiveWorksheet;
worksheet.Cells.CellPropertyChanged += this.Cells_CellPropertyChanged;

private void Cells_CellPropertyChanged(object sender, CellPropertyChangedEventArgs e)
{
    Worksheet worksheet = this.radSpreadsheet.ActiveWorksheet;

    if (e.Property == CellPropertyDefinitions.ValueProperty)
    {
        CellRange cellRange = e.CellRange;
        for (int rowIndex = cellRange.FromIndex.RowIndex; rowIndex <= cellRange.ToIndex.RowIndex; rowIndex++)
        {
            for (int columnIndex = cellRange.FromIndex.ColumnIndex; columnIndex <= cellRange.ToIndex.ColumnIndex; columnIndex++)
            {
                FormulaCellValue formulaValue = worksheet.Cells[rowIndex, columnIndex].GetValue().Value as FormulaCellValue;
                if (formulaValue != null)
                {
                    formulaValue.Value.ValueInvalidated += this.Value_ValueInvalidated; // use weak events to prevent memory leaks
                }
            }
        }
    }
}

private void Value_ValueInvalidated(object sender, EventArgs e)
{
    // the formula needs recalculation
}

Regards,
Nikolay Demirev
Telerik

[Fabrice]

Thanks for the feedback, will give it a try