vba 如何在vba中的字符串中放置双引号？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9024724/
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
How do I put double quotes in a string in vba?
提问by user793468
I want to insert an if statement in a cell through vba which includes double quotes.
我想通过包含双引号的 vba 在单元格中插入一个 if 语句。
Here is my code:
这是我的代码：
Worksheets("Sheet1").Range("A1").Value = "=IF(Sheet1!B1=0,"",Sheet1!B1)"
Due to double quotes I am having issues with inserting the string. How do I handle double quotes?
由于双引号，我在插入字符串时遇到问题。我如何处理双引号？
回答by Brain2000
I find the easiest way is to double up on the quotes to handle a quote.
我发现最简单的方法是将引号加倍来处理引号。
Worksheets("Sheet1").Range("A1").Formula = "IF(Sheet1!A1=0,"""",Sheet1!A1)"
Some people like to use CHR(34)*:
有些人喜欢使用 CHR(34)*：
Worksheets("Sheet1").Range("A1").Formula = "IF(Sheet1!A1=0," & CHR(34) & CHR(34) & ",Sheet1!A1)"
*Note: CHAR() is used as an Excel cell formula, e.g. writing "=CHAR(34)" in a cell, but for VBA code you use the CHR() function.
*注意：CHAR() 用作 Excel 单元格公式，例如在单元格中写入“=CHAR(34)”，但对于 VBA 代码，您使用 CHR() 函数。
回答by D Zeller
Another work-around is to construct a string with a temporary substitute character. Then you can use REPLACE to change each temp character to the double quote. I use tilde as the temporary substitute character.
另一种解决方法是构造一个带有临时替换字符的字符串。然后您可以使用 REPLACE 将每个临时字符更改为双引号。我使用波浪号作为临时替代字符。
Here is an example from a project I have been working on. This is a little utility routine to repair a very complicated formula if/when the cell gets stepped on accidentally. It is a difficult formula to enter into a cell, but this little utility fixes it instantly.
这是我一直在从事的项目中的一个示例。如果/当单元格被意外踩到时，这是一个小实用程序，用于修复非常复杂的公式。输入单元格是一个困难的公式，但是这个小实用程序可以立即修复它。
Sub RepairFormula()
Dim FormulaString As String
FormulaString = "=MID(CELL(~filename~,$A),FIND(~[~,CELL(~filename~,$A))+1,FIND(~]~, CELL(~filename~,$A))-FIND(~[~,CELL(~filename~,$A))-1)"
FormulaString = Replace(FormulaString, Chr(126), Chr(34)) 'this replaces every instance of the tilde with a double quote.
Range("WorkbookFileName").Formula = FormulaString
This is really just a simple programming trick, but it makes entering the formula in your VBA code pretty easy.
这实际上只是一个简单的编程技巧，但它使在 VBA 代码中输入公式变得非常容易。
回答by Sharunas Bielskis
All double quotes inside double quotes which suround the string must be changed doubled. As example I had one of json file strings : "delivery": "Standard", In Vba Editor I changed it into """delivery"": ""Standard""," and everythig works correctly. If you have to insert a lot of similar strings, my proposal first, insert them all between "" , then with VBA editor replace " inside into "". If you will do mistake, VBA editor shows this line in red and you will correct this error.
字符串周围双引号内的所有双引号都必须加倍更改。例如，我有一个 json 文件字符串："delivery": "Standard"，在 Vba 编辑器中，我将其更改为 """delivery"": ""Standard""," 并且一切正常。如果你要插入很多相似的字符串，我的建议是先把它们都插入在 "" 之间，然后用 VBA 编辑器把里面的 " 替换成 ""。如果你做错了，VBA 编辑器会用红色显示这一行，你会更正这个错误。
回答by Jobin Lopez
I prefer the answer of tabSF . implementing the same to your answer. here below is my approach
我更喜欢 tabSF 的答案。对您的答案执行相同的操作。下面是我的方法
Worksheets("Sheet1").Range("A1").Value = "=IF(Sheet1!A1=0," & CHR(34) & CHR(34) & ",Sheet1!A1)"
回答by shrivallabha.redij
I have written a small routine which copies formula from a cell to clipboard which one can easily paste in Visual Basic Editor.
我编写了一个小例程，将公式从单元格复制到剪贴板，可以轻松地将其粘贴到 Visual Basic 编辑器中。
Public Sub CopyExcelFormulaInVBAFormat()
Dim strFormula As String
Dim objDataObj As Object
'\Check that single cell is selected!
If Selection.Cells.Count > 1 Then
MsgBox "Select single cell only!", vbCritical
Exit Sub
End If
'Check if we are not on a blank cell!
If Len(ActiveCell.Formula) = 0 Then
MsgBox "No Formula To Copy!", vbCritical
Exit Sub
End If
'Add quotes as required in VBE
strFormula = Chr(34) & Replace(ActiveCell.Formula, Chr(34), Chr(34) & Chr(34)) & Chr(34)
'This is ClsID of MSFORMS Data Object
Set objDataObj = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
objDataObj.SetText strFormula, 1
objDataObj.PutInClipboard
MsgBox "VBA Format formula copied to Clipboard!", vbInformation
Set objDataObj = Nothing
End Sub
It is originally posted on Chandoo.org forums' VaultSection.
它最初发布在 Chandoo.org 论坛的Vault部分。
