Often Excel / VBA Developers mix two distinguished properties in VBA when wanting to reference their Workbook – the first being the ActiveWorkbook object and the second being ThisWorkbook. In situations when running code on the same workbook you are working you needn’t really worry which reference you use as both will keep pointing at the same Workbook. The problem starts to arise when you work in an environment where there can be multiple Excel Workbooks and you may be executing code in one Workbook and wanting it to run/modify another open Workbook.
In today’s VBA Tip of the Day let’s explain the distinct difference between ActiveWorkbook vs ThisWorkbook.
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.
Using ActiveWorkbook may be risky sometimes see try running the following piece of code to see why:
Private Sub Workbook_Open() MsgBox Iif(Application.ActiveWorkbook Is Nothing, _ "Current Workbook is not Active", _ "Current Workbook is Active") End Sub
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 macro code is running. Always return a Workbook object
ActiveWorkbook vs ThisWorkbook
If you are still confused about the difference between these two properties see below:
Hope that’s clear now!
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)