使用 Excel VBA 插入公式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15513529/
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
Inserting a formula using Excel VBA
提问by Joel Stephenson
I am needing to insert a formula in using vba coding.
我需要在使用 vba 编码时插入一个公式。
at the moment the line of code is
目前代码行是
=IF(
AND(
Compliance="No",
OR(
Delivery <> "",
C31 <> ""
)
),
"Please Delete Compliance Data",
IF(
AND(
E11="",
E13="",
E23="",
E25="",
E26="",
E28="",
E30="",
E31=""),
VLOOKUP(C15,'Extrapolated RV Calculator'!B:S,18,0),
"Please complete form / SC request"
)
)"
It seems to error out on the text portions inside the code.
代码中的文本部分似乎出错了。
Any help would be appreciated.
任何帮助，将不胜感激。
回答by Dick Kusleika
Here are four ways to use quotation marks inside strings in VBA:
以下是在 VBA 中在字符串中使用引号的四种方法：
- Replace "" with Chr$(34). 34 is the ASCII code for quotation marks
- Use String(2,Chr$(34)) to make double quotes
- Use two double quotation marks for every one (per Tim's comment)
- Replace two double quotation marks with a different method, like ISBLANK for worksheet functions.
- 用 Chr$(34) 替换 ""。34是引号的ASCII码
- 使用 String(2,Chr$(34)) 制作双引号
- 每一个都使用两个双引号（根据 Tim 的评论）
- 用不同的方法替换两个双引号，例如工作表函数的 ISBLANK。
None of these is better. I usually use two double quotation marks to escape them. But sometimes I get so many quotation marks together that it's hard to read and maintain, so I'll use a different method. Here's an example:
这些都不是更好。我通常使用两个双引号来转义它们。但有时我把这么多引号放在一起，很难阅读和维护，所以我会使用不同的方法。下面是一个例子：
Dim aFormula(1 To 5) As String
aFormula(1) = "=IF(AND(Compliance=""No"",OR(Delivery<>" & String(2, Chr$(34))
aFormula(2) = ",C31<>" & String(2, Chr$(34)) & ") ),"
aFormula(3) = Chr$(34) & "Please Delete Compliance Data" & Chr$(34)
aFormula(4) = ",IF(AND(ISBLANK(E11),ISBLANK(E13),ISBLANK(E23),ISBLANK(E25),ISBLANK(E26),ISBLANK(E28),ISBLANK(E30),ISBLANK(E31)),"
aFormula(5) = "VLOOKUP(C15,'Extrapolated RV Calculator'!B:S,18,0),""Please complete form / SC request""))"
Sheet1.Range("R13").Formula = Join(aFormula, vbNullString)
回答by K_B
To empower you for the future I strongly recommend you do the following when you want to use functionality in Excel in your VBA code that you are not acquainted with yet.
为了在未来为您提供支持，我强烈建议您在您不熟悉的 VBA 代码中使用 Excel 中的功能时执行以下操作。
Go to the developer menu and press the record macro button.
转到开发人员菜单，然后按录制宏按钮。
Now fill in a formula in a cell, Clear a cells content by pressing delete and clearing a cells content by going into the cell and deleting all the characters and press enter.
现在在单元格中填写一个公式，通过按删除键清除单元格内容，通过进入单元格并删除所有字符并按回车键来清除单元格内容。
Now stop the macro. And go to the VBA window. See how the code is structured to accomplish these things.
现在停止宏。然后转到 VBA 窗口。看看代码是如何结构化来完成这些事情的。
Apply this also whenever you want to do other things to get a good idea of what you can use to get the results you want without having to browse around on the internet for ages!
每当您想做其他事情时也可以应用此方法，以了解可以使用什么来获得所需的结果，而无需在互联网上浏览很长时间！
Also watch the local variables in the locals window or Debug.Print the string you try to build to see hwat it looks so far
还可以在 locals 窗口或 Debug.Print 中查看本地变量。打印您尝试构建的字符串以查看它到目前为止的外观
