Put Double Quotes in a String in Excel VBA

In VBA, strings assigned to variables or properties are enclosed in double quotes "". However, if you use double quotes to close a string that already has a quote or double quote, then VBE will prompt you with an error.

For example: If you want to add a formula =IF(A1="Excel",A1,"") to cell B1 with VBA, please follow the steps below:

1. Get your code, in this example, the code is: =IF(A1="Excel",A1,"").
2. Replace all " with "". Result: =IF(A1=""Excel"",A1,"""")
3. Enclose the entire string =IF(A1=""Excel"",A1,"""") in double quotes "". Result: "=IF(A1=""Excel"",A1,"""")"
4. Final code in VBA: Range("B1").Formula = "=IF(A1=""Excel"",A1,"""")"

Some people like to use Chr(34), you can replace all " with Chr(34), then use the ampersand (&) text operator to concatenate the string. The final code:

Range("B1").Formula = "=IF(A1=" & Chr(34) & "Excel" & Chr(34) & ",A1," & Chr(34) & Chr(34) & ")"

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: Press CTRL + ` to display Formula.