Excel 2000 formula recalc problem
 

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

Excel 2000 formula recalc problem
 

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?

-----Original Message-----
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
.

Excel 2000 formula recalc problem
 

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


"Ken" wrote in message ...
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?

-----Original Message-----
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
.