VBA Call Sub

What is Excel VBA Call Sub?

In VBA (Visual Basic for Applications), “Call Sub” is a statement used to call a subroutine or a procedure within a VBA module. A subroutine is a block of code that performs a specific task or a series of tasks. It can be defined within a VBA module and called from other parts of the code using the “Call Sub” statement or simply utilizing the subroutine name followed by parentheses.

Consider the following example.

Here, we write a subroutine to print a text and call it from the function, CallSub(), to get the output printed.

Note: In newer versions of VBA, starting with VBA 6 (used in Office 2000 and later versions), the “Call” keyword is optional. So, you can omit it and directly call the subroutine.

How to Call Subroutine in Excel VBA?

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Examples

Let us look at a few examples of implementing the VBA Sub Call procedure.

Example #1 – VBA Call Sub from another module

Consider an example where we must implement a VBA Call Sub from another sheet by adding three numbers.

Step 1: In the “Developer” tab in the toolbar, click the “Visual Basic” option and open a new module. Define a sub-procedure that adds three numbers and prints the output.

Step 2: Define an integer that will hold the sum value of all three numbers.

Step 3: Add all three variables and assign the sum to the result variable declared in the earlier steps.

Step 4: Print the result variable.

Step 5: Click the “Insert” and “Module” buttons to create a new module or blank page.

Step 6: Create a new subroutine in the new module that performs VBA Call Sub with variable.

Step 7: Declare three integer variables to be added.

Step 8: Get the numbers from the user.

Step 9: Now, perform the Call Sub from another sheet by calling the Module name along with the subroutine.

It performs VBA Call Sub with parameters. Here, we can call subroutines from a module to any other modules.

Code:

Sub SumOfThreeNumbers(ByRef n1 As Integer, ByRef n2 As Integer, ByRef n3 As Integer)
Dim res As Integer
res = n1 + n2 + n3
Debug.Print “The sum is: ” & res
End Sub

In another module:

Sub GetThreeNumbers()
Dim n1 As Integer
Dim n2 As Integer
Dim n3 As Integer
n1 = InputBox(“Enter a number”)
n2 = InputBox(“Enter a number”)
n3 = InputBox(“Enter a number”)
Module1.SumOfThreeNumbers n1, n2, n3
End Sub

Step 10: Run the VBA code in the second module. The output is printed as shown:

The sum will be printed in the Immediate tab as shown.

Example #2 – VBA Call Sub with multiple arguments

Consider an example where you need to VBA Call Sub with multiple arguments. There may be more than one argument in a subroutine. You must create subroutines that implement the AND Gate function with three inputs.

Step 1: In a new module, initialize a sub-procedure to mimic Boolean AND Gate.

Step 2: Declare a result variable as Boolean.

Step 3: Initialize an If-Else condition to check whether all three variable values are “True.”

If all three of them are “True, ” the result is also returned as True. If not, the result value is returned as “False.”

Step 4: Print the result.

Step 5: Declare a subroutine that accepts Boolean input arguments as input from the user and performs VBA Call Sub with parameters.

Step 6: Declare three variables as the Boolean datatype.

Step 7: Accept input of all three variables.

Step 8: VBA Sub Call with arguments for the And Gate subroutine.

Code:

Sub AndGate(x As Boolean, y As Boolean, z As Boolean)
Dim res As Boolean
If (x = True And y = True And z = True) Then
res = True
Else: res = False
End If
Debug.Print “The result is: ” & res
End Sub

Sub InputsForGate()
Dim x As Boolean
Dim y As Boolean
Dim z As Boolean
x = InputBox(“True or False”)
y = InputBox(“True or False”)
z = InputBox(“True or False”)
AndGate x, y, z
End Sub

Step 9: Run the above code.

This will result in “True” being printed in the immediate tab.

Example #3

Consider an example where you need to perform string concatenation using VBA Sub Call with variables. Here we accept two string values and concatenate both string values into one consolidated string.

Step 1: Initialize a sub-procedure to concatenate two strings.

Step 2: Declare a variable, result, as a string to hold the value of the concatenated string values.

Step 3: Print the concatenated string.

Step 4: Declare the subroutine to call the string concatenation subroutine.

Step 5: Declare two strings and initialize their values.

Step 6: VBA Sub Call with Argument the concatenation subroutine.

Code:

Sub ConcatenateStrings(s1 As String, s2 As String)
Dim result As String
result = s1 & s2
Debug.Print “The concatenated strings are: ” & result
End Sub

Sub CallString()
Dim string1 As String
Dim string2 As String
string1 = “Hello”
string2 = “World!”
ConcatenateStrings string1, string2
End Sub

Step 7: The output is as shown:

Important Things To Note

• Pass arguments to subroutines as needed. Arguments allow you to pass values or objects to subroutines, enabling them to perform tasks based on specific data.
• Ensure that the argument values or variables match the called subroutine’s expected parameters.
• Don’t use the “Call” keyword unnecessarily. In newer versions of VBA, starting with VBA 6, the “Call” keyword is optional, and you can directly call a subroutine without it.
• Don’t use subroutines for tasks that can be better accomplished using functions. Functions can return values, allowing you to use the result directly in expressions.

Frequently Asked Questions (FAQs)

Recommended Articles

This has been a guide to VBA Call Sub. Here we learn how to call subroutine or procedure within a VBA Module with examples & downloadable template. You can learn more from the following articles –

• VBA Clear Content
• VBA Class Modules
• VBA CDBL Function