vba 如何计算整个工作簿，而不是所有打开的工作簿？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/43806643/
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
How can I calculate an entire workbook, but not all open workbooks?
提问by Steven G
Is there a way to calculate entire workbook but not all open workbooks in VBA?
有没有办法在 VBA 中计算整个工作簿而不是所有打开的工作簿？
I know about
我知道
worksheet.Calculate
but it will only do 1 sheet. I also know about
但它只会做 1 张。我也知道
Application.CalculateFull
But I think this recalculate all open workbooks at the same time.
但我认为这会同时重新计算所有打开的工作簿。
Is it possible to do only 1 workbook?
是否可以只做 1 个工作簿？
Edit:I get the approach:
编辑：我得到了方法：
For Each wks In ActiveWorkbook.Worksheets
wks.Calculate
Next
but this is very dangerous if sheets have links between them, then the order of calculation is important. I know I could find the exact sequence and apply it, problem is that on very large workbooks this can get somewhat tricky
但是如果工作表之间有链接，这是非常危险的，那么计算顺序很重要。我知道我可以找到确切的顺序并应用它，问题是在非常大的工作簿上这可能会变得有些棘手
采纳答案by Steven G
After some investigation and testing, selecting all sheets first and calculating afterward works :
经过一些调查和测试，首先选择所有工作表，然后计算工作：
Application.Calculation = xlManual
ThisWorkbook.Sheets.Select
ActiveSheet.Calculate
this calculate all selected sheet at the same time creating the right order of calculation
这会同时计算所有选定的工作表，创建正确的计算顺序
回答by antonsachs
None that I know about, Steven.
我不知道，史蒂文。
You actually lose time doing Application.Worksheets(1).Calculate
你实际上浪费时间做 Application.Worksheets(1).Calculate
I ran a timing test: 3 workbooks open in the same instance of Excel.
我运行了一个计时测试：在同一个 Excel 实例中打开了 3 个工作簿。
One with 8200 volatile functions, 8000 in one worksheet on 4 non-contiguous ranges, 200 in other worksheets.
一个具有 8200 个易失性函数，8000 个在 4 个非连续范围的工作表中，200 个在其他工作表中。
Two other workbooks with a combined total of 100 functions. The results:
另外两个工作簿，总共有 100 个函数。结果：
Application.Calculate- 4.41 msThisWorkbook.Worksheets(1).Calculate- 52.05 ms for each worksheetThisWorkbook.Worksheets(1).Range("R1").Calculate(repeated 4 times) - 84.64 ms
Application.Calculate- 4.41 毫秒ThisWorkbook.Worksheets(1).Calculate- 每个工作表 52.05 毫秒ThisWorkbook.Worksheets(1).Range("R1").Calculate（重复 4 次） - 84.64 毫秒
It's counter-intuitive, but true.
这是违反直觉的，但确实如此。
You also lose time declaring Dim wks as Worksheet. Unless you do For ... Each referring to it as ThisWorkbook.Worksheets(1)- is faster
你也浪费时间声明Dim wks as Worksheet. 除非你做 For ... 每个人都称它为ThisWorkbook.Worksheets(1)- 更快
Also, be careful referring to ActiveWorkbook- when you code runs your primary workbook might not be active one. ThisWorkbook(the one with code) is safer.
此外，请注意ActiveWorkbook- 当您运行代码时，您的主要工作簿可能不是活动的。ThisWorkbook（带代码的）更安全。
回答by Sean O'Reilly
ThisWorkbook will use only the workbook that the code resides in
ThisWorkbook 将仅使用代码所在的工作簿
For Each wks In ThisWorkbook.Worksheets
wks.Calculate
Next wks
回答by Vikas Soni
Just use Calculate
只需使用计算
eg:
例如：
Sub Cal_()
Calculate
End Sub
回答by Dave Scott
The answer by Steven G doesn't actually work, it just looks like it does.(I can't reply to that post as I don't have 50+ rep, which is annoying).
史蒂文 G 的答案实际上不起作用，只是看起来确实有效。（我无法回复该帖子，因为我没有 50 多个代表，这很烦人）。
It still calculates the sheets in 1 directional order. I tested this by creating 5 sheets and having them all + 1 to another cell, on each sheet in different directions. Using the select sheets then calculate method will give the wrong result.
它仍然按 1 个方向顺序计算工作表。我通过创建 5 张纸并将它们全部加 1 到另一个单元格来测试这一点，在每张纸上的不同方向。使用选择表然后计算方法将给出错误的结果。
As it requires that the sheets are visible, elow is function I built to replace the calculate function using that method. (Requires dictionaries reference library). Do not use though.
由于它要求工作表可见，下面是我构建的函数，用于使用该方法替换计算函数。（需要字典参考库）。虽然不要使用。
EXAMPLE, METHOD DOES NOT WORK
Function Calculate_Workbook(Optional Wb As Workbook)
'Application.calculate will calculate all open books, which can be very slow.
'Looping through all sheets in workbook to calculate can be risky due to formula referencing a cell thats not yet calculated, calculation order may be important.
Dim DicShtVisible As New Dictionary
DicShtVisible.CompareMode = TextCompare
Dim Asht As Worksheet, AWkbk As Workbook 'Previously selected books
Dim Sht As Worksheet
Set Asht = ActiveSheet
Set AWkbk = ActiveWorkbook
If Wb Is Nothing Then Set Wb = ThisWorkbook
Wb.Activate
'Unhide all sheets as can't select very hidden stuff
For Each Sht In Wb.Sheets
DicShtVisible.Add Sht.Name, Sht.Visible
Sht.Visible = xlSheetVisible
Next Sht
'Select all sheets and calculate the lot
Wb.Sheets.Select
ActiveSheet.Calculate
'Reapply visibility settings
Dim Key As Variant
For Each Key In DicShtVisible.Keys
Wb.Sheets(Key).Visible = DicShtVisible.Item(Key)
Next Key
'Reset selections
AWkbk.Activate
Asht.Select
End Function
As far as I can see there is no solution, the only answer is to either know the calculation order of your workbook and manually calculate sheets in that order, or accept using Calculate and that it will calculate all open workbooks.
据我所知，没有解决方案，唯一的答案是要么知道工作簿的计算顺序并按该顺序手动计算工作表，要么接受使用计算并计算所有打开的工作簿。
I would love to be proven wrong however. It's such an annoying and stupid issue.
然而，我很想被证明是错误的。这是一个令人讨厌和愚蠢的问题。
回答by jnm21
We can use
我们可以用
Application.CalculateBeforeSave = True
and then save the workbook? May be more finessed to trap the current value, set if not true, save, reset if was not true.
然后保存工作簿？可能更巧妙地捕获当前值，如果不为真则设置，如果不为真，则保存，重置。
回答by William C.
Try this:
尝试这个：
Sub CalcBook()
Dim wks As Worksheet
Application.Calculation = xlManual
For Each wks In ActiveWorkbook.Worksheets
wks.Calculate
Next
Set wks = Nothing
End Sub
