Calculating an expression stored in a string in VBA

Jorgen

Board Regular
Joined
Mar 25, 2004
Messages
67
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
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 a moderator:

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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
 
Upvote 0
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...
 
Upvote 0
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)
 
Last edited:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top