如何在vba中的字符串中加双引号？

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?

由于双引号，我在插入字符串时遇到问题。我该如何处理双引号？

6 个解决方案

#1

113

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（）函数。

#2

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$1),FIND(~[~,CELL(~filename~,$A$1))+1,FIND(~]~, CELL(~filename~,$A$1))-FIND(~[~,CELL(~filename~,$A$1))-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代码中输入公式非常容易。

#3

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 show this line in red and you will correct this error.

双引号内的所有双引号都必须加倍。例如，我有一个json文件字符串：“delivery”：“Standard”，在Vba编辑器中我将其更改为“”“delivery”“：”“Standard”“，”并且everythig正常工作。如果你必须插入很多类似的字符串，我的建议首先将它们全部插入“”之间，然后用VBA编辑器替换“inside into”“。如果你会出错，VBA编辑器会显示这行为红色，你会纠正这个错误。

#4

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)"

#5

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' Vault Section.

它最初发布在Chandoo.org论坛的Vault部分。

#6

-1

The TEXT function can reproduce an empty (or zero-length) string without using quotes.

TEXT函数可以在不使用引号的情况下重现空（或零长度）字符串。

with Worksheets("Sheet1").Range("A2")
    .formula = "=IF(Sheet1!A1=0, TEXT(,), Sheet1!A1)"
    'also good for comparing to a zero-length string; e.g. IF(A1="", ...
    .formula = "=IF(Sheet1!A1=TEXT(,), TEXT(,), Sheet1!A1)"
end with

I've use the Range.Formula property rather than Range.Value property for clarity. Using .Value to put a formula into a cell formatted as text (e.g. @) can result in a cell containing text-that-looks-like-a-formula.

为清楚起见，我使用Range.Formula属性而不是Range.Value属性。使用.Value将公式放入格式化为文本的单元格（例如@）可以生成包含文本的单元格，看起来像一个公式。

#1

113