Calculating an expression stored in a string in VBA
Results 1 to 5 of 5

Thread: Calculating an expression stored in a string in VBA
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Mar 2004
    Posts
    65
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Calculating an expression stored in a string in VBA

    I need to evaluate a VBA expression in VBA, which sounds easy but I cannot figure it out...

    Test1 is how I want it to work, I want the Msgbox to display 2 like it does in the Test2-macro.

    I haven't got any versions of Evaluate to work, Application.Evaluate, ActiveSheet.Evaluate or Evaluate(CStr(Eq))
    Code:
    Sub Test1()
    Dim Eq As String, Ary1 As Variant, Ans As Variant
        Ary1 = Array(3, 6, 9)
        Eq = "Application.Match(6, Ary1, 0)"
        Ans = Evaluate(Eq)
        MsgBox Ans      'Ans is here = "Error 2029"
     End Sub
    
     Sub Test2()
    Dim Ary1 As Variant, Ans As Variant
        Ary1 = Array(3, 6, 9)
        Ans = Application.Match(6, Ary1, 0)
        MsgBox Ans      'Ans is here = 2
    End Sub
    Any creative solutions?
    Last edited by RoryA; Apr 8th, 2016 at 10:05 AM.

  2. #2
    Board Regular
    Join Date
    Dec 2009
    Location
    Canterbury, UK
    Posts
    3,371
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculating an expression stored in a string in VBA

    Here's how you can fix Test1:

    Code:
    Sub Test1()
    Dim Eq As String, Ary1 As Variant, Ans As Variant
    Ary1 = Array(3, 6, 9)
    Eq = "Match(6, {" & Join(Ary1, ",") & "}, 0)"
    Ans = Evaluate(Eq)
    MsgBox Ans 'Ans is here = "Error 2029"
    End Sub

  3. #3
    Board Regular
    Join Date
    Mar 2004
    Posts
    65
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculating an expression stored in a string in VBA

    Hi Comfy and a big thank you for looking at this.

    But ideally I don't want to re-write the expression, I want to get the string evaluated as it is...(being optimistic as the weekend is getting close)

    Like the way Evaluate() works, but with the VBA syntax...

  4. #4
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,679
    Post Thanks / Like
    Mentioned
    47 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Calculating an expression stored in a string in VBA

    You can't do that in VBA I'm afraid.

    You'd either need to actually write the code into another routine, or use something like:
    Code:
    msgbox callbyname(application, "Match", VbMethod, 6, array(3, 6, 9), 0)

  5. #5
    Board Regular
    Join Date
    Mar 2004
    Posts
    65
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculating an expression stored in a string in VBA

    Thank you Rory!

    It is an interesting solution that I don't think I have seen before.

    So between this and Comfys answers I think I have enough to keep me busy over the weekend!

    Great thanks to you both for helping me out!
    //Jorgen

Some videos you may like

User Tag List

Tags for this Thread

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
  •