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.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
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
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,
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
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.
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).
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
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.