Force complete recalculation?

[Jerry Park]

I have a workbook where entries are examined and changed by the
Worksheet_Change() event. This works well, but I have a problem that
some rows/columns don't recalculate their values properly. An F9 doesn't
correct the problem (I suppose a recalculation only recalculates changed
cells?) and the problems seems to be that excel does not recognize that
the cell value has changed. If any value in the affected column or row
is changed or reentered, the row or column is properly recalculated.
[This does not happen with every data entry, just sometimes.]

I can 'fix' this by rewriting every item in the data entry area, but
this takes a substantial portion of a second.

Is there any command, function key, etc. which will force excel to
recalculate every formula on the worksheet, even when there has been no
change to the data?

Thanks

 

[Dave Peterson]

This is from xl2002's help:

Calculate a worksheet or workbook now

Press F9 Calculates formulas that have changed since the last calculation, and
formulas dependent on them, in all open workbooks. If a workbook is set for
automatic calculation, you do not need to press F9 for calculation.

Press SHIFT+F9 Calculates formulas that have changed since the last
calculation, and formulas dependent on them, in the active worksheet.

Press CTRL+ALT+F9 Calculates all formulas in all open workbooks, regardless of
whether they have changed since last time or not.

Press CTRL+SHIFT+ALT+F9 Rechecks dependent formulas, and then calculates all
formulas in all open workbooks, regardless of whether they have changed since
last time or not.

(I think that the last one is new with xl2002.)


Another option may be to Edit|replace = with =. Excel will see that as a change
to the formula and reevaluate.

I have a workbook where entries are examined and changed by the
Worksheet_Change() event. This works well, but I have a problem that
some rows/columns don't recalculate their values properly. An F9 doesn't
correct the problem (I suppose a recalculation only recalculates changed
cells?) and the problems seems to be that excel does not recognize that
the cell value has changed. If any value in the affected column or row
is changed or reentered, the row or column is properly recalculated.
[This does not happen with every data entry, just sometimes.]

I can 'fix' this by rewriting every item in the data entry area, but
this takes a substantial portion of a second.

Is there any command, function key, etc. which will force excel to
recalculate every formula on the worksheet, even when there has been no
change to the data?

Thanks

 

[Jerry Park]

Thanks. Exactly what I needed. You are correct that the
CTRL+SHIFT+ALT+F9 combination is not available in Office 2000.

Running a macro with the CTRL+ALT+F9 combination yeilds the VBA command
I really needed: Application.CalculateFull .

Works -- and fixes the problem.

This is from xl2002's help:

Calculate a worksheet or workbook now

Press F9 Calculates formulas that have changed since the last calculation, and
formulas dependent on them, in all open workbooks. If a workbook is set for
automatic calculation, you do not need to press F9 for calculation.

Press SHIFT+F9 Calculates formulas that have changed since the last
calculation, and formulas dependent on them, in the active worksheet.

Press CTRL+ALT+F9 Calculates all formulas in all open workbooks, regardless of
whether they have changed since last time or not.

Press CTRL+SHIFT+ALT+F9 Rechecks dependent formulas, and then calculates all
formulas in all open workbooks, regardless of whether they have changed since
last time or not.

(I think that the last one is new with xl2002.)


Another option may be to Edit|replace = with =. Excel will see that as a change
to the formula and reevaluate.

I have a workbook where entries are examined and changed by the
Worksheet_Change() event. This works well, but I have a problem that
some rows/columns don't recalculate their values properly. An F9 doesn't
correct the problem (I suppose a recalculation only recalculates changed
cells?) and the problems seems to be that excel does not recognize that
the cell value has changed. If any value in the affected column or row
is changed or reentered, the row or column is properly recalculated.
[This does not happen with every data entry, just sometimes.]

I can 'fix' this by rewriting every item in the data entry area, but
this takes a substantial portion of a second.

Is there any command, function key, etc. which will force excel to
recalculate every formula on the worksheet, even when there has been no
change to the data?

Thanks