Document Modules

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 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 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!

16 thoughts on “Document Modules”

  1. 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!

    Like

  2. 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 😉

    Like

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

    Liked by 1 person

    1. 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.

      Like

      1. 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….

        Like

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

        Like

  4. “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.

    Liked by 1 person

  5. 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?

    Like

    1. 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.

      Liked by 1 person

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s