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代码来启动由表单发起的计算，结果是中断计算过程并不那么容易，但仍然有可能。(我可以通过单击表单上的close 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).
我见过应用程序的引用。计算状态，但是在中断计算之后，值是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错误不会被触发。
Is there anything I'm missing? Any ideas?
有什么我遗漏的吗?什么好主意吗?
Any ideas?
什么好主意吗?
8 个解决方案
#1
2
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 and there are no outstanding queries.
我认为您需要实现的技巧(如果您的应用程序在excel2007或更高版本中运行)是使用应用程序来处理这个问题。AfterCalculate事件，在两个计算完成后进行，并且没有未查询。
If you've never worked with events in VBA before, there is a good overview from cpearson.com.
如果您以前从未使用过VBA中的事件，那么cpearson.com提供了一个很好的概述。
#2
1
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或Pivot表中的volatile计算)，这可能对您有用:
Within VB, you can utilize .EnableCalculation and .Calculate to 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 time in 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和. compute将整个工作表设置为“Dirty”(需要计算)，然后重新计算。这与您当前流程的关键区别在于，我们将在手动模式下每次执行一个工作表。通过在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。
#3
1
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
#4
0
I've never actually used it but I think this might work to prevent calculation from being interrupted.
我从未用过它，但我认为它可以防止计算被中断。
Application.CalculationInterruptKey = xlNoKey
应用程序。CalculationInterruptKey = xlNoKey
#5
0
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.
不能说我已经测试过它，也不能说我知道应用程序的功能有多健壮。CalculationState实际上是要确定是否发生了“完整”的计算，而不是中断进程并将计算状态标记为已完成。
#6
0
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
#7
0
On the status bar, right hand side, it will say Calculating (N processors) X% (where N is 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.
在状态栏的右侧，当重新计算时，它会显示(N个处理器)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，但是应该有所有版本。这有点微妙，所以很容易错过。
#8
-1
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中的数组可能有点傻。也就是说，为了完成一些任务，人们避免使用中间的列/行来存储(临时的)数据，所以数组必须从头开始重新计算工作人员，从而变得非常慢。我的建议是:
- fix arrays to avoid multiple searches. Use hidden cells or even hidden sheets
- 修复数组以避免多次搜索。使用隐藏的单元格甚至隐藏的床单
- Avoid using A:A and rather use A1:A1000 specially in excel 2007 or later
- 避免使用A:A，而是使用A1:A1000，特别是在excel 2007或以后。
- 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.
- 使用公式为零或错误(ex: NA())，而以前的项目没有计算，因此您可以清楚地看到是否已经完成了操作。
- 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...
- 一些VBA可以用来一次一步地插入公式，执行计算，然后继续下一步，但是这可能意味着大量的工作……
#1
2
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 and there are no outstanding queries.
我认为您需要实现的技巧(如果您的应用程序在excel2007或更高版本中运行)是使用应用程序来处理这个问题。AfterCalculate事件，在两个计算完成后进行，并且没有未查询。
If you've never worked with events in VBA before, there is a good overview from cpearson.com.
如果您以前从未使用过VBA中的事件，那么cpearson.com提供了一个很好的概述。
#2
1
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或Pivot表中的volatile计算)，这可能对您有用:
Within VB, you can utilize .EnableCalculation and .Calculate to 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 time in 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和. compute将整个工作表设置为“Dirty”(需要计算)，然后重新计算。这与您当前流程的关键区别在于，我们将在手动模式下每次执行一个工作表。通过在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。
#3
1
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
#4
0
I've never actually used it but I think this might work to prevent calculation from being interrupted.
我从未用过它，但我认为它可以防止计算被中断。
Application.CalculationInterruptKey = xlNoKey
应用程序。CalculationInterruptKey = xlNoKey
#5
0
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.
不能说我已经测试过它，也不能说我知道应用程序的功能有多健壮。CalculationState实际上是要确定是否发生了“完整”的计算，而不是中断进程并将计算状态标记为已完成。
#6
0
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
#7
0
On the status bar, right hand side, it will say Calculating (N processors) X% (where N is 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.
在状态栏的右侧，当重新计算时，它会显示(N个处理器)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，但是应该有所有版本。这有点微妙，所以很容易错过。
#8
-1
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中的数组可能有点傻。也就是说，为了完成一些任务，人们避免使用中间的列/行来存储(临时的)数据，所以数组必须从头开始重新计算工作人员，从而变得非常慢。我的建议是:
- fix arrays to avoid multiple searches. Use hidden cells or even hidden sheets
- 修复数组以避免多次搜索。使用隐藏的单元格甚至隐藏的床单
- Avoid using A:A and rather use A1:A1000 specially in excel 2007 or later
- 避免使用A:A，而是使用A1:A1000，特别是在excel 2007或以后。
- 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.
- 使用公式为零或错误(ex: NA())，而以前的项目没有计算，因此您可以清楚地看到是否已经完成了操作。
- 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...
- 一些VBA可以用来一次一步地插入公式，执行计算，然后继续下一步，但是这可能意味着大量的工作……