Start Process, Wait Until Finished, Resume

BBrandt

Board Regular
Joined
Jul 14, 2008
Messages
155
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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,729
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You could try a loop:
Code:
Do Until Application.CalculationState = xlDone
   DoEvents
Loop
 

ajwillshire

New Member
Joined
Mar 4, 2009
Messages
31
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

Board Regular
Joined
Jul 14, 2008
Messages
155
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:

Code:
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
Code:
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,729
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Yes - just where you put it! :)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,026
Messages
5,639,616
Members
417,101
Latest member
amoverton2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top