Run verb, macro with arguments

[Neal Zimm]

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.

 

[Jim Cone]

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" <[email protected]>
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 Zimm]

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