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.
Table of contents
- The Call Sub is used to call a sub-procedure from within a VBA module.
- To call a Sub procedure from another procedure, type the name of the procedure and include values for any required arguments. The Call statement is not required, but if you use it, you must enclose any arguments in parentheses.
- The Call Sub does not return a value, unlike a function.
- The Call Sub does not have a Return statement. A statement used in a Sub procedure passes values to a called procedure using named arguments with a colon and equal to symbol.
How to Call Subroutine in Excel VBA?
To implement VBA Call Subroutine in Excel VBA, we perform the following steps as shown below:
- Go to the “Developer” section in the toolbar and click the “Visual Basic” option. Now, the VBA Editor opens to add functions and Sub procedures. Then, click the “Insert” and “Module” buttons to create a new module or blank page.
- Initialize a sub-procedure to perform addition on a given variable by 1.
- Accept an Integer input variable.
- Add one to the integer variable and print it.
- Initialize another subroutine to perform VBA Call Sub.
- Perform VBA Call Sub.
num = 10
num = num + 1
- Print the number by pressing the F5 or Run buttons on the VBA toolbar.
It prints 11 since we declared num as 10, and we add 1 to it as defined in the function. Hence, the output is shown as 11.
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.
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
In another module:
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
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.
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
Debug.Print “The result is: ” & res
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
Step 9: Run the above code.
This will result in “True” being printed in the immediate tab.
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.
Sub ConcatenateStrings(s1 As String, s2 As String)
Dim result As String
result = s1 & s2
Debug.Print “The concatenated strings are: ” & result
Dim string1 As String
Dim string2 As String
string1 = “Hello”
string2 = “World!”
ConcatenateStrings string1, string2
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)
No, you cannot call a private subroutine directly from outside the module it is defined in.
No, you cannot directly call a subroutine within a function in VBA.
• Incorrect subroutine name or syntax: Double-check that you are using the correct subroutine name and that the syntax is accurate.
• Subroutine not defined: Ensure that the subroutine you are trying to call is defined in the same module or in a referenced module.
• The subroutine is private: If the subroutine is declared private, it cannot be called from outside the module where it is defined.
• Subroutine not in scope: Make sure that the subroutine is accessible from the location where you are trying to call it. Check for any scoping issues or modules that need to be referenced.
• Compilation errors: Check for any compilation errors in your VBA project that may prevent the subroutine from being called.
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 –