The first time you discovered the Visual Basic Editor and visualized the VBA project in the Project Explorer, when you first met
Sheet1, document modules were the world: your baby steps in VBA were very likely in the code-behind of
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
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.
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
Document modules can’t be instantiated or destroyed, so there’s no
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.NewSheet, for example. For
Worksheet modules, that’s worksheet-specific events like
Worksheet.Change, that you can use to “hook” custom code when such or such thing happens in the host document.
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)
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.
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?
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
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
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
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
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
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
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
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
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.
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"
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
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
The macro recorder doesn’t declare local variables: instead, it
Selects 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
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
16 thoughts on “Document Modules”
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!
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 😉
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
Member of Excel.Worksheet
Property Cells As Range
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! =)
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.
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:
Unless it’s another hidden default for the default….
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).
“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). […]
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?
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 […]
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?
Thanks for asking, I’ll be honored!
[…] Автор: Mathieu Guindon Источник: Document Modules […]