Sometimes when you are building a text string in VBA you may need to insert quotations marks. That poses a problem because quotation marks are used to surround the text you want to join and using them within the text is problematic. There is a solution.

Here’s a recent problem I was faced with.

Someone wanted to highlight text within an email and they were using HTML to build the email message within VBA.

They asked me how to do it. I has no idea, so I googled HTML and highlighting and found some code to highlight text yellow.

<span style="background-color: #FFFF00;">text to highlight</span>

Adding this to the VBA should highlight the text to highlight in the middle of the above HTML code.

The problem is that the style uses quotation marks around the background-color.

To get around that we can use an ASCII character for a quotation mark.

The Chr function can return individual characters. The character we need is 34 – again a google search for ASCII character numbers. So Chr(34) can be used in place of a quotation mark.

The piece of VBA code that we want to highlight is.

Range("A1").Value

Here is the code that will highlight the value from cell A1.

"<span style=" & Chr(34) & "background-color: #FFFF00;" & Chr(34) & ">" & Range("A1").Value
& "</span>"

Replacing the quotation marks with Chr(34) enables you to create the text string required.