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.

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.

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.

• In the Visual Basic window click Insert >> 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

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

---

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.

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

The following message will pop-up in your worksheet:

---

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.

---

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

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

---

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

 

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

---

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.

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

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

Read More: Excel VBA to Call Sub with Parameters

---

Excel VBA to Call Sub From Another Sheet

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

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

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

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

The following message box appears on your screen.

Read More: Excel VBA Call Sub from Another Sheet

---

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.

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

The following message box appears on your screen.

Read More: Excel VBA Call Sub from Another Module

---

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

---

Further Reading

• How to Call Private Sub in Excel VBA
• How to Use Excel VBA to Call Private Sub from Userform
• How to Run a Private Sub in VBA

Get FREE Advanced Excel Exercises with Solutions!