Excel VBA Call Sub from Another Module

Here in this article, we are going to discuss how in Excel, you can use Excel VBA to call sub from another module. While writing code for carrying out various tasks, the user may need to call a sub from another module for various reasons. The reason could be subdividing long codes into different sections or modules. Which can lead to increasing the clarity of the code. Which also can lead to making the code more reusable.

Excel VBA to Call Sub from Another Module: 3 Methods

Below we presented separate methods to call sub from another module using various functions, commands, or even without any commands or methods. User needs to understand the methods and use them appropriately for their need. In order to avoid any kind of incompatibility issue, the user needs to opt for the Excel 365 edition.

1. Using Call Function

Using the Call Function we can call a sub and execute the task inside the sub directly. There are three methods of doing this, one is to call directly, with arguments, and finally without the arguments.

1.1 Call a Sub Directly

In this method, we are going to call a sub directly. We just have to put the sub-name with an argument with it.

In the beginning, we need to open the VBA code editor from the developer tab.
Here, we can call a sub directly from another module without using the Call The VBA code is given below. Paste this VBA code in the code editor.

Sub call_sub_directly()
SortData_with_arguments sortOrder:="descending"
End Sub

The main code that is going to be executed is given below. Data saved in a certain range will be sorted according to the Age column value.

Sub SortData()
Dim sortRange As Range
Set sortRange = Range("B5:G9")
sortRange.Sort key1:=Range("D5"), _
order1:=xlAscending, _
Header:=xlNo, _
Orientation:=xlSortColumns
End Sub

Here, we can see that the code has a line that will execute the SortData_with_arguments sub stored in Module 3, which will sort the data stored in the range of B5:G9 in ascending order.

1.2 Call a Sub with Arguments

Here, we can call a sub directly from another module with the specified argument.

In the beginning, we need to open the VBA code editor from the developer tab.
The VBA code is given below. Paste this VBA code in the code editor.

Sub SortData_with_arguments(ByVal sortOrder As String)
Dim sortRange As Range
Set sortRange = Range("B5:G9")
If sortOrder = "ascending" Then
sortRange.Sort key1:=Range("D5"), _
order1:=xlAscending, _
Header:=xlNo, _
Orientation:=xlSortColumns
ElseIf sortOrder = "descending" Then
sortRange.Sort key1:=Range("D5"), _
order1:=xlDescending, _
Header:=xlNo, _
Orientation:=xlSortColumns
Else
MsgBox "Invalid sort order. Please specify either 'ascending' or 'descending'."
End If
End Sub

We stored this code in Module 3. This code will take an argument, about whether the dataset will be sorted in ascending or in descending order.
To call this sub from another module, we need to paste the below code to another editor.

Sub call_with_arguments()
Call SortData_with_arguments("descending")
End Sub

We saved this code to Module 6, after this we will run the code. Doing this will call the SortData_with_arguments sub, with descending argument input.
After that, we will notice that the data in the range of B5:G9 is now sorted according to the Age column.

1.3 Call a Sub without Arguments

Here, we can call a sub directly from another module without any argument input.

In the beginning, we need to open the VBA code editor from the developer tab.
The VBA code is given below. Paste this VBA code in the code editor.

Sub SortData()
Dim sortRange As Range
Set sortRange = Range("B5:G9")
sortRange.Sort key1:=Range("D5"), _
order1:=xlAscending, _
Header:=xlNo, _
Orientation:=xlSortColumns
End Sub

We saved the code in Module 2. Now to run this code from another module, the example code is given below.

Sub call_without_argument()
Call SortData
End Sub

After running this code, we will see that the SortData sub is now called and then we will notice that the data stored in the B5:G9 is now sorted in Descending order(as set in the code).

2. Call Sub from Another Module Using Parentheses

This method can be attributed to the extension of the Call function. We need to save the below code in another module. But first, we need to enter the code that we need to call from another module. And that code will sort the data stored in a range of cells.

In the beginning, we need to open the VBA code editor from the developer tab.
Paste the code given below in the code editor.

The code is given below,

Sub SortData()
Dim sortRange As Range
Set sortRange = Range("B5:G9")
sortRange.Sort key1:=Range("D5"), _
order1:=xlAscending, _
Header:=xlNo, _
Orientation:=xlSortColumns
End Sub

We saved the code in Module 1. Now we will write the code that will call this sub from another module. The code is given below.

Sub Call_with_parentheses()
Call Module1.SortData
End Sub

After running the code, we will see that the SortData sub is called. The data in the range of cell B5:G9 is now sorted according to the Age column values.

3. Using Run Command to Call Sub

Another method of calling a module from another module is to use the Application. Run method.

In the beginning, we need to open the VBA code editor from the developer tab.
For this, we need to create a Source code that will be called from another module. We will give the source code below. This code will sort the data stored in a selected range.

Sub SortData()
Dim sortRange As Range
Set sortRange = Range("B5:G9")
sortRange.Sort key1:=Range("D5"), _
order1:=xlAscending, _
Header:=xlNo, _
Orientation:=xlSortColumns
End Sub

The final code will call the source code to execute the source code is given below.

Sub call_with_Run()
Application.Run "Module1.SortData"
End Sub

Now we can run this code, and after running this code we will see that the code has sorted data in the range of cell B5:G9 now according to the Age column.

Read More: How to Run a Private Sub in VBA

4. Call a Private Sub from Another Module in Excel VBA

Normally it is not possible to call a private sub directly from another sub. A private sub is the sole property of the mother module. So it is not possible directly call a sub from another outsider module. But there is another way to do that. And the way is to use a public sub with a private sub. And make a connection between them. After that, that public sub will be available to call from another module. For this, enter the below code in the editor window.

In the beginning, we need to open the VBA code editor from the developer tab.
And then paste the code in the editor window.

Private Sub Call_sub_private()
Dim sortRange As Range
Set sortRange = Range("B5:G9")
sortRange.Sort key1:=Range("D5"), _
order1:=xlAscending, _
Header:=xlNo, _
Orientation:=xlSortColumns
End Sub
Public Sub Public_sub()
Call_sub_private
End Sub

Here, the public module will connect the private module altogether.
Then you need to run this whole code from another module.
Open a new module and then enter the following code in the code editor.

Sub call_public_sub()
Call Public_sub
End Sub

After pressing Run, you will notice that the dataset in the range of B5:G9 is now sorted based on the Age column.

Things to Remember

Make sure that the module containing the Sub you want to call is referenced correctly. You can reference a module using the Call statement, the Application.Run method, or by creating a reference to the module using the Public keyword.
If the Sub you want to call is Private, you will need to create a Public Sub or Function in the same module that calls the Private Sub or Function. This allows you to call the Private Sub or Function indirectly from another module.
If the Sub you want to call requires arguments, make sure you provide the correct number and type of arguments in the correct order. If you’re not sure what arguments a Sub requires, check the Sub’s declaration in the module where you have defined.
If the Sub you want to call modifies data or objects, make sure that you fully understand the impact of the changes it makes. Make sure that the Sub doesn’t overwrite data or objects that other parts of your program rely on, and make sure that you intented the changes it makes.
If the Sub you want to call relies on data or objects from another module, make sure that the data or objects are accessible from the module where you have defined the Sub. If necessary, use references to pass data or objects between modules.

Frequently Asked Question

How do you call a sub within a sub in Excel VBA?

You can call a sub from within a sub in an Excel VBA, using the call function, or simply just put the macro name within the other code. Follow the code given below.

Sub OuterSub()
InnerSub ' Call InnerSub
End Sub

We have the article of the Innser sub given below, we just put the macro name in the Outersub.

Sub InnerSub()
' Code for InnerSub
End Sub

So following the above code will be a good example of using a sub within another sub.

Can you have a sub within function VBA?

Yes, you can call a sub from another function in Excel VBA. Normally you can create a sub and you can put the sub name inside the function. Like you can see in the function given below, in which we are going to put the sub name inside the function.

Function MyFunction(arg1 As Integer, arg2 As Integer) As Integer
'Perform some calculations here
Dim result As Integer
result = arg1 + arg2
'Call a sub to perform additional steps
DoSomething arg1, arg2
'Return the result
MyFunction = result
End Function

And we have given the sub below which you are going to be put inside the function. When the function is called, the sub will also be called at the same time.

Sub DoSomething(arg1 As Integer, arg2 As Integer)
'Perform additional steps here
End Sub

Can a sub call itself VBA?

Yes, a sub can call itself in VBA. This is called recursion, and it can be a useful technique for solving problems that involve repetitive calculations or iterations.

When a sub calls itself, it creates a new instance of the sub within the current instance. This new instance has its own set of variables and executes independently of the original instance. The new instance can call itself again, creating yet another instance, and so on, until it met a specific condition and the recursion stops.

Can I use a variable from another sub in VBA?

Yes, you can use a variable from another sub in VBA, but it depends on the scope of the variable.

In VBA, the scope of a variable determines where it can be accessed from. If you declare a variable within a sub, it has local scope and you can only access it within that sub. If you declare a variable outside of any sub, it has the module-level scope and you can access it from any sub or function within the same module. Like the example code given below, where we actually declared the variable outside the sub, and then we defined the sub.

Dim myVar As Integer 'Declare the variable with module-level scope
Sub Sub1()
myVar = 1 'Set the value of myVar within Sub1
End Sub

And the sub given as below,

Sub Sub2()
MsgBox "The value of myVar is " & myVar 'Access the value of myVar within Sub2
End Sub

Now we also can declare a sub publicly which we can accessed by any sub from any module. You have to declare it as shown below,

Public myPublicVar As Integer

If you declare a sub like this, then you can access this variable from any module and any sub stored in the Excel workbook.

Download Practice Workbook

Call Sub from Another Module.xlsm

Conclusion

In this article, users showed how in Excel, using VBA, you can call a sub from another module. The methods range from no functions, with arguments, without arguments, etc. We have also shown how the user can call a private sub from another module. User needs to find their task application and then use the appropriate method shown above.

Get FREE Advanced Excel Exercises with Solutions!