The first time you discovered the Visual Basic Editor and visualized the VBA project in the Project Explorer, when you first met ThisWorkbook
and Sheet1
, document modules were the world: your baby steps in VBA were very likely in the code-behind of ThisWorkbook
or Sheet1
– document modules are the gateway to VBA-land.
In the VBIDE Extensibility object model, the modules are accessed via the VBComponents
property of a VBProject
object, and the VBComponent.Type
property has a value of vbext_ComponentType.vbext_ct_Document
(an enum member value) for both ThisWorkbook
and Sheet1
modules, but would be vbext_ct_StdModule
for a standard module like Module1
. Clearly if document modules are class modules, there’s enough “special” about them that they need to be treated differently by the VBA compiler.
Document?
VBA itself is host-agnostic: whether it’s hosted in Excel, PowerPoint, AutoCAD, Sage, Corel Draw, or any other one of over 200 licensed hosts, it’s the same VBA (there’s no such thing as “Excel VBA”, really), and it doesn’t have any intrinsic knowledge of what an Excel.Worksheet
is – only the Excel
library does; when your VBA project is hosted in Excel, then the Excel
type library is automatically added to (and locked; you can’t remove it from) your project. Document modules are a special kind of module that the VBIDE treats as part of the VBA project, but it can’t add or remove them: If you want to add or remove a Worksheet
module from a VBA project, you need to actually add or remove a worksheet from the host Excel workbook.
So, other than we can’t directly add such components to a VBA project without involving the host application’s object model, what’s special about them?
They’re in the VBA project, because the host application decided that’s what’s in a VBA project hosted in – here Excel, but each host application gets to decide whether a project includes a module to represent the host document, and under what circumstances to add or remove other types of modules, like Worksheet
, or Chart
.
Document modules can’t be instantiated or destroyed, so there’s no Initialize
or Terminate
event handler for them. However, you get to create a handler for any or every one of many convenient events that the host application fires at various times and that the authors of the object model deemed relevant to expose as programmatic extensibility points. For ThisWorkbook
, this includes events like Workbook.Open
and Workbook.NewSheet
, for example. For Worksheet
modules, that’s worksheet-specific events like Worksheet.SelectionChange
or Worksheet.Change
, that you can use to “hook” custom code when such or such thing happens in the host document.
ThisWorkbook
Your VBA project is hosted inside an Excel document. In the VBA project, the host document is referred to with an identifier, and that identifier is (by default anyway, and renaming it is not something I’d recommend doing) ThisWorkbook
.
The Excel workbook that contains the current VBA project: that’s ThisWorkbook
. When you’re in the code-behind of that module, you’re extending a Workbook
object: if you type Me.
, the VBE’s IntelliSense/autocomplete list will display all the public members you’d find on any other Workbook
object, plus any Public
(explicitly or not) member. That’s what’s special about a document module: it literally inherits members from another class, as in inheritance – something VBA user code cannot do. Isn’t it fascinating that, under the hood, Visual Basic for Applications apparently has no problem with class inheritance? Something similar happens with UserForm
code: the UserForm1
class inherits the members of any other UserForm
, “for free”. And of course every Sheet1
inherits the members of every other Worksheet
in the world.
So, procedures you write in a document module, should logically be very closely related to that particular document. And because host-agnostic logic can’t add/remove these modules, you’ll want to have as little code as possible in them – and then as a bonus, your VBA project becomes easier to keep under source control, because the code is in modules that VBE add-ins (wink wink) are able to properly import back in and synchronize to & from the file system.
What about ActiveWorkbook
?
ActiveWorkbook
refers to the one and only workbook that is currently active in the Excel Application
instance, which may or may not be ThisWorkbook
/ the host document. It’s easy to confuse the two, and even easier to write code that assumes one is the other: the macro recorder does it, many documentation examples and Stack Overflow answers do it too. But reliable code is code that makes as few assumptions as possible – sooner or later, built-in assumptions are broken, and you’re faced with an apparently intermittent error 1004 that sometimes happens when you’re debugging and stepping through the code, and it happened to a user once or twice but the problem always seemed to vaporize just because you showed up at the user’s cubicle and stood there watching as nothing blew up and everything went fine. *Shrug*, right?
Accessing Worksheets
You shouldn’t be dereferencing worksheets all the time. In fact, you rarely even need to. But when you do, it’s important to do it right, and for the right reasons. The first thing you need to think of, is whether the sheet exists in ThisWorkbook
at compile-time. Meaning, it’s there in the host document, you can modify it in Excel and there’s a document module for it in the VBA project.
That’s the first thing you need to think about, because if the answer to that is “yep, it’s right there and it’s called Sheet3
“, then you already have your Worksheet
object and there’s no need to dereference it from any Sheets
or Worksheets
collection!
Dim sheet As Worksheet
Set sheet = ThisWorkbook.Worksheets("Sheet1") '<~ bad if Sheet1 exists at compile-time!
Set sheet = Sheet1 '<~ bad because redundant: the variable obfuscates the target!
sheet.Range("A1").Value = 42 '<~ bad if sheet is a local variable, but good if a parameter
Sheet1.Range("A1").Value = 42 '<~ most reliable way to refer to a worksheet
The magic Sheet1
identifier comes from the (Name)
property of the Sheet1
document module under ThisWorkbook
in the VBA project: set that property to a valid and meaningful name for that specific worksheet, and you have a user-proof way to refer to your ConfigurationSheet
, the SummarySheet
, and that DataSheet
. If the user decides to rename the DataSheet
to “Data (OLD)” for some reason, this code is now broken:
ThisWorkbook.Worksheets("Data").Range("A1").Value = 42
Meanwhile this code will survive any user-induced sheet-name tampering:
DataSheet.Range("A1").Value = 42
Wait, is it .Sheets() or .Worksheets()?
The first thing to note, is that they aren’t language-level keywords, but member calls. If you don’t qualify them, then in the ThisWorkbook
module you’ll be referring to Me.Worksheets
(i.e. ThisWorkbook.Worksheets
), and anywhere else in the VBA project that same code be implicitly referring to ActiveWorkbook.Worksheets
: that’s why it’s important to properly qualify member calls. Worksheets
is a member of a Workbook
object, so you explicitly qualify it with a Workbook
object.
Now, Sheets
and Worksheets
both return an Excel.Sheets
collection object, whose default member Item
returns an Object
. Both are happy to take a string with a sheet name, or an integer with a sheet index. Both will be unhappy (enough to raise runtime error 9 “subscript out of range”) with an argument that refers to a sheet that’s not in the (implicitly or not) qualifying workbook object. Both will return a Sheets
collection object if you give it an array of sheet names: that’s one reason why the Item
member returns an Object
and not a Worksheet
. Another reason is that sometimes a sheet is a Chart
, not a Worksheet
.
Use the Worksheets
collection to retrieve Worksheet
items; the Sheets
collection contains all sheets in the qualifying workbook, regardless of the type, so use it e.g. to retrieve the Chart
object for a chart sheet. Both are equivalent, but Worksheets
is semantically more specific and should be preferred over Sheets
for the common Worksheet
-dereferencing scenarios.
Dereferencing Workbooks
If you only need to work with ThisWorkbook
, then you don’t need to worry about any of this. But as soon as your code starts opening other workbooks and manipulating sheets that are in these other workbooks, you need to either go nuts over what workbook is currently the ActiveWorkbook
as you Activate
workbooks and repeatedly go Workbooks("foo.xlsm").Activate
…. or properly keep a reference to the objects you’re dealing with.
When you open another workbook with Application.Workbooks.Open
, that Open
is a function, a member of the Excel.Workbooks
class that returns a Workbook
object reference if it successfully opens the file.
Workbooks.Open
is also side-effecting: successfully opening a workbook makes that workbook the new ActiveWorkbook
, and so global state is affected by its execution.
When you then go and work off ActiveWorkbook
or unqualified Worksheets(...)
member calls, you are writing code that is heavily reliant on the side effects of a function, and global state in general.
The right thing to do, is to capture the function’s return value, and store the object reference in a local variable:
Dim book As Workbook
Set book = Application.Workbooks.Open(path) '<~ global-scope side effects are irrelevant!
Dim dataSheet As Worksheet
Set dataSheet = book.Worksheets("DATA")
If a workbook was opened by your VBA code, then your VBA code has no reason to not have a Workbook
reference to that object.
So when is ActiveWorkbook useful then?
As an argument to a procedure that takes a Workbook
parameter because it doesn’t care what workbook it’s working with, or when you need to assign a Workbook
object variable (presumably a WithEvents
module-scope private variable in some class module) to whatever workbook is currently active. With few specific exceptions, that’s all.
ActiveWorkbook.Whatever
is just not code that you normally want to see anywhere.
Cha-cha-cha-Chaining Calls
Strive to keep the VBA compiler aware of everything that’s going on. Moreover if all the code you write is early-bound, then Rubberduck understands it as completely as it can. But implicit late binding is alas too easy to introduce, and the primary cause for it is chained member calls:
book.Worksheets("Sheet1").Range("A1").Value = 42 '<~ late bound at .Range
Everything after the Worksheets("Sheet1")
call is late-bound, because as described above, Excel.Sheets.Item
returns an Object
, and member calls against Object
can only ever be resolved at run-time.
By introducing a Worksheet
variable to collect the Object
, we cast it to a usable compile-time interface (that’s Worksheet
), and now the VBA compiler can resume validating the .Range
member call:
Dim sheet As Worksheet
Set sheet = book.Worksheets("Sheet1")
sheet.Range("A1").Value = 42 '<~ all early bound
Chained early-bound member calls are fine: the compiler will be able to validate the Range.Value
member call, because the Excel.Worksheet.Range
property getter returns a Range
reference. If it returned Object
, we would have to declare a Range
local variable to capture the Excel.Range
object we want to work with, like this:
Dim sheet As Worksheet
Set sheet = book.Worksheets("Sheet1") '<~ good: casts Object into Worksheet.
Dim cell As Range
Set cell = sheet.Range("A1") '<~ redundant: sheet.Range("A1") returns an early-bound Range.
cell.Value = 42 '<~ early-bound, but sheet.Range("A1").Value = 42 would also be early-bound.
Avoid declaring extraneous variables, but never hesitate to use a local variable to turn an Object
into a compile-time type that gives you IntelliSense, autocompletion, and parameter quick-info: you’ll avoid accidentally running into run-time error 438 for typos Option Explicit
can’t save you from. Using the compiler to validate everything it can validate, is a very good idea.
If you need to repeatedly invoke members off an early-bound object, introducing a local variable reduces the dereferencing and helps make the code feel less repetitive:
sheet.Range("A1").Value = "Rubberduck"
sheet.Range("A1").Font.Bold = True
sheet.Range("A1").Font.Size = 72
sheet.Range("A1").Font.Name = "Showcard Gothic"
By introducing a local variable, we reduce the cognitive load and no longer repeatedly dereference the same identical Range
object pointer every time:
Dim cell As Range
Set cell = sheet.Range("A1")
cell.Value = "Rubberduck"
cell.Font.Bold = True
cell.Font.Size = 72
cell.Font.Name = "Showcard Gothic"
Arguably, nested With
blocks could hold all the object references involved, and reduces the dereferencing to a strict minimum (.Font
is only invoked once, and the reference is witheld), but it’s very debatable whether it enhances or hurts readability:
With sheet.Range("A1")
.Value = "Rubberduck"
With .Font
.Bold = True
.Size = 72
.Name = "Showcard Gothic"
End With
End With
Avoiding nested With
blocks seems a fair compromise:
Dim cell As Range
Set cell = sheet.Range("A1")
cell.Value = "Rubberduck"
With cell.Font
.Bold = True
.Size = 72
.Name = "Showcard Gothic"
End With
All this is fairly subjective, of course, and really applies to everything you ever write in VBA (not just when coding against the Excel object model), but any of it is better than this (very cleaned-up) macro-recorder code:
Range("A1").Select
ActiveCell.Value = "Rubberduck"
With Selection.Font
.Name = "Showcard Gothic"
.Bold = True
.Size = 72
End With
We note the implicit ActiveSheet
reference with the implicitly-qualified Range
member call; we note the use of Range.Select
followed by a use of ActiveCell
; we note a With
block holding a late-bound reference to Range.Font
through the Selection
object, and the compiler gets to validate absolutely nothing inside that With
block.
The macro recorder doesn’t declare local variables: instead, it Select
s things and then works late-bound against the Selection
. That’s why it’s a bad teacher: while it’s very useful to show us what members to use to accomplish something, it does everything without leveraging any compile-time checks, and teaches to Activate
the sheet you want to work with so that your unqualified Range
and Cells
member calls can work off the correct sheet… but now you know why, how, and when to dereference a Worksheet
object into a local variable, you don’t need any Select
and Activate
!
Thank you Mathieu! I’m reading Thomas Hansen’s articles on hyperlambda Active Events. (MSDN Mag). What is your opinion? Ultimately, I am looking for the future of VBA. You must think VBA really has a future to put this much work into it. I don’t understand — I hear nothing from MS except office-js, which itself does not have its future spelled out to be as good as VBA! And then O365 vs the Enterprise Office I use thru my gov job. MS is _so_ confusing!
LikeLike
Hey Kevin, I haven’t read Thomas’ article, but I think Office-JS is a very nice way to bring scripting & automation to web/SharePoint-based Office, and it very likely has a bright future: everything being asynchronous is awesome and it’s really a whole new world of possibilities… but nothing can replace VBA on desktop for the foreseeable future. As long as it works, people are going to be learning and using VBA – I think a lot of the bad code and general bad vibe VBA gets, is because despite all the available resources after all these years, many VBA professionals don’t really grasp the fundamentals in the same way someone with a formal programming background might, and this blog and Rubberduck’s static code analysis are there to, I hope, help bridge that gap. Office-JS code feels like it would be written by your IT department, while VBA code would be written by… your “Shadow IT” department 😉
LikeLike
Thanks Matt, another great insight.
Could you explain why sheet.Range(“A1”) give you IntelliSense and all that nice stuff, but sheet.Cells(1,1) leave you with an empty feeling of loneliness and isolation?
The documentation indicate that they should both return a Range object:
Property Range(Cell1, [Cell2]) As Range
read-only
Member of Excel.Worksheet
Property Cells As Range
read-only
Member of Excel.Worksheet
(I think I might have just figured it out looking at what I just pasted and the optional parameter but I would like your take on it to confirm).
LikeLiked by 1 person
Bingo: Cells returns a Range, and it’s that Range that takes the arguments – courtesy of implicit default member calls! =)
LikeLike
The missing link is the hidden Range.[_Default] property: that’s what’s taking the arguments (the Range class, not the Range property 😉 …I don’t think the hidden members are documented, but you can make them visible via the object browser’s context menu.
LikeLike
TIL: There’s a “Show Hidden Members” option in the Object Browser’s context menu.
You really are a fountain of knowledge.
LikeLiked by 1 person
And while I am drinking from that fountain, I might as well fill up my water bottle:
Property _Default([RowIndex], [ColumnIndex])
Default member of Excel.Range
How do you find out what the return type is? Because surely there must be one right? Otherwise how else can you do this:
sheet.cells(1, 1).value
OR
sheet.cells.[_Default](1, 1).value
Unless it’s another hidden default for the default….
LikeLike
If it doesn’t say an explicit return type, it’s safe to assume it returns a Variant. The actual runtime type returned by Range._Default depends on what arguments it’s given and how many cells are in that range: there’s literally no way to find out at compile-time. Without any arguments, you get Range.Value (wrapped in a variant); if that’s 2+ cells, the variant holds a 2D array; otherwise, the variant holds the cell’s value.
So Debug.Print sheet.Cells(1, 1), fully spelled out, is sheet.Cells._Default(1, 1)._Default… of course not all implicit default member calls are worth making explicit, but rule of thumb you will want to have as much code as possible all early-bound (so as much as possible, no member calls against Object and/or Variant).
LikeLike
“VBA itself is host-agnostic: whether it’s hosted in Excel, PowerPoint, AutoCAD, Sage, Corel Draw, or any other one of over 200 licensed hosts, it’s the same VBA (there’s no such thing as “Excel VBA”, really),” — If we put this on a marquee plane in 20-foot high letters, do you think the message will sink in?
RE: Sheets vs Worksheets — It’s not just worksheets. The items in Sheets can be one of three types: Worksheet, Chart, DialogSheet. Workbook exposes the Sheets collection which returns all of them and allows getting any of them by index or name. It also has Worksheets, Charts, and DialogSheets properties, for each specific type.
LikeLiked by 1 person
[…] if you have userforms they’re all under a “Forms” folder, and then the document modules are all lumped under some “Microsoft Excel Objects” folder (in an Excel host, anyway). […]
LikeLike
Hello Mathieu, thank you for bringing me insight in chaining calls. Keep writing these articles! A benefit I see in Office-JS is updating a code base along multiple users. Right now if someone makes a copy of my workbook and I fix a bug the update process involves manual work copying code in and out. Do you already use Office-JS and do you create worksheets that are used along multiple users?
LikeLike
Hi, thanks for the feedback!
Office-JS has *plenty* of benefits! Unfortunately it’s only on O365 and in *many* environments, Office-JS is simply not an option. I work in one such environment: up until a few months ago my Win7 work laptop was still running Excel 2010!
TBH I find the single-document paradigm of Excel Online & Office-JS is, for me at least, essentially a showstopper (happy to be wrong here): while there definitely is space for automation, and asynchronous *everything* is awesome, many automation scenarios involve opening and manipulating multiple workbooks, and while scripted solutions are certainly (probably?) possible, VBA remains a simple, proven way to do this without involving an IT department.
The guiding principles are the same regardless of whether you’re writing TypeScript or VBA though: you’ll want small, specialized functions with as few side-effects as possible, compiler assistance everywhere (i.e. member calls made against typed objects), with meaningful identifier names, comments that say “why” (with the code itself saying “what”), etc.
Once upon a time I was a merchandise planner, and our team used pretty complex worksheets to calculate Open-to-Buy budgets and plan & forecast gross margins. The VBA code I wrote for that (gosh I’d cry if I saw that code today!) pulled data from an Access database to update the weekly actual sales, and more code actually generated these worksheets. The workbooks themselves didn’t have any macros, but I would regularly update an Excel add-in (back then add-ins had to be local, but today I’d just deploy it to a network share), and the entire team (5-6 heads) was always up-to-date, and I can’t say I ran into any annoying issues with that workflow.
On the other hand, more recently I had to make an “order form” that should have been a web application, but the sales team wanted their good old familiar worksheets, so I built it… and I have to say that keeping everyone on the same version was a (predictable, …and predicted, too) nightmare and had we been on O365 I would definitely have used Office-JS for that distributed scenario.
I need to play with it some more, but I’m “the IT guy” now, and the way I see Office-JS is it makes a solid attempt at curbing “shadow IT” by eliminating VBA from the equation and taking scripts under the umbrella of IT (or at least makes the code more visible to IT), …but I don’t see it replacing VBA anytime soon: devs in IT won’t write Office-JS code for something that should be a web application. Because the truth is, a lot of what we do in Excel, we do in Excel just because that’s a tool we know and love: it doesn’t mean it’s a rational thing to do.
LikeLiked by 1 person
[…] can’t be added to the host project by the VBE (the host application owns these modules: see this article): for this reason you will want to minimize the amount of code you have in modules like […]
LikeLike
Hello Mathieu!
I’d like to translate Russian your great posts on VBA with links to original posts and pointing to your authorship.
Would you give your permission?
LikeLike
Thanks for asking, I’ll be honored!
LikeLike
[…] Автор: Mathieu Guindon Источник: Document Modules […]
LikeLike