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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>
>