excel vba double quotes in string literal
'Including double-quote characters in VBA string literals can be confusing.
'First, realize that VBA (unlike many languages) does not recognize the
'apostrophe as a quotation mark. In JavaScript, for example you can simply
'do this:
' var s = 'The " is called the double-quote character.'
'And since the single-quote character and the double-quote character
'are both recognized for quotation, there is no ambiguity for the compiler.
'But VBA only recognizes the double-quote character as a quotation mark. As
'a result, the following VBA would be ambiguous and WILL NOT compile:
' s = "The " is called the double-quote character."
'The solution is to realize that the INTERNAL double-quote character must be
'escaped by another double-quote character:
s = "The "" is called the double-quote character."
MsgBox s '<--displays: The " is called the double-quote character.
'Of course, the entire string literal, like all string literals, must include
'quotes both at the beginning and the end of the literal.
'Some examples:
MsgBox "" '<--displays: (Nothing... an empty string)
MsgBox " "" " '<--displays: " (1 quote w/1 space before and after)
MsgBox " """ '<--displays: " (1 quote w/1 space before)
MsgBox """ " '<--displays: " (1 quote w/1 space after)
MsgBox """" '<--displays: " (1 quote)
MsgBox """""" '<--displays: ""
MsgBox """""""" '<--displays: """
MsgBox """Quoted text""" '<--displays: "Quoted text"
'Remeber that the very first quote and the very last are required to denote
'the entire string literal. All of the INTERNAL quotes are escaped (doubled-up).
'-------------------------------------------------------------------------------
'Another method is to break the text into multiple string literals and
'concatenate:
MsgBox """" & "Quoted text" & """" '<--displays: "Quoted text"
'Or using a constant...
Const Q As String = """"
MsgBox Q & "Quoted text" & Q '<--displays: "Quoted text"
'-------------------------------------------------------------------------------
'Another method is to use ASCII character 34:
MsgBox Chr(34) & "Quoted text" & Chr(34) '<--displays: "Quoted text"
'Or...
q = Chr(34)
MsgBox q & "Quoted text" & q '<--displays: "Quoted text"
'-------------------------------------------------------------------------------
'Another method is to use substitution:
MsgBox Replace("/qQuoted text/q", "/q", """") '<--displays: "Quoted text"
'The substitution method can really pay off if there are a lot of internal
'quotation marks that need to be included in the string literal. We are
'simply replacing embedded /q with actual double-quote characters.
'
'There is nothing special about /q. You could use a different code, but be
'sure to pick something unique so that you do not unintentionally replace
'inappropriate text.
'
'
'