Excel VBA 等待 Application.CalculateFull
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26354753/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA wait for Application.CalculateFull
提问by chandanrs
When I call Application.CalculateFulland 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.
当我调用Application.CalculateFull并在下一条语句处放置断点时，我在状态栏中看到处理器在一段时间后处理但已经到达下一行代码。
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 CalculateFullfor the worksheet, it corrects it but doesn't wait.
我查看了论坛，人们建议使用DoEvents. 在我的情况下，这是一个问题，因为工作表有很多公式，尽管公式是正确的，但其中一些在第一次运行后显示错误的结果，所以当我调用CalculateFull工作表时，它会更正它但不会等待。
If I use DoEvents, the CalculateFulldoesn't update the worksheet.
如果我使用DoEvents，CalculateFull则不会更新工作表。
So I am in a bind.
所以我陷入了困境。
How can I wait for the processor to complete processing on Application.CalculateFulland only after that go to next line of code?
如何等待处理器完成处理，Application.CalculateFull然后才转到下一行代码？
I used Application.Waitalso but it seems to hold the processor and once released it starts calculation then, which doesn't solve my problem.
我Application.Wait也使用过，但它似乎可以容纳处理器，一旦释放它就会开始计算，这并不能解决我的问题。
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.calculatefullRebuildtakes 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.
这是我现在使用的代码。当我将断点放在下一行代码时，我看到这Application.calculatefullRebuild需要几秒钟并转到下一行，当我现在检查活动表时，又过了 2-3 秒，我在状态栏计算（4 个处理器）中看到： xx%，之后工作表值更改为正确的值。
This is my issue. When Application.CalculateFullRebuildcompletes 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.
这是我的问题。当Application.CalculateFullRebuild完成并转到下一行时，我的工作表结果仍然不准确，只有在处理器计算状态栏消息几秒钟后，工作表才会更新。
How can I wait for this to complete, otherwise the remaining code will pick up wrong results.
我怎么能等到这个完成，否则剩下的代码会得到错误的结果。
回答by Brad
So I believe that the question herehas the answer you are looking for (modified slightly below to put it in a while loop):
所以我相信这里的问题有你正在寻找的答案（在下面稍作修改以将其放入 while 循环中）：
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.
在开始计算过程后立即将上述循环粘贴到您的代码中应该是所需要的。
回答by DrMarbuse
I used the same solution as Bradextended 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.
我使用了与Brad相同的解决方案，通过计时器扩展来通知用户是否出现问题。我发现在没有触发 Application.Calculate 命令（或CalculateFull）的情况下，excel 并没有通过 DoEvents 进行计算。
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
