why VBA need 4 doublequote instead of 3 for a single doublequote?

[Flicker]

Hi,

I want to convert Excel file to CSV but Excel won't allow to add double quote in text data. So, I have to find a way on my own.

I found a very useful code here http://www.ozgrid.com/forum/showthread.php?t=132763 by AAE (Thank you very much, AAE)

However, the above code does not fulfill my need. The result I want are as follow

<table border="0" cellpadding="0" cellspacing="0" width="128"><col style="width: 48pt;" span="2" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 48pt;" height="20" width="64">Data</td> <td style="width: 48pt;" width="64">Result</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">1234</td> <td align="right">1234</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">abc</td> <td>"abc"</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">"abc"</td> <td>"abc"</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">"def</td> <td>""def"</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">ghi"</td> <td>"ghi""</td> </tr> </tbody></table>
So, I modify the code by trial and error and the final working code are follow;

Sub Add_DQuote()
    Dim r As Range
    With Selection
        For Each r In Selection
        'MsgBox (r.Value)
        'MsgBox Mid(r.Value, 1, 1)
        'MsgBox Mid(r.Value, Len(r.Value), 1)
        'MsgBox Mid(r.Value, 1, 1) = """" And Mid(r.Value, Len(r.Value), 1) = """"
        If Mid(r.Value, 1, 1) = """" And Mid(r.Value, Len(r.Value), 1) = """" Then
           r.Value = r.Value
        Else
            If IsNumeric(r.Value) = True Then
                r.Value = r.Value
            Else
                r.Value = """" & r.Value & """"
            End If
        End If
        Next
    End With
End Sub

The question is I do not understand why VBA need 4 doublequote here instead of 3 ?
If Mid(r.Value, 1, 1) = """"

 

[Michael M]

Because originally you wanted a string of text therefore you enclose it in double quotes
if you then want a string of text to include double quotes you have to enclose everything in another set of double quotes.
eg,

a= "a string" will provide the result a string without the quotes

a = "" a string "" will provide the result " a string " with quotes

remember, the quotes have to open AND close the string.

 

[Flicker]

Because originally you wanted a string of text therefore you enclose it in double quotes
if you then want a string of text to include double quotes you have to enclose everything in another set of double quotes.
eg,

a= "a string" will provide the result a string without the quotes

a = "" a string "" will provide the result " a string " with quotes

remember, the quotes have to open AND close the string.

That's exactly what I understood, Michael M.

However, the above statement [If Mid(r.Value, 1, 1) = """"] use to test 1 character is a single quote or not. The first time I use """ and it won't work and Excel fixed my code automatically. But I still want to know how the escape syntax work anyway.

 

[Andrew Poulsom]

Actually:

a = ""a string""

gives rise to a syntax error. You need:

a = """a string"""

I imagine it's because "" is interpreted as null. As you say if you type:

a = """

VBA converts it to:

a = """"

presumably to satisfy parsing rules.