VBA & Macro Problems

How to Add Quotation Marks Around a Value in Excel VBA

Aamir Naveed Akram Oct 20, 2026 868 views

Question details

The user needs to assign a date value (such as 03/31/2023) to a cell using a VBA macro so that the value is explicitly wrapped in visible quotation marks.

How to Add Quotation Marks Around a Value in Excel VBA

Product: Excel VBA
Device & OS: not provided
Scenario: Writing a VBA script to output a date string (either a static date or a dynamically calculated one like the end of the previous month) into a specific spreadsheet cell.
Observed behavior: When the date is assigned directly to the cell in VBA, the spreadsheet automatically recognizes it as a standard date format and displays it without the literal quotation marks.

Before you start

Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have enabled the Developer tab to access the VBA editor.

Solution 1Recommended

Use Doubled Quotation Marks in the VBA String

The standard way to include literal quotation marks within a VBA text string is by doubling them up. This acts as an escape sequence.

In VBA, quotation marks denote the beginning and end of a string. To output an actual quotation mark character inside the cell, you must type two quotation marks consecutively inside the string.

Open the VBA Editor

Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor, and locate your target module.

Define your variable with double quotes

Create a string variable and wrap your date in three sets of quotation marks. Type: Dte = """03/31/2023""".

Assign the value to the cell

Assign the variable to your desired cell. For example, type: Range("H5").Value = Dte. Run the macro, and the cell will display the date wrapped in quotes.

Use Doubled Quotation Marks in the VBA String

Pro Tip: The outermost quotes define the string in VBA, while the inner doubled quotes translate into a single literal quotation mark printed in your spreadsheet cell.

Solution 2

Apply a Dynamic Date Formula with Escaped Quotes

If you need a dynamic date (like the last day of the previous month) enclosed in quotes, you can assign an Excel formula directly via VBA.

Write VBA Macros in WPS Office

Use WPS Spreadsheet for Powerful VBA Macro Execution

WPS Spreadsheet provides excellent built-in VBA support, allowing you to run macros, format cells, and automate data entry seamlessly without having to change your existing Microsoft Excel scripts.

1. Download and Install WPS Office: Get the free WPS Office suite from the official website and install it on your device.
2. Open your macro-enabled file: Launch WPS Spreadsheet and open your existing .xlsm or .xlsx workbook.
3. Access the Developer Tab: Navigate to the Developer tab on the ribbon to open the VBA Editor and execute your scripts natively.

Fully compatible with Microsoft Excel VBA syntaxLightweight and fast for processing large macrosIntuitive Developer tools interfaceFree to download and use

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel automatically remove my quotation marks in VBA?

When you input a standard string that looks like a date or number into a cell, Excel automatically converts it to its underlying serial value format, which drops any text-based formatting or literal quotes unless they are explicitly escaped.

Can I use Chr(34) instead of double quotation marks in VBA?

Yes. Chr(34) generates a literal double quote character. Many developers prefer writing Range("H5").Value = Chr(34) & "03/31/2023" & Chr(34) because it can be easier to read than multiple consecutive quotation marks.

How do I format a cell as Text using VBA before inserting a date?

You can format a cell as text prior to inserting your value to prevent Excel from auto-converting it to a date. Use the code Range("H5").NumberFormat = "@" right before your Range("H5").Value assignment.

Excel & Data Problems