Excel VBA to Call Sub with Parameters (3 Suitable Examples)

If you want to learn Visual Basic for Applications (Excel VBA), one of the most important concepts to know is 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. Here’s an overview of subroutines.

What Is a Subroutine in Excel VBA?

In Visual Basic for Applications (VBA), a Subroutine is a code block that executes 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 by other functions 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.

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 Sub

ByRef – 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.

Sub subroutine_name(ByRef variablename as Integer)
End Sub

The default is ByRef.

Excel VBA Call Sub with Parameters: 3 Examples

Example 1 – Call a Sub with a Single Parameter

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 them with a message.

• Create a Module in the Visual Basic Editor.
• 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

• Press F5 or go to the Run tab and click Run Sub/Userform to execute the code.

Sub mySub()
    Call myName("name")
End Sub

Sub myName(name As String)
    name = InputBox("Enter your name:")
    MsgBox "Good Evening " & name
End Sub

• In the myName subroutine, we included an InputBox and a MsgBox.
• Type a name in the InputBox and press OK.

• 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 a VBA Sub with Multiple Parameters in Excel

In Excel 2013 and later versions, the maximum number of parameters you can assign to a sub is limited to 60. In this example, we will call a sub with two parameters. We modified the previous code to ask for the user’s age along with their name in InputBox. The code will print a MsgBox with the user’s name and age.

• Create a Module in the Visual Basic Editor.
• Paste this code into the Module.

Sub mySub()
    Call myName("name", "age")
End Sub
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

Sub mySub()
    Call myName("name", "age")
End Sub

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

• Press F5 or click Run Sub/Userform to execute the code.

• In myName subroutine, we included two inputBox in the variables name and Age and 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 an 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.

Example 3 – Call a Sub with Named Parameters in Excel VBA

When you name your parameters, you don’t have to pass the parameters in the same order you assign them in the subroutine.

• Create a Module in the Visual Basic Editor.
• 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

• Press F5 or click Run Sub/Userform to execute the code.

• In the 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

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.

• Paste this code into a 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

Function Agecalculator(day1 As Date, day2 As Date) As Double
    Agecalculator = (day1 - day2) / 365
End Function

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

• Press F5 or click Run Sub/Userform to execute the code.

• Insert your birthday in the InputBox and press OK.

• You will get the output in the MsgBox.

How to Call a Sub from Another Sheet Using Excel VBA

Suppose we have a Sub named myName located in Sheet1 and we want it to call in Sheet2.

• Go to Project Explorer and double-click on Sheet1.
• Use this code in the code module.

Sub myName()
    Dim name As String
    name = InputBox("Enter your name:")
    MsgBox "Good Evening " & name
End Sub

• From Project Explorer, double-click on Sheet2 and insert the code below.
• Press F5 or 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.

• You’ll get the output in a new MsgBox.

How to Call a Sub from Another Workbook Using Excel VBA

To call a sub from another workbook, you have to mention the workbook name, module name and sub name in your new sub. 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

• Create a Module in the Visual Basic Editor.
• Paste the below code into the Module.
• Press F5 or click Run Sub/Userform to execute the code.

As you can see, in Application.Run we provided the address of the sub we called in detail. You must open Workbook1.

• Insert any name and then press OK.

• A MsgBox will appear.

Frequently Asked Questions

Can I call a sub with parameters from a button click event?

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?

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?

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 the Practice Workbook

Related Articles

• 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