How to: Put Double Quotes in a String in Excel VBA

In VBA, strings assigned to variables or properties are enclosed in double quotes "", Sometimes you might want to place Double Quotes "" in a string in Excel VBA. For example:

A1 =IF(B1="Excel",B1,"")

In VBE, you can replace " with "", and then enclose the entire string =IF(B1=""Excel"",B1,"""") in double quotes "". Here is the full code in VBA:

Worksheets("Sheet1").Range("A1").Formula = "=IF(B1=""Excel"",B1,"""")"

Some people like to use Chr(34):
Worksheets("Sheet1").Range("A1").Formula = "=IF(B1=" & Chr(34) & "Excel" & Chr(34) & ",B1," & Chr(34) & Chr(34) & ")"

VBA Code Examples:

Sub DoubleQuotes()
    With Worksheets("Sheet1")
        .Range("A1") = ""
        .Range("A2") = """"
        .Range("A3") = """"""
        .Range("A4") = Chr(34)
        .Range("A5") = Chr(34) & "Excel" & Chr(34)
        .Range("A6").FormulaR1C1 = "=IF(R[-5]C="""",""Blank"",0)"
        .Range("A7").Formula = "=IF(A1="""",""Blank"",0)"
        .Range("A8").Formula = "=IF(A1=" & Chr(34) & Chr(34) & "," & Chr(34) & "Blank" & Chr(34) & ",0)"
    End With
End Sub

Results: Show Formulas (CTRL + `)

Leave a Reply

Your email address will not be published. Required fields are marked *