CODEWITHSUNDEEP
excelvba
chandanrs
chandanrs

Reputation: 19

Excel VBA wait for Application.CalculateFull

When I call Application.CalculateFull and put a breakpoint at the next statement, I see in the status bar that processor processing after a while but has already reached the next line of code.

I checked the forum and people suggested to use DoEvents. In my case that's a problem as the worksheet has many formulas and some of them show wrong result after first run though formula is correct, so when I call the CalculateFull for the worksheet, it corrects it but doesn't wait.

If I use DoEvents, the CalculateFull doesn't update the worksheet.

So I am in a bind.

How can I wait for the processor to complete processing on Application.CalculateFull and only after that go to next line of code?

I used Application.Wait also but it seems to hold the processor and once released it starts calculation then, which doesn't solve my problem.

waittime (1000)
SendKeys "+^%{F9}", True
Application.CalculateFullRebuild

This is the code I use for now. When I put the breakpoint at the next line of code, I see that the Application.calculatefullRebuild takes a few secs and goes the next line and when I check the activesheet now, in another 2-3 sec I see in status bar Calculating (4 processors):xx% and after which the worksheet values are changed to the right values.

This is my issue. When Application.CalculateFullRebuild completes and goes to the next line, my worksheet results are still not accurate and only after a couple of secs the processors calculating statusbar message, the worksheet is updated.

How can I wait for this to complete, otherwise the remaining code will pick up wrong results.

Upvotes: 2

Views: 14712

Answers (2)

DrMarbuse
DrMarbuse

Reputation: 870

I used the same solution as Brad extended by a timer to inform the user if things go wrong. I found that without triggering the Application.Calculate command (or CalculateFull) excel did not proceed calculating by the DoEvents allone.

Const MAXTIME_S = 10
Dim t As Double
t = Timer()

If Application.CalculationState <> xlDone Then Application.Calculate

Do While Application.CalculationState <> xlDone
    DoEvents
    If Timer() - t > MAXTIME_S Then Exit Do
Loop

If Application.CalculationState <> xlDone Then
    MsgBox "Calculation not done. Please restart this Macro.", vbInformation + vbOKOnly, "Aktualisieren"
    Exit Sub
End If

Upvotes: 0

Brad
Brad

Reputation: 272

So I believe that the question here has the answer you are looking for (modified slightly below to put it in a while loop):

Do While Application.CalculationState <> xlDone
     DoEvents
Loop

Sticking the above loop in your code right after starting the calculation process should be all that is required.

Upvotes: 1

Related Questions