How to insert double quotes into a string in a VBA Excel macro

This topic is for Excel macro lovers. Inserting double quotes into a string in a macro is a hassle, because if you just insert a quote, you will get a syntax error. This happens because double quotes serve as a line breaker.

In other programming languages to solve this problem, you can use another string limiter: single or back quotes, for example. However, in VBA, single quotes comment out all the characters on the right, and it simply doesn’t understand back quotes, so this option doesn’t work. So what to do? There are three options:

1. Chr() function

This function allows you to insert any character into the string. The code for quotation marks is 34. So, we can add a quote by splitting the string into two substrings and connecting the values with the & (ampersand) character:

Range("A1").Value = "this is " & Chr(34) & " quote"

2. Variable

To shorten the first option entry, you can create a variable with the value Chr(34) and use it:

a = Chr(34)
Range("A1").Value = "this is " & a & " quote"

3. Two quotes (escaping)

You can also escape a quote with a second quote. If you need to insert them at the beginning or end of a line, there will be three quotes:

Range("A1").Value = """this is - "" quote"""

When inserting formulas with strings in macros — you have to spend a lot of time on this.

I hope this post will save someone some time and nerves. If it’s useful, give it a like and subscribe to my Medium so you don’t miss out 😊. More useful articles on my website ItWebMind.com, Facebook, Twitter, LinkedIn and Reddit. You can support me on Patreon or ByMeACoffee.