VBA ThisWorkbook

Excel VBA ThisWorkbook

VBA ThisWorkbook means the workbook in which we are writing the excel code. 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.

I am sure you must have also seen the word “Active Workbook” when you are referring to the others code. This is also one of the 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 of the human tendency is typing the workbook name wrongly, which result in throwing an error message.

One more important reason why VBA ThisWorkbook is more reliable because just in case if 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

Example #1

We will see some of the examples where we can use 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 then, 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 each and 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, isn’t it??

Let me explain the code to you guys.

First, I 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 I have used the “ThisWorkbook” reference.

Set Wb = ThisWorkbook

Now the variable “Wb” is referenced to the workbook where we are writing the code at the moment. From here on going forward 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 I told at the beginning of the article, many coders use the words Active Workbook & ThisWorkbook very often in their VBA coding. As a reader or a new learner, it is not easy to understand these two. 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 writing the code at the moment. If you have multiple opened workbooks and whichever the workbook is visible on your screen is considered as 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 doesn’t matter which workbook is active. It always takes the reference of the workbook where we are writing the code.

Recommended Articles

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

  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>