Placing double quotes inside a string in VBA

I am having a hard time figuring out how to put a double quote (") in a String in VBA. I know I can easily do this with a function char(34)

. I also understand that another way to do this is to use 4 double quotes:. """"

It all comes from previous SO post:

How to put double quotes in a string in vba?

However, my question is ... Why are 4 quotes needed? The first two act as an escape, the third is the quote itself, and the fourth is the closing quote? Or does it work differently? I haven't been able to find a specific answer as to how VBA treats these double quotes.

I also noticed that if I try to add or remove the number of double quotes in the String, Visual Studio will dynamically add or remove double quotes. For example, I initially had the following line:

data = TGName + """ + iterator.Value + """


... which prints the following in the message field:

enter image description here

However, if I try to adjust the second set of double quotes at the end of line ( + """

) 3 through 4, Visual Studio will automatically adjust that to 5. There is no way for me to have only 4 quotes at the end. This is the resulting line in the message box:

enter image description here

The lines in the message blocks are not the actual output that I hope to get, they are purely for experimental purposes. However, I noticed that there is clearly a need for the number of quotes allowed inside a String in VBA. Does anyone know what this is? Why does the IDE force the insertion of an extra quote on the second line? Can anyone explain the differences between the actual content of the string and the formatting quotes in both cases that I have described?

As always, any help on this would be greatly appreciated :)


source to share

2 answers

The general rule is as follows.

The first double quote (DQ) declares the beginning of a string. Subsequently, some DQs announce line termination. However, if DQ precedes DQ, it is "escaped". Escaped means that it is the character part of the string, not a delimiter.

In simple terms, when you have an even number of consecutive double quotes within a string, let's say 2n

that means there are n escaped double quotes. When the number is odd, let's say 2n+1

you have n escaped DQs and a separator.

Examples of

  """ + iterator.Value + """
' delimiter " + iterator.Value + " delimiter
'           ^ escaped            ^ escaped

  """ + iterator.Value + """"
' delimiter " + iterator.Value + ""  ' (missing enclosing delimiter) 
'           ^ escaped            ^^ both escaped.


In the latter case, the last separator is missing, for this reason VS inserted it for you and you got 5 DQs.

Finally, a specific case """"

(4 DQs in total), the first and last are delimiters, and inside there a DQ escaped. This is equivalent chr(34)




To add the iterator value to TGName with quotes, you can do this:

Data = TGName & """" & iterator.Value & """"


or that:

Data = TGName & Chr(34) & iterator.Value & Chr(34)


Note. I replaced the + signs with and because this is just VBA best practice when concatenating strings.



All Articles