Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro - onAction arguments | Excel Worksheet Functions | |||
adding custom verb to OLE object that changes its appearance | Excel Programming | |||
The opposite of .Verb in OLE Object | Excel Programming | |||
passing arguments from an excel macro to a word macro | Excel Discussion (Misc queries) | |||
Run macro in another workbook with arguments | Excel Programming |