Application.Calculate vs. F9

M

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.
 
M

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?
 
J

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.
 
J

Jim Thomlinson

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top