2

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.

feetwet
  • 3,248
  • 7
  • 46
  • 84
chandanrs
  • 19
  • 1
  • 1
  • 4
  • could you maybe provide an image of what happens or something else so we can see what you mean – Goos van den Bekerom Oct 14 '14 at 07:21
  • AFAIK, `.CalculateFull` isn't async so with a single-threaded environment like VBA it's very unlikely that the code jumps to the next line without finishing calculation :/ strange at least –  Oct 14 '14 at 07:24
  • Does your workbook use asynchronous functions? And why do you have to use CalculateFull rather than Calculate? – Charles Williams Oct 14 '14 at 07:34
  • waittime (1000) SendKeys "+^%{F9}", True Application.CalculateFullRebuild 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. When Application.CalculateFullRebuild completes and goes to the next line, worksheet results are still not accurate and only after a couple of secs the processors calculating statusbar message, the worksheet is updated. How to fix this – chandanrs Oct 14 '14 at 07:47
  • Application.Calculate works but the results in one of the worksheet which has many formula does not give correct results and then reexecute Application Rebuild helps to correct the value but macro isn't waiting for the process to finish and going to next line – chandanrs Oct 14 '14 at 07:49
  • Does your workbook use any worksheet functions that are not native Excel Functions? If so what are they? – Charles Williams Oct 14 '14 at 10:16

2 Answers2

1

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.

Community
  • 1
  • 1
Brad
  • 272
  • 2
  • 7
  • 22
0

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
Community
  • 1
  • 1
DrMarbuse
  • 804
  • 11
  • 30