Delaying VBA processing to allow xls to catch up

[RafFiniert]

I'm happy to read the answer somewhere else if this has already been answered. However, so far, I've been unable to find it.

Problem:
VBA's speed of processing can outstrip the pace of xls processing, necessitating some method of delay in the VBA code

Conventional wisdom:
Set these...

Application.DisplayAlerts = False
Application.DisplayStatusBar = False
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

Then manually recalculate sheets as needed.

If the VBA code is still proceeding before the manual recalculations complete, use Wait, Delay, or Loop to force a delay.

I have tried these with varying degrees of success. What bothers me is that waiting for a definitive period of time seems so inefficient--since you have to account for a worst-case scenario. (And the worst-case scenario might vary widely--especially if you're waiting for something to return from a webservice call, which also introduces the speed of your internet connection as a variable.)

It seems like there should be something that says to "wait until processing complete." So far, I haven't found it.

The only thing I have found that works is to do the manual recalculations and then ThisWorkbook.Save (and, yes, I understand that if Application.CalculateBeforeSave is set to True, the manual recalculations are redundant). It seems that the VBA code won't proceed until the save step is complete.

Is there a more elegant solution?

 

[MARK858]

Maybe...

Application.Calculate
Do Until Application.CalculationState = xlDone
   DoEvents
Loop

 

[Burrgogi]

Mark that gives me an idea for one of my VBA projects. I've got a spreadsheet containing some VLOOKUP formulas and it makes some calculations based on the values that it returns.

Will the IF statement also work?

Application.Calculate
If Application.CalculationState = xlDone, Then
do some stuff

 

[MARK858]

If Application.CalculationState= xlDone, Then

Yes it would work if using it as a Boolean test, but no comma in the line and you should tell it what not to do if it is still calculating or pending

 

[Burrgogi]

Yes it would work if using it as a Boolean test, but no comma in the line and you should tell it what not to do if it is still calculating or pending

ah yes. How would I tell it to WAIT until it's done finishing?

 

[MARK858]

You wouldn't with a Boolean test, it just tells you it has either finished or not. The code in Post 2 won't move on until calculations are done.

 

[RafFiniert]

Thanks, Mark. That made a measurable (and real) difference. I have 17 uses cases that I'm using for regression testing of what I'm developing. The greatest reduction in processing time was 82%; the least was 42%; average was 52%.

 

[MARK858]

You're welcome