Excel VBA Workbook
VBA Workbook is an object which is part of the Workbooks object collection. We easily see the difference of part of object collection and object itself, plural word of “Workbooks” refers it has many “Workbook”. In VBA, we have several other objects like Worksheets, Cells & Ranges, Charts, and Shapes.
In the excel platform excel file is called as “Workbook,” especially in VBA. We never call it a file; rather, we call it a “Workbook.”
By referring to the workbook, we can do all the tasks related to it. Some of the important tasks are “Open Workbook,” “Save Workbook,” “Save As Workbook,” and “Close Workbook.” We can select, activate the workbooks which are opened.
Now, look at what is the syntax of the Workbook.
An index is nothing but which workbook you want to select. We can refer to the workbook by workbook number or by workbook name.
Use of VBA WorkBook Object Code
For example, I have two files open right now. The first workbook name is “File 1” and the second workbook name is “File 2”.
Now I am writing the code in the third file. From this file, I want to activate the workbook named as “File 1”.
Step 1: Start the macro by creating a VBA subprocedureVBA SubprocedureSUB in VBA is a procedure which contains all the code which automatically gives the statement of end sub and the middle portion is used for coding. Sub statement can be both public and private and the name of the subprocedure is mandatory in VBA..
Sub Workbook_Example1() End Sub
Step 2: Now select the workbook object.
Step 3: Now, enter the workbook that we want to activate.
Sub Workbook_Example1() Workbooks("File 1 End Sub
Step 4: After entering the workbook name, we need to enter the file extension as well. I have saved this workbook as a regular workbook, i.e., “xlsx” workbook.
Sub Workbook_Example1() Workbooks ("File 1.xlsx") End Sub
Step 5: Now, we need to decide what we want to do with this workbook. Enter dot to see all the options available with this workbook.
Step 6: Now, we need to activate the workbook, select the method as “Activate.”
Sub Workbook_Example1() Workbooks("File 1.xlsx").Activate End Sub
It doesn’t matter which workbook you are in. It will activate the specified workbook.
As soon as you select the workbook, it becomes an “Active Workbook.”
Example #2 – Enter Values in the Workbook
As I told as soon as you select the workbook, it becomes an Active Workbook. Using Active Workbook, we can reference the cellReference The CellCell reference in excel is referring the other cells to a cell to use its values or properties. For instance, if we have data in cell A2 and want to use that in cell A1, use =A2 in cell A1, and this will copy the A2 value in A1..
In the active workbook, we need to select the sheet by its name, or else we use the word Active Sheet.
In the active worksheet, we need to select the cell by using the Range object.
Sub Workbook_Example1() Workbooks("File 1.xlsx").Activate ActiveWorkbook.ActiveSheet.Range("A1").Value = "Hello" End Sub
When you run this code using the F5 key or manually, it will insert the word “Hello” in the cell A1 in the workbook “File 1.xlsx”.
We can also use the below code to do the same job.
Sub Workbook_Example1() Workbooks("File 1.xlsx").ActiveSheet.Range("A1").Value = "Hello" End Sub
This will also insert the word “Hello” to the workbook “File 1.xlsx.”
Example #3 – Assign Workbook to Variable
We can also assign the data type as a “workbook” to the declared variable. Declare the variable as Workbook.
Dim WB As Workbook
Now we need to set the object variable to the workbook name by using the word “Set.”
Sub Workbook_Example2() Dim WB As Workbook Set WB = Workbooks("File 1.xlsx") End Sub
From now onwards, the variable “WB” holds the name of the workbook “File 1.xlsx”.
Using the variable name, we can insert the words.
Sub Workbook_Example2() Dim WB As Workbook Set WB = Workbooks("File 1.xlsx") WB.Worksheets("Sheet1").Range("A1") = "Hello" WB.Worksheets("Sheet1").Range("B1") = "Good" WB.Worksheets("Sheet1").Range("C1") = "Morning" End Sub
Run this code manually or use shortcut key F5 and see the result, as shown in the below screenshot.
WB.Worksheets("Sheet1").Range("A1") = "Hello"
Here WB is referencing the workbook, in that workbook, we are referencing the worksheet Sheet1 by using the Worksheets object. In that worksheet cell, A1 is equal to the value of “Hello.”
We can also reference the workbook by index number as well. For example, look at the below code.
Sub Workbook_Example3() Workbooks(1).Activate Workbooks(2).Activate Workbooks(3).Activate End Sub
Here Workbooks (1) means whichever the workbook first on the list, like this similarly Workbooks (2) refers to the second workbook, and Workbooks (3) refers to the third workbook.
The main problem with this index number referencing is we don’t know exactly which workbook activated. It is dangerous to use index numbers.
Example #4 – For Each Loop for Workbook Object
As I told in the beginning, the workbook is a collection object of Workbooks in VBA. Whenever we want to perform the same kind of activity for all the opened workbooks, we need to use For Each loop in VBAFor Each Loop In VBAVBA For Each Loop helps the user to inspect and analyze the groups of objects or values individually. It even facilitates performing the specific activity for every object or value by passing a statement or group of statements in this reference..
For Each Loop is the loop for all the objects in VBA. Use the below code to save all the opened workbooks.
Sub Save_All_Workbooks() Dim WB As Workbook For Each WB In Workbooks WB.Save Next WB End Sub
When you run this code through the F5 key or manually, then a Pop-up comes, which asks to save the workbook. Click on Ok to save.
Use the below code to close all workbooks except the one you are working on.
Sub Close_All_Workbooks() Dim WB As Workbook For Each WB In Workbooks If WB.Name <> ThisWorkbook.Name Then WB.Close End If Next WB End Sub
A pop-up window comes before closing the workbook.
This has been a guide to VBA Workbook. Here we learn how to use VBA Workbook Object code along with practical examples and a downloadable excel template. Below you can find some useful excel VBA articles –