VBA Call Sub

Introduction to Excel VBA Call Sub

In VBA, we have a function as CALL, which is used for calling the values stored in another Subcategory or Sub procedure. Suppose we have written a code somewhere in a workbook, now while writing another code we need the same code written earlier. So instead of writing the same code again, we can call that complete code or Sub procedure into the current Subcategory or Sub procedure. By this can avoid doing the same activity of workbook again and again.

How to Call Sub in Excel VBA?

Below are the different examples to call Sub in Excel VBA:

Excel VBA Call Sub – Example #1

First, let’s see a single example where we will CALL already written code subcategory or procedure. For this, we need a module.

Go to VBA window and under Insert menu option click on Module as shown below.

After that, we will get a blank page or window of Module. Now in that write a subcategory of code which we are performing or in any other name as shown below.

Code:

Sub Calling()

End Sub

Now use a command of the message box and type any text or word you want to see in the message box. Here we are using “First” as shown below.

Code:

Sub Calling()

MsgBox ("First")

End Sub

Now compile the code and run it by clicking on the Play button which is below the menu bar. We will see a message box containing the message “First” as shown below.

Now after the End in the same module, write another Subcategory or procedure in any name as shown below.

Code:

Sub Arriving()

End Sub

In that again use the command MsgBox and give it message or text as per your choice. Here we have given it “Second” as shown below.

Code:

Sub Arriving()

MsgBox ("Second")

End Sub

Now if we run the complete code then we will get the output of only last Subcategory which is a message box containing message “Second” as shown below.

Here comes the function CALL, which we will use to call both messages one by one. This will be used in the first subcategory.

For this type Call in the first subcategory before End, followed by the name of that subcategory whose code we want to call. Hereafter MsgBox we are using Call followed by Arriving which is the subcategory name of the code written below.

Code:

Sub Calling()

MsgBox ("First")

Call Arriving

End Sub

Sub Arriving()

MsgBox ("Second")

End Sub

Now compile the complete code from start to end and run it. We will see the message box named with the message “First”. Now click on Ok to proceed further. Once we do that, we will get the second message box containing the message “Second” as shown below. And if click on Ok again, then it will exit from the procedure.

What if we change the position of Call function from before End to after the first Subcategory as shown below? Now let run the complete code again.

Code:

Sub Calling()

Call Arriving

MsgBox ("First")

End Sub

Sub Arriving()

MsgBox ("Second")

End Sub

It will pop up the message stored in the second subprocedure first which is “Second” followed by the message “First” when we click on Ok as shown below.

So it’s all up to us, which Sub procedure or category we want to call first.

Excel VBA Call Sub – Example #2

Let’s see another example where we will use a Call function to call different subcategory. For this, we need a new module. Open a Module from the Insert menu. And it gives a Subcategory in any name as shown below.

Code:

Sub VBACall()

End Sub

Define 3 variables Num1, Num2, and Ans1 in DIM and assign then with Long. We can use Integer or Double as well, depending on one’s use. Long will allow us to consider any length of number.

Code:

Sub VBACall()

Dim Num1 As Long
Dim Num2 As Long
Dim Ans1 As Long

End Sub

Now give Num1 and Num2 any number of your choice. We have given them 100 and 50 respectively. Considering 100 and 50 will help us to identify the output quickly.

Code:

Sub VBACall()

Dim Num1 As Long
Dim Num2 As Long
Dim Ans1 As Long

Num1 = 100
Num2 = 50

End Sub

Now use multiplication formula to multiply the numbers stores in Num1 and Num2 and store their answer in Ans1 variable as shown below.

Code:

Sub VBACall()

Dim Num1 As Long
Dim Num2 As Long
Dim Ans1 As Long

Num1 = 100
Num2 = 50

Ans1 = Num1 * Num2

End Sub

In the next line of code, we will use the VBA Object. For this select the sheet with command Worksheet and give it a range of any cell. We have selected the range cell as B1. And at last print the result with any name such as Result or Answer as shown below.

Code:

Sub VBACall()

Dim Num1 As Long
Dim Num2 As Long
Dim Ans1 As Long

Num1 = 100
Num2 = 50

Ans1 = Num1 * Num2

Worksheets(1).Range("B1").Value = "Answer"

End Sub

Now give it the location where we want to see the answer in the same manner as shown above. Here we are choosing cell C1 as output cell and put the last variable Ans1 here.

Code:

Sub VBACall()

Dim Num1 As Long
Dim Num2 As Long
Dim Ans1 As Long

Num1 = 100
Num2 = 50

Ans1 = Num1 * Num2

Worksheets(1).Range("B1").Value = "Answer"
Worksheets(1).Range("C1").Value = Ans1

End Sub

Now run the code. We will see a multiplication result at cell C1.

Writing another Subcategory below the same code after End.

Code:

Sub VBACall2()

End Sub

Again define 3 variables Num3, Num4, and Ans2 in DIM and assign then with Long.

Code:

Sub VBACall2()

Dim Num3 As Long
Dim Num4 As Long
Dim Ans2 As Long

End Sub

Give Num3 and Num4 the same values like 100 and 50 and add both the numbers.

Code:

Sub VBACall2()

Dim Num3 As Long
Dim Num4 As Long
Dim Ans2 As Long

Num3 = 100
Num4 = 50

Ans2 = Num3 + Num4

End Sub

Now use VBA Object in a similar way as used above and give then range cell as B2 for Answer and C2 for output of Ans2.

Code:

Sub VBACall2()

Dim Num3 As Long
Dim Num4 As Long
Dim Ans2 As Long

Num3 = 100
Num4 = 50

Ans2 = Num3 + Num4

Worksheets(1).Range("B2").Value = "Answer"
Worksheets(1).Range("C2").Value = Ans2

End Sub

To call both results one by one, use Call function it gives it the name of 2^nd Subcategory as shown below.

Code:

Sub VBACall()
Dim Num1 As Long
Dim Num2 As Long
Dim Ans1 As Long
Num1 = 100
Num2 = 50
Ans1 = Num1 * Num2
Worksheets(1).Range("B1").Value = "Answer"
Worksheets(1).Range("C1").Value = Ans1

Call VBACall2

End Sub

Sub VBACall2()
Dim Num3 As Long
Dim Num4 As Long
Dim Ans2 As Long
Num3 = 100
Num4 = 50
Ans2 = Num3 + Num4
Worksheets(1).Range("B2").Value = "Answer"
Worksheets(1).Range("C2").Value = Ans2
End Sub

Now compile the entire code and run. We will the first at cell C2, we got the result of the multiplication and at cell C3, a result of the addition.

To know the proper sequence, we can use MsgBox command as used in Example-1 and see which values called at what sequence.

Pros of Excel VBA Call Sub

• VBA Call Sub saves time in writing the same code again and again.
• Calling the VBA sub procedure store in same excel reduces the size of the excel file as well.

Things to Remember

• Use the message box for test purpose to see the sequential run of multiple codes.
• Compile the bigger code lines by pressing the F8 key so that the error part can be identified.
• Save the file in Macro Enable Excel format to retain the written code.
• Using CALL before End will run the first code first and after the first Subprocedure will run the second code first.

Recommended Articles

This is a guide to VBA Call Sub. Here we discuss how to Call Sub in Excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. VBA Do Until Loop
2. VBA Get Cell Value
3. VBA While Loop
4. VBA RGB