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)
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.
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
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.
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)
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:
- How to Return a Value in VBA Function (Both Array and Non-Array Values)
- Use LCase Function in VBA in Excel (With 4 Examples)
- How to Use VBA SPLIT Function in Excel (5 Examples)
- Use TRIM Function in VBA in Excel (Definition + VBA Code)
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.
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
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.
Now we’ve called Sub1 from Function1 by the line of code:
Call Sub1(10)
Now if we insert Function1 in any cell of our worksheet, it will show 10 in a Message Box.
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.
⧭ 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.