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

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

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

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

When you run the code manually or using the F5 key, the above code will show the workbook name in the message box in VBA.

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

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

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

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.