VBA Workbook

Excel VBA Workbook

In excel platform excel file is called as “Workbook”, especially in VBA we never call it a file rather we call it as a “Workbook”. We can call it as an alternative way of referring to the excel file.

What is Workbook in Excel VBA?

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

Within the VBA workbook, we have several other objects like Worksheets, Cells & Ranges, Charts, and Shapes.

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

Work with Workbook in VBA

We can select, activate the workbooks which are opened. Now, look at what is the syntax of VBA Workbook.

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

For example, I have two files opened right now, 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”.

Start the macro by creating a VBA sub procedure.

Code:

Sub Workbook_Example1()

End Sub

Now select the workbook object.

Now enter the workbook that we want to activate.

Code:

Sub Workbook_Example1()

  Workbooks("File 1

End Sub

After entering the workbook name we need to enter the file extension as well. I have saved this VBA workbook as regular workbook i.e. “xlsx” workbook.

Code:

Sub Workbook_Example1()

  Workbooks ("File 1.xlsx")

End Sub

Now we need to decide what we want to do with this workbook, enter dot to see all the options available with this workbook.

Now we need to activate the workbook, select the method as “Activate”.

Code:

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 “Active Workbook”.

Enter Values in the Workbook

As I told as soon as you select the workbook it becomes Active Workbook. Using Active Workbook we can reference the cell.

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.

Code:

Sub Workbook_Example1()

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

End Sub

When you run this code using 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.

Code:

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”

Assign Workbook to Variable

We can also assign the data type as “workbook” to the declared variable. Declare the variable as Workbook.

Code:

Sub Workbook_Example2()

  Dim WB As Workbook

End Sub

Now we need to set the object variable to workbook name by using the word “Set”.

Code:

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.

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

Run this code manually or use shortcut key F5 and see the result as shown in 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 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.

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 second workbook and Workbooks (3) refers 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.

For Each Loop for VBA Workbook Object

As I told in the beginning 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 VBA.

For Each Loop is the loop for all the objects in VBA. Use 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

When you run this code through F5 key or manually then a Pop-up comes which asks to save the workbook. Click on Ok to save.

Use 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

A pop-up window comes before closing the workbook.

Recommended Articles

This has been a guide to VBA Workbook. Here we learn how to use VBA Workbook Object along with practical examples and downloadable excel template. Below you can find some useful excel VBA articles –

• ThisWorkbook in VBA
• Font Color in VBA
• VBA StrComp Function
• CSTR Function in VBA
• Like Operator in VBA
• VBA Boolean
• VBA Right Function
• VBA Long
• Data Types in VBA