ThisWorkbook refers to the workbook in which Excel VBA code is being executed. ActiveWorkbook on the other hand refers to the Excel Workbook that current has focus, meaning is the front facing Excel Window.
Often Excel VBA Developers mix these two common types of Workbooks in VBA. The problem starts to arise when you work in multiple Excel Workbooks at the same time and by accident run a macro from a Workbook while another Excel Workbook is currently active. This may have disastrous effects if you use VBA Range or VBA Cell properties directly.
In this article let me explain the distinct difference between the Excel VBA ActiveWorkbook vs ThisWorkbook objects.
ActiveWorkbook
The ActiveWorkbook is not just the visible Workbook as many would think, as you can just as well have multiple Workbooks open and placed side by side. However, only one Excel Workbook is considered Active in any point in time. Being Active is a top-most property – only the Workbook who’s window is currently selected is considered Active. See below for a precise definition
Will return the Workbook object that represents the workbook in the active window (the window on top). The property will return Nothing if the Info window or the Clipboard window is the active window.
Excel VBA ActiveWorkbook example
'Returns the name of the Active Workbook in Excel VBA
Debug.Print ActiveWorkbook.Name
'Get Sheet1 from the Active Workbook
ActiveWorkbook.Sheets("Sheet1")
ThisWorkbook
The ThisWorkbook property is much easier to understand as it simply references the Excel Workbook in which the VBA code is executing. For the full definition see below:
Will return the Workbook object that represents the workbook where the current VBA macro code is running.
Excel VBA ThisWorkbook example
'Returns the name of the Workbook which is executing this Macro
Debug.Print ActiveWorkbook.Name
'Get Sheet1 from the Workbook executing this Macro
ActiveWorkbook.Sheets("Sheet1")
ActiveWorkbook vs ThisWorkbook
If you are still confused about the difference between these two properties see below:
On the left the Excel Workbook is visible and is the foremost application. In this case if you run a macro in this Workbook the ActiveWorkbook property is the same as ThisWorkbook.
On the right the Excel Workbook is not visible or is not the foremost application. In this case if you run a macro in this Workbook the ActiveWorkbook property represents the Workbook on the left, and ThisWorkbook represents the Workbook on the right.
Hope that’s clear now!
Conclusions
Now that you can distinguish between both these properties it is important to keep a couple of things in mind:
- On a daily basis use ThisWorkbook over the more erroneous ActiveWorkbook, when you expect your code to run on the same Workbook you are working on
- Use ActiveWorkbook carefully, as it is a top-most property it might not always return a property if other Excel pop-up windows will be involved. Although you might need the ActiveWorkbook property when working with Excel AddIns (which execute code on the Active Workbook not your AddIn)
