In VBA, you can use the ThisWorkbook property to refer to the workbook where you are writing the code. When you use it, you get access to all the properties and methods of the current workbook. In simple words, you can execute all the tasks in the current workbook without using the name.
Let’s say you want to count the sheets from the current workbook. The code you need to write would be like the following.
Sub vba_thisworkbook() MsgBox ThisWorkbook.Sheets.Count End Sub
When you run this code shows you a message box with the count of the sheets that you have in the current workbook.
As I said, when you use the ThisWorkbook it gives you access to the properties and method for the current workbook. You can see all of these when you enter a (.) dot to see the complete list.
Helpful Links: Run a Macro – Macro Recorder – Visual Basic Editor – Personal Macro Workbook
Using ThisWorkbook with a Variable
Next, I’ll show you a real-life code example to declare a variable to use the ThisWorkbook property. Let’s say you want to perform multiple activities in the current workbook.
Sub vba_thisworkbook() Dim myWB As Workbook Set myWB = ThisWorkbook With myWB .Activate .Sheets(1).Activate .Range("A1") = Now .Save .Close End With End Sub
In the first part of this code, you have a variable and then assign “ThisWorkbook” property to it. And then in the second part, you have the WITH STATEMENT to perform multiple activities with the current variable.
ActiveWorkBook Vs. ThisWorkbook
Let me explain it with a simple analogy, so, be with me for the next 2 minutes.
You have two workbooks open at the same time (Book1 and Book2). You are writing code in book1 but book2 is active at this point. So when you run that code which refers to the ActiveWorkbook it will perform that activity in Book2.
But when you use the ThisWorkbook VBA always refers to the workbook where code is being written.
Here’s the thing: It’s always preferable to use the ThisWorkbook because it eliminates the chances of any error.
More on VBA Workbooks
VBA Save Workbook | VBA Close Workbook | VBA Delete Workbook | VBA Rename Workbook | VBA Activate Workbook | VBA Combine Workbook | VBA Protect Workbook (Unprotect) | VBA Check IF a Workbook is Open | VBA Open Workbook | VBA Check IF an Excel Workbook Exists in a Folder| VBA Create New Workbook (Excel File)