In VBA, you can use 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 into 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.
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 Tutorials on VBA Workbooks
- How to Create a New Workbook using VBA
- How to Save a Workbook using a VBA
- How to Close a Workbook using a VBA
- How to Delete a Workbook using VBA
- How to Rename a Workbook using VBA
- How to Activate a Workbook using VBA
- How to Combine Workbooks using VBA
- How to Protect\Unprotect a Workbook using VBA in Excel
- How to Check IF a Workbook is OPEN using VBA in Excel
- How to Open a Workbook in Excel using VBA
- How to Check IF an Excel Workbook Exists in a Folder