excel-vba quotes in string literal code example

Example 1: excel formula how to create strings containing double quotes

'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."""

Example 2: 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.
'
'
'

Example 3: 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)"