Escape Single Quotes and Double Quotes in VBA

In Excel VBA, text strings assigned to properties or variables are enclosed in double quotes “ ”.

If we attempt to use double quotes to close a text string that already has double quotes or a single quote, Excel VBA will prompt us with an error.

We have to escape the single or double quote to prevent such errors. This tutorial shows how to escape single and double quotes in VBA.

How to Escape Double Quotes in Excel VBA

We use extra double quotes to escape double quotes in Excel VBA.

Suppose we have the following dataset showing the phrase “Excel VBA” in cell A2 in a worksheet named “Escape Double Quote.”

We want to use Excel VBA to insert a formula in cell B2 that will return the phrase in cell A2 exactly as it is with the double quotes.

We use the following steps:

Open the Visual Basic Editor and insert a new module.
Type the following code in the module:

Sub escapeDoubleQuote()
    With Worksheets("Escape Double Quote")
        .Range("B2").Formula = "=IF(A2= Char(34) & ""Excel VBA"" & Char(34),A2,"""")"
    End With
End Sub

Sub escapeDoubleQuote()

With Worksheets("Escape Double Quote")

.Range("B2").Formula = "=IF(A2= Char(34) & ""Excel VBA"" & Char(34),A2,"""")"

End With

End Sub

Place the cursor anywhere in the code and press F5 to run the code.
Press Alt + F11 to switch to the active worksheet that contains the dataset.

Graphical user interface, application, table

Description automatically generated

The formula returned in cell B2 the exact phrase in cell A2 with double quotes.

Explanation of the sub-procedure

The formula in the sub-procedure is =IF(A2= Char(34) & “”Excel VBA”” & Char(34),A2,””””) but the formula entered in cell B2 is =IF(A2= CHAR(34) & “Excel VBA” & CHAR(34),A2,””). This shows that extra double quotes were used to escape double quotes in Excel VBA.

The CHAR function returned the double quotes enclosing the phrase “Excel VBA.” The code number for double quotes is 34.

How to Escape Single Quotes in Excel VBA

We use the formula CHAR(39) to escape single quotes in Excel VBA.

Suppose we have the following dataset showing the phrase ‘Excel VBA’ in cell A2 in a worksheet named “Escape Single Quote.” Notice that in the formula bar, two single quotes precede the phrase.

Table

Description automatically generated

We want to use Excel VBA to insert a formula in cell B2 that will return the phrase in cell A2 exactly as it is with the single quotes.

We proceed as follows:

Open the Visual Basic Editor and insert a new module.
Type the following sub-procedure in the module:

Sub escapeSingleQuote()
    With Worksheets("Escape Single Quote")
        .Range("B2").Formula = "=IF(A2= Char(39) & ""Excel VBA"" & Char(39),A2,"""")"
    End With
End Sub

Sub escapeSingleQuote()

With Worksheets("Escape Single Quote")

.Range("B2").Formula = "=IF(A2= Char(39) & ""Excel VBA"" & Char(39),A2,"""")"

End With

End Sub

Place the cursor anywhere in the procedure and press F5 to run the code.
Press Alt + F11 to switch to the active worksheet containing the dataset.

The formula returned in cell B2 the exact phrase in cell A2 with single quotes.

Explanation of the formula

The formula in the sub-procedure is =IF(A2= Char(39) & “”Excel VBA”” & Char(39),A2,””””) but the formula entered in cell B2 is =IF(A2= CHAR(39) & “Excel VBA” & CHAR(39),A2,””). This shows that the formula CHAR(39) was used to escape the single quotes in the phrase.

The CHAR function used code number 39 to return the single quotes used to escape the single quotes in the phrase. The code number for the single quote is 39.

Conclusion

This tutorial showed how to escape single quotes and double quotes in Excel VBA. We hope you found the tutorial helpful.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.

Post Views: 784