Passing multiple arguments to a subroutine using the .onaction command in a menuitem

[LD2000]

I am trying to pass 2 arguments to a subroutine by assigning it to a .onaction command. The code works when I only pass one string argument but falls down ( I get the "The macros is not available in this workbook or all macros are disabled" error) when I try and add a second integer argument.

The code that works is:-
.OnAction = "'PopulateFields(""" & myArray(x) & """)'"

The subroutine in this instance is declared as:-
PopulateFields(strAppType As String)

The failing code looks like this:-
.OnAction = "'PopulateFields(""" & myArray(x) & """, " & 3 & ")'"
PopulateFields(strAppType As String, intTier As Integer)

I have checked the value of the text passed to the .onaction command in the immediate window and it looks like this:-
'PopulateFields("Screed And Overscreed", 3)'

In the instance that works it looks like this
'PopulateFields("Screed And Overscreed")'

Can anyone see a problem??

 

[RoryA]

You should not be using brackets.

 

[dmt32]

Hi,

welcome to forum

try removing the Parentheses (brackets)

not tested

 .OnAction = "'PopulateFields """ & myarray(x) & """, " & 3 & " '"

Dave

 

[LD2000]

Thank you Dave and Rory, that did it.

I don't suppose one of you knows why it worked with parentheses with the single string argument but not once I added a second argument? Not critical but interested to know.

LD

 

[dmt32]

Thank you Dave and Rory, that did it.

I don't suppose one of you knows why it worked with parentheses with the single string argument but not once I added a second argument? Not critical but interested to know.

LD

if you use the Call keyword to call a procedure that passes argument(s) then argument list must be enclosed in parentheses. If not, then you omit the parentheses around list.

As you are assigning the macro to OnAction you exclude parentheses however, I am little puzzled why it worked as you say for single argument and have no definitive answer to your question and if find time today, will have a play but maybe Rory as a better idea for this.

Dave

 

[LD2000]

Thanks for that.

Just another one of those little quirks I suppose.

 

[RoryA]

Without the Call keyword, if you use brackets round arguments, VBA tries to evaluate the contents of the brackets as an expression, then pass the result to the routine. Evaluating a single argument is ok (unless it’s an object) since VBA will evaluate say (5) or (“hello”) as simply those values, but trying to evaluate (5, “hello”) as one expression makes no sense to VBA.