dynamically call sub via string
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:
Code:
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.)
Code:
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:
Code:
Application.Run "'my_sub(2+3)'"
So far, so good. Now, one would think that running the sub with two parameters should be straightforward:
Code:
Application.Run "'my_sub(2+3,4+5)'"
But that gives the error
Quote:
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?
not working for non-numerical expressions
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:
Code:
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
Quote:
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:
Code:
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.