Calculating an expression stored in a string in VBA

[Jorgen]

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

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?

 

[Comfy]

Here's how you can fix Test1:

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

 

[Jorgen]

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

 

[RoryA]

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:

msgbox callbyname(application, "Match", VbMethod, 6, array(3, 6, 9), 0)

 

[Jorgen]

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