VBA ThisWorkbook

Updated on January 2, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

Excel VBA ThisWorkbook

VBA ThisWorkbook means the workbook in which we are writing the Excel code. So, for example, if you are working in the workbook named “Sales 2019.xlsx,” we usually refer to the workbook like this.

Workbooks(“Sales 2019.xlsx”).Activate

The code will activate the workbook named “Sales 2019.xlsx”.

Instead of writing like this, we can simply write the VBA codeWrite The VBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more below.

ThisWorkbook.Activate’

Here, ThisWorkbook refers to the workbook where we are writing the code. By referring to this word, we can execute all the tasks in the current workbook and avoid lengthy code with a fully named workbook name.

We are sure you must have also seen the word “Active Workbook” when referring to the other code. It is also one of the most often used words in coding. We will see what the differences between these two words in basic are.

Working with ThisWorkbook in Excel VBA

The reference word “ThisWorkbook” is more reliable than the Workbooks object qualifier. One human tendency is typing the workbook name wrongly, which results in an error message.

One more important reason why VBA ThisWorkbook is more reliable is that just in case we change the workbook name, we need to alter the code because we have used the word “ThisWorkbook.”

VBA-ThisWorkbook

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 ThisWorkbook (wallstreetmojo.com)

So, ThisWorkbook is safer to use for referring to the workbook where we are writing the code.

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

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

Example #1

We will see examples of using the word ThisWorkbook in Excel VBA. The following code will print the workbook name.

Code:

Sub TWB_Example1()

    Dim WBName As String

    WBName = ThisWorkbook.Name

    MsgBox WBName

End Sub
VBA ThisWorkbook Example 1

When you run the code manually or using the F5 key, the above code will show the workbook name in the message box in VBAMessage Box In VBAVBA MsgBox function is an output function which displays the generalized message provided by the developer. This statement has no arguments and the personalized messages in this function are written under the double quotes while for the values the variable reference is provided.read more.

VBA ThisWorkbook Example 1-2

Example #2

Instead of using the word “This Workbook,” we can use variables to set the workbook reference and even reduce the length of the code drastically in VBA. For example, look at the below code first.

Code:

Sub TWB_Example2()

ThisWorkbook.Activate
ThisWorkbook.Worksheets("Sheet1").Activate
ThisWorkbook.Save
ThisWorkbook.Close
ThisWorkbook.SaveAs

End Sub
Wb Example 2

The above code has used “ThisWorkbook” in every line of the code. How hard it is to type the word every time. So, we can minimize this by using variables.

Now, look at the below code with the variable.

Code:

Sub TWB_Example2()

  Dim Wb As Workbook
  
  Set Wb = ThisWorkbook

  Wb.Activate
  Wb.Worksheets("Sheet1").Activate
  Wb.Save
  Wb.Close
  Wb.SaveAs

End Sub
Wb Example 2-1

Looks beautiful, doesn’t it?

Let us explain the code.

First, we have declared the variable as a Workbook object.

Dim Wb As Workbook

Since this is an object variable, we need to set the reference to the particular workbook. So, we have used the “ThisWorkbook” reference.

Set Wb = ThisWorkbook

We reference the variable “Wb” in the workbook where we are currently writing the code. So, as we advance in the procedure, we need not use the word “ThisWorkbook”; instead, we can use the variable “Wb.”

Active Workbook vs. ThisWorkbook in Excel VBA

As we said at the beginning of the article, many coders use the words “Active Workbook” and “ThisWorkbook” very often in their VBA coding. However, it is difficult to understand these two as a reader or a new learner. So, let me explain to you some of the differences.

Difference #1: Meaning

  • Active Workbook: Active Workbook is not necessarily the workbook where we are currently writing the code. If you have multiple opened workbooks and the workbook is visible on your screen is considered an Active Workbook.
  • ThisWorkbook: ThisWorkbook is always the workbook where we are writing the code at the moment.

Difference 2: Error Chances

  • Active Workbook: Using Active in the coding may lead to many errors and confusion because we never know which workbook is active unless we specifically mention the workbook to activate before using the word Active Workbook.
  • ThisWorkbook: ThisWorkbook cannot go wrong because it does not matter which workbook is active. It always takes the reference of the workbook where we are writing the code.

Recommended Articles

This article is a guide to VBA ThisWorkbook. Here, we learn how to use ThisWorkbook as a reference in Excel VBA, with practical examples and a downloadable Excel template. Below you can find some useful Excel VBA articles: –