Start Process, Wait Until Finished, Resume

[BBrandt]

I'm having some trouble with a workbook that I've set to only calculate when the user clicks a button to force a total calculation with an

Application.CalculateFullRebuild

line. The trouble is *I think* that the code continues to progress while the sheet is rebuilding/recalculating, so it's conceivable that the code arrives at a point where it's manipulates data/ranges *before* they've finished recalculating. This is bad.

Is there a way to make the code wait until the application is finished calculating before resuming the next bit of code? Alternately, is there a way to start recalculating, pause for x seconds, and then continue? This is a new area of Excel/VBA for me so any help is appreciated (I'm still learning). Thanks!

- Bill

 

[RoryA]

You could try a loop:

Do Until Application.CalculationState = xlDone
   DoEvents
Loop

 

[ajwillshire]

Hi Bill,

Only speaking from my personal experience, but I've always found that when you do a calculation then VBA waits for it to finish, although I normally use

Application.Calculate

Often I'll make sure I've switched off the calculation mode in the code with

Application.Calculation = xlCalculationManual

and use

Application.Calculate to trigger the recalcs at an appropriate point.

Application.Calculation = xlCalculationAutomatic

sets it back to automatic calculation.

Hope this helps,
Andrew

 

[BBrandt]

Would it have mattered that I had been previously trying to force the calculation on a sheet-by-sheet basis? For example, users input data on Sheet1, which feeds a ton of data to Sheet2, where the majority of the calculating is actually happening. The original methodology was:

Application.Calculation = xlCalculationManual
Sheet1.Calculate
Sheet2.Calculate
'rest of code

The reason that I suspect that it wasn't letting the sheets finish calculating before moving on is that I could occasionally get different results if I clicked the button and let it run its course than if I did a Ctrl-Shift-Alt-F9 *before* clicking the button, and in testing the difference was that Ctrl-Shift-Alt-F9 *always* made Sheet2 totally recalculate, while sometimes that wasn't the case if I clicked the button.

Rorya - Thanks, I think that'll work. How would I implement that though? Mainly asking specifically where I would put the CalculateFullRebuild line and where the rest of the code goes. I'm not super familiar with Do loops.

Would it be something like

Application.CalculateFullRebuild
 
Do Until Application.CalculationState = xlDone
   DoEvents
Loop
 
'rest of code?

Sorry if that seems elementary... I find that simple syntax errors are where I get tripped up most frequently, and that the forums are more conducive to improving that than the help files. Thanks again.

 

[RoryA]

Yes - just where you put it!

 

[BBrandt]

Fantastic! Thanks so much.