There are so many ways to get ahold of a Worksheet
reference: you can dereference it from a Sheets
collection, and even then you need to decide whether that’ll be off Workbook.Sheets
or Workbook.Worksheets
, two properties that both return a Sheets
collection that will contain the worksheet you’re looking for. The Workbook
might be the ActiveWorkbook
, or it could be some object variable that was assigned earlier, with the result of Workbooks.Open
. Or you might like living on the edge, and activate the Window
that has some path/filename as a caption, and then work off the ActiveWorkbook
. Every single one of these cases have a thing in common: the Workbook
involved isn’t necessarily ThisWorkbook
.
ActiveWorkbook vs. ThisWorkbook
In Excel, only one single Workbook
is ever the ActiveWorkbook
at any given point in time. If all workbooks are closed, then ActiveWorkbook
will be Nothing
(add-ins in particular, need to mind this). When a workbook is activated, it fires an Activate
event; if another workbook was active before that, then that workbook fired a Deactivate
event.
The ActiveWorkbook
can change in the middle of a loop that uses a DoEvents
statement to keep Excel responsive, because the user clicked somewhere and that click was allowed to be handled, because Excel remains responsive: if the user can interact with Excel, you can never assume what ActiveWorkbook
is referring to – it can be literally any workbook, or none at all. And after the next instruction it might be something else.
For all these reasons, ActiveWorkbook
and ActiveSheet
are object you will want to capture into a local variable at the beginning of whatever it is that you need to do, and then use that variable and never refer to ActiveSheet
– explicitly or not, for the rest of that procedure. For example instead of this:
Public Sub DoSomething()
ActiveSheet.Range("A1").Value = 42
ActiveSheet.Range("A2").Value = VBA.DateTime.Date
End Sub
You’d do that:
Public Sub DoSomething()
Dim sheet As Worksheet
Set sheet = ActiveSheet
sheet.Range("A1").Value = 42
sheet.Range("A2").Value = VBA.DateTime.Date
End Sub
Of course that’s just an example: if I had to write such a small procedure in real code, I’d skip the local variable and have a With
block withold the object reference for me – note the .
dereferencing operator qualifying the Range
member calls:
Public Sub DoSomething()
With ActiveSheet
.Range("A1").Value = 42
.Range("A2").Value = VBA.DateTIme.Date
End With
End Sub
This would be very, very different:
Public Sub DoSomething()
With ActiveSheet
Range("A1").Value = 42
Range("A2").Value = VBA.DateTIme.Date
End With
End Sub
Note the missing dereferencing .
operator now: the With ActiveSheet
block variable is never actually accessed here. So what sheet is it that these Range
member calls are referring to? If that code is written anywhere other than in some worksheet module, then they’re implicitly referring to ActiveSheet
. If that same identical code is written in some worksheet module (say, Sheet1
), then it refers to that sheet (that’s Me
, aka Sheet1
).
Implicit qualifiers are evil: they strip vital context out of the code, and suddenly you need to do more than just read the code to understand what’s going on. If you’re going to be referring to ActiveSheet
, you might as well be explicit about it.
So what’s ThisWorkbook
then? In a word, it’s the host document: the Excel workbook in which your VBA project is hosted. ThisWorkbook
always refers specifically to this host document, even if your VBA project is an add-in. Maybe it’s the ActiveWorkbook
. Maybe it isn’t.
A very common mistake, is to treat the worksheets of ThisWorkbook
like the worksheets of any other workbook (active or not).
Compile-Time, Run-Time
Another common mistake, is to treat worksheets of ThisWorkbook
that already exist in ThisWorkbook.Worksheets
at compile-time, the same way you’d treat worksheets that only come into existence at run-time.
If the sheet is already in the workbook when your VBA project is in design mode, then at compile-time a project-scope automagic Workbook
variable (constant?) exists, named after the (Name)
property of the module:

By default the code name of the first sheet of an empty workbook, is Sheet1
, same as its Name
property value. When you do this:
Dim sheet As Worksheet
Set sheet = ThisWorkbook.Worksheets("Sheet1")
sheet.Range("A1").Value = 42
You are using this Name
property, …and if a user renames the sheet, the statements suddenly starts raising run-time error 9 subscript out of range.
But if you gave the (Name)
property a nice meaningful identifier name, say SummarySheet
, then you could do this instead:
SummarySheet.Range("A1").Value = 42
SummarySheet
is a programmatic identifier that is much harder to tamper with than the sheet tab’s caption, if you’re the worksheet’s end user.
You can’t use worksheets’ code names to access any other sheets than those that exist in ThisWorkbook
at compile-time, so a really good habit to take early on, is to name things. Leave ThisWorkbook
alone, but name every worksheet module in your project. And then use these names whenever you can: these worksheets are part of your VBA project, they never need to be obtained from a Sheets
collection.
In fact, Set sheet = Sheets("Sheet1")
is at best a missed opportunity, when the “Sheet1” in question exists in ThisWorkbook
. At worst, it’s an outright bug… and that’s the reasoning behind Rubberduck’s sheet accessed using string inspection.
Code name is not reliable if… we do not rename default codename given by VBE itself (e.g. “Sheet1” in english). There are a few SO issues with regional settings e.g. with german or polish version (“Tabelle1”, “Arkusz1” is changing automatically to “Sheet1” just by opening file in english VBA host!). So use codename, but rename it manually in VBE 🙂
LikeLike