Excel 2000 formula recalc problem

  • Thread starter Thread starter Tom Pachulka
  • Start date Start date
T

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
 
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?
 
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
 
Back
Top