VBA Workbook

What is Excel VBA Workbook?

VBA Workbook refers to the entire Excel file. VBA Workbook is an object that contains other objects like worksheets, chart objects, chart sheets, cells, ranges, shapes, and many more.

A simple Excel file is called a workbook in VBA language, and you must have come across this word instead of an Excel file in VBA language. By using the VBA workbook, we can do a lot of stuff like open the workbook, save the workbook, save the workbook, close the workbook, activate the workbook, select the workbook, and delete the workbook. To perform all these tasks, we must refer to the object, the VBA workbook. For example, the following code will activate the workbook named “Sales Report.xlsx.”

Workbooks(“Sales Report.xlsx “).Activate

Key Takeaways
  • VBA Workbook is an object. The whole bundle of Excel is considered as a workbook.
  • VBA Workbook object allows us to reference any open workbook.
  • We can assign any of the open workbook to variable and we can use the variable to activate or do any activities related to the VBA workbook instead of using the whole workbook name.
  • FOR EACH loop is used to loop through all the open workbooks


Let us look at the syntax of the VBA Workbook. 

VBA Workbook - Syntax

The syntax is straightforward. We can reference any of the workbooks using the index number of the workbook, or we can also reference the workbook by using the workbook name along with the file extension. As we can see, the output of this is also a workbook.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

How to use VBA Workbook Object code?

We will show you a step-by-step approach to using the VBA Workbook object code. Before we start the steps, we have two files open on our computer, i.e., “04. VBA Workbook. xlsm” and “Employee Survey.xlsx.”

VBA Workbook - Workbook

Assume we write the code in the workbook “04. VBA Workbook.xlsm.”

Step 1: Go to the Visual Basic Editor window from your Excel worksheet by pressing the shortcut key ALT + F11.

Step 2: As soon as we press the shortcut key, it will open the following visual Basic editor window.

VBA Workbook - Step 2

Step 3: To write code, we must insert a module. Select “04. VBA Workbook.xlsm”, go to the Insert tab, and click “Module”.

Step 3.jpg

It will insert a module in the workbook “04. VBA Workbook.xlsm”.

Inserted Module.jpg

Once we insert the module, we see a space on the right side to write our code.

Step 4: Start the sub procedure by naming the macro as follows.

Step 4

Step 5: Inside the macro sub-procedure, start typing the word “Workbook.” We can see the IntelliSense list shows the matching result.

Step 5

Step 6: Choose the “Workbooks” object and open a parenthesis.

VBA Workbook - Step 6

Step 7: Here, enter the workbook name in double quotes along with the file extension of the workbook. In our case, the workbook name and file extension are “Employee Survey.xlsx.”

VBA Workbook - Step 7

Step 8: Close the bracket and enter a dot to see the properties and methods associated with the VBA Workbooks object.

VBA Workbook - Step 8.jpg

Step 9: Since we need to activate the given workbook, choose “Activate” from the IntelliSense list.

VBA Workbook - Step 9

We are done! When we run the code, it will activate the workbook “Employee Survey.xlsx.”

vba Workbook - Step 9 - Employee Survey

Sub VBA_Workbook()
Workbooks(“Employee Survey.xlsx”).Activate
End Sub

Examples of Excel VBA Workbook

Example #1: Enter Values in the Workbook

Using the VBA Workbook Object, we will show you how to insert values into a workbook. Here, we will not insert values in the workbook; instead, we will insert them into cells. The flow will be like this Excel Workbook >>> Excel Worksheet >>> Worksheet Cells.

Let’s explore more on this.

For example, assume we must insert a value in the workbook “Employee Survey.xlsx.” First, we must decide in which worksheet of the workbook we would insert the value and then the range of cells.

Step 1: First, we must activate the workbook we will work with. In this example, activate the workbook “Employee Survey.xlsx” using the VBA Workbooks object.

VBA Workbook - Example 1 - Step 1

Step 2: Next, we must activate the desired worksheet. For this, we can use the VBA Worksheet object. Assume we must activate the worksheet “Sheet1.” Write the code as follows.

VBA Workbook - Example 1 - Step 2

Step 3: After activating the desired worksheet, we must reference the cell where we insert the value. In this case, we will insert a value into cell A1; hence, the VBA workbook range will be cell A1.

Sub VBA_Workbook_Ex1()
Workbooks(“Employee Survey.xlsx”).Activate
Range(“A1”).Value = “Hello VBA Workbook”
End Sub

Let’s run this code, and it should insert the value into the workbook “Employee Survey.xlsx.”

VBA Workbook - Example 1 - Output

Example #2: Assign Workbook to Variable

One of the best coding methods in VBA is mastering and using variables efficiently. The same logic goes with the VBA workbooks. Let us show you how to assign workbook references to variables. 

For example, assume we must activate the workbook “Employee Survey.xlsx” again and again in our code; then, we need to write a long code like the following.

Workbooks(“Employee Survey.xlsx”).Activate

Instead, what we will do is assign the workbook reference to a variable, and use it instead of the workbook name. Let’s follow the steps listed below.

Step 1: Create a new macro sub-procedure.

VBA Workbook - Example 2 - Step 1

Step 2: Inside the sub-procedure, define a variable with the DIM keyword.

VBA Workbook - Example 2 - Step 2

Step 3: We need to assign a VBA Workbook object data type for this variable.

VBA Workbook - Example 2 - Step 3

Step 4: Now, we can assign a workbook reference to the variable. However, we cannot simply assign because the VBA workbook is an object variable, so we must use the “Set” keyword to assign object references to any object variables.

VBA Workbook - Example 2 - Step 4

Step 5: Now, the variable “WB” holds the reference for the workbook “Employee Survey.xlsx.” Instead of using the long workbook name, we simply use the variable name “WB” and still access all the properties and methods of the VBA Workbook object.

To test this, enter a variable name and enter a dot after that.

VBA Workbook - Example 1 - Step 5

As we can see, the moment we use the VBA workbook variable name, it shows all the properties and methods associated with the VBA workbook object.

Using this variable, we can enter values and do many other things related to the VBA workbook.

Step 6: Choose the Activate method.

VBA Workbook - Example 1 - Step 6


Sub VBA_Wrokbook_Ex2()
Dim WB As Workbook
Set WB = Workbooks(“Employee Survey.xlsx”)
End Sub

Once we run this code, it will activate the referenced workbook from any active workbook. The beauty of using variables is that it will allow us to shorten the code without worrying about the wrong typing of the workbook name.

Example #3: For Each Loop for Workbook Object

There are instances where we will work with multiple workbooks. Since we must go through all the workbooks and perform similar tasks, we must use “FOR EACH” loops.

For example, assume we have five workbooks opened, and we must save and close them. We can use the following code.

Sub VBA_Wrokbook_Ex3()
Dim WB As Workbook
For Each WB In Workbooks
WB.Close ‘ vba workbook close
Next WB
End Sub

This code will go through all the open workbooks, save them, and close them simultaneously. However, this code will close all the workbooks, including the active workbook where the VBA code resides.

Hence, we can add some conditions to the code to not close the active workbook. The following code will check for the workbook name, and if it is equal to the current workbook, it will not close it.

Sub VBA_Wrokbook_Ex3()
Dim WB As Workbook
For Each WB In Workbooks
‘If condition will check for the current looping workbook name
‘If the current looping workbook name is not equal to the active workbook then it will save the workbook and close the workbook
If WB.Name <> ActiveWorkbook.Name Then
End If
Next WB
End Sub

Example#4: Save all Workbooks to Specific Location.

If we are working with multiple newly created workbooks and want to save them to one specific location, we can use the following code.

VBA Workbook - Example 4

This code will go through all the open workbooks and checks if the current looping workbook is already saved. If the workbook is not saved already, it will save it to the given folder path or it will show the following in a message box.

A screenshot of a computer error

Description automatically generated

Errors Associated with the Excel VBA Workbook

It is essential to know the errors associated with the VBA workbook object because there will be situations where you must deal with various errors and fix them accordingly.

A screenshot of a computer

Description automatically generated

Run-time error ‘9’: Subscript out of range.

This error usually occurs because of the following reasons:

  • The workbook we are trying to access or activate does not exist, the workbook is not open now, or the given workbook name is wrong due to some spelling mistakes.
  • The file extension should be the same as in the workbook.

Important Things to Note

  • Set is a keyword used to assign object references to object variables.
  • If the referenced workbook name is invalid, we will get the Run-time error ‘9’: Subscript out of range.
  • We will get the Run-time error ‘1004’ if the given folder address to save the workbook is invalid.
  • We cannot open the two workbooks with the same name.
  • VBA Workbook Open is the method to be used to open any specific workbook from a specific location.

Frequently Asked Questions (FAQs)

1. How to enable macros when opening workbook in VBA?

We get the following warning message whenever we open a macro-enabled workbook from a different location.

Click on “Enable Editing” and enable the macros.

2. How do you check if the workbook in VBA is opened by another user?

We must check the Read Only property of the VBA workbook. If it is TRUE, then it is opened by another user.

3. How to close an Excel workbook without saving in VBA?

To close an Excel workbook without saving it, we must use the “Close” method of the workbook.


And set the “SaveChanges” argument to “FALSE.”

WB.Close SaveChanges:= False

4. What is the difference between workbook and workbooks in VBA?

Workbooks: A collection of all the open workbooks in the Microsoft Excel Application.

Workbook: The workbook is a member of all the Workbooks object collection.

Download Template

This article must be helpful to understand the VBA Workbook, with its syntax and examples. You can download the template here to use it instantly.

This has been a guide to VBA Workbook. We learn how to use Workbook object in excel VBA with its syntax, examples & errors. You can learn more from the following articles –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *