vba 如何确定计算是否完成，或检测中断的计算？

How do I determine whether calculation was completed, or detect interrupted calculation?

提问by BenTobin

I have a rather large workbook that takes a really long time to calculate. It used to be quite a challenge to get it to calculate all the way, since Excel is so eager to silently abort calculation if you so much as look at it.

我有一个相当大的工作簿，需要很长时间来计算。让它一直计算过去是一个相当大的挑战，因为如果你看它，Excel 非常渴望默默地中止计算。

To help alleviate the problem, I created some VBA code to initiate the the calculation, which is initiated by a form, and the result is that it is not quite as easy to interrupt the calculation process, but it is still possible. (I can easily do this by clicking the close X on the form, but I imagine there are other ways)

为了帮助缓解这个问题，我创建了一些 VBA 代码来启动计算，它是由一个表单启动的，结果是中断计算过程并不那么容易，但仍然可以。（我可以通过单击表单上的关闭 X 轻松完成此操作，但我想还有其他方法）

Rather than taking more steps to try and make it harder to interrupt calculation, I'd like to have the code detect whether calculation is complete, so it can notify the user rather than just blindly forging on into the rest of the steps in my code. So far, I can't find any way to do that.

与其采取更多步骤来尝试使计算更难中断，我希望让代码检测计算是否完成，以便它可以通知用户，而不是盲目地继续执行代码中的其余步骤. 到目前为止，我找不到任何方法来做到这一点。

I've seen references to Application.CalculationState, but the value is xlDone after I interrupt calculation, even if I interrupt the calculation after a few seconds (it normally takes around an hour).

我已经看到对 Application.CalculationState 的引用，但在我中断计算后，该值是 xlDone，即使我在几秒钟后中断计算（通常需要大约一个小时）。

I can't think of a way to do this by checking the value of cells, since I don't know which one is calculated last. I see that there is a way to mark cells as "dirty" but I haven't been able to find a way to check the dirtiness of a cell. And I don't know if that's even the right path to take, since I'd likely have to check every cell in every sheet.

我想不出通过检查单元格的值来做到这一点的方法，因为我不知道最后计算的是哪个。我看到有一种方法可以将单元格标记为“脏”，但我无法找到一种方法来检查单元格的脏度。而且我不知道这是否是正确的路径，因为我可能必须检查每张纸中的每个单元格。

The act of interrupting calculation does not raise an error, so my ON ERROR doesn't get triggered.

中断计算的行为不会引发错误，因此不会触发我的 ON ERROR。

Is there anything I'm missing? Any ideas?

有什么我想念的吗？有任何想法吗？

Any ideas?

有任何想法吗？

回答by Tre Jones

I think the trick you need to implement (if you're application runs in Excel 2007 or later) is to handle this with the Application.AfterCalculate event, which is raised after both calculation is complete andthere are no outstanding queries.

我认为您需要实现的技巧（如果您的应用程序在 Excel 2007 或更高版本中运行）是使用 Application.AfterCalculate 事件处理此问题，该事件在两个计算完成并且没有未完成的查询后引发。

If you've never worked with events in VBA before, there is a good overview from cpearson.com.

如果您以前从未在 VBA 中使用过事件，那么cpearson.com 上有一个很好的概述。

回答by Brenton Frahn

The (MSDN) solution by Charles Williams above worked for me where I had 1000's of VLOOKUP's that neeeded to recalculate as the code was changing the lookup value because of an iteration loop. Results were skewed as calculations were not running to 100% completion.

上面 Charles Williams 的 (MSDN) 解决方案对我有用，我有 1000 个 VLOOKUP 需要重新计算，因为代码由于迭代循环而改变了查找值。由于计算没有运行到 100% 完成，结果是有偏差的。

At the beginning of my subroutine the code executes

在我的子程序开始时，代码执行

Application.Calculation = xlManual

This eliminated unnecessary calculations by Excel until I was ready.

在我准备好之前，这消除了 Excel 不必要的计算。

Now at the critical point the code executes

现在在代码执行的临界点

Application.Calculation = xlAutomatic
ThisWorkbook.ForceFullCalculation = True
Application.Calculate

Having forced Excel to perform a full calculation, the code could then saved the result and move onto the next iteration ... but before doing so

强制 Excel 执行完整计算后，代码可以保存结果并进入下一次迭代……但在此之前

ThisWorkbook.ForceFullCalculation = False
Application.Calculation = xlManual

Remembering at the very end

记住最后

Application.Calculation = xlAutomatic  

回答by Simple_One

Perhaps the following would work:

也许以下方法可行：

Do Until Application.CalculationState = xlDone
    DoEvents
Loop

Can't say I've tested it, nor that I know how robust the functionality of Application.CalculationState really is to determine whether 'complete' calculation occurred, as opposed to something interrupting the process and flagging the calculation state as done.

不能说我已经测试过它，也不能说我知道 Application.CalculationState 的功能在确定是否发生“完整”计算方面的功能有多强大，而不是中断过程并将计算状态标记为已完成。

回答by William

Private sub SomeCodeThatGeneratesFormulas
    Application.Calculation = xlCalculation.xlCalculationManual
    '...Some formulas are copied here'
    Application.OnTime DateTime.DateAdd ("s",.01,DateTime.Now), "Module1.CalculateFullRebuildAndSubsequentSteps" 'By using Application.OnTime, this method will be called in a way that locks the end-user out of providing inputs into Excel until the calculation itself is complete.
end sub

public sub CalculateFullRebuildAndSubsequentSteps
    Application.CalculateFullRebuild
    '...Do next steps, i.e. paste as values'
end sub

回答by Adrian

On the status bar, right hand side, it will say Calculating (N processors) X%(where Nis the number of processors on your computer and X%is how much it has completed) when recalculating. If you don't see text there, it's not recalculating.

在状态栏上，右手边，它会说Calculating (N processors) X%（其中N是您的计算机上的处理器数量和X%是多少，它已经完成）重新计算时。如果您在那里没有看到文本，则不会重新计算。

I'm using Office 2010, but it should be there in all versions. It's just kinda subtle so it's easy to miss.

我使用的是 Office 2010，但它应该在所有版本中都有。它只是有点微妙，所以很容易错过。

回答by JAGAnalyst

I think I'm hearing that you need a way to monitor whether each step within the calculations being performed was executed.

我想我听说您需要一种方法来监视正在执行的计算中的每个步骤是否已执行。

Assuming that you're not interested in re-engineering the workbook to use methods that are easier to track than spreadsheet calculations (such as volatile calculations within VBA or Pivot Tables), this may work for you:

假设您对重新设计工作簿以使用比电子表格计算更容易跟踪的方法（例如 VBA 或数据透视表中的易失性计算）不感兴趣，这可能对您有用：

Within VB, you can utilize .EnableCalculationand .Calculateto set an entire worksheet as "Dirty" (needing calculation) and then recalculate. The key difference between this and your current process is that we will perform these actions one worksheet at a timein manual mode. By initiating the calculations one worksheet at a time from within VBA, you will be able to perform additional intermediate actions that can be used to track how far you got in the calculation process.

在VB中，你可以利用.EnableCalculation和.Calculate设置整个工作表为“脏”（需要计算）和然后重新计算。这与您当前的流程之间的主要区别在于，我们将在手动模式下一次一张工作表执行这些操作。通过从 VBA 中一次一个工作表开始计算，您将能够执行额外的中间操作，这些操作可用于跟踪您在计算过程中的进展情况。

Please note that this approach assumes a fairly linear workbook structure such that your workbook will produce the correct results if we first recalculate Sheet1, then Sheet2, Sheet3, and so on, in whatever order you wish. If your formula dependencies are more "spaghetti" than linear, this probably won't work for you. It also assumes you are working in Excel 2000 or later.

请注意，此方法假设了一个相当线性的工作簿结构，这样，如果我们按您希望的任何顺序首先重新计算 Sheet1、Sheet2、Sheet3 等，您的工作簿将产生正确的结果。如果您的公式依赖项比线性更“意大利面”，这可能对您不起作用。它还假定您在 Excel 2000 或更高版本中工作。

For example, you could write a VBA routine that accomplishes the following steps. You will need to know your dependencies in order to know which calculations must come before others, and start with the worksheet in a "clean" state where no calculations are currently pending.

例如，您可以编写一个 VBA 例程来完成以下步骤。您需要了解您的依赖关系，以便知道哪些计算必须先于其他计算，并从处于“干净”状态的工作表开始，其中当前没有任何计算未决。

Step 1: Set the active sheet to the first worksheet where recalculation is needed

步骤 1：将活动工作表设置为需要重新计算的第一个工作表

Step 2: Set the calculation mode to manual as follows:

步骤 2：将计算模式设置为手动，如下所示：

    Application.Calculation = xlCalculationManual

Step 3: "Dirty" the entire active sheet as follows:

第 3 步：“弄脏”整个活动表，如下所示：

    With ActiveSheet

    .EnableCalculation = False

    .EnableCalculation = True

Step 4: Initiate a recalculation for this worksheet only (not the entire workbook) using:

第 4 步：仅针对此工作表（而非整个工作簿）启动重新计算：

    .Calculate

    End With

Note that if the calculation mode were set to automatic, Step 3 would initiate a re-calculation across the entire workbook. By using manual mode and With, we are constraining that calculation to the current sheet.

请注意，如果计算模式设置为自动，则步骤 3 将启动对整个工作簿的重新计算。通过使用手动模式和 With，我们将该计算限制在当前工作表中。

Now you have dirtied and re-calculated the first sheet (hurray!). Now, by embedding Steps 3 and 4 above into a For/Each or For/Next loop, you can repeat the process for each worksheet in your workbook. Again, make sure you know the order in which your worksheets need to be calculated (if an order is needed).

现在您已经弄脏并重新计算了第一张纸（万岁！）。现在，通过将上述第 3 步和第 4 步嵌入 For/Each 或 For/Next 循环中，您可以对工作簿中的每个工作表重复该过程。同样，请确保您知道需要计算工作表的顺序（如果需要订单）。

Now for the big finish - by creating a counter variable within your loop, you can track how far you got in the calculations by updating your counter variable value each time you complete a worksheet calculation. For example, after you recalculate a worksheet, you can set the counter value to current value + 1 and store the results either in a global variable (so that it will persist even after your VBA routine ends), or in a cell within your worksheet. That way, you can check this value later to see how many worksheets were updated before the calculations finished or were interrupted.

现在大功告成——通过在循环中创建一个计数器变量，您可以通过在每次完成工作表计算时更新计数器变量值来跟踪计算的进展情况。例如，在重新计算工作表后，您可以将计数器值设置为当前值 + 1 并将结果存储在全局变量中（这样即使在 VBA 例程结束后它也将持续存在），或存储在工作表中的单元格中. 这样，您可以稍后检查此值，以查看在计算完成或中断之前更新了多少工作表。

If you have relatively few worksheets in your workbooks, the same approach could be applied to one range at a time rather than a sheet.

如果您的工作簿中的工作表相对较少，则可以一次将相同的方法应用于一个范围而不是工作表。

I won't go into detail about how to construct a "counter", loops, or global variables here, but if needed, this information can be easily found using your favorite search engine. I would also highly recommend re-enabling automatic calculations once you are done as it is easy to forget that it's been set to manual mode.

我不会在这里详细介绍如何构造“计数器”、循环或全局变量，但如果需要，可以使用您喜欢的搜索引擎轻松找到这些信息。我还强烈建议您在完成后重新启用自动计算，因为很容易忘记它已设置为手动模式。

I hope this works for you - for more information on calculation modes and recalculation, this is a helpful link: http://msdn.microsoft.com/en-us/library/bb687891.aspx

我希望这对您有用 - 有关计算模式和重新计算的更多信息，这是一个有用的链接：http: //msdn.microsoft.com/en-us/library/bb687891.aspx

回答by mischab1

I've never actually used it but I think this might work to prevent calculation from being interrupted.

我从未真正使用过它，但我认为这可能有助于防止计算被中断。

Application.CalculationInterruptKey = xlNoKey

Application.CalculationInterruptKey = xlNoKey

回答by Apostolos55

Arrays in Excel can be a bit stupid. That is that in order to accomplish some tasks people avoid to use intermediate columns/rows to store (temporary) data, so arrays have to recalculate staff from the beginning every time, thus getting really slow. My Suggestion would be:

Excel 中的数组可能有点愚蠢。也就是说，为了完成一些任务，人们避免使用中间列/行来存储（临时）数据，因此数组每次都必须从头开始重新计算人员，因此变得非常慢。我的建议是：

1. fix arrays to avoid multiple searches. Use hidden cells or even hidden sheets
2. Avoid using A:A and rather use A1:A1000 specially in excel 2007 or later
3. use formulas to equal zero or error (ex: NA()) while previous items aren't calculated, so you can clearly see if an operation is done at all.
4. some VBA could be used to inject formulas in place one step at a time, perform calculations, then proceed to next step, but this could mean lots of work...

1. 修复数组以避免多次搜索。使用隐藏的单元格甚至隐藏的工作表
2. 避免使用 A:A，而是在 excel 2007 或更高版本中专门使用 A1:A1000
3. 使用公式等于零或错误（例如：NA()），而不会计算先前的项目，因此您可以清楚地看到某个操作是否已完成。
4. 一些 VBA 可用于一次一个步骤地注入公式，执行计算，然后继续下一步，但这可能意味着大量工作......