Pass Values from One Macro to Another Macro

Add to Favorites
Author: | Edits: don

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

8c5172f9d2214ffecc12dcf33e8a3b59.png

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

fc9a9ac1216a5c223ff25046574f4fdf.jpg

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

689dcd528ed105394c3162238bead50d.jpg

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

f55f9f39f27184764c2686d6589736e7.jpg

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

358503d0bc3dd2092e35d7a385aad4b8.jpg

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.

c7b9319a099c4555819449faf02b3e77.jpg

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

fd56f3ef5fc0b96fcb38272bcf97eca0.jpg

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.


Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Our Excel Courses

  • Skill Level: All Levels
  • |
  • 5 hours+

How to make a fully featured professional form in Excel that is unbreakable. This includes how to use the form to store, view, edit, and delete data from a data storage worksheet.

  • Skill Level: All Levels
  • |
  • 2 hours

Send Emails from Excel using VBA and Macros. This course starts from the Basics and builds up to more advanced examples with attaching workbooks, worksheets, PDF's, automatically sending emails, including a signature, error handling, increasing speed, and more.

Similar Content on TeachExcel
Pass Arguments to a Macro Called from a Button or Sheet in Excel
Tutorial: How to pass arguments and values to macros called from worksheets, buttons, and anything e...
Use Macros with UserForms
Tutorial: This tutorial explains how macros interact with UserForms. This includes an explanation of...
Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
Macro: This free Excel macro illustrates all of the possible parameters and arguments that yo...
Get Values from a Chart
Macro: This macro will pull the values from a chart in excel and list those values on another spr...
Make Users Enable Macros in Order to View a Workbook in Excel
Tutorial: Tutorial showing you how to make a user enable macros in a workbook in order to view the w...
5 Tips for Passing Values to Macros (VBA) - Beginner to Advanced
Tutorial: VBA Course Learn 5 simple tips for how to send values from one macro to another macro in ...