Skip to main content

Put Double Quotes in a String in Excel VBA

Table of contents
  1. Examples

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


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.

Leave a comment

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

Format your code: <pre><code class="language-vba">place your code here</code></pre>