dynamically call sub via string

[Micromegas]

How can one run a sub with parameters, given a call string like "my_sub par1 par2 ..."?
For the sub, let's simply use this:

Sub my_sub(Optional par1 As Variant = "niente", Optional par2 As Variant = "nada")
    MsgBox par1 & "," & par2
End Sub

For this example, let's assume we parsed the call string and got individual strings for the name of the sub and for each parameter. If we have the string constant "foo" for the first parameter, the language reference tells us to use the following. (Except that we have to leave out the prescribed parentheses of the Run method since we're calling a sub here and thus have no result to return.)

    Application.Run "my_sub", "foo"

This works nicely. But how can we pass an expression, rather than a string constant? (E.g., if the call string looked like "my_sub 2+3"?) With Unix shells one could use backticks or $(), but that doesn't work here, of course. Luckily, it seems possible to use the syntax of a function call wrapped in single quotes:

    Application.Run "'my_sub(2+3)'"

So far, so good. Now, one would think that running the sub with two parameters should be straightforward:

    Application.Run "'my_sub(2+3,4+5)'"

But that gives the error

Run-time error '1004':
Cannot run the macro ''my_sub(2+3,4+5)''. The macro may not be available in this workbook or all macros may be disabled.

While it's correct that my_sub(2+3,4+5) is not a macro, it's a pity that VBA can't simply execute this function call, just as it did with my_sub(2+3).

Any idea how to achieve this?

[georgiboy]

Like this?

Application.Run "'my_sub(2+3),(4+5)'"

[Micromegas]

With some more trial and error i found that just moving the end quote gives the right result:

Application.Run "'my_sub'(2+3,4+5)"

However, this has two problems:

1. It executes the sub twice.
2. It doesn't stop at a breakpoint in the sub.

[Micromegas]

Perfect, thanks! Now, how can i mark this as the solution?

(This was meant as a reply to georgiboy)
(Never mind. It seems this forum doesn't support marking individual posts as solution.)

[Micromegas]

While i was very happy with georgiboy's ultra fast solution, sadly i can't get it to work when the expressions are non-numerical, as in this example:

Sub my_call()
    Application.Run "'my_set_interior(Selection),(vbGreen)'"
End Sub


Sub my_set_interior(r As Range, Optional lColor As Long = vbRed)
    r.Interior.Color = lColor
End Sub

, which gives me

Microsoft Visual Basic Run-time error '424':
Object required

Since the selection is on the calling sub's End Sub, it's not immediately clear where VBA is missing an object. But the problem seems to have to do with the parentheses, as can be seen from the following:

Sub calling_directly()
    my_Set_interior Selection, vbGreen
End Sub


Sub calling_directly_with_parentheses()
     my_Set_interior (Selection), (vbGreen)
End Sub

The first one works as expected, while the second one gives the same error message as above. So, with much regret, i have to reopen this case.

[georgiboy]

Application.Run "'my_set_interior Selection,vbGreen'"

[Micromegas]

Actually, in this case the call works without parentheses:

Sub calling_without_parentheses()
Application.Run "'my_set_interior Selection, vbGreen'"
End Sub

(edit: Wow, georgiboy beat me to posting this, again!)

So, the problem seems to boil down to knowing when to use the parentheses. What are they doing, anyway?

[georgiboy]

They should both work without:

Sub my_call()
    Application.Run "'my_set_interior Selection, vbGreen'"
End Sub
Sub my_set_interior(r As Range, Optional lColor As Long = vbRed)
    r.Interior.Color = lColor
End Sub

And:

Sub my_call2()
    Application.Run "'my_sub 2+3,4+5'"
End Sub
Sub my_sub(Optional par1 As Variant = "niente", Optional par2 As Variant = "nada")
    MsgBox par1 & "," & par2
End Sub

[Micromegas]

They should both work without

Yes, they do for me, too.

[Micromegas]

And the parentheses weren't needed already in my first post. So it seems that we can forget about parentheses and finally close this thread, but i first want to try it out with my real code this time.

[georgiboy]

No, not needed, but for some reason I thought you wanted them. Hope it works for you in the real code.

[Micromegas]

Hope it works for you in the real code.

Yes, it does. Thanks a lot!

[georgiboy]

Happy to help