ThisWorkbook in VBA – Examples

You must know about an Active Workbook in Excel. In this blog, we will learn about some properties and methods for ThisWorkbook in VBA and the difference between ThisWorkbooka and ActiveWorkbook.

So let us begin learning.

What is ThisWorkbook in Excel?

ThisWorkbook refers to the workbook in which we are writing the VBA code. We do not need to remember the actual name of the workbook in which we write code. Instead, we can use ThisWorkbook. It reduces the chances of any mistake in writing the real workbook name.

We can refer to a workbook using its name by the following method.

Workbooks("<<workbook_real_name>").Name

We do not need to think about the name of the workbook when we use ThisWorkbook in which we are writing the code.

ThisWorkbook.Activate

This approach is also useful when we change the name of our workbook. In this case, we would not need to change the name of the workbook in the VBA code. All thanks to ThisWorkbook.

Difference Between ThisWorkbook and ActiveWorkbook

People are often confused between ThisWorkbook and ActiveWorkbook in VBA. ThisWorkbook is the workbook in which we are writing the VBA code currently or the Workbook containing the Current Sub Procedure which we are writing.

On the other hand, ActiveWorkbook refers to the Workbook which is active at the moment. There can be multiple workbooks opened at a time but there is only one Active Workbook in which we would work.

Let us say we have opened two workbooks opened as follows. The name of the two workbooks is Book1 and Book2.

The Cursor is in Book1 which makes it editable and Active Workbook. Now Press Alt and F11 keys to open the VBE.

• Select the VBA Project for Book2 (And not Book1) and Go to Insert Tab. Insert a New Module in Book2.
• Add the following code to the Module1 of Book2.

Sub Example()
'differentiating between ThisWorkbook and ActiveWorkbook
'two strings to store workbook name
Dim This As String
Dim Active As String
'storing the name of ThisWorkbook
This = ThisWorkbook.Name
'storing the name of ActiveWorkbook
Active = ActiveWorkbook.Name
'printing the name of Active and This
MsgBox "ThisWorkbook:= " & This
MsgBox "ActiveWorkbook:= " & Active
End Sub

• Put your cursor in the VBA code in Module1 of Book2 and press F8 key multiple times to run this Sub Procedure line by line.

The Name Property allows us to access the name of ThisWorkbook in which we are writing the code.

Example to Use ThisWorkbook

We would now understand with the help of example.

In this example, we would learn to activate ThisWorkbook, Save and then close it.

To do this, we will use the following methods of ThisWorkbook.

• ThisWorkbook.Activate – This would activate the workbook in which we are writing the code.
• ThisWorkbook.Save – This would save the workbook.
• ThisWorkbook.Close – This would close the workbook.

Now open the VBE and place the following code in a new module.

Sub Activate_Save_Close()
'activate This Workbook
ThisWorkbook.Activate
'save This Workbook
MsgBox "We are about to save the workbook", vbInformation
ThisWorkbook.Save
'close This Workbook
MsgBox "We are about to close the Workbook", vbCritical
ThisWorkbook.Close
End Sub

Run this Sub procedure to first activate and then Save As (Macro Enabled File) and close ThisWorkbook along with the Message box on the screen.

Note that this is not saving the workbook as a macro enabled workbook. This macro will be lost when you reopen the workbook.

This brings us to end.

Thank you for reading.