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’

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

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

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

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.

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.

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

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.

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

  • VBA Workbook OpenRefresh Pivot Table in VBAPrint Labels From ExcelVBA Worksheets