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

Doug,

This is how that works:

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

RBS


"Doug Glancy" wrote in message
...
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

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" wrote in message
...
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

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

"Bob Phillips" wrote in message
...
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" wrote in message
...
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