VBA Workbook

Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

Excel VBA Workbook

VBA Workbook is an object which is part of the Workbooks object collection. We easily see the difference between the part of object collection and the object itself. The plural word of “Workbooks” refers to many “Workbooks.” In VBA, we have several other objects like Worksheets, Cells & Ranges, Charts, and Shapes.

In the Excel platform, an Excel file is called a “Workbook, “especially in VBA. However, we never call it a file; rather, we call it a “Workbook.

By referring to the workbook, we can do all the related tasks. Some of the important tasks are “Open Workbook,” “Save Workbook,” “Save As Workbook,” and “Close Workbook.” In addition, we can select and activate the open workbooks.

Syntax

Look at what is the syntax of the Workbook.

VBA Workbook syntax

An index is nothing but which workbook you want to select. We can refer to the workbook by workbook number or by workbook name.

VBA Workbook

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA Workbook (wallstreetmojo.com)

–>> If you want to learn Excel VBA professionally, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

Use of VBA WorkBook Object Code

You can download this VBA Workbook Excel Template here – VBA Workbook Excel Template

Example #1

We have two files open right now. The first workbook name is “File 1,” and the second workbook name is “File 2.”

Now, we are writing the code in the third file. But, first, we want to activate the workbook named “File 1” from this file.

Step 1: Start the macro by creating a VBA sub procedureVBA 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.read more.

Code:

Sub Workbook_Example1()

End Sub
VBA Workbook Example 1

Step 2: Now, select the workbook object.

VBA Workbook Example 1-1

Step 3: Now, enter the workbook that we want to activate.

Code:

Sub Workbook_Example1()

  Workbooks("File 1

End Sub
VBA Workbook Example 1-2

Step 4: After entering the workbook name, we must also enter the file extension. We have saved this workbook as a regular workbook, i.e., the “xlsx” workbook.

Code:

Sub Workbook_Example1()

  Workbooks ("File 1.xlsx")

End Sub
VBA Workbook Example 1-3

Step 5: We must decide what we want to do with this workbook. Enter the dot to see all the options available with this workbook.

VBA Workbook Example 1-4

Step 6: We need to activate the workbook and select the method as “Activate.”

Code:

Sub Workbook_Example1()

  Workbooks("File 1.xlsx").Activate

End Sub
VBA Workbook Example 1-4

It doesn’t matter which workbook you are in. It will activate the specified workbook.

When you select the workbook, it becomes an “Active Workbook.

Example #2 – Enter Values in the Workbook

As we told you, as soon as you select the workbook, it becomes an active workbook. Using an 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.read more.

VBA Workbook Example 1-6

In the active workbook, we need to select the sheet by its name or use the word “Active Sheet.”

VBA Workbook Example 1-7

We must select the cell in the active worksheet using the Range object.

Code:

Sub Workbook_Example1()

  Workbooks("File 1.xlsx").Activate
  ActiveWorkbook.ActiveSheet.Range("A1").Value = "Hello"

End Sub
Example 1-8

When you run this code using the F5 key or manually, it will insert the word “Hello” in cell A1 in the workbook “File 1.xlsx.

VBA Workbook Example 1-9

We can also use the below code to do the same job.

Code:

Sub Workbook_Example1()

  Workbooks("File 1.xlsx").ActiveSheet.Range("A1").Value = "Hello"

End Sub

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

Code:

Sub Workbook_Example2()

  Dim WB As Workbook

End Sub

Example 2

We need to set the object variable to the workbook name using the word “Set.”

Code:

Sub Workbook_Example2()

 Dim WB As Workbook

 Set WB = Workbooks("File 1.xlsx")

End Sub
Example 2-1

From now onwards, the variable “WB” holds the name of the workbook “File 1.xlsx”.

Using the variable name, we can insert the words.

Code:

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
VBA Workbook Example 2-2

Run this code manually or use shortcut key F5. See the result, as shown in the below screenshot.

VBA Workbook Example 2-3
WB.Worksheets("Sheet1").Range("A1") = "Hello"

Here, WB is referencing the workbook. In that workbook, we reference 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. For example, look at the below code.

Code:

Sub Workbook_Example3()

Workbooks(1).Activate
Workbooks(2).Activate
Workbooks(3).Activate

End Sub

Workbooks (1) means whichever workbook is first on the list. 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 which workbook is activated. Therefore, it is dangerous to use index numbers.

Example #4 – For Each Loop for Workbook Object

As we said in the beginning, the workbook is a collection object of Workbooks in VBA. Therefore, whenever we want to perform the same 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.read more.

For Each Loop is the loop for all the objects in VBA. Use the below code to save all the opened workbooks.

Code:

Sub Save_All_Workbooks()

    Dim WB As Workbook

   For Each WB In Workbooks
   WB.Save
   Next WB

End Sub
Example 3

When you run this code through the F5 key or manually, a pop-up asks to save the workbook. Click on “OK” to save.

 Example 3-1

Use the below code to close all workbooks except the one you are working on.

Code:

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
Example 3-2

A pop-up window comes before closing the workbook.

Example 3-3

This article is a guide to the VBA Workbook. Here, we learn how to use VBA Workbook Object code, practical examples, and a downloadable Excel template. Below you can find some useful Excel VBA articles: –