Replace Function Doesn’t Replace Double Quotation in VBA

13-11-2012

I had to remove all double quotation marks from a string using VBA.

I tried to do this by using the Replace function as shown below.

Also Note that two double quotation marks are used to represent the quotation mark. This is because the quotation mark is a special character in VBA and we need to escape it by using the escape character. In VBA the escape character is quotation character itself so we need to use two quotation marks.

' * Note the Replace part
str = """" + item.Subject + """ """ + item.Sender + """ """ + Replace(item.body, """", "'") + """"

This did not replace the ” values from my string.

First I thought the Replace function was not working.

Then I figured that the double quotation character ” you type in vba editor may not be the same as the double quotation character produced by other editors  (like outlook).

For example the usual double quotation character (produced by the VBA editor) has the ASCII code of 34 but the double quotation character produced by outlook has the ASCII code 147.

VBA has Chr() function which returns the character when you pass in the ASCII code. You should use this instead of typing the double quotation character itself.

Solution:

Replace str = """" + item.Subject + """ """ + item.Sender + """ """ Replace(item.body, Chr(147), "'") + """"

A better solution:
As I mentioned some programs use ASCII 34 for double quotes while some uses 147. So to be safe, you can remove both as described below:

str = """" + item.Subject + """ """ + item.Sender + """ """ + Replace(Replace(item.body, Chr(34), "'"), Chr(147), "'") + """"

Note: if you want to get the ASCII code of a character, you can use the function ASC() this does the reverse of the Chr() function.
e.g.

Msgbox Asc("X")  ' This will display 88 which is the ASCII code for "X" (capital X)

I hope this helped!
Menol
ILT