Placing Double Quotes Within a String in VBA

问题:

I'm having a hard time understanding how to place a double quote (") within a String in VBA. I know that I can easily do this using the char(34) function. I also understand that another way of doing this is to use 4 double quotes: """". All of this comes from a previous SO post:

How do I put double quotes in a string in vba?

However, my question is.... Why are 4 quotes needed? Do the first two act as the escape, the third is the quote itself, and the fourth is the terminating quote? Or does it work in a different way? I haven't been able to find a concrete answer as to how VBA treats these double quotes.

I've also noticed that if I try adding or removing the number of double quotes within a String, Visual Studio will dynamically add or remove double quotes. For example, I initially had the following String:

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

...which produces the following within a message box:

However, if I try adjusting the second set of double quotes at the end of the String (+ """) from 3 to 4, Visual Studio automatically adjusts this to 5. There's no way for me to only have 4 quotes at the end. This is the resulting String within a message box:

The Strings within the message boxes aren't the actual output that I'm hoping to have, they're purely for experimental purposes. However, what I've noticed is that there clearly is a requirement for the number of quotes that are allowed within a String in VBA. Does anyone know what that requirement is? Why is the IDE forcefully inserting an additional quote in the second String? Can someone explain the differences between the actual String contents and the formatting quotes within both cases that I've described?

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

回答1:

The general rule is as follows.

The first double-quote (DQ) announces the beginning of a string. Afterwards, some DQ announces the end of the string. However, if a DQ is preceded by a DQ, it is "escaped". Escaped means it is a character part of the string, not a delimiter.

Simply put, when you have any even number of consecutive double-quotes inside a string, say 2n, this means there are n escaped double-quotes. When the number is odd, say 2n+1, you have n escaped DQs and a delimiter.

Examples

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

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

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

Finally the particular case """" (just 4 DQs), the first and last are delimiters, and inside there's one escaped DQ. This is equivalent to chr(34).

回答2:

To append iterator value to TGName in quotes, you can do this:

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

or this:

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

Note: I replaced + signs with & because that's simply a VBA best practice when concatenating strings.