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
Table of contents
- 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.
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.”
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.
Range(“A1”).Value = “Hello VBA Workbook”
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.
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.
Dim WB As Workbook
Set WB = Workbooks(“Employee Survey.xlsx”)
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.
Dim WB As Workbook
For Each WB In Workbooks
WB.Close ‘ vba workbook close
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.
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
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)
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.
We must check the Read Only property of the VBA workbook. If it is TRUE, then it is opened by another user.
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
• 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.
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 –