Micromegas
09-19-2023, 12:59 AM
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 (https://learn.microsoft.com/en-us/office/vba/api/Excel.Application.Run) 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?
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 (https://learn.microsoft.com/en-us/office/vba/api/Excel.Application.Run) 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?