quotes vba
Results 1 to 9 of 9

Thread: quotes vba
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jan 2013
    Posts
    193
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default quotes vba

    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.

  2. #2
    Board Regular
    Join Date
    Jul 2006
    Location
    Brussels
    Posts
    7,944
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: quotes vba

    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?
    Regards,

    Wigi

    http://www.wimgielis.com

    Excel MVP 2011-2014

  3. #3
    MrExcel MVP
    Moderator
    Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    8,787
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

    Default Re: quotes vba

    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.


    Code:
    Sub Test1()
    '--Evaluate the worksheet formula =2*3
    
        Range("A1") = Evaluate("=2*3")       'Result: 6
    
        '--Eliminating the equals sign yields the same result from the Evaluate Method
    
        Range("B1") = Evaluate("2*3")        'Result: 6
    
        
        '--Adding spaces between wrapping quotes yields the same result
    
        Range("C1") = Evaluate(" 2*3 ")      'Result: 6
    
    End Sub
    Code:
    Sub Test2()
    '--Evaluate the worksheet formula ="Year " & "2013"
    
        Range("A2") = Evaluate("""Year "" & ""2013""")  'Result: Year 2013
    
        Debug.Print """Year "" & ""2013"""              'Displays: "Year " & "2013"
        
    End Sub
    Code:
    Sub Test3()
    '--Evaluate the worksheet formula ="Year " & "2013" using a variable year
    
        Const sYear As String = "2013"
        Range("A3") = Evaluate("""Year "" & """ & sYear & """")   '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("""Year "" & """ & iYear & """")   '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.
    Last edited by Jerry Sullivan; Apr 14th, 2013 at 12:51 PM.
    Using Excel 2016

  4. #4
    Board Regular
    Join Date
    Jan 2013
    Posts
    193
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: quotes vba

    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!


  5. #5
    MrExcel MVP
    Moderator
    Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    8,787
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

    Default Re: quotes vba

    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 & " "" "
    Using Excel 2016

  6. #6
    Banned user
    Join Date
    May 2014
    Location
    Germany
    Posts
    1,336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: quotes vba

    Quote Originally Posted by Jerry Sullivan View Post
    Working with quotes inVBA can be very confusing.
    … …...


    Quote Originally Posted by dulitul View Post
    Hey,
    Quote Originally Posted by dulitul View Post

    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.


    .. Hi.
    . 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!!).
    . I thought I had it covered through participation in a couple of other threads:
    http://www.mrexcel.com/forum/excel-questions/799417-using-variable-references-indicate-range-cells-merge.html?#post3908684
    www.excelfox.com/forum/f22/concatenating-balls-1891/

    … Then I read this andstarted thinking again

    Quote Originally Posted by JerrySullivan View Post
    ……..
    ……A key principal to understand is that
    Quote Originally Posted by JerrySullivan View Post
    singlequotes are used to build the formulaexpression to be evaluated
    expression = "String1" & stringVariable2 &"String3"

    ...and
    doublequotes are usedwherever aLiteralquotation markcharacter is needed in the resulting expression…...


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

    . I would say the Microsoft definition ( Application.Evaluate Method (Excel) ) issomewhat lacking
    . 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:
    ….You type this:     Evaluate(“     “), and then in the spaceyou type exactly what you would in the Spreadsheet cell but omitting the =

    . 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

    . To demonstrate. Following arguments in Post #9    http://www.mrexcel.com/forum/excel-questions/799417-using-variable-references-indicate-range-cells-merge.html     we have various combinationsof Excel Spreadsheetfunctions and VBA code within the Evaluate(“    “)to give various combinations of the value in the first cell in a Workbook

    A
    1Cookies
    HarryDATA3





    Sub EvaluateSyntax1()

    MsgBox "First cell value is" & Range("A1").Value ' Full VBA
    MsgBox "First cell value is" & Evaluate("A1")' Equivalent to writing spreadsheet formula =A1
    MsgBox "First cell value is" & Evaluate(Range("A1").Address) ' VBA code can be directly written in EvaluateFunction…..
    MsgBox "First cell value is "& Evaluate("   " & 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…
    MsgBox "First cell value is" & Evaluate("    " & Range("A1").Address) ' ..Combination of the two !

    End Sub

    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.

    Sub EvaluateSyntax2()

    MsgBox "First cell value written a couple of times is " &Evaluate("A1" &"&"& "$A$1")' We are evaluating 3 spreadsheet things here
    MsgBox "First cell value written a couple of times is "&Evaluate("A1"& "&" & Range("A1").Address)
    MsgBox "First cell value written a couple of times is " &Evaluate("A1"&"&" & Range("A1").Address& " ")

    End Sub

    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:
    MsgBox ……      …… & Evaluate("A1"& "&" &"" -"" & "&"& "A1")
    . It did however not work because of the syntax problems discussed.
    . The following solution was discussed
    MsgBox ……      …… & Evaluate("A1" & "&"" - ""&"&"A1")

    . 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.
    . So this works
    MsgBox ……      …… & Evaluate("   ""- "" ")
    . But this does not.
    MsgBox ……     …… & Evaluate("    "    " - " " ")

    . But to be quite honest I am beginning to … ….. again on this one.
    . Coming back to a similar situation to the OP original problem:

    Sub EvaluateSyntax3()

    'MsgBox "First cell value is" &Evaluate("SUBSTITUTE(A1,"Cook","Kook")")
    MsgBox "First cell value is" &Evaluate("SUBSTITUTE(A1,""Cook"",""Kook"")")
    End Sub 'EvaluateSyntax3()

    . The first line does not work. Similar argument to my example in    http://www.mrexcel.com/forum/excel-questions/799417-using-variable-references-indicate-range-cells-merge.html    : Evaluate is confused with seeing Cook afterwhat it takes as a closing quote    "   rather than a spreadsheetquote   "
    . the solution would appear to be “coercing somehow text with a double quote”

    . Any comments?

    Alan Elston.

    ……………………………………………
    Codes again in code tags:

    Code:
    Code:
    
    SubEvaluateSyntax1()
    MsgBox "First cell value is" &   Range("A1").Value
    MsgBox "First cell value is" &  Evaluate("A1")' Equivalent to writing spreadsheet formula  =A1
    MsgBox "First cell value is" &   Evaluate(Range("A1").Address)'  VBA code can be directlywritten in Evaluate Function…..
    MsgBox "First cell value is" &   Evaluate(" "& Range("A1").Address & " ") ' ..orafter coming into spreadsheet World with Evaluate ("  we go  into VBA Code  World with " & and follow a similarargument to come out… …
    MsgBox "First cell value is" & Evaluate(" " &   Range("A1").Address) '..combination of the two !
    EndSub 'EvaluateSyntax1()
    '
    SubEvaluateSyntax2()
    
    MsgBox "First cell value writtena couple of times is " &  Evaluate("A1" &"&" & "$A$1")
    MsgBox "First cell value writtena couple of times is " &  Evaluate("A1" &"&" & Range("A1").Address)
    MsgBox "First cell value writtena couple of times is " &  Evaluate("A1" &"&" & Range("A1").Address & " ")
    'MsgBox ".....     ...... " &Evaluate("A1"  &  "&" & "" -"" & "&" & "A1")
    MsgBox "......      ...... " &Evaluate("A1" & "&""- ""&"& "A1")
    MsgBox "......      ...... " & Evaluate(""" - "" ")
    EndSub 'EvaluateSyntax2()
    '
    SubEvaluateSyntax3()
    
    'MsgBox "First cellvalue is " &Evaluate("SUBSTITUTE(A1,"Cook","Kook")")
    MsgBox "First cell value is" & Evaluate("SUBSTITUTE(A1,""Cook"",""Kook"")")
    EndSub 'EvaluateSyntax3()


  7. #7
    MrExcel MVP
    Moderator
    Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    8,787
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

    Default Re: quotes vba

    Quote Originally Posted by DocAElstein View Post
    I am struggling now to see exactly the parallel with Jerry’s and Wigi’sarguments.. But it appears I may have coerced somehow [/COLOR][COLOR=#7030A0]text with a double quote. The keyseems to be the intimacy of the two quotes.
    . So this works
    MsgBox …… **** …… & Evaluate("***""- "" ")
    . But this does not.
    MsgBox …… ****…… & Evaluate("*** " ***" - " " ")

    . But to be quite honest I am beginning to … ….. again on this one.
    . Coming back to a similar situation to the OP original problem:
    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...

    Quote Originally Posted by Jerry Sullivan View Post
    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.
    Using Excel 2016

  8. #8
    Banned user
    Join Date
    May 2014
    Location
    Germany
    Posts
    1,336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: quotes vba

    Jerry.

    . 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….
    (… 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 …
    …...
    )


    . 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”!!)
    …… The simplified clear explanation.      Evaluate(     String Expression here      )      is most likely correct and Ijust need to get my head clear on that…
    …. My abstract ideasare maybe thinking along the lines of “Paringup” “Evaluate” Purple quotes           like Code or HTML tags within the Evaluatebrackets.      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….
    …… 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     ""     ""     

    . 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!!?).

    . But your and other profi’s inputs to theseThreads / Post are always muchappreciated. Helps me stop flying off ina (too) wild tangent

    Thanks Again
    Alan

    .P.s.
    I think I can explaincodes like these working and being equivalent from my “Matched Quote Pair” /Different “Worlds” Theory.
    Evaluate(Range("A1").Address)
    Evaluate("    " & Range("A1").Address&"     ")
    Evaluate("     "& Range("A1").Address)
    Evaluate(" "& Range("A1").Address& "")
    Evaluate("    " & & Range("A1").Address&"")
    Evaluate(Range("A1").Address)
    Evaluate("    "& Range("A1").Address&"    ")
    Evaluate("    "& Range("A1").Address& "    "& "      " &"")
    Evaluate("    "& Range("A1").Address& "    "& "" & "      ")
    Evaluate("    "& Range("A1").Address& ""& "" & "" & "" )
    Evaluate("     "& Range("A1").Address )
    Evaluate(""& Range("A1").Address& "")
    Evaluate("    " & Range("A1").Address&"")

    … 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      Evaluate("         ")
    does give me anerror)


    … I do not immediatelysee a clear explanation using the clearer      Evaluate(     String Expression here      )      …..But I probably will when Ithink about it some more!

  9. #9
    Banned user
    Join Date
    May 2014
    Location
    Germany
    Posts
    1,336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: quotes vba

    Hi
    . 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.
    . In an attempt to satisfy my lust to finally really understand this I did a rather big Thread response starting from here.
    http://www.excelfox.com/forum/f2/special-concatenation-2042/index3.html#post9517
    .. again I continually looked back to this Thread for inspiration.
    .
    . I think I can add a good follow up here.

    . I will try to follow closely Gerry’s arguments from Post #3, adding to them or modifying slightly:
    .
    . 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.
    .
    . 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.
    .
    . 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..
    . 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 VB Editor) can help greatly in working through
    .
    . 10_ strEvaluate = "=" & """Year*_"""
    Debug.Print strEvaluate '____________________Displays: ="Year "
    Debug.Print Evaluate(strEvaluate) '___________Displays: Year_ ( with a space at the end )
    .
    . Further In the above Thread I had a Theory proving that linking ""&"" is the required syntax to link text such that
    .
    . 20_ strEvaluate = "=" & """Year_""&""2013"""
    Debug.Print strEvaluate '_____________________Displays: ="Year "&"2013"
    Debug.Print Evaluate(strEvaluate) '___________Displays: Year 2013
    .
    . 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....

    . 30 strEvaluate = "=" & """Year_""_&_____""2013"""
    Debug.Print strEvaluate '_____________________Displays: ="Year "_&_____"2013"
    Debug.Print Evaluate(strEvaluate) '___________Displays: Year 2013
    All consistent with “Spreadsheet World”

    BUT a useful point to note: Any attempt to try variations of this
    "="__ & __________ """Year_""&""2013"""
    Will not be accepted – VBA will put the syntax back to " & " as this is “VBA World” and are “governed” by code syntax
    . 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:
    .
    . 40_ strEvaluate = "=" & """Year_""&""20" & "13"""
    Debug.Print strEvaluate '_____________________Displays: ="Year "&"2013"
    Debug.Print Evaluate(strEvaluate) '___________Displays: Year 2013

    ...................................................................

    . 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..
    . Taking slowly a simplification first of the code line so far.

    .50 _ strEvaluate = "=" & """Year_""&""__"""
    Debug.Print strEvaluate '_____________________Displays: ="Year "&" "
    Debug.Print Evaluate(strEvaluate) '___________Displays: Year ( with 1 +2 = three spaces at the end )

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

    .60 strEvaluate = "=" & """Year_""&""__"""""""
    Debug.Print strEvaluate '_____________________Displays: ="Year "&" """
    Debug.Print Evaluate(strEvaluate) '___________Displays: Year "

    .. 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
    ""__""""""
    . 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 ""
    . To give my argument some acceptance it will be seen that this errors:

    .70 strEvaluate = "=" & """Year_""&""__""__"""""
    Debug.Print strEvaluate '_____________________Displays: ="Year "&" " ""
    Debug.Print Evaluate(strEvaluate) '___________Displays: ERROR

    ....................................................................


    ...I hope that gives something back to a Thread I have learnt a lot from.....
    Alan.
    .........................



    Codes:

    Code:

    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()

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




    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()



Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •