It is often necessary to call a VBA subroutine (sub) from one workbook to another. This makes it simpler to organize and arrange your VBA projects because it enables easy execution of code across several workbooks. You can access and use code created in other files by calling a sub from another workbook, promoting code reuse and improving the overall effectiveness of your VBA applications.
In this article, we have demonstrated how one can utilize VBA to call sub from another workbook. You will learn how one can call a sub from an opened workbook as well as from a closed workbook. Also, you will get to know some other uses of VBA such as calling a sub from another sheet and calling a sub from another module.
2 Examples of Excel VBA to Call Sub From Another Workbook
Utilizing proper VBA Macros, you can call a subroutine from an open workbook as well as from a closed workbook. In this article, we have used two workbooks named VBA Call Sub From Another Workbook and Workbook_1. Here, we are going to call several subroutines from the workbook Workbook_1 in workbook VBA Call Sub From Another Workbook.
1. Call Sub From an Open Workbook
The first scenario can be when Workbook_1 is opened means both workbooks are opened.
Read More: How to Call a Sub in VBA in Excel
1.1 Calling Sub Without Arguments
Let’s say, one of the modules in Workbook_1 contains the following VBA code with subroutine Sub_1. This subroutine is without arguments.
- Now we will call this sub Sub_1 in the workbook VBA Call Sub From Another Workbook.
- First, you have to create a module.
- Go to the Developer tab from the workbook VBA Call Sub From Another Workbook.
- Click on Visual Basic.
- Follow these steps from the Visual Basic window: Insert >> Module.
- Copy and paste the following VBA code 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
VBA Breakdown
- Set wbA = Workbooks(“Workbook_1”)
Workbook named Workbook_1 is assigned to variable wbA by this statement.
- Run “‘” & wbA.Name & “‘!Sub_1”
This line calls a sub named Sub_1 from the workbook assigned to wbA. We have used the Application.Run method to execute the specified sub.
- And the sub is called from Workbook_1 and pops up the following message on your workbook.
1.2 Calling Sub With Arguments
You can call subroutines with arguments from another workbook also.
- Let’s say, one of the modules in Workbook_1 file contains the following macro.
- The subroutine is set as MySub.
- Now, we will call that sub from Workbook_1 in the workbook VBA Call Sub From Another Workbook.
- Create a new module like previously.
- Insert the below code in the module section 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
VBA Breakdown
- Set wbA = Workbooks(“Workbook_1.xlsm”)
Workbook named Workbook_1 is assigned to variable wbA by this statement.
- macroToCall = “MySub”
This line sets the variable macroToCall 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
This line executes the workbook’s sub-specified in the macroToCall variable, which is assigned to the wbA workbook.
- And the following message will pop-up on your worksheet as shown.
2. Call Sub From a Closed Workbook
We have already seen that the workbook titled Workbook_1 has both types of subroutines- with arguments and without arguments. So let’s have a look at how you can call sub from workbook Workbook_1 when it is closed.
Let’s presume, your Workbook_1 file is now closed and is situated in the following location.
Now, we will call sub from this closed workbook in the below section.
2.1 Calling Sub Without Arguments
- To call a sub without arguments from Workbook_1 to workbook VBA Call Sub From Another Workbook, create a module window as before.
- Copy and paste the following code in the module section and 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
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.
- And the closed workbook Workbook_1 will be opened and a message box as shown below will appear on your worksheet.
2.2 Calling Sub With Arguments
- To call a sub with arguments from Workbook_1 to workbook VBA Call Sub From Another Workbook, create a module window as before.
- Copy and paste the following VBA code in the module section and 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
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”
This line sets the variable macroToCall to the string MySub.
- argumentValue = “Hello from ExcelDemy”
This line 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.
- And 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, in workbook Workbook_1, the following subroutine Sub_3 with parameters is in a module section.
- Now let’s call this sub in the workbook VBA Call Sub From Another Workbook.
- Create a new module from the VBA Call Sub From Another Workbook.
- Type down the code below and hit 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
VBA Breakdown
- Path = “E:\Workbook_1.xlsm”
Assigns E:\Workbook_1.xlsm to variable Path.
- Name = “Sub_3”
This line 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.
- And the workbook Workbook_1 will be opened if it is closed and the following message box will appear on your screen.
Read More: Excel VBA to Call Sub with Parameters
Excel VBA Call Sub From Another Sheet
Assume, the following code with subroutine named Sub_2 is in Sheet1 in file Workbook_1.
- Now to call this sub in a different worksheet of the file Workbook_1, create a new module first.
- Copy and paste the following VBA code in the module section then click on Run.
Sub CallSubFromAnotherSheet()
Sheets("Sheet1").Sub_2
End Sub
VBA Breakdown
- Sheets(“Sheet1”).Sub_2
Calls subroutine named Sub_2 from sheet named Sheet1.
- And the following message box appears on your screen.
Read More: Excel VBA Call Sub from Another Sheet
Excel VBA Call Sub From Another Module
Presume, you have the following macro with a subroutine named Sub_1 in Module1 in file Workbook_1.
- Now, let’s call this sub in a new module.
- Create a new module.
- Copy and paste the following code and click on Run.
Sub CallSubFromAnotherModule()
Module1.Sub_1
End Sub
VBA Breakdown
- Module.Sub_1
Calls subroutine named Sub_1 from module named Module1.
- And 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 and 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?
To get values from another workbook in Excel using VBA, follow these steps:
- 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?
You can not call private subs straight 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 could access and run the sub, you would first need to enter the password and unlock the workbook.
Download Practice Workbook
You can download the workbooks used to illustrate methods in this article from here.
Conclusion
This article describes the basics of VBA to call sub from another workbook. Utilizing VBA call sub features, one can easily call a subroutine from a different workbook or from the same workbook’s different sheets or the same workbook’s different modules. Hope, you have figured out whatever you were looking for.
