Pass Arguments to a Macro Called from a Button or Sheet in Excel

How to pass arguments and values to macros called from worksheets, buttons, and anything else from the Excel interface.

This is different than passing a value from one macro to another.

Sections:

Create the Macro to Accept the Value

Pass a Text Value to the Macro

Pass a Numeric Value to the Macro

Pass Multiple Values to the Macro

Pass the Value of a Cell to the Macro

Pass the Value of a Name to the Macro

Call the Macro from a Button or Other Object/Shape

Alternate Format to Reference the Macros

Notes

Create the Macro to Accept the Value

To allow a macro to accept a value, you simply type a name for that value between the parentheses at the top of the macro.

Here is our sample macro:

Sub show_msg(message)

'output a message that is given to this macro

MsgBox message

End Sub

This macro accepts a value in the message variable and then outputs it into a message box pop-up.

Pass a Text Value to the Macro

When you go to run this macro from the worksheet Excel interface, you will actually not see it in the list of available macros.

Hit Alt + F8 to get to the Run Macro window:

We must type in the name of the macro and then also the value for the argument.

Type in the name of the macro and then a space and then the value that you want to send to the macro.

'show_msg "my value"'

Note: the text is surrounded by double quotation marks and then the entire thing is surrounded by single quotation marks.

Click Run and you're done:

Pass a Numeric Value to the Macro

Hit Alt + F8 to run the macro.

The name of the macro will not appear in the window so we must type it in under where it says Macro name.

'show_msg 5'

Notice the 5 after the name of the macro. That is how you pass a numeric argument to the macro; simply put a space after the name of the macro and type the number.

Note: the name of the macro and the argument must be surrounded by single quotation marks. Look closely in the above code and you will see this. However, the number does not have to be surrounded by double quotation marks like if you were inputting text for the argument.

Run the macro and you will see the result:

Pass Multiple Values to the Macro

You follow the same pattern as passing a single argument/value/parameter, except you put a comma between the first and second arguments.

First, I've added a macro that accepts two arguments.

Sub show_msg_2(message, message_2)

'output a message that is given to this macro

MsgBox message & " " & message_2

End Sub

Hit Alt + F8 to go to the Macro window. You will not see this macro since it accepts arguments.

We must type the name of the macro and then a space and the first argument and then a comma and the second argument.

'show_msg_2 10,"hi"'

Note: the name of the macro and arguments, everything that was typed into the macro window, must be surrounded by single quotation marks; text must additionally be surrounded by double quotation marks. You can see this in the example above.

Run the macro and we see this:

Both values were successfully passed to the macro.

You can have, basically, as many arguments as you need, just follow this pattern for adding additional ones. Also, it doesn't matter if you pass a text or numeric value; just make sure to put all text values within double quotation marks.

Pass the Value of a Cell to the Macro

You can also pass the value of a cell to a macro.

Hit Alt + F8 to get to the macro window. Remember, you won't see the macro in the list.

Under where it says Macro name, input the name of the macro and the argument just as in previous examples, with one difference this time, we use Evaluate() to get the cell reference into the macro:

'show_msg Evaluate("A1")'

Evaluate() is used to reference a cell; the cell reference inside of Evaluate() must be surrounded by double quotation marks.

Note: everything must be surrounded with single quotation marks.

Run the macro and you should see this:

You can see the value of cell A1 was put into the macro and output in the pop-up message box.

Pass the Value of a Name to the Macro

If you have Names in your spreadsheet, you can also pass those to a macro.

First, hit Alt + F8 to get to the Macro window. Since we are passing a value to a macro, the macro won't appear in the window and we will have to type it in following the pattern of the above examples where you have the name of the macro and then the argument to pass to the macro.

'show_msg Evaluate("Cell_Stuff")'

Evaluate() is used to bring the value of the name into the macro. You must put the name inside of Evaluate() and surround the name with double quotation marks. This way, whatever value the name contains or references will be passed to the macro.

Note: everything must be surrounded with single quotation marks, as you can see in the example.

Run the macro and you should see this:

The output is the same as the last example only because I set the name Cell_Stuff equal to the value of cell A1 on Sheet1.

Call the Macro from a Button or Other Object/Shape

To assign a macro that you pass arguments to a button, shape, image, or any object, you first right-click that object and click Assign Macro and then type the name of the macro and the argument, following the pattern described in the above examples, and then click OK.

Here is a sample:

'show_msg "I clicked a button!"'

Note: you must surround everything with single quotation marks and text must always have double quotation marks around it. If you want to send the value of a cell or a name or multiple arguments at once, reference the above examples for this; it works the same calling the macro from a button as it does from the Macro window that you get to by hitting Alt + F8.

Now, whenever I click my button I see this:

Alternate Format to Reference the Macros

You can also reference the macro like this:

'show_msg(5)'

Instead of putting a space after the macro name and then the arguments, you put parentheses and then the arguments inside of them.

The format for the arguments remains the same as for the above examples.

Notes

This tutorial covers a lot of examples but, the main thing is to remember to surround everything with single quotation marks, type the name of the desired macro, put double quotation marks around any text arguments, and make sure the macro itself can accept arguments.

Make sure to download the file attached to this tutorial to work with these examples in Excel.