xl vba double quotes in string literal
'The double quotation mark (") is ASCII character 34, so the
'following Excel formula works:
=CHAR(34) & "Excel rocks." & CHAR(34)
'This formula will display: "Excel rocks."
'Using triple double quotes has the same result:
="""Excel rocks."""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.
'
'
'Copyright © 2021 Codeinu
Forgot your account's password or having trouble logging into your Account? Don't worry, we'll help you to get back your account. Enter your email address and we'll send you a recovery link to reset your password. If you are experiencing problems resetting your password contact us
