Announcement

Collapse
No announcement yet.

Evaluate - Most Powerful Command in VBA?

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Evaluate - Most Powerful Command in VBA?

    Oh just something to ponder for a Friday.

    What's the most powerful command in VBA?
    Hmmm... perhaps this one. But few seem to use it, or mention it. I posted this one to my mail group and received no comments... it got burried quickly so I thought I'd allow it to live a little longer here. It's one of my favorites.


    Code:
    'One of the most powerful commands in VBA: "EVALUATE" but hardly
    'anyone knows about it, understands it or uses it.
    
    'Can't use worksheet formulas directly in VBA right?
    'Run this macro:
    
    Sub Neato()
         MsgBox = EVALUATE("SUM(A1:A10)")
    End Sub
    
    
    'Yeah I know, what about:
    Set Fn = Application.WorksheetFunction
    x = Fn.SUM(Range("A1:A10"))
    
    'or if you prefer just:
    x = Application.SUM(Range("A1:A10"))
    
    '...but in most cases, why bother?
    
    
    'Another little known EVALUATE fact; you're familiar with the
    'shorthand brackets for referencing ranges right?
    
    Range("A1:A10").Select
    [A1:A10].Select
    
    'Did you know those brackets were shorthand for EVALUATE?
    
    Sub NeatoNeato()
    
        'given...
        [A1:A10].Select
        
        'is the same as...
        Evaluate("A1:A10").Select
        
        'then this should work right?
        x = [SUM(A1:A10)]
        MsgBox x
        
        'or just...
        MsgBox [SUM(A1:A10)]
        
        'hey... you know with those brackets, it looks just like a cell
        'in VBA doesn't it? hehehehe...
    
    End Sub

    Oh, and did I mention you can use EVALUATE in defined range names
    too? But thats EVALUATE as it existed in the old command language
    as opposed to VBA, so there are ways to access the power of EVALUATE
    in your cell formulas without even using VBA, perhaps a secret for
    another day...


    I'm working on posting an example file... here's the intro text I'm working on.

    EVALUATE is probably the single most powerful command in VBA. It's a bit of a surprise to me that I don't see people suggesting its usage more often; probably just a situation where people in general 'think' they know what a command does but can't really see the benefit of it. Very similar I think to the SUMPRODUCT worksheet formula; if you just took it at face value it doesn't seem like that big of a deal. If you dig a little further, a whole world of options starts to unfold.

    The VBA help topic for EVALUATE is a bit vague and probably doesn't do it justice. In fact, after reading through it for yourself, you probably wouldn't see much cause to use it. Here's what it can do:

    1. Converts string math expressions to values.
    2. Converts 1D and 2D string arrays to their array equivalents.
    3. Capable of processing any formula a worksheet cell can process! (wow, WOW)

    That's right, it can do anything a cell can do! It contains all the functionality of a worksheet cell wrapped in a single VBA command. In fact, it can even do one thing that cells can't do... it can return whole arrays. So it's like having free access to a worksheet cell... only it's better than a worksheet cell in the sense that it can evaluate and return arrays.

    EVALUATE, this is one of the better ones, remember it!


    Hmmmm... "can do anything a worksheet cell can do", a lofty claim. Maybe too much, maybe not?
    Sub All_Macros(Optional control As Variant)

  • #2
    Re: Evaluate - Most Powerful Command in VBA?

    Neat, Aaron,

    Thank you for sharing this.
    Best Regards,
    Tom
    ---------------------------
    Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

    Comment


    • #3
      Re: Evaluate - Most Powerful Command in VBA?

      Hi Aaron

      I have used & read about [] before, but never thought along the lines that you suggest here. Seems like a whole lot easier than writing

      Code:
       Dim myRange As Range
          Set myRange = Worksheets("Sheet1").Range("A1:C10")
          answer = Application.WorksheetFunction.Min(myRange)
          MsgBox answer
      Something to play with this weekend I think.
      Hope that Helps

      Roy

      New users should read the Forum Rules before posting

      For free Excel tools & articles visit my web site

      If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

      RoyUK's Web Site

      royUK's Database Form

      Where to paste code from the Forum

      About me.

      Comment


      • #4
        Re: Evaluate - Most Powerful Command in VBA?

        Just saw someone on another thread mention that VBA couldn't convert strings to arrays...

        So here's the usage of Evaluate in both shorthand and explicit terms that shows how Evaluate can be used to return and assign arrays as I previously mentioned.

        Code:
        Sub test() 
             
            Dim xArray() As Variant 
             
             '1D array string conversion
            xArray = [{1,2,3}] 
            Range("A1").Resize(1, UBound(xArray)).Value = xArray 
             
             '2D array string conversion
            xArray = [{1,2;3,4;5,6}] 
            Range("A5").Resize(UBound(xArray, 1), UBound(xArray, 2)).Value = xArray 
             
             '2D array string conversion with a string variable
            y = "{1,2;3,4;5,6}" 
            xArray = Evaluate(y) 'have to be more explicit, the shorthand won't work
            Range("A5").Resize(UBound(xArray, 1), UBound(xArray, 2)).Value = xArray 
             
        End Sub
        Sub All_Macros(Optional control As Variant)

        Comment


        • #5
          Re: Evaluate - Most Powerful Command in VBA?

          I also love to use Evaluate on my useforms to allow me to process formulas if I should choose to do so.

          Play around with this when you get the chance...

          Code:
          'Userform textboxes that behave like cells
          
          'allow one textbox to be a formula for another
          Private Sub TextBox1_Change()
              On Error Resume Next
              TextBox2.Value = Evaluate(TextBox1.Text)
          End Sub
          
          '...or maybe just a double-click to convert my formula to an entry?
          'This one seems like such the obvious useful feature for userform entry
          'and so easy to apply.  
          Private Sub TextBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
              TextBox1.Value = Evaluate(TextBox1.Text)
          End Sub
          Last edited by Aaron Blood; June 17th, 2006, 01:09.
          Sub All_Macros(Optional control As Variant)

          Comment


          • #6
            Re: Evaluate - Most Powerful Command in VBA?

            Cool stuff, but two questions.

            On this example, what does {} represent?
            Code:
             xArray = [{1,2,3}]
            Last, I've read about using [] and so far it has been labled as "bad programming" due to ease of reading. What's that about?

            I'm not questioning your skills or anything; I've just learned that the book isn't always correct and was wondering if this was another instance.

            I believe the first instance was labeling "Select Case" as being slow and to use nested IF's instead.

            Comment


            • #7
              Re: Evaluate - Most Powerful Command in VBA?

              Originally posted by JMAN
              Cool stuff, but two questions.

              On this example, what does {} represent?
              Code:
               xArray = [{1,2,3}]
              Last, I've read about using [] and so far it has been labled as "bad programming" due to ease of reading. What's that about?

              I'm not questioning your skills or anything; I've just learned that the book isn't always correct and was wondering if this was another instance.

              I believe the first instance was labeling "Select Case" as being slow and to use nested IF's instead.
              The {} is string syntax for an array. Works the same way in a cell formula. Try this in a cell for instance: =SUM({1,2,3})

              Where comma delimiters assume a 1D (horizontal) array and semicolon is used for 2D array inputs. Another formula to try: =INDEX({1,2,3;4,5,6;7,8,9},2,2)

              Yeah... bad programming... I guess in the truest sense we should also do things like explicitly state LET as opposed to just allowing it to be implied as in: x = 10 vs. Let x = 10

              Probably folks out there who still want to put line numbers at the beginning of all their code too...

              I guess for the explicit crowd you can just always state EVALUATE( ... ) instead of just the brackets. Half the people who suggest its bad form don't even realize it's a call to the Evaluate method. They just think its shorthand for the Range method, which is not correct.

              But... all that said... typically, yes, I explicitly state Evaluate, the only real drawback to the [] approach that I can see is that it's a shortcut that won't allow you to hand it a string variable. I was more just pointing out that the brackets are in fact a shorthand call to the EVALUATE method. Nothing wrong with writing out EVALUATE. I'll leave it to you to decide what's good form.

              As for select case vs. nested ifs... unless you're making thousands of calls to the thing, you're kidding yourself if you think you'll notice a difference. I haven't come across a situation yet that I would abandon a nice select case structure for a bunch of IFs... but I guess that's just me.
              Sub All_Macros(Optional control As Variant)

              Comment


              • #8
                Re: Evaluate - Most Powerful Command in VBA?

                Cool, thanks for sharing.

                In regards to evalulate with Worksheet Functions, have you done any tests to see if it's faster/slower then .WorksheetFunction?

                Comment


                • #9
                  Re: Evaluate - Most Powerful Command in VBA?

                  Originally posted by Dave Hawley
                  Cool, thanks for sharing.

                  In regards to evalulate with Worksheet Functions, have you done any tests to see if it's faster/slower then .WorksheetFunction?

                  Hmmm... good question, for 10k loop calls you'll notice a 1/10th of a second performance drop using Evaluate in the following macros.


                  Code:
                  Option Explicit
                  
                  Sub Eval_Formula()
                      Dim i As Long
                      Dim x As Double
                      For i = 1 To 10000
                          x = Evaluate("SUM(A1:G100)")
                      Next i
                  End Sub
                  
                  Sub WSfunc_Call()
                      Dim i As Long
                      Dim x As Double
                      For i = 1 To 10000
                          x = Application.WorksheetFunction.Sum(Range("A1:G100"))
                      Next i
                  End Sub
                  
                  'WSfunc_Call is Faster
                  '
                  'Eval_Formula:   0.4375 10
                  'WSfunc_Call:    0.3406 99
                  'Diff:   0.0968 11   Seconds
                  '
                  'QueryPerformanceCounter
                  '    min resolution: 1 / 3,579,545 sec
                  '    API Call Overhead: 0.0006 00 sec
                  For 100k calls I get:
                  WSfunc_Call is Faster

                  Eval_Formula: 4.6424 20
                  WSfunc_Call: 4.3974 61
                  Diff: 0.2449 59 Seconds

                  QueryPerformanceCounter
                  min resolution: 1 / 3,579,545 sec
                  API Call Overhead: 0.0006 00 sec




                  Interestingly, if you're going for pure performance, shortening the WorksheetFunction call to just:
                  x = Application.Sum(Range("A1:G100"))

                  ...is faster still by another 1/10th of a second for 10k call loops. Also, testing the explicit vs. shorthand use of evaluate seemed to suggest the explicit usage was faster (as you might expect), but by such a small margin it didn't even register in the 1/10th of a sec range. But my testing was limited to the very simple formulas listed. I'm assuming the same relationships would exist on more complex formulas, but I haven't tested beyond the above. If you have some specific formulas in mind, I'd be happy to test em.

                  In my book, 1/10th of a second performance drops on 10k loops or 1/4th of a second on 100k loops are inconsequential and the decision to be explicit or not is merely a matter of taste and really limited only by the fact that using the brackets disallows the use of string variables.
                  Last edited by Aaron Blood; June 17th, 2006, 23:35.
                  Sub All_Macros(Optional control As Variant)

                  Comment


                  • #10
                    Re: Evaluate - Most Powerful Command in VBA?

                    Forgot to mention...

                    For my testing I used the tool I previously posted here:
                    http://www.ozgrid.com/forum/showthread.php?t=47350

                    If you want to use it, make sure you get the later one posted at the bottom of the thread.

                    Maybe a mod could delete the first file for me or put a pointer in to the newer one at the bottom or something...
                    Sub All_Macros(Optional control As Variant)

                    Comment


                    • #11
                      Re: Evaluate - Most Powerful Command in VBA?

                      Hi Aaron

                      Thanks for the detailed answer. I agree, there is not much difference.

                      I have deleted the older testing tool in the Thread you have linked to.

                      Comment


                      • #12
                        Re: Evaluate - Most Powerful Command in VBA?

                        If you have a few extra moments it's worthwhile to try this one...

                        Code:
                        Private Sub TextBox1_Enter()
                            TextBox1.Text = TextBox1.Tag 'retrieve formula from tag
                        End Sub
                        
                        Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
                            On Error Resume Next
                            With TextBox1
                                .Tag = .Text 'store formula in tag
                                .Value = Evaluate(.Text)
                            End With
                        End Sub
                        So...

                        User enters a formula in the textbox. On exit the formula is stored in the tag and the answer is displayed in the textbox. On entry, the formula is retrieved from the tag and the user can see how the value is being calculated. Very much like a worksheet cell.
                        Sub All_Macros(Optional control As Variant)

                        Comment


                        • #13
                          Re: Evaluate - Most Powerful Command in VBA?

                          Can Evaluate() deal with an expression that includes a user defined function? If so, how should I refer to the udf?

                          Comment


                          • #14
                            Re: Evaluate - Most Powerful Command in VBA?

                            Hi JBC

                            Welcome to ozgrid

                            Why not Call the custom function from within the Procedure?

                            Comment


                            • #15
                              Re: Evaluate - Most Powerful Command in VBA?

                              Thanks for the welcome.

                              Let me give a little more background. I have a number of worksheets that use a UDF called ShowIfEqual(), which has a ParamArray as its argument. If the arguments of ShowIfEqual() are equal, the function returns that value. If the arguments are unequal, it returns CVErr(xlErrNum). I use the function as a check in various parts of my worksheets. A typical use would be ShowifEqual(SUM(E32:E37),F76), though the arguments are often more complex.

                              I'd like to write a macro to simplify the error hunt when ShowIfEqual() returns #NUM!. First I'd write a UDF called ShowIfEqualInfo() that would take the same arguments as ShowIfEqual() and would provide more information on an error, returning a text string, for example, that reports that "Argument 1 equals 100, argument 2 equals 200". I'd then write a macro that would read the ShowIfEqual() formula in the offending cell, retain the arguments but substitute ShowifEqualInfo() for ShowIfEqual(), and use Evaluate(ShowifEqualInfo()) to obtain more detailed information on the error. The macro would then pop a dialog box showing the text returned by ShowifEqualInfo(). Because the arguments would change, I wouldn't be able to hard code ShowIfEqualInfo() into the macro.

                              Hope this explanation is understandable.

                              Comment

                              Working...
                              X