Wait for Calculations to Complete

DBCox

Board Regular
Joined
Jul 5, 2012
Messages
52
Hi everyone,

I have an interesting problem. I thought Excel would not advance to the next line of the VBA code until all calculations were complete (assuming automatic calculations are turned on). However, that doesn't seem to be the case. So, I tried to add a while loop to allow it to run ALL the calculations before proceeding, but it isn't working.

I tried:
Code:
    While Application.CalculationState = xlCalculating
    Application.Wait (500)
    Wend

Also, I tried telling the workbook to wait a given amount of time to insure all the calculations completed, but that didn't seem to work either. It was as if it completely stopped everything, including calculations, until the time passed, then picked up where it left off.

How can I make sure all the calculations have been run before preceding to the next step in the code?

Thanks!

David
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Matt Rogers

Well-known Member
Joined
Sep 23, 2011
Messages
1,030
Hello, try:

Application.Wait Now() + TimeValue("0:00:10") '10 seconds
 

DBCox

Board Regular
Joined
Jul 5, 2012
Messages
52
Matt,

Thanks for the suggestion. Unfortunately, it did not work. I even switched it to 2 minutes rather than 10 seconds.

At one point, my macro performs a "save as" on a separate Workbook it opens. It asks if I want to overwrite a file. At that point, I can see if the data has updated. When I click cancel, the macro faults and goes into debug mode. If I exit debug mode, the workbook proceeds with the calculations. It will not proceed with the calculations until the macro stops (either because I stop it, or allow it to run through).
 

Siyanna

Well-known Member
Joined
Nov 7, 2011
Messages
1,146
Does this work


While Application.CalculationState = xlCalculating
DoEvents
Wend
 

DBCox

Board Regular
Joined
Jul 5, 2012
Messages
52
Siyanna,

Thank you for the suggestion. I tried it, but unfortunately, received the same results. It floors me that Excel will move on to the next line in the code while calculations are still occurring.

Any other ideas?

Could it be that I have multiple workbooks and sheets, and multiple associated calculations? Maybe Excel runs the first set, and quickly moves on, before it realizes that the calculations from the first set triggered calculations in another. the problem is, I have no idea how many iterations it takes, if that is the case!
 

Siyanna

Well-known Member
Joined
Nov 7, 2011
Messages
1,146
Not sure, hopefully one of the MVP gurus can help you
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,485
Why not just control the calculations. HTH. Dave
Code:
'start of code
Application.Calculation = xlManual
'tell XL to calculate
Application.Calculate
'end of code
Application.Calculation = xlAutomatic
 

DBCox

Board Regular
Joined
Jul 5, 2012
Messages
52
Dave,

Thanks for the info. I have tried that too. My first setup turned off calculations while certain tasks were being performed, and then turned them back to automatic. When I noticed the problem of the calculations not completing, I tried the options above, to not avail. So, I then removed the code to turn calculations off and back on, but I received the same result.

I even tried turning on the manual calculations, and then telling it to calculate each time, rather than turning the automatic calculations back on.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,060
Messages
5,466,343
Members
406,475
Latest member
suechetty

This Week's Hot Topics

Top