Application.Calculate vs. F9

[MDW]

One of my colleagues has a spreadsheet that contains a great deal of
complicated calculations. This is in XL2003.

When he hits F9 to force Excel to calculate, there is a noticable pause - 3
seconds or so - for all the recalcing to occur.

He also has a macro in which he uses the Application.Calculate and it runs a
lot quicker. His concern is that VBA is not waiting for the calculation to
complete before moving to the next line.

Is there a difference in what Excel does when you programmatically calculate
vs. using F9?


I'm asking this on behalf of a co-worker and have not seen the workbook in
question.

 

[Jim Thomlinson]

Check out this link. Everything you ever wanted to know about calculations
but were affraid to ask...

http://www.decisionmodels.com/calcsecrets.htm

 

[MDW]

Thanks. That's a lot of useful information; however, it doesn't answer the
root questions of my post - If Application.Calculate and F9 are the same
(which, according to that link, they are), why does it take so much longer
for the F9, and how do we know that VBA is waiting for the
Application.Calculate to be completed before it goes to the next line of code?

 

[Jim Thomlinson]

The length of time it takes to recaluclate depends on the number of dirty
cells. The more cells that have been flagged as dirty the longer the
calculation will take. If you hit F9 twice in a row, the first calulation
will take a while but the second calc should be almost instantaneous as all
of the dirty flags will have been removed (assuming less than 65,535
dependancies and few volatile functions). As for the code continuing
execution prior to the calculations finishing, the calculation will complete
prior to the code continuing on.

 

[Charles Williams]

If you have a lot of VBA user-defined functions Application.calculate runs a
lot faster than F9
(unless you trap F9 with an Application.Onkey statement and redirect to
Application.Calculate)

see http://www.decisionModels.com/calcsecretsj.htm for more details

Charles
______________________
Decision Models
FastExcel 2.2 Beta now available
www.DecisionModels.com

 

[MDW]

Thanks.

I asked him to send me the file, and he said he couldn't because it used an
add-in that I didn't have. That helped clarify things, and I ended up finding
info about the perfomance issue here:

http://www.decisionmodels.com/calcsecretsj.htm#performance ("Automatic and
Function key Calculation slower than VBA calculation")

 

[Jim Thomlinson]

Thanks... I neglected to mention that one... I was assuming it was a smart
calc issue. By the way, excellent web site.