Excel 和 VBA：跳过宏中的计算公式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18418575/
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: Skip calculating formula in Macro
提问by waterplea
I'm having a rather simple issue with Macro. In it, I assign a formula to a cell. I would like it not to calculate it at first and do calculation only after some other part of Macro is finished. I thought I would do something like this:
我有一个相当简单的宏问题。在其中，我为一个单元格分配了一个公式。我希望它首先不要计算它，只有在宏的其他部分完成后才进行计算。我以为我会做这样的事情：
Application.Calculation = xlCalculationManual
Cells(StartRow, 4 + i).Formula = "FORMULA"
...
Application.Calculation = xlCalculationAutomatic
But that doesn't work. It stops automatic calculations, but not for that cell - it still performs the calculation right after I assign the formula. Is there a way to skip it?
但这不起作用。它会停止自动计算，但不会针对该单元格 - 在我分配公式后，它仍会立即执行计算。有没有办法跳过它？
To clarify the exact prupose of this: in my actual code I'm assigning a formula to a group of cells in a cycle. Everytime I assign it to one cell - it calculates it. I figured if I will first assign them all and then do the calculation - it would be faster. As a matter of fact it is. So instead of assigning it in a cycle, I assign it to the first cell and then do autofill. Autofilled formulas wait until I enable automatic calculation and I get a much faster macro. However, the initial assignemnt is still calculated which makes macro almost twice as slow.
为了澄清这一点的确切目的：在我的实际代码中，我将一个公式分配给一个循环中的一组单元格。每次我将它分配给一个单元格时 - 它都会计算它。我想如果我首先将它们全部分配然后进行计算 - 会更快。事实上它是。因此，我没有在循环中分配它，而是将其分配给第一个单元格，然后进行自动填充。自动填充公式会等到我启用自动计算并获得更快的宏。但是，仍然计算初始分配，这使得宏几乎慢了两倍。
回答by Gary's Student
- place the formula in the cell with a prefix character
- continue the macro
- "activate" the formula
- 将公式放在带有前缀字符的单元格中
- 继续宏
- “激活”公式
for example:
例如：
Sub dural()
With Range("A1")
.Value = "'=1+2"
MsgBox " "
.Value = .Value
End With
End Sub
回答by A.Sommerh
@Alex, you can delay the calculation as @Gary answer. However, you was asking the question because you need "SPEED to CYCLE through cells"while assign a formula, right?
@Alex，您可以按照@Gary 的回答延迟计算。但是，您问这个问题是因为在分配公式时需要“通过单元格进行循环的速度”，对吗？
If yes, from my point of view, if you are NOTusing the formulas until ALLthe formulas are assigned in the excel sheet, you will gain a lot of speedby writing all the formulas at once using an array (a single step in VBA).
如果是，从我的角度来看，如果您在 Excel 表中分配所有公式之前不使用公式，则通过使用数组一次编写所有公式（VBA 中的一个步骤），您将获得很大的速度）。
The procedure is: first put all the formulas to an VBA array of strings, and later on to use for example Range("B1:B100").Formula = ArrayWithFormulas. In that example you are assigning 100 formulas at once, without recalculation in between.
过程是：首先将所有公式放入一个 VBA 字符串数组中，然后再使用例如Range("B1:B100").Formula = ArrayWithFormulas. 在该示例中，您一次分配 100 个公式，中间没有重新计算。
You will see a large improve in SPEED if use an array to write all the cells at ones instead of writing cell by cell! (Don't loop using cells(r,c+i)if you have a lot of cells to go through). Here one example:
如果使用数组将所有单元格写入一个单元格而不是逐个单元格写入，您将看到速度有很大提高！（cells(r,c+i)如果您有很多单元格要通过，请不要循环使用）。这是一个例子：
Sub CreateBunchOfFormulas()
Dim i As Long
Dim ARRAY_OF_FORMULAS() As Variant 'Note: Don't replace Variant by String!
ReDim ARRAY_OF_FORMULAS(1 To 100, 1 To 1)
' For Vertical use: (1 to NumRows,1 to 1)
' for Horizontal: (1 to 1,1 to NumCols)
' for 2D use: (1 to NumRows,1 to NumCols)
'Create the formulas...
For i = 1 To 100
ARRAY_OF_FORMULAS(i, 1) = "=1+3+" & i ' Or any other formula...
Next i
' <-- your extra code here...
' (New formulas won't be calculated. They are not in the Excel sheet yet!
' If you want that no other old formula to recalculate use the old trick:
' Application.Calculation = xlCalculationManual )
'At the very end, write the formulas in the excel at once...
Range("B1:B100").Formula = ARRAY_OF_FORMULAS
End Sub
If you want an extra delay in the new formula, then you can use @Gary trick, but applied to a range, not to a single cell. For that start the formulas with a 'like '=1+2and add the following code at the end:
如果你想在新公式中额外延迟，那么你可以使用@Gary 技巧，但应用于一个范围，而不是单个单元格。为此，在公式开头加上一个“ 'like”，'=1+2并在最后添加以下代码：
'... previous code, but now formulas starting with (')
Range("B1:B100").Formula = ARRAY_OF_FORMULAS
'Formulas not calculated yet, until next line is executed
Range("B1:B100").Value = Range("B1:B100").Value ' <~~ @Gary's trick
End Sub
Last, a small snipped: if your formulas are in a horizontal arrangement (Means one formula for column A, other for column B, etc) and just a small number of columns, then you can keep in mind the a shorter version of previous code:
最后，一个小片段：如果您的公式处于水平排列（意味着 A 列的一个公式，B 列的另一个公式等）并且只有少量列，那么您可以记住以前代码的较短版本：
Dim a as Variant 'Note that no () needed
a = Array("=1+3","=4+8","=5*A1","=sum(A1:C1)")
Range("A1:D1").Formula = ARRAY_OF_FORMULA ' Just a single row
' or...
Range("A1:D100").Formula = ARRAY_OF_FORMULA ' If you want to repeat formulas
' in several rows.
Finally, You can use the method .FormulaR1C1instead of .Formulain all the previous code examples, if you want an easy way to use relative references in your formula...
最后，如果您想要一种在公式中使用相对引用的简单方法，您可以使用该方法.FormulaR1C1而不是之前.Formula的所有代码示例...
Hope this helps!
希望这可以帮助！
