In Excel VBA, write two double-quote characters inside a string literal wherever you want one actual double quote. For example, message = "She said, ""Hello.""" stores She said, "Hello."; you can also add a quote character with Chr(34).
The doubled-quote method is usually the clearest choice for a short, fixed string. Chr(34) can be easier to read when building text dynamically or when many quote characters are involved.
Why VBA uses doubled quotes
A VBA string literal begins and ends with a double quote. Because that same character marks the boundary of the literal, VBA needs a way to distinguish a quote that belongs in the text from the quote that closes the literal. Inside a literal, two consecutive double quotes represent one quote character in the resulting string.
For example, VBA reads "The sign says ""Open""" as one string whose contents are The sign says "Open". The first and last quote in the source are delimiters; each doubled pair in the middle contributes one quote to the text. The doubled marks are VBA syntax, not two quote characters in the final result.
This rule applies to ordinary VBA string literals whether you assign the text to a variable, show it in a message box, or pass it to an Excel object. It does not mean that every quotation mark in every file format is escaped the same way. A worksheet formula, CSV file, and SQL statement each have their own context and rules.
Write a quoted string directly
For a short message, put two quotes around the text you want quoted. This complete macro shows the source syntax and displays the resulting string:
Sub ShowQuotedMessage()
Dim message As String
' Double embedded quotes to include quote characters in the text.
message = "She said, ""Hello."""
MsgBox message
End Sub
The message box displays She said, "Hello.", with just one quote on each side of Hello. The example is useful for checking the distinction between the VBA code and the text the code produces. Before running any macro, save a copy of the workbook: macro actions generally cannot be reversed with Ctrl+Z.
To include a quoted word inside a longer sentence, use the same pattern wherever needed. For instance, the source literal "Enter ""Approved"" in the status box." results in Enter "Approved" in the status box. You do not need to add a plus sign or concatenate separate pieces just to include a quote.
Build quotes with Chr(34)
The character code 34 represents a double quote in VBA. Chr(34) returns that character, while Chr$(34) returns it as a string. Both are commonly used in VBA string-building expressions. For example:
Sub BuildQuotedMessage()
Dim message As String
' Chr$(34) supplies one double-quote character.
message = "She said, " & Chr$(34) & "Hello." & Chr$(34)
MsgBox message
End Sub
This produces the same displayed text as the doubled-quote example. The ampersand joins string pieces: the unquoted opening words, a quote character, the word Hello, and another quote character.
Choose this approach when quotes are inserted conditionally or assembled from several variables. It can also make a long expression easier to scan if doubled quote marks would be hard to count. On the other hand, for a simple fixed phrase, a literal with doubled quotes is shorter and often more readable. Neither method changes the final string.
Put quoted text in an Excel cell
A VBA string can be assigned to a cell’s value just like any other text. This macro writes a sentence containing quotation marks to cell A1:
Sub WriteQuotedText()
Dim quotedText As String
' The cell receives text, not a formula.
quotedText = "The response was ""Complete."""
ThisWorkbook.Worksheets("Sheet1").Range("A1").Value = quotedText
End Sub
After it runs, A1 contains The response was "Complete.". The cell displays the quote characters as part of the text. Change Sheet1 to the actual worksheet name in the workbook. Using .Value is appropriate when the desired result is ordinary cell content rather than a formula.
If a macro writes text that begins with an equals sign, Excel may interpret it as a formula when assigned to a cell. In that case, decide whether you intend to store a formula or literal text, and use the appropriate cell property and formatting. Quotes inside a VBA string do not, by themselves, determine whether Excel treats the assigned value as text or as a formula.
Include quotes in a worksheet formula
When a VBA macro assigns a formula, there are two layers to consider: VBA’s string syntax and the formula’s syntax. A formula uses double quotes to mark text values, and VBA also uses double quotes to mark its string literal. Therefore, each quote required by the formula must be doubled in the VBA source.
For example, this macro assigns an IF formula to B1. It assumes the worksheet is named Sheet1:
Sub WriteFormulaWithText()
Dim formulaText As String
' Double formula quotes so VBA includes them in the assigned formula.
formulaText = "=IF(A1=""Yes"",""OK"",""No"")"
ThisWorkbook.Worksheets("Sheet1").Range("B1").Formula = formulaText
End Sub
The formula stored in B1 is =IF(A1="Yes","OK","No"). If A1 contains Yes, the formula returns OK; otherwise, it returns No. The commas separate the formula’s arguments in this example. In locales or Excel configurations that use different formula separators, formula entry may require the applicable separator, but the VBA string-literal rule for embedded quotes remains the same.
A frequent mistake is to double only the quotes around Yes, but not the quotes around OK and No. Every quote that must appear in the formula text has to be escaped for VBA by doubling it. When diagnosing an error, compare the formula string you intend to assign with the sequence of doubled quotes in the VBA source.
Quote a variable or user-provided text
When the text is stored in a variable, concatenate quote characters around it. This is more maintainable than trying to build a complex literal by hand:
Sub WrapTextInQuotes()
Dim userText As String
Dim result As String
' Add one quote before and after the variable contents.
userText = "North Region"
result = Chr$(34) & userText & Chr$(34)
MsgBox result
End Sub
The result is "North Region". However, if userText itself contains a quote, simply adding a quote at each end does not escape the quote inside the value. That distinction matters when producing CSV or another format that has a rule for embedded quotes.
For a CSV field, a common rule is to surround the field with quotes and represent each quote inside the field as two quotes. This helper function performs that transformation for one field:
Function CsvQuote(ByVal value As String) As String
' Double embedded quotes, then surround the field with quotes.
CsvQuote = Chr$(34) & Replace(value, Chr$(34), Chr$(34) & Chr$(34)) & Chr$(34)
End Function
If the input is She said, "Go", the returned field is "She said, ""Go""". A CSV writer may also need to account for commas, line breaks, and the chosen character encoding. This helper quotes every field, which is allowed by common CSV conventions, but it is not a complete file-writing routine.
Common errors and how to fix them
Most quote-related errors come from confusing the text shown in VBA code with the text VBA will create. A string that appears to have an odd number of quote marks may be correct if the last mark closes the literal; a quote pair in the middle produces one character. If VBA reports a compile error such as an expected end of statement, check for a missing closing delimiter or a pair that was typed as only one embedded quote.
- One quote inside the literal: Double it in the VBA source. A single embedded quote may end the literal unexpectedly.
- Too many visible quotes: Count the pairs inside the literal and remember that each pair produces one character. Do not count the outer delimiters as output.
- Quote in a formula: Double every formula quote in the VBA source, then check the formula text that Excel receives.
- Quotes around a variable: Concatenate quote characters with
Chr(34); quote marks typed outside a literal are not automatically added to the variable’s value. - Text file output: Follow the destination format’s escaping rules. VBA’s doubled-quote notation applies to VBA source code, not automatically to every output format.
When an expression becomes difficult to read, split it into named variables or use Chr$(34) for the quote characters. Avoid changing quote marks at random: first write down the exact final text or formula you want, then work backward to express it in VBA.
Editing and running VBA on Windows and Mac
In Excel for Microsoft 365 on Windows, the Visual Basic Editor can usually be opened with Alt+F11. On Mac, use Option+F11; some keyboards require Fn+Option+F11. Keyboard settings and laptop function-key behavior can affect the shortcut. The doubled-quote syntax and Chr(34) technique are VBA language features and work in Excel VBA on both platforms.
Macros must be saved in a macro-enabled workbook format, such as .xlsm, if you want to retain the VBA project in the workbook. Excel for the web can open a workbook containing macros, but it cannot create, edit, or run VBA macros. Use the desktop Excel application for those tasks. Macro security settings can also prevent a macro from running; only enable macros in workbooks you trust.
Frequently Asked Questions
How do I put a double quote inside a VBA string?
Type two consecutive double quotes inside the string literal. For example, "Say ""Hi""" evaluates to Say "Hi".
What does Chr(34) mean in VBA?Chr(34) returns the character with code 34, which is a double quote. You can concatenate it with other strings to add quote characters around text.
Do I need to escape single quotes in a VBA string?
No. Single quotes do not delimit VBA strings; double quotes do. A single quote can appear directly inside a double-quoted string, although it has a separate role when used to start a VBA comment outside a string.
How do I add quotation marks to a cell with VBA?
Build a string using doubled quotes or Chr(34), then assign it to the cell’s .Value property. Use .Formula instead when you intend to assign an Excel formula.
Why does my VBA formula show too many quotes?
The VBA source displays doubled pairs so that one quote reaches the formula. Inspect the formula stored in the cell: it should contain the normal quote marks required by Excel, not VBA’s doubled-quote notation.
Related
- Excel Split Cell Value by Delimiter: Methods and Tips
- Excel Split Cells Into Multiple Columns
- Excel Split Cell Data Into Multiple Columns
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Word VBA: Remove Formatting While Keeping StylesTo remove direct formatting from selected text in Word VBA while keeping its assigned styles, reset both its…
- Trim in VBA: What It Removes and When to Use ItIn VBA, Trim is a standard built-in string function that removes ordinary spaces from the beginning and end…
- Excel VBA Active Range: What Selection Really MeansExcel VBA does not have a built-in ActiveRange property. To refer to the range currently selected in Excel,…
- Excel VBA: Get a Value From a UserForm TextBoxIn Excel VBA, read a UserForm TextBox with its Value property: for example, userName = Me.txtName.Value inside the…
- Excel VBA: How to Include Double Quotes in StringsIn Excel VBA, put two double-quote characters inside a string literal wherever you want the resulting text to…
- Excel VBA: Check Whether a Cell Contains TextTo check whether a cell contains a particular string in Excel VBA, use InStr and test whether its…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.