How to Call a VBA Sub from Another Module in 3 Minutes (Excel)

Written by Kasper Langmann

Visual Basic for Applications (VBA) is a programming language developed by Microsoft that is primarily used for automating tasks in Microsoft Office applications. One of the most common uses of VBA is in Excel, where it can be used to automate repetitive tasks, create custom functions, and more. One of the key features of VBA is the ability to call a Sub procedure from another module. This can be extremely useful in a variety of situations, such as when you want to reuse code or when you need to perform a specific task in multiple places in your program.

Understanding VBA Sub Procedures

A Sub procedure in VBA is a block of code that performs a specific task. Sub procedures are defined using the Sub keyword, followed by the name of the procedure and a pair of parentheses. The code for the procedure is written between the Sub and End Sub keywords. For example, the following code defines a simple Sub procedure that displays a message box:

Sub ShowMessage()
    MsgBox "Hello, world!"
End Sub

Sub procedures can be called from anywhere in your VBA code, including from other Sub procedures and from Function procedures. To call a Sub procedure, you simply use the name of the procedure followed by a pair of parentheses. For example, the following code calls the ShowMessage procedure:

Sub CallShowMessage()
    ShowMessage()
End Sub

Calling a Sub Procedure from Another Module

In VBA, a module is a container for code. Each Excel workbook can contain multiple modules, and each module can contain multiple Sub and Function procedures. By default, Sub procedures are only accessible within the module in which they are defined. However, you can call a Sub procedure from another module by using the Call statement, followed by the name of the procedure.

For example, suppose you have a Sub procedure named ShowMessage in a module named Module1, and you want to call this procedure from a Sub procedure in another module. You can do this by using the following code:

Sub CallShowMessage()
    Call Module1.ShowMessage
End Sub

Understanding the Call Statement

The Call statement in VBA is used to call a Sub procedure from another part of your code. The Call statement is followed by the name of the procedure you want to call. If the procedure is in another module, you must also include the name of the module, separated by a period.

It’s important to note that the Call statement is not required when calling a Sub procedure from within the same module. In this case, you can simply use the name of the procedure followed by a pair of parentheses. However, the Call statement is required when calling a Sub procedure from another module.

Passing Arguments to a Sub Procedure

In addition to performing a specific task, a Sub procedure can also accept arguments. Arguments are values that you pass to the procedure when you call it. The procedure can then use these values in its code.

To define a Sub procedure that accepts arguments, you include the arguments in the parentheses after the Sub keyword. Each argument is defined by a name and a data type. For example, the following code defines a Sub procedure that accepts a single argument:

Sub ShowMessage(message As String)
    MsgBox message
End Sub

To call a Sub procedure that accepts arguments, you include the arguments in the parentheses after the name of the procedure. Each argument is separated by a comma. For example, the following code calls the ShowMessage procedure with a single argument:

Sub CallShowMessage()
    Call ShowMessage("Hello, world!")
End Sub

Conclusion

Calling a VBA Sub procedure from another module is a powerful feature that allows you to reuse code and perform specific tasks in multiple places in your program. By understanding how to use the Call statement and how to pass arguments to a Sub procedure, you can write more efficient and maintainable VBA code.

Remember, practice is key when it comes to mastering any programming language, including VBA. So, don’t hesitate to experiment with calling Sub procedures from different modules and passing different types of arguments. Happy coding!