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

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.


Download Practice Workbook

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


4 Ways to Call a Sub in VBA in Excel

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.


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.


Similar Readings:


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


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


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.

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

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo