VBA to Call Sub From Another Workbook in Excel (With Examples)

In this article, we will demonstrate how to utilize VBA to call a subroutine (sub) from another workbook, both opened and closed. We will also discuss related uses of VBA such as calling a sub from another sheet and calling a sub from another module.

To illustrate our methods, we’ll use two workbooks named VBA Call Sub From Another Workbook and Workbook_1. We’ll call several subroutines from the workbook Workbook_1 in the second workbook.

Example 1 – Call Sub From an Open Workbook

In the first scenario Workbook_1 is open.

Image showing 2 Opened workbooks

Read More: How to Call a Sub in VBA in Excel

1.1 – Calling Sub Without Arguments

Suppose one of the modules in Workbook_1 contains the following VBA code with subroutine Sub_1. This subroutine is without arguments.

Image showing the subroutine name that will be called in a different workbook

Let’s call this sub Sub_1 in the workbook VBA Call Sub From Another Workbook.

Steps:

Go to the Developer tab in the workbook VBA Call Sub From Another Workbook.
Click on Visual Basic.

Go to the developer tab and then click on visual basic

In the Visual Basic window click Insert >> Module.

Inserting new module

Copy the following VBA code and paste in the module window then click on Run:

Sub CallSubInAnotherWorkbookOpened()
    Dim wbA As Workbook
    Set wbA = Workbooks("Workbook_1")
    Application.Run "'" & wbA.Name & "'!Sub_1"
End Sub

Insert and run vba code

VBA Breakdown

Set wbA = Workbooks(“Workbook_1”)

Workbook Workbook_1 is assigned to variable wbA.

Run “‘” & wbA.Name & “‘!Sub_1”

A sub named Sub_1 is called from the workbook assigned to wbA. The Application.Run method executes the specified sub.

The sub is called from Workbook_1 and pops up the following message.

Sub is called from an open workbook and message box appears on screen

1.2 – Calling Sub With Arguments

We can call subroutines with arguments from another workbook too.

Suppose one of the modules in Workbook_1 contains the following macro, set as MySub.

Image showing subroutine with arguments in a module in Workbook_1

Let’s call that sub from Workbook_1 in the workbook VBA Call Sub From Another Workbook.

Steps:

Create a new module like previously.
Insert the below code in the module then click on Run:

Sub CallSubInAnotherWorkbookArgumentOpened()
    Dim wbA As Workbook
    Dim macroToCall As String
    Dim argumentValue As String
    Set wbA = Workbooks("Workbook_1.xlsm")
    macroToCall = "MySub"
    argumentValue = "Hello from ExcelDemy"
    Application.Run "'" & wbA.Name & "'!" & macroToCall, argumentValue
End Sub

Clicking on run after inserting vba code

VBA Breakdown

Set wbA = Workbooks(“Workbook_1.xlsm”)

The workbook named Workbook_1 is assigned to variable wbA.

macroToCall = “MySub”

The variable macroToCall is assigned to the string MySub. The name of the sub that will be called in the other workbook is represented by this.

argumentValue = “Hello from ExcelDemy”

Assigns the string Hello from ExcelDemy to the variable argumentValue.

Run “‘” & wbA.Name & “‘!” & macroToCall, argumentValue

Executes the workbook’s sub specified in the macroToCall variable, which is assigned to the wbA workbook.

The following message will pop-up in your worksheet:

Sub with arguments called from another workbook and message box appears on screen

Example 2 – Call Sub From a Closed Workbook

The workbook titled Workbook_1 has both types of subroutines – with arguments and without. Let’s now call a sub from workbook Workbook_1 when it is closed.

We’ll presume your Workbook_1 file is now closed and is situated in the following location.

Image showing location of closed workbook titled Workbook_1

2.1 – Calling Sub Without Arguments

Create a module window as before.
Copy the following code and paste in the module, then click on Run:

Sub CallSubInAnotherWorkbookClosed()
    Dim wbA As Workbook
    Set wbA = Workbooks.Open("E:\Workbook_1.xlsm")
    Application.Run "'" & wbA.Name & "'!Sub_1"
End Sub

Inserting code to call sub from a closed workbook

VBA Breakdown

Set wbA = Workbooks.Open(“E:\Workbook_1.xlsm”)

Opens the workbook named Workbook_1 located in E:\ then assigns it to variable wbA.

Run “‘” & wbA.Name & “‘!Sub_1”

Calls the Sub_1 sub in the workbook that was assigned to variable wbA.

The closed workbook Workbook_1 will be opened and a message box will appear in your worksheet as below.

Closed workbook opened and message box appeared on screen

2.2 – Calling Sub With Arguments

Create a module window as before.
Copy the following VBA code and paste in the module then click on Run.

Sub CallSubInAnotherWorkbookArgumentClosed()
    Dim wbA As Workbook
    Dim macroToCall As String
    Dim argumentValue As String
    Set wbA = Workbooks.Open("E:\Workbook_1.xlsm")
    macroToCall = "MySub"
    argumentValue = "Hello from ExcelDemy"
    Application.Run "'" & wbA.Name & "'!" & macroToCall, argumentValue
End Sub

Inserting code to call sub with arguments from a closed workbook

VBA Breakdown

Set wbA = Workbooks.Open(“E:\Workbook_1.xlsm”)

Opens the workbook named Workbook_1 located in E:\ then assigns it to variable wbA.

macroToCall = “MySub”

Sets the variable macroToCall to the string MySub.

argumentValue = “Hello from ExcelDemy”

Assigns the text Hello from ExcelDemy to variable argumentValue.

Run “‘” & wbA.Name & “‘!” & macroToCall, argumentValue

Calls the sub named in the macroToCall variable from the workbook assigned to wbA.

The closed workbook Workbook_1 will be opened and a message box will appear on your screen as follows:

Closed workbook opened and message box pops-up on screen

Excel VBA Run Macro From Another Workbook With Parameters

Let’s assume that in workbook Workbook_1 the following subroutine Sub_3 with parameters is in a module.

Image showing subroutine with parameters in workbook titled Workbook_1

Let’s call this sub in the workbook VBA Call Sub From Another Workbook.

Steps:

Create a new module from the VBA Call Sub From Another Workbook.
Enter the code below and click Run:

Sub RunMacroFromWorkbook1()
    Dim wb As Workbook
    Dim Path As String
    Dim Name As String
    Dim parameter_1 As String
    Dim parameter_2 As Integer
    Path = "E:\Workbook_1.xlsm"
    Name = "Sub_3"
    parameter_1 = "Welcome to ExcelDemy"
    parameter_2 = 12345
    Set wb = Workbooks.Open(Path)
    Application.Run "'" & wb.Name & "'!" & Name, parameter_1, parameter_2
End Sub

Inserting code to call sub from another workbook with parameters

VBA Breakdown

Path = “E:\Workbook_1.xlsm”

Assigns E:\Workbook_1.xlsm to variable Path.

Name = “Sub_3”

Assigns Sub_3 subroutine to variable Name.

parameter_1 = “Welcome to ExcelDemy”

Assigns string Welcome to ExcelDemy to variable parameter_1.

parameter_2 = 12345

Assigns integer 12345 to variable parameter_2.

Set wb = Workbooks.Open(Path)

Opens the workbook assigned to variable Path then assigns that workbook to variable wb.

Run “‘” & wb.Name & “‘!” & Name, parameter_1, parameter_2

Calls the sub named in the Name variable from the workbook assigned to the variable wb.

The workbook Workbook_1 will be opened if it is closed and the following message box will appear:

Message box with parameters values shown on screen

Excel VBA to Call Sub From Another Sheet

Assume the following code with subroutine named Sub_2 is in Sheet1 in file Workbook_1.

Image showing subroutine in a sheet of workbook_1

To call this sub in a different worksheet of the file Workbook_1, create a new module first.

Inserting module

Copy the following VBA code and paste in the module section, then click on Run:

Sub CallSubFromAnotherSheet()
    Sheets("Sheet1").Sub_2
End Sub

Inserting code to call sub from another sheet

VBA Breakdown

Sheets(“Sheet1”).Sub_2

Calls subroutine named Sub_2 from sheet named Sheet1.

The following message box appears on your screen.

Sub from another sheet is called and the result is shown in the image

Excel VBA Call Sub From Another Module

Assume you have the following macro with a subroutine named Sub_1 in Module1 in file Workbook_1.

Image showing code in a module of workbook_1

Let’s call this sub in a new module.

Create a new module.
Copy the following code and paste in the module, then click on Run:

Sub CallSubFromAnotherModule()
    Module1.Sub_1
End Sub

Inserting code to call sub from another module

VBA Breakdown

Module.Sub_1

Calls subroutine named Sub_1 from module named Module1.

The following message box appears on your screen.

Sub from another module is called and message box appears on screen

Frequently Asked Questions

1. What is sub () in VBA?
A subroutine sub() is a block of code that is used to carry out a specified task in VBA. It is a process that a VBA module or piece of code can call and carry out. You can define a Sub with or without parameters that does not return a value. Sub is usually the first word, followed by the name of the subroutine and two brackets ().

2. How to get values from another workbook in Excel using VBA?

Declare a variable for the other workbook.
Use the Open method to open the desired workbook.
Access the values using the appropriate method (e.g., Sheets(“Sheet1”).Range(“A1”).Value).
Close the other workbook when finished.
Use the retrieved values as needed.

3. Is it possible to call a private sub from another workbook?
No, you can not call private subs directly from a different worksheet. Only the module or class in which they are specified can access private subs.

4. Can I call a sub from another workbook if it is protected with a password?
No, you can not call a sub immediately from a password-protected workbook. Before you can access and run the sub, you would first need to enter the password and unlock the workbook.

Download Practice Workbook

Call Sub From Another Workbook.xlsm

Workbook_1.xlsm