How to Put Double Quotes in a String in VBA

In this lesson you will learn how to put double quotes in a string in Excel VBA. Sometimes your VBA application needs to output some text in an Excel sheet, in a form control or in the immediate window. For an example, assume you want to write Hello World! In cell A1 of the Sheet1. You can simply do it like this.

Sub WriteHelloWorldInSheet()

Worksheets("Sheet1").Range("A1").Value = "Hello World!"

End Sub

Or you can simply print this in the immediate window like this.

Sub PrintHelloWorldInImmediateWindow()

Debug.Print "Hello World!"

End Sub

But what if you want to output something like this in an Excel sheet or immediate window.,

"I admired both of them, especially Kevin," John said.

Excel VBA will give us a syntax error if we follow the above methods.

And if you try to output this in the immediate window, text will convert to a strange form like this.

So how do we put quotation marks in a string? There are two ways to solve this problem.

Method 1

Add an extra double quote in front of each quotation mark. So the quotation marks will be doubled. If you want to show two double quotes inside the string, then you will have to write four double quotes. I will explain this little more with our sample string.

As you can see there are two types of quotation marks in this image. Two red color double quotes and two green color double quotes. Red color quotes are ourter quotes. You don’t need to do anything with them. You have to double only the green color double quotes. So the final code should look like this.

Here is the complete subroutine.

Sub DoubleQuotesExample()

Worksheets("Sheet1").Range("A1").Value = """I admired both of them, especially Kevin,"" John said."

End Sub

You can use this same technique to print string in the immediate window as well.

Sub DoubleQuotesExampleImmediateWindow()

Debug.Print """I admired both of them, especially Kevin,"" John said."

End Sub

Next let’s look at how to use this method when there are consecutive double quotes.

Example - Output following in the cell A1 of the Sheet 1

"Apple""Mango""Orange"

So the rule of this method is, you have to add an extra double quote for each quotation mark. So the completed subroutine should look like this.

Sub ConsecutiveDoubleQuotes()

Worksheets("Sheet1").Range("A1").Value = """Apple""""Mango""""Orange"""

End Sub

Method 2

Use the ASCII code of the double quote. In this method you can use Chr(34) where you want the double quotes. So let’s consider our first example string again.

"I admired both of them, especially Kevin," John said.

We can replace the double quotes with Chr(34) like this.

Chr(34) & "I admired both of them, especially Kevin," & Chr(34) & " John said."

Remember to use ampersand(&) to concatenate text strings with Chr(34). Here is the completed subroutine to write that string to cell A1 of the Sheet1.

Sub DoubleQuotesExample_Method2()

Worksheets("Sheet1").Range("A1").Value = Chr(34) & "I admired both of them, especially Kevin," & Chr(34) & " John said."

End Sub

And here is how you can use this second method to print a string with double quotes in the immediate window.

Sub PrintDoubleQuotesImmediateWindow_Method2()

Debug.Print Chr(34) & "I admired both of them, especially Kevin," & Chr(34) & " John said."

End Sub

Next let’s look at how to use this method when there are consecutive double quotes in the strings. So let’s try to print the string below in the immediate windows using this second method.

"Apple""Mango""Orange"

The rule is, you need to replace " with Chr(34) and concatenate with ampersand(&). Therefore we can rewrite the above string like this.

Chr(34) & "Apple" Chr(34) & Chr(34) & "Mango" Chr(34) & Chr(34) & "Orange" & Chr(34)

Here is the completed subroutine

Sub ConsecutiveDoubleQuotes_Method2()

Debug.Print Chr(34) & "Apple"; Chr(34) & Chr(34) & "Mango"; Chr(34) & Chr(34) & "Orange" & Chr(34)

End Sub

You can also use these ASCII codes for various other characters. Check this page to see what characters you can print with ASCII codes.

ASCII Table