How to call a sub in Excel VBA

VBA (Visual Basic for Applications) can be a highly effective tool for automating tasks and improving productivity in many industries. Its primary strength is its ability to manipulate and automate tasks within Microsoft Office applications, such as Excel, Word, and Access. VBA can be used to create custom functions, automate repetitive tasks, and even create user interfaces. With VBA, users can reduce manual input, improve accuracy, and streamline complex workflows. 

In this tutorial we’ll learn how to call a sub procedure in VBA Excel. There are a few variations to the process of calling a Sub in Excel VBA. We’ll discuss the following possibilities in this tutorial;

• Calling a Sub from another Sub without arguments
• Calling a Sub from another Sub with arguments
• Calling a Sub from user defined function without arguments
• Calling a Sub from user defined function with arguments

Method 1: Calling a Sub from another Sub without arguments

The most common method to call a sub from another sub is by simply typing the name of the sub in another sub. Follow the steps below to learn how it can be done in VBA.

Step 1 – Open VBA Editor 

• Open VBA Editor by going to the Developer tab and then pressing the Visual Basic action button.
• This can also be done by pressing the shortcut keys ALT+F11

Step 2 – Write the simple code to call subs

• In the VBA Editor write the following code. Subs can be called directly by using their names or through keyword Call. So, we’ll use both methods in this example.

Step 3 – Call Test Sub from other subs

• With the VBA Editor in active state click in the boundaries of Calling_Sub and then press the F5 shortcut key or press the play button to run the code. 
• Repeat the same procedure with Calling_Sub1.
• You will see the message boxes displaying the required message both times as shown above.

Method 2: Calling a Sub from another Sub with arguments

The other method to call a sub from another sub is by passing some arguments to the other sub and calling through simply typing the name of the sub in another sub or using the Call keyword. Follow the steps below to learn how it can be done in VBA.

Step 1 – Open VBA Editor 

• Open VBA Editor by going to the Developer tab and then pressing the Visual Basic action button.
• This can also be done by pressing the shortcut keys ALT+F11

Step 2 – Write the simple code to call sub from other subs

• In the VBA Editor write the following code. Subs can be called directly by using their names or through keyword Call. So, we’ll use both methods in this example.

• In this example the sub, Test_Sub requires a parameter Msg in the form of a string. Therefore, we’ll call it with the specific string message from each sub, otherwise, we’ll get an error.

Step 3 – Call Test Sub from other subs

• With the VBA Editor in active state click in the boundaries of Calling_Sub and then press the F5 shortcut key or press the play button to run the code. 
• Repeat the same procedure with Calling_Sub1.
• You will see the message boxes displaying the required message both times as shown above.

Method 3: Calling a Sub from User Defined Function without arguments

In this method we’ll create a user defined custom function and then call the Test_Sub through the function. Functions are user defined functions and can be used just like the built-in functions of Excel e.g., SUM, COUNT etc. These functions can then be invoked from the workbook.

Step – 1 Insert a Module through VBA Editor

• In VBA Editor, click on the insert button to open up the insert menu.
• Click on Module, this will insert a Module1 in the Modules section.

Step 2 – Write the simple code to call sub from function

• In the VBA Editor write the following code. Subs can be called directly by using their names or through keyword Call. So, we’ll use both methods in this example.

Step 3 – Call Test Sub from User Defined Function

• The method to execute Subs through User Defined Functions is a little bit different from executing the code through VBA Editor. We’ll write the function’s name just like a formula in Excel’s worksheet and that will execute the code written inside the function as shown above.

Method 4: Calling a Sub from User Defined Function with arguments

In this method we’ll create a user defined custom function and then call the Test_Sub through the function and also pass arguments to the Sub. Functions are user defined functions and can be used just like the built-in functions of Excel e.g., SUM, COUNT etc. These functions can then be invoked from the workbook.

Step – 1 Insert a Module through VBA Editor

• In VBA Editor, click on the insert button to open up the insert menu.
• Click on Module, this will insert a Module2 in the Modules section.

Step 2 – Write the simple code to call sub from function

• In the VBA Editor write the following code. Subs can be called directly by using their names or through keyword Call. So, we’ll use both methods in this example.

Step 3 – Call Test Sub from User Defined Function

• The method to execute Subs through User Defined Functions is a little bit different from executing the code through VBA Editor. We’ll write the function’s name just like a formula in Excel’s worksheet and that will execute the code written inside the function as shown above.