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 as “Sales 2019.xlsx” we usually refer to the workbook like this.
The code will activate the workbook named “Sales 2019.xlsx”.
Instead of writing like this, we can simply write the VBA code below.
Here ThisWorkbook refers to the workbook where we are writing the code. By referring 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”.
So, ThisWorkbook is safer to use for referring to the workbook where we are writing the code.
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.
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 then, the above code will show the workbook name in the message box in VBA.
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 each and 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, isn’t it??
Let me explain the code to you guys.
First I have declared the variable as 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.
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 downloadable excel template. Below you can find some useful excel VBA articles –