Excel VBA - worksheet.calculate 不计算我的所有公式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25687320/
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 - worksheet.calculate doesn't calculate all of my formulas
提问by Thomas Almond
Private Sub Worksheet_Activate()
If Worksheets("Input").Range("S5").Value = "Yes" Then
MsgBox "Please make sure you've completed the historical deductible amounts for EL"
End If
Worksheets("Input").Calculate
Worksheets("EL").Calculate
Worksheets("PPL").Calculate
Worksheets("Auto").Calculate
Worksheets("AL").Calculate
Worksheets("APD").Calculate
Worksheets("Loss Layering").Calculate
Worksheets("Sheet2").Calculate
Worksheets("Premium").Calculate
End Sub
In an effort to speed up a very large workbook I have switched off auto calculate and have created a hierarchy in which I calculate sheets as I move through my workbook. My issue is that with any heavy duty formulas such as sumifor sumproduct, the values are not calculated within my active sheet, they stay as zeroes. I have tried application.calculate and CalculateFull, these both work but I find they take up way too much time. I'm trying to find a way to do this while keeping my template as quick, simple and user friendly as possible. Any suggestions?
为了加速一个非常大的工作簿，我关闭了自动计算并创建了一个层次结构，当我在我的工作簿中移动时，我在其中计算工作表。我的问题是，对于诸如sumif或sumproduct 之类的任何重型公式，这些值不会在我的活动表中计算出来，它们保持为零。我试过 application.calculate 和CalculateFull，这两个都有效，但我发现它们占用了太多时间。我试图找到一种方法来做到这一点，同时尽可能保持我的模板快速、简单和用户友好。有什么建议？
回答by Charles Williams
It is not clear which worksheet is the active sheet and contains this code, but I can think of 2 possible reasons for your problem.
目前尚不清楚哪个工作表是活动工作表并包含此代码，但我可以想到导致您的问题的 2 个可能原因。
1) You have not called worksheet.calculate on the active sheet.
2) Since worksheet.calculate ignores dependencies on other worksheets the calculation sequence you are using will only work as you want if the formulas on the sheets always refer to other sheets that have already been calculated. In other words the sheet calculation sequence must exactly match the inter-sheet references sequence and there must be NO forward inter-sheet references whatsoever (including defined names etc).
1) 您尚未在活动工作表上调用 worksheet.calculate。
2) 由于 worksheet.calculate 忽略了对其他工作表的依赖，如果工作表上的公式总是引用其他已经计算过的工作表，那么您使用的计算顺序只会按您的意愿工作。换句话说，工作表计算顺序必须与工作表间引用序列完全匹配，并且必须没有任何前向工作表间引用（包括定义的名称等）。
as a general point I would not usually expect using worksheet.calculate to calculate an entire workbook would be faster than using Application.Calculate (although I am sure sometimes It will be faster)
一般来说，我通常不会期望使用 worksheet.calculate 来计算整个工作簿会比使用 Application.Calculate 更快（尽管我确信有时它会更快）
回答by Pierre Langlois Lacerte
I know it's been a while, but it has been bugging me for a while and I just came out with a workaround. I created a sub which I call when I want to force calculation on a range.
我知道这已经有一段时间了，但它一直困扰着我一段时间，我只是想出了一个解决方法。我创建了一个子程序，当我想在一个范围内强制计算时调用它。
Sub forceRngCalc(rng As Range)
For Each c In rng
formulaToCopy = c.Formula
c.ClearContents
c.Value = formulaToCopy
Next
End Sub
It's the equivalent of retyping the formula and thus forces to recalculate. It's not very fancy, but it works.
这相当于重新键入公式，从而强制重新计算。这不是很花哨，但它有效。
回答by Ph??c Nguy?n Vi?t
''simple solution, example:
Call Recalculate(Sheet3.[A10:G10])
调用重新计算(Sheet3.[A10:G10])
End Sub Sub Recalculate(rng As Range) rng.value = rng.Formula End Sub
End Sub Sub Recalculate(rng As Range) rng.value = rng.Formula End Sub
