How to Call a Sub in VBA in Excel (4 Examples)

Get FREE Advanced Excel Exercises with Solutions!

In this article, I’ll show you how you can call a Sub from another Sub or Function in VBA in Excel. You’ll learn to call a Sub with or without arguments, as well as both Public and Private Subs.


How to Call a Sub in VBA in Excel (Quick View)

Call a Sub from Another Sub in VBA in Excel

Note: Here a Sub called Sub2 calls a Sub called Sub1.


How to Call a Sub in VBA in Excel: 4 Examples

Here we’ve got a Sub in VBA called Sub1.

Sub to Call a Sub from Another Sub in VBA

If you run Sub1, you’ll get the message “Sub1 is Run.”

Today our objective is to learn how we can call this Sub from another Sub or Function in all possible ways.


1. Call a Sub without Arguments from Another Sub in VBA in Excel

First, we’ll call a Sub without any argument from another Sub in VBA.

Here, Sub1 is the Sub without arguments.

Now we’ll call the Sub Sub1 from another Sub called Sub2.

To call the Sub Sub1 from another Sub, you have to use the line of code:

 

Sub1

Or

 

Call Sub1

Call a Sub from Another Sub in VBA in Excel

Now if you run Sub2, Sub1 will be called and the message “Sub1 is Run.” will be displayed.

Read More: Excel VBA Call Sub from Another Module


2. Call a Sub with Arguments from Another Sub in VBA in Excel

Now we’ll call a Sub with arguments from another Sub in VBA.

Here we’ve changed the Sub Sub1 in such a way that it contains an argument called Input_Value, and when run, displays that argument.

Sub with Arguments to Call a Sub from Another Sub in Excel

To call this Sub from another Sub (Sub2), we have to use the line of code:

 

Sub1(Input_Value)

Or

 

Call Sub1(Input_Value)

Here, we’ve used:

 

Call Sub1(10)

VBA Code to Call a Sub from Another Sub in Excel

Now, when we’ll run Sub2, Sub1 will be called with the input 10, and 10 will be displayed in a Message Box.

Read More: VBA to Call Sub From Another Workbook in Excel 


3. Call a Sub with/without Arguments from A User-Defined Function in VBA in Excel

You can also call a Sub from a User-Defined Function in VBA.

Sub without Arguments

Let’s call a Sub without arguments first.

Here we’ve again changed the Sub Sub1 to the one without arguments.

Sub without Arguments to Call a Sub from Another Sub in Excel

Now we’ll create a Function called Function1 and call Sub1 from that function.

To call a Sub from a function, the line of code to be used is the same:

 

Sub1

Or

 

Call Sub1

VBA Code to Call a Sub from A Function in Excel

Now, if you insert Function1 in any cell of your worksheet, Sub1 will be called and a Message Box will display “Sub1 is Run.”.

Sub with Arguments

You can also call a Sub with arguments from a User-Defined Function in VBA in Excel.

Here we’ve changed Sub1 to the one with arguments again.

VBA Code to Call a Sub from Another Sub in Excel

Now we’ve called Sub1 from Function1 by the line of code:

 

Call Sub1(10)

VBA Code to Call a Sub from A Function in VBA in Excel

Now if we insert Function1 in any cell of our worksheet, it will show 10 in a Message Box.

Inserting Function to Call a Sub in VBA in Excel

Read More: Excel VBA Call Sub from Another Sheet


4. Call a Private Sub from Another Sub or Function in VBA in Excel

Up till now, we’ve called a Public Sub from another Sub or Function. This time, we’ll show how you can call a Private Sub from another Sub or Function in VBA.

Calling from a Sub:

You can only call a Private Sub from another Sub if the two are in the same module of your VBA window.

Here we’ve changed Sub1 to a Private Sub by adding the term Private in the first line. And called it from Sub2 which is in the same module.

Now if you run Sub2, you will get a Message Box displaying 10.

Output to Call a Sub from Another Sub in VBA in Excel

Calling from a Function:

Same for functions. To call a Private Sub from a Function in VBA, the Sub and the Function must be in the same module.

Here we’ve inserted the Private Sub Sub1 and the Function Function1 in the same module.

Now if we insert Function1 in any cell of our worksheet, a Message Box will display 10.

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


Summary

Here is the summary of all the points discussed today:

  • You can call a Sub from another Sub or User-Defined Function in VBA by using the term “Call” with the name of the Sub, or simply putting the name of the Sub.
  • If the Sub to be called contains an argument, you have to call the Sub with any value of that argument.
  • If the Sub to be called is declared as a Private one, you have to call it from another Sub or Function of the same module.

Download Practice Workbook

Download this practice book to exercise the task while you are reading this article.


Conclusion

Using these methods, you can call a Sub from another Sub or Function in VBA in Excel. Do you have any questions? Feel free to ask us.


Further Readings

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo