Passing Multiple parameters to VBA macro from a form button

[WanderingSoul]

I have a problem passing parameters to ‘macros’ in Excell. My version is from Microsoft Office Professional Plus 2021 (Microsoft® Excel® 2021 MSO (Version 2409 Build 16.0.18025.20160) 64-bit)

I want to use a simple Form button to call a VBA Sub which takes three parameters, from looking through the web it should work, but it doesn’t!
I can call a macro with no parameters or one parameter but if I try and pass more than one parameter I get the error message of “Cannot run the macro "'TwoParms(45, 75)'", The macro may not be available in this workbook or all macros may be disabled.”
TwoParms is my test macro.

To eliminate potential problems and keep things as simple as possible I have a created a new workbook with a single sheet (Sheet1) The VBA module was created using a simple record macro, then I added

Sub NoParms()
MsgBox ("No Parameters")
End Sub

Sub OneParm(parm1)
MsgBox ("OneParm: " & parm1)
End Sub

Sub TwoParms(parm1, parm2)
MsgBox ("TwoParms: " & parm1 & " | " & parm2)
End Sub

To the module, Debug/Compile produces no errors or warnings.
Calling NoParms() and OneParm(parm) work, calling TwoParms(parm1, parm2) does not.

I know I could pass a range of 3 cells, or perhaps use ActiveX controls, but I can see no reason why I should have to. As far as I can research this should work using a simple Form button.

Should this work and if so, what am I missing?

 

[RoryA]

What format is the workbook? (won't work in an xlsb file) How did you assign the macro specifically?

 

[WanderingSoul]

xslm, macro assigned te same way for no parms one parm and 2 parms, right click button and assign macro, then type it in.

As I said the one parameter and no parameter macros work fine, I only get the error when trying to use more than one parameter.

 

[HighAndWilder]

I have a problem passing parameters to ‘macros’ in Excell. My version is from Microsoft Office Professional Plus 2021 (Microsoft® Excel® 2021 MSO (Version 2409 Build 16.0.18025.20160) 64-bit)

I want to use a simple Form button to call a VBA Sub which takes three parameters, from looking through the web it should work, but it doesn’t!
I can call a macro with no parameters or one parameter but if I try and pass more than one parameter I get the error message of “Cannot run the macro "'TwoParms(45, 75)'", The macro may not be available in this workbook or all macros may be disabled.”
TwoParms is my test macro.

To eliminate potential problems and keep things as simple as possible I have a created a new workbook with a single sheet (Sheet1) The VBA module was created using a simple record macro, then I added

Sub NoParms()
MsgBox ("No Parameters")
End Sub

Sub OneParm(parm1)
MsgBox ("OneParm: " & parm1)
End Sub

Sub TwoParms(parm1, parm2)
MsgBox ("TwoParms: " & parm1 & " | " & parm2)
End Sub

To the module, Debug/Compile produces no errors or warnings.
Calling NoParms() and OneParm(parm) work, calling TwoParms(parm1, parm2) does not.

I know I could pass a range of 3 cells, or perhaps use ActiveX controls, but I can see no reason why I should have to. As far as I can research this should work using a simple Form button.

Should this work and if so, what am I missing?

Have you created a Form Control or ActiveX Control?

xslm, macro assigned te same way for no parms one parm and 2 parms, right click button and assign macro, then type it in.

As I said the one parameter and no parameter macros work fine, I only get the error when trying to use more than one parameter.

What are you typing in and where?

 

[WanderingSoul]

Using as I said a Form Button.

'OneParm(45)' works, 'TwoParms(45,75)' does not.

 

[dmt32]

Using as I said a Form Button.
'OneParm(45)' work, 'TwoParms(45,75)' does not.

Hi
try it like this & see if resolves

'TwoParms 45,75 '

Dave

 

[WanderingSoul]

Thank you Dave, that works.

Weird that 'OneParm(45)' works both with brackets and without. but Two parameters only works without brackets!
I tried it on a three parameter macro and using string parameters which all work fine without brackets.
If passing an Evaluate("B4") then the evaluate function must have the brackets, it doesn't work without them!

 

[dmt32]

I have passed arguments from objects in many projects but even now, get myself in a muddle with the correct syntax

You may find this article helpful Using procedures with arguments

Glad we were able to assist & appreciate your feedback

Dave