3

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:

enter image description here

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:

enter image description here

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 :)

Community
  • 1
  • 1
coolDude
  • 647
  • 2
  • 11
  • 27
  • My best guess is that the count of quotes within a string can't be even. This is likely due to a pair of quotes being seen as a string, and in the case of four quotes it would be seen as two sets of "" which would just result in two null strings. It likely tries to overcome this by making sure there is only an odd count of strings. Honestly, I cant say for sure if this is exactly what is happening, but the logic would make the most sense. Also, why not just avoid the headache? Use Chr(34) so you get exactly what you want (especially since you note that you know this.) – Brandon Barney Mar 22 '17 at 19:37

2 Answers2

7

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).

A.S.H
  • 29,101
  • 5
  • 23
  • 50
  • I would like to confirm my understanding of this. For the 4 DQ case, the 2nd DQ is the actual escape character. Is this synonymous to \ in Java (which is what I'm more familiar with)? So `\n` indicates a new line and \ is the actual escape character. And any characters following the \ will be interpreted differently. Is that correct? Or am I misunderstanding something? – coolDude Mar 22 '17 at 21:59
  • @coolDude correct, the escaped characters in *C-derived* languages is the `\ `. In those languages, sequential DQs usually announce *concatenation*; i.e. `"a "" b"` <==> "a b". On the other hand, `\ ` has no special meaning in VB languages. You escape a DQ with a DQ. For a similarly, think of how you escape the `\ ` in Java with another `\ `. An even number of `\ `, say 2n, is n escaped backslashes. – A.S.H Mar 22 '17 at 22:09
  • And by the way, there is no general *escape* in VB. Only double-quotes can be escaped, with another double-quote. To insert a new line in a string you have to *compose it* like `"line 1" & vbCrLf & "line 2"`. – A.S.H Mar 22 '17 at 22:32
  • These 2 are correct to concatenate quotes and a variable: `""" + iterator.Value + """`, `"""" + iterator.Value + """"`, this is not correct because it leads to `"..""`: `""" + iterator.Value + """"` – Timo Oct 06 '21 at 14:46
0

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.

Gordon Bell
  • 13,337
  • 3
  • 45
  • 64