Pause to Calc

[sresnick]

I've got vba code that copies formulas into a range, calculates, then
copy/paste-values. Since there are lots of formulas, the calculation
takes 10 or 15 seconds, during which time the cursor shows an
hourglass. If the user just waits during this time, it works fine. But
if the user starts clicking around, the calculation doesn't work, and
the code finishes with bad values. It's as if the code changes the
formulas to values before the calculation is complete.

How do I make it so that the calculation is complete, regardless of
what the user does? One thing I'm thinking of is to have a pop up
message of "Please be patient..." or whatever to appear while the
calculation is going on. Would this help? Though I'm a skilled user of
vba, I have never done this specifically (having a pop up appear while
code runs in the background) and don't know how to do it.

Many thanks in advance.

Stuart
http://home.comcast.net/~sresnick2/mypage.htm

 

[Corey]

Have a look here.
http://j-walk.com/ss/excel/tips/tip34.htm
Should be right up your alley for what you are after.

Corey....

 

[NickHK]

Stuart,
May be sandwich your code in Application.Interactive=False/True statements.

Make sure you use an error handling with Application.Interactive=True, just
in case.

NickHK

 

[Joerg]

I've got vba code that copies formulas into a range, calculates, then
copy/paste-values. Since there are lots of formulas, the calculation
takes 10 or 15 seconds, during which time the cursor shows an
hourglass. [snip]

You should avoid calculation of formulas during execution of your code:
Application.Calculation = xlCalculationManual
(your code goes here)
Application.Calculation = xlCalculationAutomatic

Joerg

 

[sresnick]

Many thanks to everyone who helped. The suggestion from NickHK below
worked fine.

In normal Excel use, if you're in manual calc mode, and you hit the
calc key, it will start to calc everything, but if you click on a cell
during this time, the calc will stop before it's complete. This seems
to be what happened with my code. When the code reached the "calculate"
line, if the user happened to click, the code would continue with the
next line without the calculation completing, resulting in bad results
when the formulas get replaced by values.

I sandwiched my "Calculate" line between the "application.interactive"
statements, and now if the user clicks during the code execution, it's
no problem.

Stuart