Run verb, macro with arguments
 

Hi All,
As part of the menu functionality in my addin I have a worksheet
with macro names, and argument values, (or none) for each macro.
..onaction contains the macro which gathers the data from the
worksheet.

Prior to the paragraph below is a sub that builds the
sArgAy string array and the ArgQty variable.

The code below is working and I don't think I'll have any
macros with more than 4 arguments, but I'm wondering if
there's a better way to code it without all the If..Else
syntax.

RunMac: 'Execute macros with arguments values
If ArgQty = 0 Then
Run MacName
ElseIf ArgQty = 1 Then
Run MacName, sArgAy(1)
ElseIf ArgQty = 2 Then
Run MacName, sArgAy(1), sArgAy(2)
ElseIf ArgQty = 3 Then
Run MacName, sArgAy(1), sArgAy(2), sArgAy(3)
ElseIf ArgQty = 4 Then
Run MacName, sArgAy(1), sArgAy(2), sArgAy(3), sArgAy(4)
End If
Return

I thought about var = array(xxx, xxx,xxx) but I still could not
figure out how to vary the qty of arguments.

I tried:
stringvar = "value1,value2"
run macname, stringvar

the above gets argument not optional when macname needs 2 arguments.

The Mso help shows application.Run(Macro, Arg1, Arg2, etc,)
but it presents the same 'problem'.

Is there a better way ?
Thanks.
--
Neal Z

Run verb, macro with arguments
 

Neal,
You can use Optional arguments in the MacName sub and it will execute
using what ever arguments are supplied...

Sub UniversalCall()
MacName "Huey", "Dewey", "Louie", "Donald"
MacName , , "Louie"
MacName
End Sub
'--
Sub MacName(Optional ByRef sArgAy1 As String, Optional ByRef sArgAy2 As String, _
Optional ByRef sArgAy3 As String, Optional ByRef sArgAy4 As String)
MsgBox sArgAy3
End Sub
-
The other way is to use a "Parameter Array" in the MacName sub.
See "Understanding Parameter Arrays" in help.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"Neal Zimm"
wrote in message
Hi All,
As part of the menu functionality in my addin I have a worksheet
with macro names, and argument values, (or none) for each macro.
..onaction contains the macro which gathers the data from the
worksheet.

Prior to the paragraph below is a sub that builds the
sArgAy string array and the ArgQty variable.

The code below is working and I don't think I'll have any
macros with more than 4 arguments, but I'm wondering if
there's a better way to code it without all the If..Else
syntax.

RunMac: 'Execute macros with arguments values
If ArgQty = 0 Then
Run MacName
ElseIf ArgQty = 1 Then
Run MacName, sArgAy(1)
ElseIf ArgQty = 2 Then
Run MacName, sArgAy(1), sArgAy(2)
ElseIf ArgQty = 3 Then
Run MacName, sArgAy(1), sArgAy(2), sArgAy(3)
ElseIf ArgQty = 4 Then
Run MacName, sArgAy(1), sArgAy(2), sArgAy(3), sArgAy(4)
End If
Return

I thought about var = array(xxx, xxx,xxx) but I still could not
figure out how to vary the qty of arguments.

I tried:
stringvar = "value1,value2"
run macname, stringvar

the above gets argument not optional when macname needs 2 arguments.

The Mso help shows application.Run(Macro, Arg1, Arg2, etc,)
but it presents the same 'problem'.

Is there a better way ?
Thanks.
--
Neal Z

Run verb, macro with arguments
 

Jim, Thanks
Am learning VBA as I go along.
Never knew about optional arguments.
Am going to Help now Param arrays.
Best,

--
Neal Z


"Jim Cone" wrote:


Neal,
You can use Optional arguments in the MacName sub and it will execute
using what ever arguments are supplied...

Sub UniversalCall()
MacName "Huey", "Dewey", "Louie", "Donald"
MacName , , "Louie"
MacName
End Sub
'--
Sub MacName(Optional ByRef sArgAy1 As String, Optional ByRef sArgAy2 As String, _
Optional ByRef sArgAy3 As String, Optional ByRef sArgAy4 As String)
MsgBox sArgAy3
End Sub
-
The other way is to use a "Parameter Array" in the MacName sub.
See "Understanding Parameter Arrays" in help.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"Neal Zimm"
wrote in message
Hi All,
As part of the menu functionality in my addin I have a worksheet
with macro names, and argument values, (or none) for each macro.
..onaction contains the macro which gathers the data from the
worksheet.

Prior to the paragraph below is a sub that builds the
sArgAy string array and the ArgQty variable.

The code below is working and I don't think I'll have any
macros with more than 4 arguments, but I'm wondering if
there's a better way to code it without all the If..Else
syntax.

RunMac: 'Execute macros with arguments values
If ArgQty = 0 Then
Run MacName
ElseIf ArgQty = 1 Then
Run MacName, sArgAy(1)
ElseIf ArgQty = 2 Then
Run MacName, sArgAy(1), sArgAy(2)
ElseIf ArgQty = 3 Then
Run MacName, sArgAy(1), sArgAy(2), sArgAy(3)
ElseIf ArgQty = 4 Then
Run MacName, sArgAy(1), sArgAy(2), sArgAy(3), sArgAy(4)
End If
Return

I thought about var = array(xxx, xxx,xxx) but I still could not
figure out how to vary the qty of arguments.

I tried:
stringvar = "value1,value2"
run macname, stringvar

the above gets argument not optional when macname needs 2 arguments.

The Mso help shows application.Run(Macro, Arg1, Arg2, etc,)
but it presents the same 'problem'.

Is there a better way ?
Thanks.
--
Neal Z