Excel 2003 worksheet doesn't auto calculate

[lbbeurmann]

I am using Excel 2003 and have my calculations set to automatic, but I notice
that there are times when my formulas do not calculate. If I click into the
offending cell and hit "enter," esentially re-submitting the formula, it
calculates. Since I am dealing with a large workbook with may worksheets
worth of interdependent formulas, I am worried that my data will be
inaccurate. Is there a limit to how many calculations an excel workbook can
do and have I reached it? Is there a way I can force a cell to calculate
even though there is nothing wrong with the formula, possibly with some vba
code or something? I appreciate any help since my credibilty at work depends
on presenting accurate data, and I have a real lack of confidence in what
excel is giving me.

Thanks,
L.B.

 

[Gary''s Student]

Try this small macro:

Sub refresh()
Application.CalculateFullRebuild
End Sub

 

[lbbeurmann]

your solution worked great for my worksheets. However, i have several charts
with text boxed linked to cells with formulas in them. The formulas in the
sheets are updating, but not the text boxes on my charts unless i do the same
thing...click in the text box and hit enter. is ther a piece of code that
will force those to update as well?

 

[Hunor-Péter Gáspár]

I have encountered the same issue. Formulas did no Calculate
themselves only if I entered them and hit ENTER or Saved the WorkBook
or issued a Calculate command on my WorkSheet Object. This was very
annoying.

The reason why Excel is acting like this is because somehow the
[Calculation] flag was set to [xlCalculationManual(-4135) - don't ask
me what changed this.

So, to fix it, you must set the Calculation flag to Automatic.
Application.Calculation=xlCalculationAutomatic


Please let me know if this helps.
Peter

 

[Gord Dibben]

"somehow" can be explained by pointing out that Excel uses the calculation
mode of the first workbook opened during a session.

If that WB was saved with "manual" setting, all workbooks opened after would
keep that setting...............no matter which calc mode they were saved
in.


Gord Dibben MS Excel MVP