Pass Values from One Macro to Another Macro

How to pass variables and values to macros. This allows you to get a result from one macro and use it in another macro, whether that result is text, numbers, a cell reference, or whatever you need.

This tutorial focuses on the case when you call one macro from another macro.

Sections:

Create the Macro that Gets the Value

Call/Reference the Macro that Gets the Value

Pass Values to the Macro

Pass Multiple Values to a Macro

Notes

Create the Macro that Gets the Value

Create a macro that can accept a value.

To do that, you create what are called arguments. You come up with a name for a variable that you can use in the macro and put it in the middle of the parentheses at the top of the macro. This argument can then be used within the macro as a regular variable.

Sub test(my_argument)
 
MsgBox my_argument
 
End Sub

This macro will output the value of the variable my_argument into a message box.

Now that we have a macro that can accept a value, we need to reference it.

Call/Reference the Macro that Gets the Value

There are a couple different ways to call a macro, which means to make a macro run from another macro.

Sub call_test()
 
'call the macro method 1
test
 
'call the macro method 2
Call test
 
End Sub

This is the way to call a macro that does NOT have an argument.

However, we have an argument that we need to send to the test macro, and that's covered in the next part.

Pass Values to the Macro

Once you know how to call the macro, you can easily pass values to it.

Pass Text Values:

Let's send a text value to the macro.

Sub call_test()
 
'call the macro method 1
test "my text"
 
'call the macro method 2
Call test("my text")
 
End Sub

When you send a text value, you must always put double quotation marks around it.

Note: when you pass a value using the Call method, the second example, you must put the values within parentheses.

Pass Numbers:

Send a number value to the macro.

Sub call_test()
 
'call the macro method 1
test 25
 
'call the macro method 2
Call test(25)
 
End Sub

When passing numbers to the macro you do not have to include any quotation marks.

Pass Variables to the Macro:

You will most likely be sending a value that is contained within a variable and that is also very easy to do.

Sub call_test()
 
my_variable = "stuff"
 
'call the macro method 1
test my_variable
 
'call the macro method 2
Call test(my_variable)
 
End Sub

When passing a variable you do not use quotation marks, simply type the name of the variable.

Pass Multiple Values to a Macro

You can pass many different arguments/variables to a macro.

First, make sure you have each argument listed in the macro that should receive them.

To have multiple arguments, separate each one with a comma.

In this example you can see that I now have my_argument and my_argument_2.

Second, we now need to send two arguments to this macro.

Sub call_test()
 
my_variable = "stuff"
 
'call the macro method 1
test my_variable, "second argument"
 
'call the macro method 2
Call test(my_variable, "second argument")
 
End Sub

You can see here that I sent a variable my_variable and also a text string second argument.

To add more arguments, just type a comma and then put the next argument.

It's straightforward and simple; follow the same rules for inputting arguments here as when we did it above and put as many as you need.

Notes

Sending a value to another macro is really easy to do once you know the steps and it is a very powerful feature of macros.

You can send text, numbers, and variables through to another macro. The variables can also include range and cell references.

Being able to "talk" to other macros like this allows you to make more flexible code and to have a few smaller macros that can be easier to manage compared with a single large and potentially confusing macro.

Also, there are more features related to sending data to another macro but this tutorial covers all of the things you need for the vast majority of cases.

Download the sample file attached to this tutorial and play around with the examples and you will quickly become comfortable with this feature.