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

Syntax

Let us look at the syntax of the VBA Workbook. 

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.”

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.

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

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

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 5: Inside the macro sub-procedure, start typing the word “Workbook.” We can see the IntelliSense list shows the matching result.

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

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.”

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

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

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

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.

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.

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
Worksheets(“Sheet1”).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.”

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.

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

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

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.

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.

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.

Code:

Sub VBA_Wrokbook_Ex2()
Dim WB As Workbook
Set WB = Workbooks(“Employee Survey.xlsx”)
WB.Activate
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.Save
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
WB.Save
WB.Close
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.

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.

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.

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)

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.

Recommended Articles

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 –

• VBA Open Workbook
• VBA StrComp
• VBA Do Until Loop