Answers for "xl vba double quotes in string literal"


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."""
Posted by: Guest on March-28-2020

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

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"


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.
Posted by: Guest on December-24-2020

use " inside strin vba

'Method 1
Worksheets("Sheet1").Range("A1").Formula = "IF(Sheet1!A1=0,"""",Sheet1!A1)" 

'Method 2
Worksheets("Sheet1").Range("A1").Formula = "IF(Sheet1!A1=0," & CHR(34) & CHR(34) & ",Sheet1!A1)"
Posted by: Guest on November-14-2020

Code answers related to "xl vba double quotes in string literal"

Code answers related to "VBA"

Browse Popular Code Answers by Language