quotes vba

[dulitul]

Hey,


I am just trying to understand the principle of the where and how many quotes I should put when using worksheetfunction in vba. For instance, in this code

ton = Evaluate("SUBSTITUTE(A1,""mon"",""mir"")") I have put double quotes by mon and mir otherwise it does not function (with single quotes) and I dont know why??

I have seen many codes with worksheetfunctions like .Value = Evaluate("IF(" & .Address & " = """","""",Text(" & .Address & ",""" & strFormat & """))"). I can understand the forumla used but not the principle of using the quotes?

Any help is appreciated.

 

[wigi]

Hello

Within the Evaluate function, you have a string, text.
Hence, if mon needs quotes (it should be regarded as text and not as a variable for example) you need to double up the quotes syntax-wise.
Is this clear?

 

[Jerry Sullivan]

Working with quotes in VBA can be very confusing.

A progression of examples might help illustrate the explanation provided by Wigi.

A key principal to understand is that single quotes are used to build the formula expression to be evaluated
expression = "String1" & stringVariable2 & "String3"

...and double quotes are used wherever a Literal quotation mark character is needed in the resulting expression

The examples below use Blue font to denote quotes and ampersands used to build the formula expressions
and Red font to denote Literal quotes and ampersands needed in the resulting expression.

Sub Test1()
'--Evaluate the worksheet formula =2*3

    Range("A1") = Evaluate([COLOR="#0000CD"][B]"[/B][/COLOR]=2*3[COLOR="#0000CD"][B]"[/B][/COLOR])       'Result: 6

    '--Eliminating the equals sign yields the same result from the Evaluate Method

    Range("B1") = Evaluate([COLOR="#0000CD"][B]"[/B][/COLOR]2*3[COLOR="#0000CD"][B]"[/B][/COLOR])        'Result: 6

    
    '--Adding spaces between wrapping quotes yields the same result

    Range("C1") = Evaluate([COLOR="#0000CD"][B]"[/B][/COLOR] 2*3 [COLOR="#0000CD"][B]"[/B][/COLOR])      'Result: 6

End Sub

Sub Test2()
'--Evaluate the worksheet formula ="Year " & "2013"

    Range("A2") = Evaluate([COLOR="#0000CD"][B]"[/B][/COLOR][COLOR="#FF0000"][B]""[/B][/COLOR]Year [COLOR="#FF0000"][B]"" & ""[/B][/COLOR]2013[COLOR="#FF0000"][B]""[/B][/COLOR][COLOR="#0000CD"][B]"[/B][/COLOR])  'Result: Year 2013

    Debug.Print """Year "" & ""2013"""              'Displays: "Year " & "2013"
    
End Sub

Sub Test3()
'--Evaluate the worksheet formula ="Year " & "2013" using a variable year

    Const sYear As String = "2013"
    Range("A3") = Evaluate([COLOR="#0000CD"][B]"[/B][/COLOR][COLOR="#FF0000"][B]""[/B][/COLOR]Year [COLOR="#FF0000"][B]"" & ""[/B][/COLOR][COLOR="#0000CD"][/COLOR][COLOR="#0000CD"][B]" &[/B][/COLOR] sYear[COLOR="#0000CD"] [B]& "[/B][/COLOR][COLOR="#FF0000"][B]""[/B][/COLOR][COLOR="#0000CD"][/COLOR][COLOR="#0000CD"]"[/COLOR])   'Result: Year 2013


    '--Detail of the 3 strings used to build the formula expression
  
  
    Debug.Print """Year "" & """                    'Displays: "Year " & "
    Debug.Print sYear                               'Displays: 2013
    Debug.Print """"                                'Displays: "
    Debug.Print """Year "" & """ & sYear & """"     'Displays: "Year " & "2013"

    '--The same syntax is needed with an Integer type variable

    Const iYear As Integer = 2013
    Range("B3") = Evaluate([COLOR="#0000CD"][B]"[/B][/COLOR][COLOR="#FF0000"][B]""[/B][/COLOR]Year [COLOR="#FF0000"][B]"" & ""[/B][/COLOR][COLOR="#0000CD"][/COLOR][COLOR="#0000CD"][B]" &[/B][/COLOR] iYear[COLOR="#0000CD"] [B]& "[/B][/COLOR][COLOR="#FF0000"][B]""[/B][/COLOR][COLOR="#0000CD"][/COLOR][COLOR="#0000CD"]"[/COLOR])   'Result: Year 2013
End Sub

Displaying the expressions in the Immediate Window (Ctrl-G in the VB Editor) can help greatly working through more complex expressions.

 

[dulitul]

Hey,

Thanks a lot for the info. I ve got just a question. In these examples


ange("A3") = Evaluate("""Year "" & """ & sYear & """") 'Result: Year 2013and
Range("B3") = Evaluate("""Year "" & """ & iYear & """") 'Result: Year 2013Why do we put three quotes in the middle and 4 quotes at the end. This is just space, right? I mean the single clothes after the $. More specifically,I mean the clothes after the first & and the clothes after the last &. What's the difference? Thanks a lot!</pre>

</pre>

 

[Jerry Sullivan]

It's clearer to look at it in terms of where 2 quotes are needed and where 1 quotes are needed.
Then is just works out that in some places the combination of consecutive quotes is 3 and in others it's 4.

Working backwards from the expression to be evaluated

2 Red Quotes are needed to literal quote in the expression
="Year " & "2013" -> =""Year "" & ""2013""

1 Blue quote is needed around each string part that isn't a variable
"string1" & variable2 & "string3"
where string1=""Year "" & ""
variable2= 2013
string3= ""

Substituting values of string1, variable2 and string3 yields
"""Year "" & """ & sYear & """"

adding spaces could make this clearer to read though it will add spaces in the re....
" ""Year "" & "" " & sYear & " "" "

 

[DocAElstein]

Working with quotes inVBA can be very confusing. <v:shapetype id="_x0000_t75" stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"> <v:stroke joinstyle="miter"> <v:formulas> <v:f eqn="if lineDrawn pixelLineWidth 0"> <v:f eqn="sum @0 1 0"> <v:f eqn="sum 0 0 @1"> <v:f eqn="prod @2 1 2"> <v:f eqn="prod @3 21600 pixelWidth"> <v:f eqn="prod @3 21600 pixelHeight"> <v:f eqn="sum @0 0 1"> <v:f eqn="prod @6 1 2"> <v:f eqn="prod @7 21600 pixelWidth"> <v:f eqn="sum @8 21600 0"> <v:f eqn="prod @7 21600 pixelHeight"> <v:f eqn="sum @10 21600 0"> </v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:formulas> <vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"> <o:lock aspectratio="t" v:ext="edit"></o:lock></vath></v:stroke></v:shapetype><v:shape style="width: 18.75pt; height: 15pt; visibility: visible; mso-wrap-style: square;" id="Bild_x0020_2" type="#_x0000_t75" alt="http://www.mrexcel.com/forum/images/smilies/icon_banghead.gif" o:spid="_x0000_i1025"> <v:imagedata o:title="icon_banghead" src="file:///C:\Users\Elston\AppData\Local\Temp\msohtmlclip1\01\clip_image001.gif"></v:imagedata></v:shape>
… …...

<o></o>
<o> </o>

Hey,

I am just trying tounderstand the principle of the where and how many quotes I should put whenusing worksheetfunction in vba. For instance, in this code

ton =Evaluate("SUBSTITUTE(A1,""mon"",""mir"")")I have put double quotes by mon and mir otherwise it does not function (withsingle quotes) and I dont know why??

I have seen many codes with worksheetfunctionslike .Value = Evaluate("IF(" &.Address & " ="""","""",Text(" & .Address& ",""" & strFormat &"""))"). I can understand the forumla used but not theprinciple of using the quotes?

Any help is appreciated.

<o></o>
<o> </o>
.. Hi. <o></o>
. I hit this Thread whilst googling through MrExcel to further understand the trickysyntax convention within the VBA Evaluate method. Maybe other people will do so inthe future so here is a furthercontribution. (If you are not viewing in the Mr Excel editor EnhancedInterface - Full WYSIWYG , then youmay want to by-pass this post as you mayget the “Hump” with the formatting I need to try to clarify my thoughts!!).<o></o>
. I thought I had it covered through participation in a couple of other threads:<o></o>
http://www.mrexcel.com/forum/excel-questions/799417-using-variable-references-indicate-range-cells-merge.html?#post3908684<o></o>
www.excelfox.com/forum/f22/concatenating-balls-1891/<o></o>
<o> </o>
… Then I read this andstarted thinking again<o></o>
<o> </o>

……..
……A key principal to understand is that

single quotes are used to build the formulaexpression to be evaluated
expression = "String1" & stringVariable2 &"String3"

...and double quotes are usedwherever a Literal quotation markcharacter is needed in the resulting expression…...

<o></o>
<o> </o>
. Maybe another helpful way to think about it is to go back to the basic definition of the VBA Evaluate Method and somewhat elaborate it.<o></o>
<o> </o>
. I would say the Microsoft definition ( Application.Evaluate Method (Excel) ) issomewhat lacking<o></o>
. Things like “…A formula or the name of the object, using the naming convention of Microsoft Excel….“ Or „….Converts aMicrosoft Excel name to an object or a value…“ give one the idea that the syntax is simply of the form:<o></o>
…. You type this: <HTML>    </HTML>Evaluate(“ <HTML>    </HTML> “), and then in the spaceyou type exactly what you would in the Spreadsheet cell but omitting the =<o></o>
<o> </o>
. I would say something along the followinglines. The VBA Evaluate Method is a method which allows Excel Spreadsheet functions to be used in VBA either alone or in combination withVBA code. In addition there are some unique rules /conventions which must be observed which only make it possible sometimes to “…type exactly what you would in the Spreadsheet cell for an Excel SpreadsheetFunction….”A by-product of one of these rules concerns the Quotes convention(or rather the “problem with quotes”), whereby additional things are possiblewith the VBA Evaluate Method , that of writing in Text <o></o>
<o> </o>
. To demonstrate. Following arguments in Post #9 <HTML>   </HTML> http://www.mrexcel.com/forum/excel-questions/799417-using-variable-references-indicate-range-cells-merge.html <HTML>   </HTML> we have various combinationsof Excel Spreadsheetfunctions and VBA code within the Evaluate(“ <HTML>    </HTML> “) to give various combinations of the value in the first cell in a Workbook<o></o>
<o> </o>
<b></b><table cellpadding="2.5px"rules="all" style=";background-color: #FFFFFF;border: 1pxsolid;border-collapse: collapse; border-color:#A6AAB6"><colgroup><col width="25px"style="background-color: #E0E0F0" /><col/></colgroup><thead><tr style=" background-color: #E0E0F0;text-align:center;color:#161120"><th></th><th>A</th></tr></thead><tbody><tr><td style="color: #161120;text-align:center;">1</td><td style="text-align: center;color:#B2B2B2;;">Cookies</td></tr></tbody></table><pstyle="width:6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em0.5em;border: 1px solid #A6AAB6;border-top:none;text-align:center;background-color: #E0E0F0;color:#161120">HarryDATA3</p><br /><br /><o></o>
<o> </o>
Sub EvaluateSyntax1()<o></o>
<o> </o>
MsgBox "First cell value is" & Range("A1").Value ' Full VBA <o></o>
MsgBox "First cell value is" & Evaluate("A1") ' Equivalent to writing spreadsheet formula =A1 <o></o>
MsgBox "First cell value is" & Evaluate(Range("A1").Address) ' VBA code can be directly written in EvaluateFunction…..<o></o>
MsgBox "First cell value is " & Evaluate("<HTML>   </HTML> " & Range("A1").Address&" ")' ..or after coming into spreadsheet worlds with Evaluate(" we go into VBA Code with " & and follow a similar argument to come out… or…<o></o>
MsgBox "First cell value is" & Evaluate(" <HTML>   </HTML> " & Range("A1").Address) ' ..Combination of the two !<o></o>
<o> </o>
End Sub<o></o>
<o> </o>
In that thread http://www.mrexcel.com/forum/excel-questions/799417-using-variable-references-indicate-range-cells-merge.htmlwe went further to give us the value of cell A1 a couple of times.<o></o>
<o> </o>
Sub EvaluateSyntax2()<o></o>
<o> </o>
MsgBox "First cell value written a couple of times is " & Evaluate("A1" & "&" & "$A$1")' We are evaluating 3 spreadsheet things here<o></o>
MsgBox "First cell value written a couple of times is " &Evaluate("A1" & "&" & Range("A1").Address)<o></o>
MsgBox "First cell value written a couple of times is " &Evaluate("A1" & "&" & Range("A1").Address& " ")<o></o>
_{<o> </o>}
End Sub<o></o>
<o> </o>
By wanting to include a space or separator “ – “ between the two values we raninto problems with this code whichfollowing the standard conventions up until now should have worked:<o></o>
MsgBox …… <HTML>    </HTML> …… & Evaluate("A1" & "&" &"" -"" & "&" & "A1")<o></o>
. It did however not work because of the syntax problems discussed. <o></o>
. The following solution was discussed<o></o>
MsgBox …… <HTML>    </HTML> …… & Evaluate("A1" & "&"" - ""&" & "A1")<o></o>
<o> </o>
. I am struggling now to see exactly the parallel with Jerry’s and Wigi’sarguments.. But it appears I may have coerced somehow text with a double quote. The keyseems to be the intimacy of the two quotes. <o></o>
. So this works<o></o>
MsgBox …… <HTML>    </HTML> …… & Evaluate("<HTML>   </HTML> ""- "" ")<o></o>
. But this does not.<o></o>
MsgBox …… <HTML>    </HTML> …… & Evaluate("<HTML>   </HTML> " <HTML>   </HTML> " - " " ")<o></o>
<o> </o>
. But to be quite honest I am beginning to … ….. again on this one.<o></o>
. Coming back to a similar situation to the OP original problem:<o></o>
<o> </o>
Sub EvaluateSyntax3()<o></o>
<o> </o>
'MsgBox "First cell value is" & Evaluate("SUBSTITUTE(A1,"Cook","Kook")")<o></o>
MsgBox "First cell value is" & Evaluate("SUBSTITUTE(A1,""Cook"",""Kook"")")<o></o>
End Sub 'EvaluateSyntax3() <o></o>
<o> </o>
. The first line does not work. Similar argument to my example in <HTML>   </HTML> http://www.mrexcel.com/forum/excel-questions/799417-using-variable-references-indicate-range-cells-merge.html <HTML>   </HTML> : Evaluate is confused with seeing Cook afterwhat it takes as a closing quote <HTML>   </HTML> " <HTML>   </HTML> rather than a spreadsheet quote <HTML>   </HTML> " <o></o>
. the solution would appear to be “coercing somehow text with a double quote”<o></o>
<o> </o>
. Any comments?<o></o>
<o> </o>
Alan Elston.<o></o>
<o> </o>
……………………………………………<o></o>
Codes again in code tags:<o></o>
<o> </o>

<o:p></o:p>[/COLOR][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT="Verdana"]<o:p>[COLOR=#000000] [/COLOR]</o:p>[/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT="Verdana"][COLOR=#000000][color=darkblue]Sub[/color]EvaluateSyntax1()<o:p></o:p>[/COLOR][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT="Verdana"][COLOR=#000000]MsgBox "First cell value is" &   Range("A1").Value<o:p></o:p>[/COLOR][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT="Verdana"][COLOR=#000000]MsgBox "First cell value is" &  Evaluate("A1")[color=green]' Equivalent to writing spreadsheet formula  =A1[/color]<o:p></o:p>[/COLOR][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT="Verdana"][COLOR=#000000]MsgBox "First cell value is" &   Evaluate(Range("A1").Address)[color=green]'  VBA code can be directlywritten in Evaluate Function…..[/color]<o:p></o:p>[/COLOR][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT="Verdana"][COLOR=#000000]MsgBox "First cell value is" &   Evaluate(" "& Range("A1").Address & " ") [color=green]' ..orafter coming into spreadsheet World with Evaluate ("  we go  into VBA Code  World with " & and follow a similarargument to come out… …[/color]<o:p></o:p>[/COLOR][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT="Verdana"][COLOR=#000000]MsgBox "First cell value is" & Evaluate(" " &   Range("A1").Address) [color=green]'..combination of the two ![/color]<o:p></o:p>[/COLOR][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT="Verdana"][COLOR=#000000][color=darkblue]End[/color][color=darkblue]Sub[/color] [color=green]'EvaluateSyntax1()[/color]<o:p></o:p>[/COLOR][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT="Verdana"][COLOR=#000000][color=green]'[/color]<o:p></o:p>[/COLOR][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT="Verdana"][COLOR=#000000][color=darkblue]Sub[/color]EvaluateSyntax2()<o:p></o:p>[/COLOR][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT="Verdana"]<o:p>[COLOR=#000000] [/COLOR]</o:p>[/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT="Verdana"][COLOR=#000000]MsgBox "First cell value writtena couple of times is " &  Evaluate("A1" &"&" & "$A$1")<o:p></o:p>[/COLOR][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT="Verdana"][COLOR=#000000]MsgBox "First cell value writtena couple of times is " &  Evaluate("A1" &"&" & Range("A1").Address)<o:p></o:p>[/COLOR][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT="Verdana"][COLOR=#000000]MsgBox "First cell value writtena couple of times is " &  Evaluate("A1" &"&" & Range("A1").Address & " ")<o:p></o:p>[/COLOR][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT="Verdana"][COLOR=#000000][color=green]'MsgBox ".....     ...... " &Evaluate("A1"  &  "&" & "" -"" & "&" & "A1")[/color]<o:p></o:p>[/COLOR][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT="Verdana"][COLOR=#000000]MsgBox "......      ...... " &Evaluate("A1" & "&""- ""&"& "A1")<o:p></o:p>[/COLOR][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT="Verdana"][COLOR=#000000]MsgBox "......      ...... " & Evaluate(""" - "" ")<o:p></o:p>[/COLOR][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT="Verdana"][COLOR=#000000][color=darkblue]End[/color][color=darkblue]Sub[/color] [color=green]'EvaluateSyntax2()[/color]<o:p></o:p>[/COLOR][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT="Verdana"][COLOR=#000000][color=green]'[/color]<o:p></o:p>[/COLOR][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT="Verdana"][COLOR=#000000][color=darkblue]Sub[/color]EvaluateSyntax3()<o:p></o:p>[/COLOR][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT="Verdana"]<o:p>[COLOR=#000000] [/COLOR]</o:p>[/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT="Verdana"][COLOR=#000000][color=green]'MsgBox "First cellvalue is " &Evaluate("SUBSTITUTE(A1,"Cook","Kook")")[/color]<o:p></o:p>[/COLOR][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT="Verdana"][COLOR=#000000]MsgBox "First cell value is" & Evaluate("SUBSTITUTE(A1,""Cook"",""Kook"")")<o:p></o:p>[/COLOR][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT="Verdana"][COLOR=#000000][color=darkblue]End[/color][color=darkblue]Sub[/color] [color=green]'EvaluateSyntax3()[/color]

<o></o>

 

[Jerry Sullivan]

I am struggling now to see exactly the parallel with Jerry’s and Wigi’sarguments.. But it appears I may have coerced somehow [/COLOR]text with a double quote. The keyseems to be the intimacy of the two quotes. <o></o>
. So this works<o></o>
MsgBox …… <HTML>****</HTML> …… & Evaluate("<HTML>***</HTML> ""- "" ")<o></o>
. But this does not.<o></o>
MsgBox …… <HTML>****</HTML> …… & Evaluate("<HTML>***</HTML> " <HTML>***</HTML> " - " " ")<o></o>
<o> </o>
. But to be quite honest I am beginning to … ….. again on this one.<o></o>
. Coming back to a similar situation to the OP original problem:<o></o>
<o> </o>

Alan,

The examples you describe and your observation that regarding double quotes having a different meaning that two single quotes separated by one or more spaces is consistent with this advice...

A key principal to understand is that single quotes are used to build the formula expression to be evaluated
expression = "String1" & stringVariable2 & "String3"

...and double quotes are used wherever a Literal quotation mark character is needed in the resulting expression

It may be clearer to think of the Evaluate function in two parts.
1. First everything in within the parenthesis of the Evaluate function must first be evaluated into a String expression. Evaluate (stringexpression)

2. Use the two rules above to build your string expression.

 

[DocAElstein]

Jerry.<o></o>
<o> </o>
. Thanks again for the replies on previousThreads. I Google through. Helps to getthings clearer and offsets a bit the frustration partly caused by wot I get thefeeling are some poor Microsoft Explanations….<o></o>
(… and also somepeculiar Browser influenced characteristics (*** and missing spaces betweenwords sometimes) of the MrExcel editor don’t help in getting the point acrosswith tricky quote and ampersand conventions.. …...

..

But itappears I may have coerced somehow text with a double quote. The keyseems to be the intimacy of thetwo quotes.
. So this works
MsgBox …… **** …… & Evaluate("***""- "" ")
. But this does not.
MsgBox …… ****…… & Evaluate("*** " ***" - " " ")

. But to be quite honest I am beginning to … <v:shapetype id="_x0000_t75" coordsize="21600,21600" o:spt="75" oreferrelative="t" path="m@4@5l@4@11@9@11@9@5xe" filled="f" stroked="f"> <v:stroke joinstyle="miter"> <v:formulas> <v:f eqn="if lineDrawn pixelLineWidth 0"> <v:f eqn="sum @0 1 0"> <v:f eqn="sum 0 0 @1"> <v:f eqn="prod @2 1 2"> <v:f eqn="prod @3 21600 pixelWidth"> <v:f eqn="prod @3 21600 pixelHeight"> <v:f eqn="sum @0 0 1"> <v:f eqn="prod @6 1 2"> <v:f eqn="prod @7 21600 pixelWidth"> <v:f eqn="sum @8 21600 0"> <v:f eqn="prod @7 21600 pixelHeight"> <v:f eqn="sum @10 21600 0"> </v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:formulas> <vath o:extrusionok="f" gradientshapeok="t" o:connecttype="rect"> <o:lock v:ext="edit" aspectratio="t"></o:lock></vath></v:stroke></v:shapetype><v:shape style="width: 18.75pt; height: 15pt; visibility: visible; mso-wrap-style: square;" id="Bild_x0020_2" type="#_x0000_t75" o:spid="_x0000_i1025" alt="http://www.mrexcel.com/forum/images/smilies/icon_banghead.gif"> <v:imagedata o:title="icon_banghead" src="file:///C:\Users\Elston\AppData\Local\Temp\msohtmlclip1\01\clip_image001.gif"></v:imagedata></v:shape> …<o></o>
…...

)<o></o>
<o> </o>
<o> </o>
. I think it is well worth mastering theEvaluate method and figuring out exactly how it is working (“Thinking”) as itgoes through the built string expression…. I have some abstract ideas of it sometimesjumping in and out of VBA and Spreadsheet “Worlds”. Then wot happens with the double quote could be thought of asa by-product rather than a planned syntax?! (But maybe I will “get over it”!!)<o></o>
…… The simplified clear explanation. <HTML>    </HTML> Evaluate(<HTML>    </HTML> String Expression here <HTML>    </HTML> ) <HTML>    </HTML> is most likely correct and Ijust need to get my head clear on that…<o></o>
…. My abstract ideasare maybe thinking along the lines of “Paringup” “Evaluate” Purple quotes <HTML>    </HTML> “ <HTML>    </HTML> like Code or HTML tags within the Evaluatebrackets. <HTML>    </HTML> So an “unmatchedpair” caused by a third Quote without an ampersand between them puts a spannerin the works which results in wot we interpret as a Literal quotation markcharacter….<o></o>
…… Your…..”Blue font to denote quotes and ampersands used tobuild the formula expressions“ are my blue VBA ampersandsand Purple Evaluate Quotes whichcombined take us in and out of the two “Worlds”
and your “Red ampersands needed in the resulting expression”, are my spreadsheet Green. Your “Red quotes” I see as a strange Purple Greencombination <HTML>    </HTML> "" <HTML>    </HTML> "" <HTML>    </HTML> <o></o>
<o> </o>
. I am probably just reading too much intoit and must find the time to let it run clearly through my head… (But sometimesmy seemingly obvious questions spark off a lot of fundamental debates with biggerbrains then mine!!?).<o></o>
<o> </o>
. But your and other profi’s inputs to theseThreads / Post are always muchappreciated. Helps me stop flying off ina (too) wild tangent<o></o>
<o> </o>
Thanks Again<o></o>
Alan<o></o>
<o> </o>
.P.s.<o></o>
I think I can explaincodes like these working and being equivalent from my “Matched Quote Pair” /Different “Worlds” Theory. <o></o>
Evaluate(Range("A1").Address)
Evaluate(" <HTML>    </HTML> " & Range("A1").Address&" <HTML>    </HTML> ")
Evaluate(" <HTML>    </HTML> "& Range("A1").Address) <o></o>
Evaluate(" "& Range("A1").Address& "")<o></o>
Evaluate(" <HTML>    </HTML> " & & Range("A1").Address&"")<o></o>
Evaluate(Range("A1").Address)
Evaluate(" <HTML>    </HTML> " & Range("A1").Address& " <HTML>    </HTML> ")
Evaluate(" <HTML>    </HTML> " & Range("A1").Address& " <HTML>    </HTML> " & " <HTML>    </HTML> " &"")<o></o>
Evaluate(" <HTML>    </HTML> " & Range("A1").Address& " <HTML>    </HTML> " & "" & " <HTML>    </HTML> ")<o></o>
Evaluate(" <HTML>    </HTML> " & Range("A1").Address& "" & "" & "" & "" )<o></o>
Evaluate(" <HTML>    </HTML> " & Range("A1").Address ) <o></o>
Evaluate(""& Range("A1").Address& "")<o></o>
Evaluate(" <HTML>    </HTML> " & Range("A1").Address&"")<o></o>
…<o></o>
… For example in theempty space between quotes I am evaluating nothing in the spreadsheet World. (As long as in totalI am evaluating at least one thing then I get no error. Trying to evaluatenothing at all </HTML> <HTML>    </HTML> Evaluate(" <HTML>    </HTML> <HTML>    </HTML> ")
does give me anerror)<o></o>
<o> </o>
… I do not immediatelysee a clear explanation using the clearer <HTML>    </HTML> Evaluate(<HTML>    </HTML> String Expression here <HTML>    </HTML> ) <HTML>    </HTML> …..But I probably will when Ithink about it some more!<o></o>

 

[DocAElstein]

Hi <o></o>
. This thread, and in particular Gerry’s Post #3 has become somewhat of a Bible and continually reference as I frequently try to get the understanding clear in my head of the “Quotes in VBA” , as well a generally the Theme of quotes in Excel and in particular handling quotes within the VBA Evaluate Method.<o></o>
. In an attempt to satisfy my lust to finally really understand this I did a rather big Thread response starting from here.<o></o>
http://www.excelfox.com/forum/f2/special-concatenation-2042/index3.html#post9517<o></o>
.. again I continually looked back to this Thread for inspiration.<o></o>
.<o></o>
. I think I can add a good follow up here. <o></o>
<o></o>
. I will try to follow closely Gerry’s arguments from Post #3, adding to them or modifying slightly:<o></o>
.<o></o>
. The examples below use Blue font to denote „VBA World“ quotes and ampersands used to build the formula expressions
and Green font to denote “Excel Spreadsheet World” quotes and ampersands needed in the resulting expression.<o></o>
.<o></o>
. In the above reference I tried very hard to give a theory to prove that "" has indeed a special working such that in particular this""Year""is the required thing to Evaluate such that the element in the string that one builds up for the Evaluate argument does look like what one would type in a Spreadsheet Cell. – The simplest definition of VBA Evaluate Method being that you put in its argument what you would write in a cell and then will give the same result as would that expression ( formula) if you typed it in the cell.<o></o>
.<o></o>
. As Gerry said, it is an “implicit default” type thing that eliminating the equals sign yields the same result from the Evaluate Method but I prefer for clarity initially to include it.. <o></o>
. The following is reproduced in the Code in the Code Window below, and as Gerry said, Displaying the expressions in the Immediate Window (Ctrl-G in the <acronym>VB</acronym> Editor) can help greatly in working through<o></o>
.<o></o>
. 10_ strEvaluate = "=" & """Year*_"""<o></o>
Debug.Print strEvaluate '____________________Displays: ="Year "<o></o>
Debug.Print Evaluate(strEvaluate) '___________Displays: Year_ ( with a space at the end )<o></o>
.<o></o>
. Further In the above Thread I had a Theory proving that linking ""&"" is the required syntax to link text such that <o></o>
.<o></o>
. 20_ strEvaluate = "=" & """Year_""&""2013"""<o></o>
Debug.Print strEvaluate '_____________________Displays: ="Year "&"2013"<o></o>
Debug.Print Evaluate(strEvaluate) '___________Displays: Year 2013<o></o>
.<o></o>
. At this point it is helpful to try lots of variations along the lines of extra spaces around the & and noting results are identical, just as when the corresponding expression is typed into a cell....<o></o>
<o></o>
. 30 strEvaluate = "=" & """Year_""_&_____""2013"""<o></o>
Debug.Print strEvaluate '_____________________Displays: ="Year "_&_____"2013"<o></o>
Debug.Print Evaluate(strEvaluate) '___________Displays: Year 2013<o></o>
All consistent with “Spreadsheet World”<o></o>
<o></o>
BUT a useful point to note: Any attempt to try variations of this<o></o>
"="__ & __________ """Year_""&""2013"""<o></o>
Will not be accepted – VBA will put the syntax back to " & " as this is “VBA World” and are “governed” by code syntax<o></o>
. Along the above lines i can legitimately break and join Spreadsheet World strung by going in and out of VBA World but doing nothing and so the results remain the same:<o></o>
.<o></o>
. 40_ strEvaluate = "=" & """Year_""&""20" & "13"""<o></o>
Debug.Print strEvaluate '_____________________Displays: ="Year "&"2013"<o></o>
Debug.Print Evaluate(strEvaluate) '___________Displays: Year 2013<o></o>
<o></o>
...................................................................<o></o>
<o></o>
. Up until now I have just re iterated what Gerry did. I attempt to add something now. - Get an extra quote to actually be displayed inn the final Evaluated string.. <o></o>
. Taking slowly a simplification first of the code line so far. <o></o>
<o></o>
.50 _ strEvaluate = "=" & """Year_""&""__"""<o></o>
Debug.Print strEvaluate '_____________________Displays: ="Year "&" "<o></o>
Debug.Print Evaluate(strEvaluate) '___________Displays: Year ( with 1 +2 = three spaces at the end )<o></o>
<o></o>
. Further in the referenced Thread I gave some attempt at proof and justification that always are double green quotes "" must always themselves be aired either so ""2013"" or so ""__"" or even so """" <o></o>
. Effectively I have in words a pair of “in” and “out” of Spreadsheet text. I had a theory if Out and in was also syntaxly OK it might cause VBA to chuck out a string indicator "but then maybe get hung up and not know what to do with it...<o></o>
. Though and behold I produced a " ( Something many people can do , but as yet could not give any explanation as to how / why it worked.. <o></o>
<o></o>
.60 strEvaluate = "=" & """Year_""&""__"""""""<o></o>
Debug.Print strEvaluate '_____________________Displays: ="Year "&" """<o></o>
Debug.Print Evaluate(strEvaluate) '___________Displays: Year "<o></o>
<o></o>
.. I try to put what is going on in words and based on my attempt to understand exactly how ExcelVBA is thinking... Breaking down this bit <o></o>
""__""""""<o></o>
. VBA goes into aspreadsheettext bit""___( 2 spaces in this case ) , before it has a chance to come out of that text bit we use accepted syntax of a grouped 4 """" to “ fool “ into going out and in of text throwing up the extra “ before the actual concluding "" is met to match up to the first "" <o></o>
. To give my argument some acceptance it will be seen that this errors:<o></o>
<o></o>
.70 strEvaluate = "=" & """Year_""&""__""__"""""<o></o>
Debug.Print strEvaluate '_____________________Displays: ="Year "&" " ""<o></o>
Debug.Print Evaluate(strEvaluate) '___________Displays: ERROR<o></o>
<o></o>
....................................................................<o></o>
<o></o>
<o></o>
...I hope that gives something back to a Thread I have learnt a lot from.....<o></o>
Alan.<o></o>
.........................



Codes:

Code:

'
Sub Test2t()'---o00o---`(_)`---o00o---
'Evaluating Qoutes in VBA   http://www.mrexcel.com/forum/excel-questions/696820-quotes-visual-basic-applications.html
Dim strEvaluate As String
 
10  strEvaluate = "=" & """Year """
Debug.Print strEvaluate '_____________________Displays:  ="Year "
Debug.Print Evaluate(strEvaluate) '___________Displays:  Year  ( with a space at the end  )
    
20  strEvaluate = "=" & """Year ""&""2013"""
Debug.Print strEvaluate '_____________________Displays:  ="Year "&"2013"
Debug.Print Evaluate(strEvaluate) '___________Displays:  Year 2013
 
30  strEvaluate = "=" & """Year "" &     ""2013"""
Debug.Print strEvaluate '_____________________Displays:  ="Year " &     "2013"
Debug.Print Evaluate(strEvaluate) '___________Displays:  Year 2013
 
40  strEvaluate = "=" & """Year ""&""20" & "13"""
Debug.Print strEvaluate '_____________________Displays:  ="Year "&"2013"
Debug.Print Evaluate(strEvaluate) '___________Displays:  Year 2013
 
50  strEvaluate = "=" & """Year ""&""  """
Debug.Print strEvaluate '_____________________Displays:  ="Year "&"  "
Debug.Print Evaluate(strEvaluate) '___________Displays:  Year  ( with 1+2=three spaces at the end  )
 
60  strEvaluate = "=" & """Year ""&""  """""""
Debug.Print strEvaluate '_____________________Displays:  ="Year "&"  """
Debug.Print Evaluate(strEvaluate) '___________Displays:  Year   "
 
70  strEvaluate = "=" & """Year ""&""  "" """""
Debug.Print strEvaluate '_____________________Displays:  ="Year "&"  " ""
Debug.Print Evaluate(strEvaluate) '___________Displays:  errors
    
End Sub 'Test2t()

………………………………….

'
Sub Test2t()'---o00o---`(_)`---o00o---
'Evaluating Qoutes in VBA   http://www.mrexcel.com/forum/excel-questions/696820-quotes-visual-basic-applications.html
Dim strEvaluate As String
 
10  strEvaluate = "=" & """Year """
Debug.Print strEvaluate '_____________________Displays:  ="Year "
Debug.Print Evaluate(strEvaluate) '___________Displays:  Year  ( with a space at the end  )
    
20  strEvaluate = "=" & """Year ""&""2013"""
Debug.Print strEvaluate '_____________________Displays:  ="Year "&"2013"
Debug.Print Evaluate(strEvaluate) '___________Displays:  Year 2013
 
30  strEvaluate = "=" & """Year "" &     ""2013"""
Debug.Print strEvaluate '_____________________Displays:  ="Year " &     "2013"
Debug.Print Evaluate(strEvaluate) '___________Displays:  Year 2013
 
40  strEvaluate = "=" & """Year ""&""20" & "13"""
Debug.Print strEvaluate '_____________________Displays:  ="Year "&"2013"
Debug.Print Evaluate(strEvaluate) '___________Displays:  Year 2013
 
50  strEvaluate = "=" & """Year ""&""  """
Debug.Print strEvaluate '_____________________Displays:  ="Year "&"  "
Debug.Print Evaluate(strEvaluate) '___________Displays:  Year  ( with 1+2=three spaces at the end  )
 
60  strEvaluate = "=" & """Year ""&""  """""""
Debug.Print strEvaluate '_____________________Displays:  ="Year "&"  """
Debug.Print Evaluate(strEvaluate) '___________Displays:  Year   "
 
70  strEvaluate = "=" & """Year ""&""  "" """""
Debug.Print strEvaluate '_____________________Displays:  ="Year "&"  " ""
Debug.Print Evaluate(strEvaluate) '___________Displays:  errors
    
End Sub 'Test2t()

<o></o>