Pause Macro for Spreadsheet Calculations to Finish

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
Hi,

I have a line of code in a macro that allows the macro to pause for 20 seconds in order for calculations to finish in the spreadsheet before going on to the next step. My code is:

Code:
Application.Wait Now + TimeValue("00:00:20")

Is there any way of replacing this line with something that pauses for the exact amount of time it takes for the spreadsheet calcs to finish. I don't really like using the fixed amount to time. 20 seconds may be too little or too much depending on the sheet size.

Thanks in Advance.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Just literally stick that as a line where you want the pause to be, i.e.
Code:
lines of code
DoEvents
more lines of code

I know this works for pausing code while stuff happens like formatting and resizing objects, etc, so I would imagine it should work the same with calculations.
 
Upvote 0
Why do you believe you need to "pause" your code for calculations to complete? If you include a Application.Calculate (or one of its variants) XL will do whatever it needs to do before your code moves to the next statement.

Hi,

I have a line of code in a macro that allows the macro to pause for 20 seconds in order for calculations to finish in the spreadsheet before going on to the next step. My code is:

Code:
Application.Wait Now + TimeValue("00:00:20")

Is there any way of replacing this line with something that pauses for the exact amount of time it takes for the spreadsheet calcs to finish. I don't really like using the fixed amount to time. 20 seconds may be too little or too much depending on the sheet size.

Thanks in Advance.
 
Upvote 0
Hi Lewiy and Tushar,

Thanks for the advice.

Both variations worked.

I did some internet research this morning on pausing a macro and I guess I got myself fixated on using the Wait method.....so there was no real reason that I believed I needed to "pause" the macro besides just wanting the calcs to happen first.

I didn't notice any speed difference really so I am not sure is one method (DoEvents or Application.Calculate) more efficient/better than the other?
 
Upvote 0
I didn't notice any speed difference really so I am not sure is one method (DoEvents or Application.Calculate) more efficient/better than the other?

My personal thoughts on this (speculation rather than fact) are that DoEvents will simply allow everything to finish whereas Application.Calculate will force a new calculation. Perhaps a speed difference would be noticable if there were a huge number of calculations to be done (or perhaps not, who knows :) )
 
Upvote 0
Okay,

I will comment one out and every once in a while when get bigger files I will toggle which command to use and compare.

Thanks for the help and advice.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

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
Back
Top