Wait for Calculations to Complete

[DBCox]

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:

    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

 

[Matt Rogers]

Hello, try:

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

 

[DBCox]

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).

 

[DBCox]

Any suggestions? Thanks!

 

[Siyanna]

Does this work


While Application.CalculationState = xlCalculating
DoEvents
Wend

 

[DBCox]

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]

Not sure, hopefully one of the MVP gurus can help you

 

[DBCox]

Hopefully so. Thank you for your suggestion though!

 

[NdNoviceHlp]

Why not just control the calculations. HTH. Dave

'start of code
Application.Calculation = xlManual
'tell XL to calculate
Application.Calculate
'end of code
Application.Calculation = xlAutomatic

 

[DBCox]

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.