WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Workbook

VBA Workbook

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.

Syntax

Now, 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

Use of VBA WorkBook Object Code

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

Example #1

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

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 need to enter the file extension as well. I have saved this workbook as a regular workbook, i.e., “xlsx” workbook.

Code:

Sub Workbook_Example1()

  Workbooks ("File 1.xlsx")

End Sub

VBA Workbook Example 1-3

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.

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

VBA Workbook Example 1-4

Step 6: Now, we need to activate the workbook, 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.

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

VBA Workbook Example 1-6

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

VBA Workbook Example 1-7

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

Example 1-8

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

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

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.

Code:

Sub Workbook_Example2()

  Dim WB As Workbook

End Sub

Example 2

Now we need to set the object variable to the workbook name by 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 and 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 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.

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

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, then a Pop-up comes, which 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

Recommended Articles

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 –

  • ThisWorkbook in VBA
  • CSTR Function in VBA
  • Excel VBA Boolean Operator
  • Data Types in VBA
0 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA Workbook Excel Template

New Year Offer - VBA Training Course (6 courses, 35+ hours video) View More