Application.Run with variable number of arguments

[Doug Glancy]

Hello,

I'm trying to create a generic sub that will call Application.Run with a
Worksheet.Module.Sub passed to it and also a variable number of arguments.
Is this possible? For example, if wishing made it so, it would run like
this:

Sub app_run(macro_to_run as string, params as variant)
Application.Run macro_to_run, params
End Sub

In my dreams, I'd call it something like this:

Call app_run("MyBook.xls!MyModule.MyMacro", array("test 1", 1)

And lo and behold MyMacro would run:

Sub MyMacro(str1 as String, lng1 as Long)
....


If that's not possible, is there any way to pass one string to my app_run
sub and have it parsed for the Workbook.Module.Macro and the args, e.g.:

Call app_run(("MyBook.xls!MyModule.MyMacro, test1, 1")

Thanks in Advance,

Doug

 

[RB Smissaert]

Doug,

This is how that works:

oXL.Run "MyBook.xls!MyModule.MyMacro", var1, var2

RBS

 

[Bob Phillips]

Doug,

Probably best to use ParamArray.

In the macro to be run, declare a single variant argument, where you know
whether it expects multiple or single parameters

Public Sub Test(p1)
Dim i As Long
For i = LBound(p1) To UBound(p1)
MsgBox p1(i)
Next i
End Sub

and in the caller, use paramarrays to give the flexibility, passing it as a
single variant

Sub app_run(macro_to_run As String, ParamArray params())

Application.Run macro_to_run, params()
End Sub

and call it like so

Public Sub Test_app_run()
app_run "Book2!Test", 1, 2, 3
End Sub




--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Doug Glancy]

Bob,

Good to hear from you.

That is a workable idea, although since the subs that will be called are
multiple and existing, I'm not sure it's practical since it would involve
rewriting them. I have a workaround, I think, but was especially interested
to see if Application.Run could call these existing subs, with an unknown
number of params.

Thanks,

Doug