Excel 2000 formula recalc problem

[Tom Pachulka]

Hi everybody,

I have a problem making sure all the formulas in the spreadsheet
recalculate, when recalc is called from within a macro.

I have a complicated spreadsheet model that takes a second or two to
recalculate. I run a macro that:

1. Sets up parameters
2. Forces recalc
3. Grabs the output
4. Cycles back to step 1 with different parameters, etc.

My code looks something like this:

For j = pageFirst To pageLast Step pageStep

<set up parameters of the j-th scenario>
Application.CalculateFull
<save the output, which is just a rectangular range in one of
the tabs>

Next

The problem is, sometimes Excel seems to grab the output before a
complete recalc is done. So the formulas are only partially updated
when the output is saved.

This happens pretty randomly, but also frequently enough (once in a
1,000 loops or so).

Why is this happening?
Is there any way to make ABSOLUTELY sure ALL formulas have been
recalc'd before proceeding to the output?

Thanks a lot!
Tom

 

[Ken]

Tom, could it be that you need to turn off the re-cal
(Application.Calculation = xlCalculationManual) until you
are ready and then turn it back on
(Application.Calculation = xlCalculationAutomatic) just
before you re-calculate?

 

[Tom Pachulka]

Ken,

You're right: I do switch Excel to manual calc at the start of the
macro. But then calling

Application.Calculation = xlCalculationAutomatic

would be identical to

Application.CalculateFull

Actually, from what I understand the latter is supposed to be a more
powerful way to force recalc than the former.

Again, it's not that my code doesn't work. It does work OK, but with
an occasional hiccup, which to me means the problem is probably not in
my code, but in the way Excel handles recalc.

Thanks for your reply, though
Tom