VBA ThisWorkbook (Current Excel File)

Contents hide

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

WHAT IS VBA IN EXCEL