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.

Overview of How to Call Sub with Parameters in Excel VBA


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.

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 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

Code to call a sub with single parameter

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

Run the code to call a sub with single parameter

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 the myName subroutine, we included an InputBox and a MsgBox.
  • Type a name in the InputBox and press OK.

InputBox to enter the name of the user

  • The code gives you a greeting in the MsgBox, taking the name from the InputBox.

Output MsgBox of the sub with single parameter

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

Code to call a sub with multiple parameters

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 doesn’t change how the code is stored or executed.
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.

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

Run the code to call a sub with multiple parameters

  • 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.

InputBox to enter the name of the user from multiple parameters sub

  • Pressing OK will prompt the second InputBox to ask for age.
  • Enter an age value and press OK.

InputBox to enter the age of the user

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.

Output MsgBox of the sub with multiple parameters

Read More: Excel VBA to Call Private Sub from Another Module


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

Code to call a sub with named parameters

We named our parameters as name and Age. Since we want our inputs to be of the string type, we assigned them as such. When we call the myName sub and assign values for our parameters, we recalled our named parameters. We didn’t have to keep the original order that we assigned in the myName sub.
  • Press F5 or click Run Sub/Userform to execute the code.

Run the code to call a sub with named parameters

  • 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.

InputBox to enter the name of the user from named parameters sub

  • Pressing OK will prompt the second InputBox to ask for age.

InputBox to enter the age of the user from named parameters sub

  • 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.

Output MsgBox of the sub with named parameters


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

Code to call a function with multiple parameters

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
  • 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.
  • y” will accept a String input from user in the InputBox.
  • The diff variable will calculate the age using the newly created function Agecalculator.
  • The MsgBox will print the message.

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

Run the code to call a function with multiple parameters

  • Insert your birthday in the InputBox and press OK.

InputBox to insert user’s birthday

  • You will get the output in the MsgBox.

Output MsgBox of calling a function


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

Code in Sheet1 that to be called

  • From Project Explorer, double-click on Sheet2 and insert the code below.
  • Press F5 or click Run Sub/Userform to execute the code.
  • Code in Sheet2 to call sub from another worksheetThe statement Call Sheet1.myName will call the sub located in Sheet1.
  • Type the name and press OK which will prompt the MsgBox.

InputBox to insert name of the user by calling sub from another worksheet

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

Output MsgBox by calling sub from worksheet


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

Sub in Workbook1

  • 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.

code to call sub from another workbook

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.

InputBox to insert name of the user by calling sub from another workbook

  • A MsgBox will appear.

Output MsgBox by calling sub from workbook


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

Get FREE Advanced Excel Exercises with Solutions!

Tags:

Mehedi Hassan
Mehedi Hassan

Mehedi Hassan, having earned a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, functions as an Excel & VBA Content Developer at ExcelDemy. His deep interest in research and innovation aligns seamlessly with his fervor for Excel. In this role, Mehedi not only skillfully addresses challenging issues but also exhibits enthusiasm and expertise in gracefully navigating intricate situations, emphasizing his unwavering dedication to consistently delivering outstanding content. His interests are Advanced... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo