Excel VBA Workbook

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

Syntax

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.

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

Use of VBA WorkBook Object Code

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

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 must also enter the file extension. We have saved this workbook as a regular workbook, i.e., the “xlsx” workbook.

Sub Workbook_Example1()

Workbooks (“File 1.xlsx”)

End Sub

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.

Step 6: We need to activate the workbook and 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.

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.

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

We must select the cell in the active worksheet 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 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

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.

Sub Workbook_Example2()

  Dim WB As Workbook

End Sub

We need to set the object variable to the workbook name 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. 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 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.

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.

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, a pop-up 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 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: –

  • ThisWorkbook in VBACSTR Function in VBAExcel VBA Boolean OperatorData Types in VBA