Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Sydney, Australia, New South Wales, Australia
    Posts
    251
    Thanks
    0
    Thanked 4 Times in 4 Posts
    Hi
    I've got some code-testing code that needs to use the eval command to evaluate strings as code.
    I've narrowed it down to actions that use array types. Here's a one liner that fails:



    b = Eval("ubound(Array(1, 2, 3))")


    It throws run-time error 2425 - "The expression you entered has a function name that Microsoft Access can't find"
    Of course, this works fine in Interactive mode:
    ? ubound(Array(1, 2, 3))
    2

    Strangley, if I use Evaluate I get a slightly different error:

    b = Evaluate("ubound(Array(1, 2, 3))")
    ? b
    Error 2029


    What's the difference between eval and Evaluate?

    It's in Access 2002; don't know whether it's a problem in other versions or other applications.
    Any ideas welcomed.

    Peter

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    If you highlight the function name Eval (or Evaluate) and press F1, do you get a specific page or the dialog indicating that it exists in multiple libraries, or an error?

  4. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,895
    Thanks
    0
    Thanked 84 Times in 80 Posts
    Eval is a method of the Access Application, Evaluate is a method of the Excel (and possibly others) application. Neither can be used to evaluate VBA like that.
    Regards,
    Rory
    Microsoft MVP - Excel.

  5. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Sydney, Australia, New South Wales, Australia
    Posts
    251
    Thanks
    0
    Thanked 4 Times in 4 Posts
    Quote Originally Posted by jscher2000 View Post
    If you highlight the function name Eval (or Evaluate) and press F1, do you get a specific page or the dialog indicating that it exists in multiple libraries, or an error?
    F1 on Eval takes me to Microsoft Visual Basic Help - Eval Method - You can use the Eval function to evaluate an expression that results in a text string or a numeric value. Variant.
    F1 on Evaluate takes me to Microsoft Visual Basic Help - Evaluate Method - Converts a Microsoft Excel name to an object or a value.

  6. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Sydney, Australia, New South Wales, Australia
    Posts
    251
    Thanks
    0
    Thanked 4 Times in 4 Posts
    OK, looks like I've got two problems.

    I don't think Access works the way I want to, but for anyone else that's ever wanted to know, here's my findings.

    1. The eval method has it's own scoping rules.
    eval can't 'see' variables outside it's scope. This isn't a big problem, you can just build up the string. Consider the str function if you don't have a string type.


    num = 2
    i = Eval("5 + num * 2") 'FAILS

    i = Eval("5 + " & str(num) & " * 2")
    Debug.Print i 'Prints 9


    2. eval doesn't work with arrays.
    It just looks like there's no support for arrays from within eval.


    Debug.Print Array(1, 2, 3)(2) 'Prints 3
    Debug.Print Eval("Array(1, 2, 3)(2)") 'Error


    Peter

  7. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,895
    Thanks
    0
    Thanked 84 Times in 80 Posts
    As I mentioned, neither Eval nor Evaluate can be used to evaluate VBA.
    Regards,
    Rory
    Microsoft MVP - Excel.

  8. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Sydney, Australia, New South Wales, Australia
    Posts
    251
    Thanks
    0
    Thanked 4 Times in 4 Posts
    Thanks Rory.
    What do you mean eval can't be used to evaluate VBA?
    I'm using it all the time to do just that - it's just I'm having trouble using eval with arrays.
    For example, this works fine:
    Code:
    num = 2
    i = Eval("5 + " & str(num) & " * 2")    'Prints 9
    Pete

  9. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Array(arg1,arg2,arg3,...) is a VBA function and eval cannot handle VBA functions or methods. It can do the regular math operations however.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,895
    Thanks
    0
    Thanked 84 Times in 80 Posts
    What you are evaluating there is a straight expression (which would work in a query for example), not VBA.
    Regards,
    Rory
    Microsoft MVP - Excel.

  11. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Sydney, Australia, New South Wales, Australia
    Posts
    251
    Thanks
    0
    Thanked 4 Times in 4 Posts
    Quote Originally Posted by pieterse View Post
    Array(arg1,arg2,arg3,...) is a VBA function and eval cannot handle VBA functions or methods. It can do the regular math operations however.
    Not true. Access VBA can handle some (most?) functions, but not all. So this works fine:
    Code:
    Debug.Print eval("chr(65)")     'Prints A

  12. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Sydney, Australia, New South Wales, Australia
    Posts
    251
    Thanks
    0
    Thanked 4 Times in 4 Posts
    Quote Originally Posted by rory View Post
    What you are evaluating there is a straight expression (which would work in a query for example), not VBA.
    Sorry, I still don't understand.
    Here's some plain VBA using Eval, which works fine in Access 2002 and 2007:
    Code:
    Sub test()
        Dim num As Integer
        num = 2
        i = Eval("5 + " & Str(num) & " * 2")
        MsgBox i    'Displays 9
    End Sub

  13. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,895
    Thanks
    0
    Thanked 84 Times in 80 Posts
    Your Eval statement there is not evaluating the str function, it is only evaluating "5 + 2 * 2" which is an expression (such as you can use in a query), not VBA.
    Regards,
    Rory
    Microsoft MVP - Excel.

  14. #13
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Sydney, Australia, New South Wales, Australia
    Posts
    251
    Thanks
    0
    Thanked 4 Times in 4 Posts
    Quote Originally Posted by rory View Post
    Your Eval statement there is not evaluating the str function, it is only evaluating "5 + 2 * 2" which is an expression (such as you can use in a query), not VBA.
    True. But this example has eval evaluating a VBA function:
    Code:
    Debug.Print eval("chr(65)")     'Prints A

  15. #14
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,895
    Thanks
    0
    Thanked 84 Times in 80 Posts
    You are quite right! I was foolishly assuming that Eval and Evaluate work the same way, but they are actually VERY different.
    Regards,
    Rory
    Microsoft MVP - Excel.

Posting Permissions

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