将字符串写入单元格时，VBA 会插入额外的单引号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7790662/
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
When writing string to a cell, VBA inserts extra single quotation marks
提问by Derek
I am trying to write this formula using VBA:
我正在尝试使用 VBA 编写此公式：
ActiveCell.Value = "=f(R[-1]C[0],Sheet1!" & ColumnLetter & i & ")"
Where ColumnLetter is some variable letter which my macro computes earlier, and f is some function, and i is some number.
其中 ColumnLetter 是我的宏之前计算的某个可变字母，f 是某个函数，而 i 是某个数字。
The problem is that when I run this, the cell is given this instead: (if ColumnLetter = F, i = 16):
问题是当我运行这个时，单元格被赋予了这个：（如果 ColumnLetter = F，i = 16）：
=f(R[-1]C[0],Sheet1!'F16')
but I want:
但我想要：
=f(R[-1]C[0],Sheet1!F16)
Why is VBA or Excel putting those single quotation marks around F16? It does not insert these extra quotation marks if I do not include R[-1][0] as an argument in my formula, but I need to include this.
为什么 VBA 或 Excel 将这些单引号放在 F16 周围？如果我的公式中不包括 R[-1][0] 作为参数，它不会插入这些额外的引号，但我需要包括它。
Help much appreciated!
非常感谢帮助！
回答by chris neilsen
Its the combination of R1C1and A1addressing. You need to pick one method and use it for both parts.
Note that if you type =f(R[-1]C[0],Sheet1!F16)into a cell you will get an error for the same reason.
它的组合R1C1和A1寻址。您需要选择一种方法并将其用于两个部分。
请注意，如果您=f(R[-1]C[0],Sheet1!F16)在单元格中键入内容，您将因同样的原因收到错误消息。
You say you need to use R1C1 style for the first address, but (assuming this is because you don't want
absolute address) you can use .Offsetinstead
您说您需要对第一个地址使用 R1C1 样式，但是（假设这是因为您不想要绝对地址）您可以.Offset改为使用
ActiveCell.Value = "=f(" & ActiveCell.Offset(-1, 0).Address(False, False) _
& ",Sheet1!" & ColumnLetter & i & ")"
回答by Delcon
The Apostrophe means to Excel that it should interpret it as text.
撇号对 Excel 意味着它应该将其解释为文本。
Write it to ActiveCell.Formula. That way it is recognized as Formula.
将其写入 ActiveCell.Formula。这样它就被识别为公式。
