Difference btween Automatic and Manual calculations.

I have a macro that is running and it is running slow since it has to wait
for the spreadsheet to do updates. I switch the calculations and it runs
faster. I am wondering if there is loss of anything. What is the difference
between the two and how does that affect calculations. Thanks in advance.

When you make a change to a spreadsheet, Excel automatically determines
(based on the change that you made) which cells need to be re-calculated and
marks these cells as dirty. In automatic mode the calculation is done
immediately and the dirty flags are removed. In Manual mode the flags are
just saved up until you manually run a calculation. If you are running code
with calculation turned off and you are grabbing the values of dirty cells,
then you are grabbing the wrong amounts. You just need to be sure that if
your code makes a change that dirties a cell that you do not use the value of
that cell until a re-calc is done.

One more thing... When you toggle the cacluation setting in code it is a
good idea to use an error handler to reset the calculation in the event of a
crash.
--
HTH...

Jim Thomlinson


"WannaBeExceller" wrote:

> I have a macro that is running and it is running slow since it has to wait
> for the spreadsheet to do updates. I switch the calculations and it runs
> faster. I am wondering if there is loss of anything. What is the difference
> between the two and how does that affect calculations. Thanks in advance.

Thank you Jim for you response. I was just wondering how the error handler
would be implemented. I am using a line that does the recalc
"worksheet(1).calculate". Thanks in advance.

"Jim Thomlinson" wrote:

> When you make a change to a spreadsheet, Excel automatically determines
> (based on the change that you made) which cells need to be re-calculated and
> marks these cells as dirty. In automatic mode the calculation is done
> immediately and the dirty flags are removed. In Manual mode the flags are
> just saved up until you manually run a calculation. If you are running code
> with calculation turned off and you are grabbing the values of dirty cells,
> then you are grabbing the wrong amounts. You just need to be sure that if
> your code makes a change that dirties a cell that you do not use the value of
> that cell until a re-calc is done.
>
> One more thing... When you toggle the cacluation setting in code it is a
> good idea to use an error handler to reset the calculation in the event of a
> crash.
> --
> HTH...
>
> Jim Thomlinson
>
>
> "WannaBeExceller" wrote:
>
> > I have a macro that is running and it is running slow since it has to wait
> > for the spreadsheet to do updates. I switch the calculations and it runs
> > faster. I am wondering if there is loss of anything. What is the difference
> > between the two and how does that affect calculations. Thanks in advance.

sub Test
on error goto ErrorHandler
with application
.calculation = xlManual
'.screenupdating = false
'.displayelerts = false
end with

'Your Code here

'No exit sub so the error handler will procedd into this section even
without an error
ErrorHandler:
with application
.calculation = xlAutomatic
'.screenupdating = true
'.displayelerts = true
end with
End Sub

--
HTH...

Jim Thomlinson


"WannaBeExceller" wrote:

> Thank you Jim for you response. I was just wondering how the error handler
> would be implemented. I am using a line that does the recalc
> "worksheet(1).calculate". Thanks in advance.
>
> "Jim Thomlinson" wrote:
>
> > When you make a change to a spreadsheet, Excel automatically determines
> > (based on the change that you made) which cells need to be re-calculated and
> > marks these cells as dirty. In automatic mode the calculation is done
> > immediately and the dirty flags are removed. In Manual mode the flags are
> > just saved up until you manually run a calculation. If you are running code
> > with calculation turned off and you are grabbing the values of dirty cells,
> > then you are grabbing the wrong amounts. You just need to be sure that if
> > your code makes a change that dirties a cell that you do not use the value of
> > that cell until a re-calc is done.
> >
> > One more thing... When you toggle the cacluation setting in code it is a
> > good idea to use an error handler to reset the calculation in the event of a
> > crash.
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "WannaBeExceller" wrote:
> >
> > > I have a macro that is running and it is running slow since it has to wait
> > > for the spreadsheet to do updates. I switch the calculations and it runs
> > > faster. I am wondering if there is loss of anything. What is the difference
> > > between the two and how does that affect calculations. Thanks in advance.