If you are planning to learn Visual Basic for Applications (Excel VBA), one of the most basic tasks for you is to understand Subroutines with parameters and how to call them. By passing values or objects as parameters, you can customize the behavior of your Subs for different scenarios without modifying the code. Additionally, parameterized Subs enhance code readability and make it easier to understand and collaborate with other developers. In this article, we shall explain the basics of a Subroutine and how you can call a Sub with parameters in Excel VBA with detailed examples. I hope this article will make VBA learning more enjoyable.
What Is Subroutine in Excel VBA?
In Visual Basic for Applications (VBA), a Subroutine refers to a code block executing a specific task defined within the code. Unlike functions, Subroutines do not return any values or results. They primarily divide extensive code into smaller, more manageable segments. These Subroutines can be invoked multiple times from any section of the program.
Subroutines in Excel VBA can be created and stored within a module in the Visual Basic Editor (VBE). They can be called from other VBA codes or by user actions such as clicking a button or selecting a menu item. Subroutines can manipulate data, perform calculations, format cells, interact with worksheets and workbooks, and much more. If you do not want to return any results, a VBA Call Subroutine is appropriate.
Subroutine Vs Function
A Subroutine (sub) in VBA is a procedure that performs a specific task or a series of actions but does not return a value. The directive in the Sub is employed to execute operations that might encompass modifying a cell, carrying out a computation, or importing a file into the Excel application. Again, a function in VBA is also a procedure, but it returns the value of the tasks performed. Usually, a function is created to do repetitive tasks.
There are some clear differences between a Subroutine and a Function. We are listing a couple of them below.
| Subroutine | Function |
|---|---|
| Performs a specific task. | Performs repetitive tasks. |
| Does not yield a value. | Yields return value. |
| Can be invoked from any part of the program and in various forms. | Invoked through a variable. |
| Cannot be directly utilized in spreadsheets as formulas. | Directly utilized in spreadsheets as formulas. |
| Users must input a value within the code before obtaining the outcome of the subroutine. | Values can be inserted in the formula or taken from cell references. |
| Excel users can execute a VBA subroutine. | Excel users cannot execute VBA functions. |
ByVal and ByRef
When you write code in Excel VBA, you need to provide some information within the subroutine or function like what kind of value will the code process, what kind of result you expect, etc. This information is passed within the subroutine or function as variables. When you define the variables inside the parenthesis of the subroutine or function, they are called arguments or parameters.
There are two ways you can pass a parameter into subroutines and functions.
ByVal – a copy of the argument’s value is made and passed to the procedure. Any changes made to the argument’s value within the procedure do not affect the original value outside the procedure.
Sub subroutine_name(ByVal variablename as String)
End SubByRef – an address or cell reference for the argument’s value is passed in the procedure, and any changes made to the argument’s value within the procedure will modify the original value outside the procedure.
Sub subroutine_name(ByRef variablename as Integer)
End SubIf you don’t specify, the default is ByRef.
Excel VBA Call Sub with Parameters: 3 Suitable Examples
Calling a Sub in Excel VBA refers to the act of executing or running a subroutine (sub) procedure. In this article, we will learn how we can call a Sub from another subroutine with one or more parameters in Excel VBA. We will also learn the advantage of naming our parameters inside a subroutine.
We have used some simple code to explain calling Sub so we hope this will be a pleasant experience for you.
Example 1: Call Sub with Single Parameter
For our first example, we will call a sub that has just a single parameter in Excel VBA. For simplicity’s sake, we have written a subroutine (mySub) that will call another subroutine (myName).
myName subroutine has a parameter (name) that accepts string type input from the user. When you pass a parameter to your sub or function you need to specify the parameter type like string, integer, variant or double, etc. If you don’t provide one, the default is Variant.
The purpose of the code is to ask for the name of the user and then greet him/her with a message. Let’s write the code.
- First, create a Module in the Visual Basic Editor.
- Then write/paste this code into the Module.
Sub mySub()
Call myName("name")
End Sub
Sub myName(name As String)
name = InputBox("Enter your name:")
MsgBox "Good Evening " & name
End Sub
- Now, press F5 or go to Run tab, and from the drop-down menu, click Run Sub/Userform to execute the code.
Code Breakdown
Sub mySub()
Call myName("name")
End Sub- Running this Sub calls on the Sub we used the Call statement to call the myName sub but you can omit this if you like. It makes absolutely no difference to how the code is stored or executed. Just easier to understand the code if that’s what it does.
Sub myName(name As String)
name = InputBox("Enter your name:")
MsgBox "Good Evening " & name
End Sub- In the myName Sub, we have a String parameter named “name”. This “name” variable accepts a String input from the user in InputBox. Then takes that input and prints it in MsgBox.
- In myName subroutine, we included an InputBox and a MsgBox. Type a name in the InputBox and press OK.
As a result, the code gives you a greeting in the MsgBox, taking the name from the InputBox.
Read More: How to Call Private Sub in Excel VBA
Example 2: Call VBA Sub with Multiple Parameters in Excel
You can also call a sub that contains multiple parameters. In Excel 2013 and later versions, the maximum number of parameters you can assign to a sub is limited to 60. This means you can have up to 60 parameters within the parentheses when declaring a sub in Excel VBA.
In this example, we will call a sub with two parameters. We just modified the previous code to ask for the user’s age along with their name in InputBox. Then the code will print a MsgBox with the user’s name and age.
- First, create a Module in the Visual Basic Editor.
- Then write/paste this code into the Module.
Sub mySub() Call myName("name", "age") End SubSub myName(name As String, Age As String) name = InputBox("Enter your name:") Age = InputBox("Enter your age:") MsgBox "Name: " & name & " and Age: " & Age End Sub
Code Breakdown
Sub mySub()
Call myName("name", "age")
End Sub- Running this Sub calls on the Sub we used the Call statement to call the myName sub but you can omit this if you like. It makes absolutely no difference to how the code is stored or executed. Just easier to understand the code if that’s what it does.
Sub myName(name As String, Age As String)
name = InputBox("Enter your name:")
Age = InputBox("Enter your age:")
MsgBox "Name: " & name & " and Age: " & Age
End Sub- In the myName Sub, we have two String parameters named “name” and “Age”. Both of the variables accept String input from the user in InputBox. Then takes those input and prints it in MsgBox.
- Now, press F5 or go to Run tab, and from the drop-down menu, click Run Sub/Userform to execute the code.
- In myName subroutine, we included two inputBox in the variables name and Age and finally a MsgBox to print the user inputs. Type a name in the InputBox and press OK.
Pressing OK will prompt the second InputBox to ask for age.
- Enter age value and press OK.
When you type the name and age in the input boxes and press OK, the code gives you the name and age in the MsgBox.
Read More: Excel VBA to Call Private Sub from Another Module
Example 3: Call Sub with Named Parameters in Excel VBA
Naming parameters works like a charm when it comes to working with a subroutine that has multiple parameters. When you name your parameters, there is a clear advantage when passing values. You don’t have to pass the parameters in the same order you assign them in the subroutine. Watch this example to understand.
- First, create a Module in the Visual Basic Editor.
- Then write/paste this code into the Module.
Sub mySub()
myName Age:=InputBox("Enter your age:"), name:=InputBox("Enter your name:")
End Sub
Sub myName(name As String, Age As String)
MsgBox "Name: " & name & " and Age: " & Age
End Sub
- Now, press F5 or go to Run tab, and from the drop-down menu, click Run Sub/Userform to execute the code.
- In myName subroutine, we included two input boxes in the variables name and age and finally a MsgBox to print the user inputs. Type a name in the input box and press OK.
- Pressing OK will prompt the second InputBox to ask for age.
When you type the name and age in the input boxes and press OK, the code gives you the name and age in the MsgBox.
How to Call a Function with Parameters in Excel VBA
Like calling a sub in another sub, you can also call a function in another sub. Unlike subroutines, Excel VBA functions can return a value. So you have to assign the function parameter type and function type as well. We are explaining calling a function with parameters by creating an age calculator.
We have created a function that will calculate age and a subroutine that will take input like the user’s birthday. The subroutine will call the function and calculate the age then show the age in a message box.
- First, create a Module in the Visual Basic Editor.
- Then write/paste this code into the Module.
Sub inputdays()
Dim x As Date
Dim y As Date
Dim diff As Integer
x = Now()
y = InputBox("When is your Birthday")
diff = Agecalculator(x, y)
MsgBox ("You are now " & diff & " years old")
End Sub
Function Agecalculator(day1 As Date, day2 As Date) As Double
Agecalculator = (day1 - day2) / 365
End Function
Code Breakdown
Function Agecalculator(day1 As Date, day2 As Date) As Double
Agecalculator = (day1 - day2) / 365
End Function- In the function Agecalculator, we have two parameters, day1 and day2 which are date-type variables. And a formula was written to find the difference between day1 and day2.
Sub inputdays()
Dim x As Date
Dim y As Date
Dim diff As Integer
x = Now()
y = InputBox("When is your Birthday")
diff = Agecalculator(x, y)
MsgBox ("You are now " & diff & " years old")
End Sub- Next, we created a Sub which calls the Function Agecalculator. We declared 3 variables. “x”, and “y” are Date variable and diff is integer type.
- We assigned Now() to “x” so that it can accept the present date.
- Next, “y” will accept a String input from user in the InputBox.
- The diff variable will calculate the age using the newly created function Agecalculator.
- Finally, the MsgBox will print the message.
- Now, press F5 or go to Run tab, and from the drop-down menu, click Run Sub/Userform to execute the code.
- Insert your birthday in the InputBox and press OK.
- Now, you will get the output in the MsgBox.
How to Call Sub from Another Sheet Using Excel VBA
You can also call sub from another sheet using Excel VBA. If a particular Sub is located in another sheet of the same workbook, you can call that Sub in your active workbook as well.
Suppose we have a Sub named myName located in Sheet1 and we want it to call in Sheet2. Follow us to learn the process.
- First, go to Project Explorer and double-click on Sheet1 which will activate the code module for Sheet1.
- Then write this code in the code module.
Sub myName()
Dim name As String
name = InputBox("Enter your name:")
MsgBox "Good Evening " & name
End Sub
- Now again, from Project Explorer, double-click on Sheet2 which will activate the code module for Sheet2, and write this code.
- Press F5 or go to Run tab, and from the drop-down menu, click Run Sub/Userform to execute the code.
The statement Call Sheet1.myName will call the sub located in Sheet1.- Type the name and press OK which will prompt the MsgBox.
The statement Call Sheet1.myName will call the sub located in Sheet1.
- Finally, get the output in the MsgBox.
How to Call Sub from Another Workbook Using Excel VBA
Suppose you have to rewrite a code again in another Workbook. But you don’t want to do that. Rather, you want to import the sub from that workbook to your active workbook. So how do you do that?
To call a sub from another workbook, you have to mention the workbook name, module name and sub name in your new sub. Follow these procedures if you want to understand them better.
Here, we have 2 Excel workbooks. Workbook1 contains our sub (myName) that we want to call in Workbook2.
Here’s the code in Workbook1.
Sub myName()
Dim name As String
name = InputBox("Enter your name:")
MsgBox "Good Evening " & name
End Sub
Now, to call the sub myName in Workbook2, do these steps.
- First, create a Module in the Visual Basic Editor.
- Then write/paste this code into the Module.
- Now, press F5 or go to Run tab, and from the drop-down menu, click Run Sub/Userform to execute the code.
As you can see, in Application.Run method we provided the address of the sub we called in detail. Please remember in order to Application.Run method to work you must open Workbook1.
- Insert any name and then press OK.
- A MsgBox will appear to greet you.
Frequently Asked Questions
- Can I call a sub with parameters from a button click event?
Ans: When you have a button on your Excel worksheet, you can assign a macro to it. This macro, which is a sub, can accept parameters. When defining the button’s click event handler, you provide the necessary parameter values in the macro’s code. This allows you to pass specific values or objects to the sub when the button is clicked. You can then perform actions or calculations based on those parameters within the sub. This enables you to create interactive buttons that execute code with specific inputs when clicked.
- Can I pass an object as a parameter to a sub?
Ans: Yes, in Excel VBA, you can pass an object as a parameter to a sub. By specifying the appropriate object type in the sub’s parameter declaration, you can pass objects such as worksheets, ranges, or custom objects. This allows you to manipulate and work with the object within the sub, making it a powerful way to perform operations on specific objects in your Excel workbook.
- Can I have optional parameters in a sub?
Ans: Yes, you can have optional parameters in a sub in Excel VBA. By specifying a default value for a parameter in the sub’s declaration, you make it optional. When calling the sub, you can either provide a value for the optional parameter or omit it. If omitted, the default value will be used. This flexibility allows you to have more versatile subs that can accommodate different scenarios without always requiring all parameters to be provided.
Download Practice Workbook
Download this file to practice for yourself.
Conclusion
Calling Subs with parameters in Excel VBA empowers you to build robust and customizable solutions. By passing values or objects as parameters, you can tailor the behavior of your Subs to suit specific needs. By understanding how to call a Sub with parameters in VBA you can unlock a world of possibilities for automating and extending the functionality of your Excel workbooks.
Related Articles
- Excel VBA Private Sub vs Sub (Comparison & Differences)
- How to Use Excel VBA Sub to Return Value
- How to Run a Private Sub in VBA
- Excel VBA Call Sub from Another Module
- How to Use Excel VBA to Call Private Sub from Userform
