使用 VBA 中生成的公式在工作表之间进行引用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6729348/
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
Referencing between sheets with formulas generated in VBA
提问by Wizards Head
I'm trying to populate a cell with a basic formula that references both the current worksheet and another worksheet.
我正在尝试使用引用当前工作表和另一个工作表的基本公式填充单元格。
The formula I am trying to input into a cell is basically... ='Sheet2'A(j) / B(i)with the following code:
我试图输入到一个单元格的公式基本上是......='Sheet2'A(j) / B(i)使用以下代码：
For i = 1 To 5
For j = 1 To 5
With shOut.Cells(i,j)
.formula = "='Sheet2'" & Cells(j,1)).Address & "/" & Cells(i,2).Address
End With
Next j
Next i
The part I am having trouble with is the sheet referencing at the start; when I run this in a .formula, it triggers an error.
我遇到问题的部分是开始时引用的工作表；当我在 a 中运行它时.formula，它会触发一个错误。
However, when I remove the '=' sign and make it output in a .Value, it prints out what I want it to, except it's not a formula.
但是，当我删除 '=' 符号并将其输出为 a 时.Value，它会打印出我想要的内容，除非它不是公式。
回答by Gary McGill
You shouldn't have to construct the address of any range yourself - you'll only leave yourself open to potential problems by doing so.
您不应该自己构建任何范围的地址 - 这样做只会让自己面临潜在的问题。
For example, in cularis' (perfectly good) answer, there are no single-quotes around the sheet name. They're not required in this case, but if the sheet name had a space in it, then you doneed the quotes. (My point is that you shouldn't have to know - and cater for - all this stuff yourself).
例如，在 cularis（非常好）的答案中，工作表名称周围没有单引号。在这种情况下它们不是必需的，但如果工作表名称中有空格，那么您确实需要引号。（我的观点是你不应该自己知道 - 并迎合 - 所有这些东西）。
Instead, you can specify External:=Truein the call to Address(), which will give you an address that includes the sheet name. I'd do something like this:
相反，您可以External:=True在对 的调用中指定Address()，这将为您提供一个包含工作表名称的地址。我会做这样的事情：
Dim oWorksheet1 As Worksheet
Dim oWorksheet2 As Worksheet
...
.Formula = "=" & oWorksheet1.Cells(j,1).Address(External:=True) & "/" & oWorksheet2.Cells(i,2).Address(External:=True)
Note that in your case you probably only need to use the Externalparameter for one of the worksheets, but doing it that extra time won't hurt, as Excel will simplify the formula for you anyway.
请注意，在您的情况下，您可能只需要使用External其中一个工作表的参数，但这样做额外的时间不会有什么坏处，因为 Excel 无论如何都会为您简化公式。
回答by Jacob
- You have a
)too much in your line that begins with.formula. You are missing the
!after the sheet:.formula = "=Sheet2!" & Cells(j,1).Address & "/" & Cells(i,2).Address
- 您的行中有
)太多以.formula. 您缺少工作
!表之后的内容：.formula = "=Sheet2!" & Cells(j,1).Address & "/" & Cells(i,2).Address
