Rubberduck.Fakes Gets an Upgrade

One of the objectively coolest features in Rubberduck is the Fakes API. Code that pops a MsgBox for example, needs a way to work without actually popping that message box, otherwise that code cannot be unit tested… without somehow hijacking the MsgBox function. The Fakes API does exactly that: it hooks into the VBA runtime, intercepts specific internal function calls, and makes it return exactly what your test setup …set up.

This API can stop time, or Now can be told to return 1:59AM on first invocation, 1:00AM on the next, and then we can test and assert that some time-sensitive logic survives a daylight savings time toggle, or how Timer-dependent code behaves at midnight.

Let’s take a look at the members of the IFakesProvider interface.

Fakes Provider

Fakes for many of the internal VBA standard library functions exist since the initial release of the feature, although some providers wouldn’t always play nicely together – thanks to a recent pull request from @tommy9 these issues have been resolved, and a merry bunch of additional implementations are now available in pre-release builds:

NameDescriptionParameter names
MsgBoxConfigures VBA.Interaction.MsgBox callsFakes.Params.MsgBox
InputBoxConfigures VBA.Interaction.InputBox callsFakes.Params.InputBox
BeepConfigures VBA.Interaction.Beep calls
EnvironConfigures VBA.Interaction.Environ callsFakes.Params.Environ
TimerConfigures VBA.DateTime.Timer calls
DoEventsConfigures VBA.Interaction.DoEvents calls
ShellConfigures VBA.Interaction.Shell callsFakes.Params.Shell
SendKeysConfigures VBA.Interaction.SendKeys callsFakes.Params.SendKeys
KillConfigures VBA.FileSystem.Kill callsFakes.Params.Kill
MkDirConfigures VBA.FileSystem.MkDir callsFakes.Params.MkDir
RmDirConfigures VBA.FileSystem.RmDir callsFakes.Params.RmDir
ChDirConfigures VBA.FileSystem.ChDir callsFakes.Params.ChDir
ChDriveConfigures VBA.FileSystem.ChDrive callsFakes.Params.ChDrive
CurDirConfigures VBA.FileSystem.CurDir callsFakes.Params.CurDir
NowConfigures VBA.DateTime.Now calls
TimeConfigures VBA.DateTime.Time calls
DateConfigures VBA.DateTime.Date calls
Rnd*Configures VBA.Math.Rnd callsFakes.Params.Rnd
DeleteSetting*Configures VBA.Interaction.DeleteSetting callsFakes.Params.DeleteSetting
SaveSetting*Configures VBA.Interaction.SaveSetting callsFakes.Params.SaveSetting
Randomize*Configures VBA.Math.Randomize callsFakes.Params.Randomize
GetAllSettings*Configures VBA.Interaction.GetAllSettings calls
SetAttr*Configures VBA.FileSystem.SetAttr callsFakes.Params.SetAttr
GetAttr*Configures VBA.FileSystem.GetAttr callsFakes.Params.GetAttr
FileLen*Configures VBA.FileSystem.FileLen callsFakes.Params.FileLen
FileDateTime*Configures VBA.FileSystem.FileDateTime callsFakes.Params.FileDateTime
FreeFile*Configures VBA.FileSystem.FreeFile callsFakes.Params.FreeFile
IMEStatus*Configures VBA.Information.IMEStatus calls
Dir*Configures VBA.FileSystem.Dir callsFakes.Params.Dir
FileCopy*Configures VBA.FileSystem.FileCopy callsFakes.Params.FileCopy
*Members marked with an asterisk are only available in pre-release builds for now.

Parameter Names

The IVerify.ParameterXyz members make a unit test fail if the specified parameter wasn’t given a specified value, but the parameter names must be passed as strings. This is a UX issue: the API essentially requires hard-coded magic string literals in its users’ code; this is obviously error-prone and feels a bit arcane to use. The IFakesProvider interface has been given a Params property that gets an instance of a class that exposes the parameter names for each of the IFake implementations, as shown in the list above, and the screenshot below:

Picking the correct parameter name from a drop-down completion list beats risking a typo, doesn’t it?

Note: the PR for this feature has not yet been merged at the time of this writing.

Testing Without Fakes (aka Testing with Stubs)

Unit tests have a 3-part structure: first we arrange the test, then we act by invoking the method we want to test; lastly, we assert that an actual result matches the expectations. When using fakes, we configure them in the arrange part of the test, and in the assert part we can verify whether (and/or how many times) a particular method was invoked with a particular parameterization.

Let’s say we had a procedure we wanted to write some tests for:

Public Sub TestMe()
    If MsgBox("Print random number?", vbYesNo + vbQuestion, "Test") = vbYes Then
        Debug.Print Now & vbTab & Rnd * 42
        Debug.Print Now
    End If
End Sub

If we wanted to make this logic fully testable without the Fakes API, we would need to inject (likely as parameters) abstractions for MsgBox, Now, and Debug dependencies: instead of invoking MsgBox directly, the procedure would be invoking the Prompt method of an interface/class that wraps the MsgBox functionality. Unit tests would need a stub implementation of that interface in order to allow some level of configuration setup – an invocation counter, for example. A fully testable version of the above code might then look like this:

Public Sub TestMe(ByVal MessageBox As IMsgBox, ByVal Random As IRnd, ByVal DateTime As IDateTime, ByVal Logger As ILogger)
    If MessageBox.Prompt("Print random number?", "Test") = vbYes Then
        Logger.LogDebug DateTime.Now & vbTab & Random.Next * 42
        Logger.LogDebug DateTime.Now
    End If
End Sub

The method is testable, because the caller controls all the dependencies. We’re probably injecting an IMsgBox that pops a MsgBox, an IRnd that wraps Rnd, a DateTime parameter that returns VBA.DateTime.Now and an ILogger that writes to the debug pane, but we don’t know any of that. I fact, we could very well run this method with an ILogger that writes to some log file or even to a database; the IRnd implementation could consistently be returning 0.4 on every call, IDateTime.Now could return Now adjusted to UTC, and IMsgBox might actually display a fancy custom modal UserForm dialog – either way, TestMe doesn’t need to change for any of that to happen: it does what it needs to do, in this case fetching the next random number and outputting it along with the current date/time if a user prompt is answered with a “Yes”, otherwise just output the current date/time. It’s the interfaces that provide the abstraction that’s necessary to decouple the dependencies from the logic we want to test. We could implement these interfaces with stubs that simply count the number of times each member is invoked, and the logic we’re testing would still hold.

We could then write tests that validate the conditional logic:

Public Sub TestMe_WhenPromptYes_GetsNextRandomValue()
    ' Arrange
    Dim MsgBoxStub As StubMsgBox ' implements IMsgBox, but we want the stub functionality here
    Set MsgBoxStub = New StubMsgBox
    MsgBoxStub.Returns vbYes
    Dim RndStub As StubRnd ' implements IRnd, but we want the stub functionality here too
    Set RndStub = New StubRnd
    ' Act
    Module1.TestMe MsgBoxStub, RndStub, New DateTimeStub, New LoggerStub
    ' Assert
    Assert.Equals 1, RndStub.InvokeCount
End Sub
Public Sub TestMe_WhenPromptNo_DoesNotGetNextRandomValue()
    ' Arrange
    Dim MsgBoxStub As StubMsgBox
    Set MsgBoxStub = New StubMsgBox
    MsgBoxStub.Returns vbNo
    Dim RndStub As StubRnd
    Set RndStub = New StubRnd
    ' Act
    Module1.TestMe MsgBoxStub, RndStub, New DateTimeStub, New LoggerStub
    ' Assert
    Assert.Equals 0, RndStub.InvokeCount
End Sub

These stub implementations are class modules that need to be written to support such tests. StubMsgBox would implement IMsgBox and expose a public Returns method to configure its return value; StubRnd would implement IRnd and expose a public InvokeCount property that returns the number of times the IRnd.Next method was called. In other words, it’s quite a bit of boilerplate code that we’d usually rather not need to write.

Let’s see how using the Fakes API changes that.

Using Rubberduck.FakesProvider

The standard test module template defines Assert and Fakes private fields. When early-bound (needs a reference to the Rubberduck type library), the declarations and initialization look like this:

Option Explicit
Option Private Module
Private Assert As Rubberduck.AssertClass
Private Fakes As Rubberduck.FakesProvider
Public Sub ModuleInitialize()
    Set Assert = CreateObject("Rubberduck.AssertClass")
    Set Fakes = CreateObject("Rubberduck.FakesProvider")
End Sub

The Fakes API implements three of the four stubs for us, so we still need an implementation for ILogger, but now the method remains fully testable even with direct MsgBox, Now and Rnd calls:

Public Sub TestMe(ILogger Logger)
    If MsgBox("Print random number?", vbYesNo + vbQuestion, "Test") = vbYes Then
        Logger.LogDebug Now & vbTab & Rnd * 42
        Logger.LogDebug Now
    End If
End Sub

With an ILogger stub we could write a test that validates what’s being logged in each conditional branch (or we could decide that we don’t need an ILogger interface and we’re fine with tests actually writing to the debug pane, and leave Debug.Print statements in place), but let’s just stick with the same two tests we wrote above without the Fakes API. They look like this now:

Public Sub TestMe_WhenPromptYes_GetsNextRandomValue()
    ' Arrange
    Fakes.MsgBox.Returns vbYes
    ' Act
    Module1.TestMe New LoggerStub ' ILogger is irrelevant for this test
    ' Assert
End Sub
Public Sub TestMe_WhenPromptNo_DoesNotGetNextRandomValue()
    ' Arrange
    Fakes.MsgBox.Returns vbNo
    ' Act
    Module1.TestMe New LoggerStub ' ILogger is irrelevant for this test
    ' Assert
End Sub 

We configure the MsgBox fake to return the value we need, we invoke the method under test, and then we verify that the Rnd fake was invoked once or never, depending on what we’re testing. A failed verification will fail the test the same as a failed Assert call.

The fakes automatically track invocations, and remember what parameter values each invocation was made with. Setup can optionally supply an invocation number (1-based) to configure specific invocations, and verification can be made against specific invocation numbers as well, and we could have a failing test that validates whether Randomize is invoked when Rnd is called.

API Details

The IFake interface exposes members for the setup/configuration of fakes:

AssignsByRefConfigures the fake such as an invocation assigns the specified value to the specified ByRef argument.
PassthroughGets/sets whether invocations should pass through to the native call.
RaisesErrorConfigures the fake such as an invocation raises the specified run-time error.
ReturnsConfigures the fake such as the specified invocation returns the specified value.
ReturnsWhenConfigures the fake such as the specified invocation returns the specified value
given a specific parameter value.
VerifyGets an interface for verifying invocations performed during the test. See IVerify.
The members of Rubberduck.IFake

The IVerify interface exposes members for verifying what happened during the “Act” phase of the test:

AtLeastVerifies that the faked procedure was called a specified minimum number of times.
AtLeastOnceVerifies that the faked procedure was called one or more times.
AtMostVerifies that the faked procedure was called a specified maximum number of times.
AtMostOnceVerifies that the faked procedure was not called or was only called once.
BetweenVerifies that the number of times the faked procedure was called falls within the supplied range.
ExactlyVerifies that the faked procedure was called a specified number of times.
NeverVerifies that the faked procedure was called exactly 0 times.
OnceVerifies that the faked procedure was called exactly one time.
ParameterVerifies that the value of a given parameter to the faked procedure matches a specific value.
ParameterInRangeVerifies that the value of a given parameter to the faked procedure falls within a specified range.
ParameterIsPassedVerifies that an optional parameter was passed to the faked procedure. The value is not evaluated.
ParameterIsTypeVerifies that the passed value of a given parameter was of a type that matches the given type name.
The members of Rubberduck.IVerify

There’s also an IStub interface: it’s a subset of IFake, without the Returns setup methods. Thus, IStub is used for faking Sub procedures, and IFake for Function and Property procedures.

When to Stub Standard Library Members

Members of VBA.FileSystem not covered include EOF and LOF functions, Loc, Seek, and Reset. VBA I/O keywords Name, Open, and Close operate at a lower level than the standard library and aren’t covered, either. VBA.Interaction.CreateObject and VBA.Interaction.GetObject, VBA.Interaction.AppActivate, VBA.Interaction.CallByName, and the hidden VBA.Interaction.MacScript function, aren’t implemented.

Perhaps CreateObject and GetObject calls belong behind an abstract factory and a provider interface, respectively, and perhaps CallByName doesn’t really need hooking anyway. In any case there are a number of file I/O operations that cannot be faked and demand an abstraction layer between the I/O work and the code that commands it: that’s when you’re going to want to write stub implementations.

If you’re writing a macro that makes an HTTP request and processes its response, consider abstracting the HttpClient stuff behind an interface (something like Function HttpGet(ByVal Url As String)): the macro code will gain in readability and focus, and then if you inject that interface as a parameter, then a unit test can inject a stub implementation for it, and you can write tests that handle (or not?) an HTTP client error, or process such or such JSON or HTML payload – without hitting any actual network and making any actual HTTP requests.

Until we can do mocking with Rubberduck, writing test stubs for our system-boundary interfaces is going to have to be it. Mocking would remove the need to explicitly implement most test stubs, by enabling the same kind of customization as with fakes, but with your own interfaces/classes. Or Excel’s. Or anything, in theory.

Rubberduck 3.0 Progress Update

The next major version of Rubberduck is currently in [very] early development stages – saying that there is a lot of work ahead would be quite an understatement, but the skeleton is slowly taking shape, and things are looking very, very good.

Since the beginning of the project, Rubberduck’s user interface components (other than dialogs) have always been hosted in traditional, native dockable toolwindows. We built everything on top of the VBIDE editor, using Office CommandBar UI to simulate a status bar and make up for the lack of in-editor integration. Over the years this early design decision slowly became a burden: tearing down the many dockable toolwindows contributed to a pesky access violation crash on exit, low-level hooks for keyboard shortcuts constantly need to detach and re-attach as focus switches between the VBE main window and other applications, autocompletion/self-closing pairs was a nightmare to implement, and while the all-or-nothing approach to parsing made it so that we could always assume we were looking at valid VBA code that could be compiled, it also painted us into a corner where actually moving towards what we wanted Rubberduck to achieve by v3.0 would be extremely difficult, if not impossible.

Behold, the Rubberduck Editor

Rubberduck’s input was always driven by the Visual Basic Editor – now the code in the VBE is going to be output by Rubberduck. Of course, the code will go both ways, but now hidden attributes probably won’t need to be hidden anymore, and the editor can now be exactly what we envision it to be.

There will only be a single toolwindow that will host the editor and UI components like the Code Explorer. At this early stage my focus is entirely on the editor itself, but the idea is ultimately to get actual document tabs and a more practical and friendly docking manager.

Here’s what it looks like as of this writing:

The dropdowns don’t have a real item source yet, but the mock data gives a good idea of what it’s going to be like to edit VBA code with Rubberduck in the future.

Typing “Sub” and hitting the spacebar immediately completes the block and places a new folding node:

The faint dotted underline under “Sub” is a text marker; the editor has the ability to display various such markers at the exact desired position in the document, so we will be using them to show inspection results right there – with tooltips:

Hint-level results will be denoted with this dotted underline indicator; suggestion level will be a green squiggly underline, warnings a blue squiggle, and error level results will appear as red squiggles:

There will also be a new “ducky button” that pops up when the caret is on one such marker, and lets you pick a quick-fix in-place to address an inspection result:

The indenter still needs to be wired up, but this editor will ultimately indent your code as you type it. All the autocompletion features also need to be ported over to work here, and then we’ll want searchable and filterable IntelliSense, parameter info tooltips, and we’ll need to simulate the VBIDE “prettification” that occurs when a line is validated, so that public sub becomes Public Sub and identifiers take the casing they’re declared with.

We get an undo stack that can handle much more than 20 steps, and did I mention the status bar?

For now, all it does is report the current caret position in the editor, but Rubberduck 3.0 will be using it to report parsing progress, instead of the CommandBar button/label we’ve been abusing forever.

There will probably still be a command bar of some sort, but it will be part of the WPF/XAML managed UI; the old Rubberduck CommandBar will be decommissioned.

The one thing that’s 100% guaranteed to not happen in the new Rubberduck editor, is everything that needs to happen beyond design-time: there is no hook into the VBIDE debugger, so Rubberduck has no way of tracking the current instruction. As a result, the editor will be sadly useless in debug mode.

The editor work is just the beginning: Rubberduck 3.0 currently doesn’t even have a parser, let alone any inspections. In the next few months, the very heart of Rubberduck will be reworked to function with the new editor. It’s essentially like rewriting Rubberduck, but with an editor we fully control instead of one we constantly need to fight with.

Meanwhile v2.5.2 is approaching 25K downloads, and there’s quite a bit of work in 2.5.x that hasn’t been “officially” released yet, including everything that happened during a very successful Hacktoberfest 2022: we’ll be releasing v2.5.3 in the near future – stay tuned!

Website News

As I wrote last July, I’ve started to get more time for myself lately, and that means I get to tackle a number of long-standing projects that have been on the backburner for way too long. One of them is the rewrite of the project’s website, which has been “under construction” ever since it was published as an ASP.NET MVC website, a few years ago already.

If you missed it, I tweeted a sneak-peek link last week:

Tweeted 09/28: “A couple of things need a bit of work still, but this website rewrite is coming along nicely – have a peek here:

Why a rewrite?

For the longest time, I wouldn’t have considered myself a web developer. I have well over a decade of experience in C# desktop development, but the web stuff essentially scared me to death. The version of the website that’s currently live was pretty much my first time doing anything like it. The site itself wouldn’t write to the database; it was another application that pulled the tag metadata, downloaded the xml-doc assets, parsed the documentation and examples, and wrote them to the database.

One of the biggest issues with the current model, is that the database is made to contain HTML that is needlessly difficult to modify:

Unreachable code is certainly unintended, and is probably either redundant, or a bug.
<p>The following quick-fixes are available for this inspection:</p>
<ul style="margin-left: 8px; list-style: none;">
<span class="icon icon-ignoreonce"></span>
<a href="">IgnoreOnce</a>
: Adds an '@Ignore annotation to ignore a specific inspection result. Applicable to all inspections whose results can be annotated in a module.</li> 
<span class="icon icon-tick"></span>
<a href="">IgnoreInModule</a>
: Adds an '@IgnoreModule annotation to ignore a inspection results for a specific inspection inside a whole module. Applicable to all inspections whose results can be annotated in a module.

Having this HTML markup, CSS classes, and inline styles as part of the data meant the data was being responsible for its own layout and appearance on the site. With the new JSON objects serialized into this Properties column, I could easily keep everything strongly typed and come up with separate view models for inspections, quick-fixes, and annotations, that each did their own thing and let the website in charge of the layout and appearance of everything.

Separation of Concerns

The solution architecture could be roughly depicted like this – I suppose I meant the arrows to represents “depends on” but note that this doesn’t necessarily mean a direct project reference: the Client/API relationship is through HTTPS, and no project in the solution references the Rubberduck.Database SQL Server database project, but ContentServices connects to a rubberduckdb database that you can deploy locally using that database project:

You could draw a thick red line between Rubberduck.Client and Rubberduck.API (actually that’s Rubberduck.WebApi now), and it would perhaps better illustrate the actual wall between the website and the data: the website project doesn’t need a connection string, only a base URL for the API!

Authentication is assured with GitHub’s API using OAuth2: if you authorize the rubberduck-vba OAuth application to your profile, the HttpContext.User is cast as a ClaimsPrincipal and claims the GitHub login as a name, and a rubberduck-org role claim is added when organization membership is validated; an additional rubberduck-admin role claim is added if the user is also a member of the WebAdmin org team.

The website packages the HttpContext.User into a Json Web Token (JWT), an encrypted string that encapsulates the claims; this token is passed as a bearer token in authenticated API requests. The API accepts an Authorize header with either such a bearer token, or a valid GitHub personal access token (PAT).

The API receives a request, and given an Authorization header, either decrypts the JWT or queries GitHub to validate the provided access token and attach the appropriate role claims, before any controller action is invoked.

Another authentication filter performs a similar task to authorize an incoming webhook payload: the rubberduck-webhook role is set and tag metadata and xml-doc content can get updated automatically whenever a new tag/release gets created.


This new website performs much, much better than the current one. It sends asynchronous (ajax) requests to the MVC controller to render partial views, fetching only enough information to paginate the data and present a decent preview. Since most pages are presenting markdown content, an asynchronous request is also sent to format the markdown and, if applicable, apply syntax highlighting to code blocks. At this stage static content isn’t being cached yet, and screenshots should be loaded dynamically – still, performance is quite decent:

Home page scores 94, but then both Code Inspections and Inspections pages (two pages with extensive content, lots of markdown, code blocks, etc.) score a full 100 with Google Lighthouse, so things are looking very good performance-wise.

Another detail: the code examples no longer trigger a page load when you select a tab, so everything just feels much smoother now. Note, as of this writing the example records have been wiped from the database while I work on fixing a problem with the xml-doc processing, so annotations, inspections, and quick-fixes aren’t showing any examples on the test site for now.

Online Indenter

This feature once worked, but then my inexperienced past self, went and broke it in an attempt to make it asynchronous. Well, it’s back online and running Rubberduck.SmartIndenter.dll version 2.5.2:

You can paste VBA code into the box there, click the Indent button, then copy the indented code back into the clipboard.

The code can be indented as per the default indenter settings (which are also used for indenting all syntax-highlighted code blocks on the site), or if you expand the Indenter Settings panel you can tweak every knob Rubberduck’s Smart Indenter port has to offer.

It wouldn’t be too hard to include a “download these settings” button here, to serialize the settings into a .xml file that Rubberduck can then import to update indenter settings.

Content Administration

Users with the appropriate claims will be able to see additional buttons and commands on the site:

A modal dialog allows authenticated users to add and edit markdown content directly on the site.

Content administration features still need a little bit of work, but they are already being used to document how to use each and every single feature in Rubberduck – once this documentation is completed, the site will be a huge user manual, and ready for launch!

What’s Next?

Once everything works as it should (getting very close now!) and all that’s left to do is to take screenshots and generate more content, I’ll shift my focus to the Rubberduck3 project, the ownership of which I’ve now transferred over to the rubberduck-vba organization – the repo remains private for now, but all Rubberduck contributors have access to it. Uploading the RubberduckWebsite solution as a public repository isn’t a priority at this point; I feel like dealing with the implications of having API secrets in a .config file would be a distraction that I don’t need right now. When the time comes, it’ll be properly setup with continuous integration and deployment, but there are other priorities for now.

Like this little guy…

Project planning has begun for Rubberduck 3.0

Untitled Icebreaker

So, I haven’t posted here for well over a year now, and Rubberduck ‘s last “green release” was in May of 2021 (18,773 downloads as of this writing – the download stats on the site’s front page are stale, as of last February). The website has been “under construction” forever, and all this time I barely answered or participated on Stack Overflow and have been pretty much a ghost in the project’s dev chat and GitHub repository.

Let’s call that a sabbatical. I mean, COVID lockdowns and 2020-2021 hit hard for pretty much everyone, so I’ll just say s..tuff happens and you just have to let things go and do your best to enjoy the ride regardless.

And I did! But as of 2022 July 1, I’m no longer a Microsoft MVP: I did not apply for a 4th renewal, because I know what MVPs do, and I know what I can do myself, and I can’t be honest with myself and pretend the little content I put up in 2021 makes a solid application worthy of consideration. It’s been an amazing experience, but I wouldn’t have been comfortable with accepting this annual award for what I did that year: the Microsoft MVP award means much more than that to me.

As of this writing, the repository has over 1.6K stars on GitHub, 275 forks, and a snapshot of it is even archived along with thousands of other public repositories in the GitHub Antarctic Vault! I’m proud of what we have accomplished, and proud to be able to say Rubberduck has made it beyond the wildest dreams we had for it back in 2015! Years fly… I haven’t written any VBA code in a while, but I wasn’t writing much VBA code anyway ever since C# has been putting the bread on my table, and that never stopped me (or anyone) from working on Rubberduck. I still have ideas and now & then think of where the project should be heading, and the time will definitely come when I can turn that into code again – just not now.

What about Rubberduck?

Rubberduck has come a long, a very long way, and we’ve learned a lot all along – devs and users alike! I kind of burned out though. Nothing seemed to ever make a dent in the growing mountain of issues, and the code base being quite daunting doesn’t make it particularly friendly to new contributors or would-be C# programmers coming from VBA: it was all going to be on pretty much just us to churn through it all. Like answering on Stack Overflow, it became a chore. I couldn’t write anymore, and figured the last post I wrote made a nice front page for the blog anyway.

I strongly doubt Rubberduck can get where we envisioned it for 3.0 in the current state of the project. We could get a v2.6 out there, perhaps up to 2.9 even, but we always wanted more out of a 3.0 build, and the scale and implications of the changes needed for that to happen are rather scary.

I want the next major version to do away with performance issues, from startup to teardown; I want a smaller memory footprint; asynchronous, continuous parsing; total control over what we get to show in a code pane… but we can’t have that without a complete overhaul of the entire architecture and a redesign of the deepest core mechanisms in Rubberduck.

So where’s Rubberduck headed as a project? On one hand we have a massive and mature code base with roughly 200 known issues (and over 700 unimplemented ideas); on the other hand we have a goal that cannot realistically be fulfilled incrementally from where we’re at. I think we need to start with a brand new shell, and bring in the original code piece by piece, adjusting them as we go.

Meanwhile twinBASIC is steadily progressing, but still isn’t quite ready to power a brand new language layer for Rubberduck yet, so we’re still stuck with our Antlr4 grammar and parser, and step one would be to implement a parser that can survive syntax errors so Rubberduck can still process code that cannot run. You see ever since we introduced Antlr4, we’ve been using its token positions to locate specific code in code panes; when a new line gets inserted or any part of any code moves around, a new parse is needed for that entire module to keep these tokens in sync with what’s in the VBE. This is what’s painted us into a corner of endless parse-edit-reparse cycles.

It’s not just the tokens, it’s also what we’ve built on top of it: the `Declaration` class has so many constructors with so many constructor parameters… everything that isn’t a comment, from a line label to variables and procedures, is a declaration. Over the years a decent inheritance hierarchy was built so we have many types of specialized declarations, but each and every one of them is a rather heavy object, and thousands upon thousands of them need to be spawned every time we parse so that the resolver can keep track of everything. Then the VBE is as always actively sabotaging our efforts by hiding attributes, so a complete parse must involve exporting every module to a temporary file that we parse for these hidden attributes, which then need to be associated with the correct declaration.

In order to happen at all, Rubberduck 3.0 needs to completely flip things around, so instead of synchronizing Rubberduck with the VBE, we’d be synchronizing the VBE with Rubberduck. The implications are pretty radical: the VBE gets reduced to a mere host, and Rubberduck needs to be the code editor now, instead of interacting (and fighting!) with the VBE. In other words, I hate to say this but Rubberduck can’t really go forward without essentially a full rewrite, and without implementing what amounts to a whole IDE, inside the VBE.

Known showstoppers include hooking up and interacting with the debugger: there is no API for this, so there wouldn’t be any way to track execution, hit breakpoints, and step through the source code in the custom editor; clearly it’s a sub-par user experience, but if it’s the best we can get at one point we’re going to have to bite the bullet and make it happen anyway, and have the legacy VBE take over when code is running or paused.

So what’s likely going to happen is that the 1.6K-stars repository will remain on the 2.x architecture, and a brand new repo will eventually be up for the 3.0 rewrite. This is obviously risky, because a “Grand Redesign in the Sky” rarely ends well, but I’m not seeing any other way. I guess we’ll just have to see where that goes. Worst-case, we scrap it, stick with the 2.x architecture, and let go of the dream.

What about the website?

Early this year I had a fully working .net 6 solution that I intended to host on Microsoft Azure, but the monthly fees would quickly have depleted the donations we get and it just would not have been sustainable at all, so I thought “hey no problem I’ll just deploy it to the current GoDaddy host”… but the latest version of .net that runs over there is Framework 4.8, which means the solution had to be re-targeted and many little pieces had to be reworked – that’s done now, but the Bootstrap stuff broke and the entire front-end makes no sense anymore and I haven’t really had a chance to spend much time fixing it since then.

Between getting the ball rolling on v3.0 and getting the new website online, my priority is on the website, because well, at the end of the day I’m paying for this hosting and https — many thanks to our donors for this!

The revamped website will have much less static content and pull feature pages from its database; members of the Rubberduck GitHub organisation can edit this content as needed, easily upload updated screenshots, etc. The idea is to make the website be a huge user manual that describes every single feature and sub-feature, without needing a new deployment every time a typo needs fixing. I can’t yet commit to an ETA, but as things in my life fall back into place I’ll start having more time for this in the next few weeks/months. Let’s just say I intend to have it up before the next edition of Hacktoberfest!

What about the blog?

I love writing, so I’m not done here. As a Microsoft MVP I ended up feeling pressured to produce regular content to justify my renewals, and that wasn’t healthy. As I usually took inspiration from various answering experiences on Stack Overflow, I didn’t want my first post in over a year to be just a casual one about programming in VBA: I needed an ice breaker of some sort, and this is it. I’ll be writing more regularly now, but at my own pace.

So yeah, I’m *slowly* coming back from a rather long hiatus with a lot of rather large projects on my plate, and I intend to not burn myself up doing any of it, so this will mean rigorously time-boxing my activities and keeping a sane balance between Rubberduck, the website, the blog, the MVVM library that I still intend to build with twinBASIC, but also with my day job and family life. I’ll be happy if that makes me a Microsoft MVP again in 2023, but it’s not what I’m shooting for.

Next post should be about the upcoming Rubberduck 2.5.3 green release, it’s well past due!

Rubberduck Style Guide

As Rubberduck started to beef up its static code analysis capabilities in late 2015, it became evident that writing VBA (or VB6) code with Rubberduck loaded up in the Visual Basic Editor (VBE) would inevitably change not only how we work in VBA, but also how we write our VBA code in the first place.

Rubberduck is essentially providing a bridge between VBA land where people just get in and have a go and the VS land where if you don’t know a great deal about software development, you just waste your time and burn. Rubberduck will put a lot of people on a big learning curve and this will result in a lot of questions.” – AndrewM- commented on Oct 9, 2015

There’s an old issue (#823, still opened as of this writing) about having a coding style guide somewhere, that would enshrine the philosophy behind what Rubberduck is, in a way, trying to make your code-writing be/become; I think that was a great idea and I’m hoping this post captures the essence of it, at least as far as thinking code goes.

About Code Inspections

If you fire up Rubberduck on any legacy VBA project with any significant amount of code, there’s a very high probability that static code analysis generates tons of inspection results, for various mundane little things. Should your goal be to quick-fix all the things and have code that doesn’t spawn any Rubberduck inspection results?

Perhaps surprisingly, the answer is a resounding “no”.

Severity Levels

In Rubberduck each inspection has a configurable “severity level” that defaults to Warning for most inspections (it’s the default-unless-specified-otherwise for all Rubberduck inspections):

  • Error level indicates a potential problem you likely want to pay immediate attention to, because it could be (or cause) a bug. If inspection results rendered in the code pane, these would be red squiggly underlines.
  • Warning level indicates a potential issue you should be aware of.
  • Suggestion level is usually used for various opportunities, not necessarily problems.
  • Hint level is also for various non-problematic opportunities. If inspection results rendered in the code pane, these would be a subtle dotted underline with a hover text.
  • DoNotShow disables the inspection: not only its results won’t show, they won’t even be generated.

By default, Rubberduck is configured to run all (that’s currently over 110, counting the hidden/Easter egg ones) inspections, with a handful of cherry-picked exceptions for inspections that would be flagging the exact opposite situation that another enabled inspection is already flagging – for example we ship implicit ByRef modifier enabled (as a Hint), but redundant ByRef modifier is disabled unless you give it a severity level that’s anything other than DoNotShow. This avoids “fixing” one inspection result only to get a new one flagging the exact opposite, which would be understandably confusing for users that aren’t familiar with static code analysis tooling.

Are inspections somehow imbued with the knowledge of whether you should treat them as errors, warnings, or mere hints and suggestions? Sometimes, yes. Missing Option Explicit should make a clear consensus at Error level. On the flipside, whether an implicit default member call or the use of an empty string literal should be a Warning, a Hint, or shown at all probably depends more on how comfortable or experienced you are with VBA/VB6 as a language, or could be just a personal preference; what matters is that the static code analysis tooling is letting you know something about the code, that the code alone isn’t necessarily saying.


One of the very first inspection to be implemented in Rubberduck was the Option Explicit inspection. Okay, part of it was just because it was a trivial one to implement even before we had an actual parser… but the basic idea was (and still is) that nobody knows everything, and it’s with our combined knowledge that we make a mighty bunch, and that is why static code analysis in Rubberduck explains the reasoning behind each inspection result: there are quite many things Rubberduck warns of, that I had no idea about 10 or 15 years ago. That never stopped me (and won’t stop you either) from writing VBA code that worked perfectly fine (except when it didn’t), but whether we realize and accept it or not… a macro written in VBA code is a set of executable instructions, which makes it a program, which makes the act of writing it programming, which makes us programmers.

Being programmers that write and maintain VBA code does set us apart, mostly because the language isn’t going anywhere and the IDE is becoming more and more severely outdated and under-featured as years pass. Yet if the volume of VBA questions on Stack Overflow means anything, VBA is still very much alive, still very much being learned, and this is where Rubberduck and static code analysis comes in.

When I started learning about .NET and C# over a decade ago, there was this exciting new language feature they called LINQ for Language-INtegrated-Query where you could start querying object collections pretty much literally like you would a database, and it was awesome (still is!). In order to make this possible, the C# compiler and the .NET framework and runtime itself had to undergo some very interesting changes Jon Skeet covers in details, but the point is… the new syntax was a bit off-putting at first, and came with new and important implications (closures, deferred execution), and the company I worked for gave us all a ReSharper license, and that is how and when I discovered that thorough & accurate static code analysis tooling could be a formidable educational tool.

And I want Rubberduck to be like that, to be the companion tool that looks at your code and reveals bits of trivia, hints like “hey did you know this conditional assignment could be simplified?“, or “if that condition was inverted you wouldn’t need this empty block here“.

Maybe we don’t agree about Hungarian Notation, and that’s fine: Rubberduck wants you to be able to find it and rename it if that’s what you want to do, but you can mute that particular inspection anytime. But I believe the tool should tell you what Systems Hungarian notation is when it calls it out, and perhaps it should even explain what Apps Hungarian is and give examples, because Apps Hungarian notation absolutely is useful and meaningful (think o-for-OneBased, or src-for-Source and dst-for-Destination prefixes). But str-for-String, lng-for-Long, o-for-Object is different, in a bad kind of way.

Rubberduck flags obsolete code constructs and keywords, too. Global declarations, On Local Error statements, explicit Call statements, While...Wend loops, all have no reason to exist in brand new, freshly-written VBA code, and quick-fixes can easily turn them into Public declarations, On Error statements, implicit Call statements (without the Call keyword!), and Do While...Loop structures.

Rubberduck wants to push your programming towards objectively, quantitatively better code.

About Code Metrics

Rubberduck could count the number of lines in a procedure, and issue an inspection result when it’s above a certain configurable threshold. In fact, things are slowly falling into place for it to eventually happen. But we wouldn’t want you to just arbitrarily cut a procedure scope at 20 lines because an inspection said so! Rubberduck can measure line count, nesting levels, and cyclomatic complexity. These metrics can be used to identify problematic areas in a code base and methodically split up large complex problems into measurably much smaller and simpler ones.

Line Count simply counts the number of lines. Eventually this would expand into Statements and Comments counts, perhaps with percentages; 10% comments is probably considered a good sign, for example. But no tool is going to tell you that ' increments i is a useless comment, and even the best tools would probably not tell the difference between a huge ' the following chunk of code does XYZ banner comment and an actually valuable comment. Common wisdom is to keep this line count metric down as much as possible, but one should not do this at the expense of readability.

Nesting Levels counts the number of… well, nesting levels. While nesting two For...Next loops to iterate a 2D array (or a Range of cells) down and across is probably reasonable, further nesting is probably better off made implicit through a procedure call. Rule of thumb, it’s always good idea to pull the body of a loop into its own parameterized procedure scope. Arrow-shaped code gets flattened, line count gets lower, and procedures become more specialized and have fewer reasons to fail that way.

Cyclomatic Complexity essentially calculates the number of independent execution paths in a given procedure (wikipedia). A procedure with a cyclomatic complexity above 5 is harder to follow than one with a complexity of 1 or 2, but it’s not uncommon for a “God procedure” with nested loops and conditionals to measure in the high 40s or above.

The code metrics feature will eventually get all the attention it deserves, but as with inspections the general idea is to highlight procedures that could be harder to maintain than necessary, and nudge our users towards:

  • Writing more, smaller, more specialized procedure scopes.
  • Passing parameters between procedures instead of using global variables.
  • Having more, smaller, more cohesive modules.

Navigating the VBE

You may or may not have noticed, but the Visual Basic Editor is nudging you in the exact opposite direction, because…

  • Having fewer, larger, more general-purpose procedures puts you in a scripting mindset.
  • Using globals instead of passing parameters around is perhaps a simpler thing to do.
  • Having fewer, larger, more general-purpose modules makes it simpler to share the code between projects, and arguably easier to find things in the Project Explorer.

If you’re actually writing a small script, you can and probably should absolutely do that.

But if you’re like me then you’ve been pushing VBA to do things it wasn’t really meant to do, and you’re maintaining actual applications that could just as well be written in any other language out there, but you’re doing it in VBA because [your reasons are valid, whatever they are].

And that’s kind of a problem, because the VBE seems to actively not want you to write proper object-oriented code: its navigation tooling indeed makes it very hard to work in a project with many small modules, let alone an OOP project involving explicit interfaces and high abstraction levels.

Rubberduck lifts pretty much all the IDE limitations that hinder treating a VBA project as more than just an automation script. Now you can have a project with 135 class modules, all neatly organized by functionality into folders that can contain any module type, so a UserForm can appear right next to the classes that use it, without needing to resort to any kind of ugly prefixing schemes. You can right-click on an abstract interface (or one of its members) and quickly find all classes that implement it. You get a Find symbol command that lets you quickly navigate to literally anything that has a name, anywhere in the project. Curious about the definition of a procedure, but don’t want to break your flow by navigating to it? Peek definition (currently only in pre-release builds) takes you there without leaving where you’re at.

The Peek Definition command pops a floating panel conveniently showing the source code for the user-defined module or member you’ve selected.
Find all References shows all the places a given identifier is being used, and shows it in context so you can easily locate the specific usage you’re looking for – and then a double-click takes you there.
The Find all Implementations command is incredibly useful in object-oriented projects that leverage polymorphism through abstract interfaces: quickly locate and navigate to any implementation of any interface (class or member).

The VBE’s Project Explorer aims to give you a bird’s eye view of your project, regrouping modules by module type which is great for a small script that can get away with a small number of components, but that makes it very hard to manage larger projects. With Rubberduck’s Code Explorer you get to drill down to member level, and regroup modules by functionality using an entirely customizable folder hierarchy:

The Code Explorer leaves the VBE’s Project Explorer in the dust, fair & square.

These navigational enhancements greatly simplify moving around a project of any size, although some of them might feel a bit overkill in a smaller project, and some of them are only useful in more advanced OOP scenarios. Still, having more than just a text-based search to look for things is very useful.


If there’s one single over-arching principle guiding everything else, it would have to be write code that does what it says, and says what it does. Everything else seems to stem from this. These are warmly recommended guidelines, not dogma.


  • Use PascalCase if you like. Use camelCase if you like. Consistency is what you want to shoot for, and in a case-insensitive language that only stores a single version of any identifier name it’s much easier and simpler to just use PascalCase everywhere and move on to more interesting things, like tabs vs spaces.
  • Avoid _ underscores in identifier names, especially in procedure/member names.
    • Causes compile errors with Implements.
  • Use meaningful names that can be pronounced.
  • Avoid disemvoweling (arbitrarily stripping vowels) and Systems Hungarian prefixing schemes.
  • A series of variables with a numeric suffix is a missed opportunity to use an array.
  • A good identifier name is descriptive enough that it doesn’t need an explainer comment.
  • Use a descriptive name that begins with a verb for Sub and Function procedures.
  • Use a descriptive name (a noun) for Property procedures and modules.
  • For object properties, consider naming them after the object type they’re returning, like Excel.Worksheet.Range returns a Range object, or like ADODB.Recordset.Fields returns a Fields object.
  • Appropriately name everything the code must interact with: if a rounded rectangle shape is attached to a DoSomething macro, the default “Rounded Rectangle 1” name should be changed to “DoSomethingButton” or something that tells us about its purpose. This includes all controls on a UserForm designer, too. CommandButton12 is useless; SearchButton is much better. Consider also naming the controls that don’t necessarily interact with code, too: future code might, and the author of that future code will appreciate that the bottom panel is named BottomPanel and not Label34.


Naming is hard enough, renaming things should be easy. With Rubberduck’s Rename refactoring (Ctrl+Shift+R) you can safely rename any identifier once, and all references to that identifier automatically get updated. Without a refactoring tool, renaming a form control can only be done from the Properties toolwindow (F4), and doing this instantly breaks any event handlers for it; renaming a variable by hand can be tedious, and renaming a single-letter variable like a or i with a local-scope find/replace (Ctrl+H) can get funny if the scope has any comments. Rubberduck knows the exact location of every reference to every identifier in your project, so if you have a module with two procedures that each declare a localThing, when you rename the local variable localThing in the first procedure, you’re not going to be affecting the localThing in the other procedure. But if you rename CommandButton1 to OkButton, then CommandButton1_Click() becomes OkButton_Click().

Parameters & Arguments

  • Prefer passing values as parameters instead of bumping the scope of a variable to module-level, or instead of declaring global variables.
  • Pass parameters ByVal whenever possible.
    • Arrays and User-Defined Type structures cannot and should not be passed by value.
    • Objects are never passed anywhere no matter the modifier: it’s only ever (ByVal: a copy of) a pointer that gets passed around – and most of the time the intent of the author is to pass that pointer by value. A pointer is simply a 32-bit or 64-bit integer value, depending on the bitness of the process; passing that pointer ByRef (explicitly or not) leaves more opportunities for programming errors.
  • Use an explicit ByRef modifier whenever passing parameters by reference.
  • Consider specifying an out prefix to name ByRef return parameters.
    • Consider using named arguments for out-prefixed ByRef return parameters.


  • Use the single quote ' character to denote a comment.
  • Avoid line-continuing comments; use single quotes at position 1 of each line instead.
  • Consider having a @ModuleDescription annotation at the top of each module.
  • Consider having a @Description annotation for each Public member of a module.
  • Remove comments that describe what an instruction does, replace with comments that explain why an instruction needs to do what it does.
  • Remove comments that summarize what a block of code does; replace with a call to a new procedure with a nice descriptive name.
  • Avoid cluttering a module with banner comments that state the obvious. We know they’re variables, or properties, or public methods: no need for a huge green comment banner to tell us.
  • Avoid cluttering a procedure scope with banner comments that split up the different responsibilities of a procedure: the procedure is doing too many things, split it up and appropriately name the new procedure instead.


  • Declare all variables, always. Option Explicit should be enabled at all times.
  • Declare an explicit data type, always. If you mean As Variant, make it say As Variant.
  • Consider using a Variant to pass arrays between scopes, instead of typed arrays (e.g. String()).
    • Pluralize these identifier names: it signals a plurality of elements/items much more elegantly than Pirate Notation (arr*) does.
  • Avoid Public fields in class modules; encapsulate them with a Property instead.
  • Consider using a backing user-defined Private Type structure for the backing fields of class properties; doing so eliminates the need for a prefixing scheme, lets a property be named exactly as per its corresponding backing field, and cleans up the locals toolwindow by grouping the fields under a single module variable.
  • Limit the scope of variables as much as possible. Prefer passing parameters and keeping the value in local scope over promoting the variable to a larger scope.
  • Declare variables where you’re using them, as you need them. You should never need to scroll anywhere to see the declaration of a variable you’re looking at.

Late Binding

Late binding has precious little to do with CreateObject and whether or not a library is referenced. In fact, late binding happens implicitly rather easily, and way too often. Strive to remain in early-bound realm all the time: when the compiler / IntelliSense doesn’t know what you’re doing, you’re on your own, and even Option Explicit can’t save you from a typo (and error 438).

  • Avoid making a member call against Object or Variant. If a compile-time type exists that’s usable with that object, a local variable of that data type should be assigned (Set) the Object reference and the member call made early-bound against this local variable.
    • Taking an object presenting one interface and assigning it to another data type is called “casting”.
  • Of course explicit late binding is OK (As Object, no library reference, create objects with CreateObject instead of the New operator). Late binding is very useful and has many legitimate uses, but generally not when the object type is accessible at compile-time through a library reference.
  • Avoid the dictionary-access (aka “bang”) operator !, it is late-bound by definition, and makes what’s actually a string literal read like a member name, and any member call chained to it is inevitably late-bound too. Rubberduck can parse and resolve these, but they’re much harder to process than standard method calls.


  • Use explicit modifiers everywhere (Public/Private, ByRef/ByVal).
  • Declare an explicit data type, even (especially!) if it’s Variant.
  • Avoid implicit qualifiers for all member calls: in Excel watch for implicit ActiveSheet references, implicit ActiveWorkbook references, implicit containing worksheet references, and implicit containing workbook references, as they are an extremely frequent source of bugs.
  • Invoke parameterless default members explicitly.
    • Note: some object models define a hidden default member (e.g. Range.[_Default]) that redirects to another member depending on its parameterization. In such cases it’s best to invoke that member directly; for example use Range.Value as appropriate, but the hidden [_Default] member is better off not being invoked at all, for both readability and performance reasons.
  • Invoke parameterized default members implicitly when they are indexers that get a particular item in an object collection, for example the Item property of a Collection. Invoking them explicitly doesn’t hurt, but could be considered rather verbose.
  • Call is not a keyword that needs to be in your program’s vocabulary when you use expressive, descriptive procedure names that imply an action taking place.
  • Consider explicitly qualifying standard module member calls with the project (and module) name, including for standard and referenced libraries, especially in VBA projects that reference multiple object models.

Structured Programming (Procedural)

  • One macro/script per module. Do have it in a module rather than a worksheet’s code-behind.
  • Public procedure first, followed by parameterized Private procedures, in decreasing abstraction level order such that the top reads like a summary and the bottom like boring, small but specific operations.
    • You know it’s done right when you introduce a second macro/module and get to pull the small, low-abstraction, specific operations into Public members of a utility module, and reuse them.
  • Don’t Repeat Yourself (DRY).
  • Consider passing the relevant state to another procedure when entering a block of code. Code is simpler and easier to follow when the body of a loop or a conditional block is pulled into its own scope.
  • Avoid using error handling to control the flow of execution: the best error handling is no error handling at all, because assumptions are checked and things are validated. For example instead of opening a file from a parameter value, first verify that the file exists instead of handling a file not found error… but still handle errors, for any exceptional situations that might occur while accessing the file.
  • When it’s not possible to avoid error handling, consider extracting a Boolean function that swallows the expected error and returns False on failure, to simplify the logic.
  • Handle errors around all file and network I/O.
  • Never trust user inputs to be valid or formatted as expected.

Object Oriented Programming

In VBA/VB6 we get to go further than mere scripting and apply Object-Oriented Programming principles, probably more relevantly so in VB6 and larger VBA projects. For many years it has been drilled into our heads that VBA/VB6 cannot do “real” OOP because it doesn’t support inheritance. The truth is that there is much, much more to OOP than inheritance, and if you want to learn and apply OOP principles in your VBA/VB6 code, you absolutely can, and you absolutely should, and Rubberduck will absolutely help you do that.

  • Adhere to standard OOP best practices, they are general, language-agnostic concepts that couldn’t care less about the capabilities of VBA/VB6:
    • Single Responsibility Principle – each abstraction should be responsible for one thing.
    • Open/Closed Principle – write code that doesn’t need to change unless the purpose of the abstraction itself needs to change.
    • Liskov Substitution Principle – code should run the exact same execution paths regardless of the concrete implementation of a given abstraction.
    • Interface Segregation Principle – keep interfaces small and specialized, avoid a design that constantly needs new members to be added to an interface.
    • Dependency Inversion Principle – depend on abstractions, not concrete implementations.
  • Leverage composition where inheritance would be needed.
  • You cannot have parameterized constructors, but you still can leverage property injection in factory methods to inject instance-level dependencies.
  • Leverage method injection to inject method-level dependencies.
  • Avoid New-ing dependencies in-place, it couples a class with another, which hinders testability; inject the dependencies instead.
    • Use the New keyword in your composition root, as close as possible to an entry point.
    • The Workbook_Open event handler (Excel) is a possible entry point.
    • Macros (Sub procedures invoked from outside the code) are also valid entry points.
    • Let go of the idea that a module must control every last one of its dependencies: let something else deal with creating or dereferencing these objects.
  • Inject an abstract factory when a dependency cannot or should not be created at the composition root, for example if you needed to connect to a database and wish to keep the connection object as short-lived and tightly-scoped as possible.
  • Keep the default instance of a class stateless as much as possible. Actively protect/guard against accidental misuse by throwing a run-time error as necessary.
  • Use standard modules instead of a utility class with a @PredeclaredId, that never gets explicitly instantiated or used as an actual object.

User Interfaces

UI code is inherently object-oriented, and thus a UserForm should be treated as the object it wants to be. The responsibilities of a user interface are simple: display and collect data to/from the user, and/or offer a way to execute commands (which typically consume or otherwise manipulate the data).

  • Avoid working directly with the form’s default instance. New it up instead.
  • Form module / code-behind should be strictly concerned with presentation concerns.
    • Do implement UI logic in form’s code-behind, e.g. enable this control when this command says it can be executed, or show this label when the model isn’t valid, etc.
  • Consider creating a model class to encapsulate the form’s state/data.
    • Expose a read/write property for each editable field on the form.
    • Expose a read-only property for data needed by the controls (e.g. the items of a ListBox).
    • Controls’ Change handlers manipulate the model properties.
    • Expose additional methods and properties as needed for data/input validation.
      • Consider having an IsValid property that returns True when all required values are supplied and valid, False otherwise; use this property to enable or disable the form’s Accept button.
  • Avoid implementing any kind of side-effecting logic in a CommandButton‘s Click handler. A CommandButton should invoke a command, right?
    • In procedural code the command might be a Public Sub procedure in a standard module named after the form, e.g. a SomeDialogCommands module for a SomeDialog form.
    • In OOP the command might be a property-injected instance of a DoSomethingCommand class; the Click handler invokes the command’s Execute method and could pass the model as a parameter.
  • Consider implementing a presenter object that is responsible for owning and displaying the form instance; the Model-View-Presenter UI pattern is well documented, and like everything OOP, its concepts aren’t specific to any language or platform.

Caveat: Microsoft Access Data-Bound UI

VBA projects hosted in Microsoft Access can absolutely use UserForm modules too, but without Rubberduck you need to hunt down the IDE command for it because it’s hidden. Instead, in Access you mostly create Access Forms, which (being document modules owned by the host application) have much more in common with a Worksheet module in Excel than with a UserForm.

The paradigm is different in an Access form, because of data bindings: a data-bound form is inherently coupled with the underlying database storage, and any effort to decouple the UI from the database is working directly against everything Access is trying to make easier for you.

Treating an Access form the way one would treat a worksheet UI in Excel puts you in a bit of a different mindset. Imagine the Battleship worksheet UI implemented as an Access form: the game would be updating game state records in the underlying database, and instead of having code to pull the game state into the UI there would only need to be code to re-query the game state, and the data bindings would take care of updating the actual UI – and then the game could easily become multi-player, with two clients connecting to the database and sharing the same game state.

This is very fundamentally different than how one would go about getting the data into the controls without such data bindings. Binding the UI directly to a data source is perfectly fine when that data source happens to be running in the very same process your VBA code is hosted in: Access’ Rapid Application Development (RAD) approach is perfectly valid in this context, and its global objects and global state make a nice beginner-friendly API to accomplish quite a lot, even with only a minimal understanding of the programming language (and probably a bit of Access-SQL).

If we’re talking about unbound MS-Access forms, then it’s probably worth exploring Model-View-Presenter and Model-View-Controller architectures regardless: in such exploratory OOP scenarios the above recommendations can all hold.

UI Design

I’m not going to pretend to be a guru of UI design, but over the years I’ve come to find myself consistently incorporating the same elements in my modal forms, and it has worked very well for me so here we go turning that into general guidelines.

  • TopPanel is a Label control with a white background that is docked at the top and tall enough to comfortably fit short instructions.
  • BottomPanel is also a Label control, with a dark gray background, docked at the bottom and no more than 32 pixels in height.
  • DialogTitle is another Label control with a bold font, overlapping the TopPanel control.
  • DialogInstructions is another Label control overlapping the TopPanel control.
  • DialogIcon is an Image control for a 16×16 or 24×24 .bmp icon aligned left, at the same Top coordinate as the DialogTitle control.
  • OkButton, CancelButton, CloseButton, ApplyButton would be CommandButton controls overlapping the BottomPanel control, right-aligned.

The actual client area content layout isn’t exactly free-for-all, and I doubt it’s possible to come up with a set of “rules” that can apply universally, but we can try, yeah?

  • Identify each field with a label; align all fields to make it look like an implicit grid.
  • Seek visual balance; ensure a relatively constant margin on all sides of the client area, space things out but not too much. Use Frame controls to group ComboBox options.
  • Avoid making a complex form with too many responsibilities and, inevitably, too many controls. Beyond a certain complexity level, consider making separate forms instead of tabs.
  • Use Segoe UI for a more modern font than MS Sans Serif.
  • Do not bold all the labels.
  • Have a ToolTip string for the label of every field the user must interact with. If a field is required or demands a particular format/pattern, indicate it.
  • Consider toggling the visibility of a 16×16 icon next to (or even inside, right-aligned) input fields, to clearly indicate any data validation errors (have a tooltip string on the image control with the validation error message, e.g. “this field is required”, or “value cannot be greater than 100”).
  • Name. All. The. Things.
  • Use background colors in input controls only to strongly signal something to the user, like a validation error that must be corrected in order to move on. Dark red text over a light pink background makes a very strong statement.
  • Keep a consistent color scheme/palette and style across all of your application’s UI components.

This pretty much concludes the “guidelines” section (although I’ll quite probably be adding more to it), but since discussing unit testing and testability lines up with everything above…

Unit Testing

A unit test is a small, simple procedure that is responsible for 3 things:

  1. Arrange dependencies and set expectations.
  2. Act, by invoking the method or function under test.
  3. Assert that the expected result matches the actual one.

When a unit test runs, Rubberduck tracks Assert.Xxxx method calls and their outcome; if a single Assert call fails, the test fails. Such automated tests are very useful to document the requirements of a particular model class, or the behavior of a given utility function with multiple optional parameters. With enough coverage, tests can actively prevent regression bugs from being inadvertently introduced as the code is maintained and modified: if a tweak breaks a test, you know exactly what functionality you broke, and if all tests are green you know the code is still going to behave as intended.

Have a test module per unit/class you’re testing, and consider naming the test methods following a MethodUnderTest_GivenAbcThenXyz, where MethodUnderTest is the name of the method you’re testing, Abc is a particular condition, and Xyz is the outcome. For tests that expect an error, consider following a MethodUnderTest_GivenAbc_Throws naming pattern. Rubberduck will not warn about underscores in test method names, and these underscores are safe because Rubberduck test modules are standard modules, and unit test naming recommendations usually heavily favor being descriptive over being concise.

What to test?

You want to test each object’s public interface, and treat an object’s private members as implementation details. You do NOT want to test implementation details. For example if a class’ default interface only exposes a handful of Property Get members and a Create factory method that performs property-injection and a handful of properties, then there should be tests that validate that each of the parameters of the Create method correspond to an injected property. If one of the parameters isn’t allowed to be Nothing, then there should be a guard clause in the Create method for it, and a unit test that ensures a specific error is being raised when the Create method is invoked with Nothing for that parameter.

Below is one such simple example, where we have 2 properties and a method; note how tests for the private InjectDependencies function would be redundant if the public Create function is already covered – the InjectDependencies function is an implementation detail of the Create function:

Option Explicit
Implements IClass1
Private Type TState
    SomeValue As String
    SomeDependency As Object
End Type
Private This As TState
Public Function Create(ByVal SomeValue As String, ByVal SomeDependency As Object) As IClass1
    If SomeValue = vbNullString Then Err.Raise 5
    If SomeDependency Is Nothing Then Err.Raise 5
    Dim Result As Class1
    Set Result = New Class1
    InjectProperties Result, SomeValue, SomeDependency
    Set Create = Result
End Function
Private Sub InjectProperties(ByVal Instance As Class1, ByVal SomeValue As String, ByVal SomeDependency As Object)
    Instance.SomeValue = SomeValue
    Set Instance.SomeDependency = SomeDependency
End Sub
Public Property Get SomeValue() As String
    SomeValue = This.SomeValue
End Property
Public Property Let SomeValue(ByVal RHS As String)
    This.SomeValue = RHS
End Property
Public Property Get SomeDependency() As Object
    SomeDependency = This.SomeDependency
End Property
Public Property Set SomeDependency(ByVal RHS As Object)
    Set This.SomeDependency = RHS
End Property
Private Property Get IClass1_SomeValue() As String
    IClass1_SomeValue = This.SomeValue
End Property
Private Property Get IClass1_SomeDependency() As Object
    IClass1_SomeDependency = This.SomeDependency
End Property

Note: the property injection mechanism doesn’t need a Property Get member on the Class1 interface, however not exposing a Property Get member for a property that has a Property Let (and/or Property Set) procedure, would leave the property as write-only on the Class1 interface. Write-only properties would be flagged as a design smell, so there’s a conundrum here: either we expose a Property Get that nothing is calling (except unit tests, perhaps), or we expose a write-only property (with a comment that explains its property injection purpose). There is no right or wrong, only a consistent design matters.

If we were to write unit tests for this class, we would need at least:

  • One test that invokes Class1.Create with an "" empty string for the first argument and fails if error 5 isn’t raised by the procedure call.
  • One test that invokes Class1.Create with Nothing for the second argument and fails if error 5 isn’t raised by the procedure call.
  • One test that invokes Class1.Create with valid arguments and fails if the returned object is Nothing.
  • One test that invokes Class1.Create with valid arguments and fails if the Class1.SomeValue property doesn’t return the value of the first argument.
  • One test that invokes Class1.Create with valid arguments and fails if the Class1.SomeDependency property doesn’t return the very same object reference as was passed for the second argument.
  • One test that invokes Class1.Create with valid arguments and fails if the IClass1.SomeValue property doesn’t return the same value as Class1.SomeValue does.
  • One test that invokes Class1.Create with valid arguments and fails if the IClass1.SomeDependency property doesn’t return the same object reference as Class1.SomeDependency does.

Obviously that’s just a simplified example, but it does perfectly illustrate the notion that the answer to “what to test?” is simply “every single execution path”… of every public member (private members are implementation details that are invoked from the public members; if they specifically need tests, then they deserve to be their own concern-addressing class module).

What is testable?

Without the Property Get members of Class1 and/or IClass1, we wouldn’t be able to test that the Create method is property-injecting SomeValue and SomeDependency, because the object’s internal state is encapsulated (as it should be). Therefore, there’s an implicit assumption that a Property Get member for a property-injected dependency is returning the encapsulated value or reference, and nothing more: by writing tests that rely on that assumption, we are documenting it.

Now SomeDependency might be an instance of another class, and that class might have its own encapsulated state, dependencies, and testable logic. A more meaty Class1 module might have a method that invokes SomeDependency.DoSomething, and the tests for that method would have to be able to assert that SomeDependency.DoSomething has been invoked once.

If Class1 wasn’t property-injecting SomeDependency (for example if SomeDependency was being New‘d it up instead), we would not be able to write such a test, because the outcome of the test might be dependent on a method being called against that dependency.

A simple example would be Class1 newing up a FileSystemObject to iterate the files of a given folder. In such a case, FileSystemObject is a dependency, and if Class1.DoSomething is newing it up directly then every time Class1.DoSomething is called, it’s going to try and iterate the files of a given folder, because that’s what a FileSystemObject does, it hits the file system. And that’s slow. I/O (file, network, …and user) is dependent on so many things that can go wrong for so many reasons, having it interfere with tests is something you want to avoid.

The way to avoid having user, network, and file inputs and outputs interfere with the tests of any method, is to completely let go of the “need” for a method to control any of its dependencies. The method doesn’t need to create a new instance of a FileSystemObject; what it really needs is actually a much simpler any object that’s capable of returning a list of files or file names in a given folder.

So instead of this:

Public Sub DoSomething(ByVal Path As String)
    With CreateObjet("Scripting.FileSystemObject")
        ' gets the Path folder...
        ' iterates all files...
        ' ...
    End With
End Sub

We would do this:

Public Sub DoSomething(ByVal Path As String, ByVal FileProvider As IFileProvider)
    Dim Files As Variant
    Files = FileProvider.GetFiles(Path)
    ' iterates all files...
    ' ...
End Sub

Where IFileProvider would be an interface/class module that might look like this:

Option Explicit
'@Description "Returns an array containing the file names under the specified folder."
Public Function GetFiles(ByVal Path As String) As Variant
End Function

That interface might very well be implemented in a class module named FileProvider that uses a FileSystemObject to return the promised array.

It could also be implemented in another class module, named TestFileProvider, that uses a ParamArray parameter so that unit tests can take control of the IFileProvider dependency and inject (here by method injection) a TestFileProvider instance. The DoSomething method doesn’t need to know where the file names came from, only that it can expect an array of existing, valid file names from IFileProvider.GetFiles(String). If the DoSomething method indeed doesn’t care where the files came from, then it’s adhering to pretty much all OOP design principles, and now a test can be written that fails if DoSomething is doing something wrong – as opposed to a test that might fail if some network drive happens to be dismounted, or works locally when working from home but only with a VPN.

The hard part is obviously identifying the dependencies in the first place. If you’re refactoring a procedural VBA macro, you must determine what the inputs and outputs are, what objects hold the state that’s being altered, and devise a way to abstract them away and inject these dependencies from the calling code – whether that caller is the original entry point macro procedure, or a new unit test.


In the above example, the TestFileProvider implementation of the IFileProvider dependency is essentially a test stub: you actually write a separate implementation for the sole purpose of being able to run the code with fake dependencies that don’t incur any file, network, or user I/O. Reusing these stubs in “test” macros that wire up the UI by injecting the test stubs instead of the actual implementations, should result in the application running normally… without hitting any file system or network.

With mocks, you don’t need to write a “test” implementation. Instead, you configure an object provided by a mocking framework to behave as the method/test needs, and the framework implements the mocked interface with an object that can be injected, that verifiably behaves as configured.

Sounds like magic? A lot of it actually is, from a VBA/VB6 standpoint. Many tests in Rubberduck leverage a very popular mocking framework called Moq. What we’re going to be releasing as an experimental feature is not only a COM-visible wrapper around Moq. The fun part is that the Moq methods we need to use are generic methods that take lambda expressions as parameters, so our wrapper needs to expose an API VBA code can use, and then “translate” it into member calls into the Moq API, but because they’re generic methods and the mocked interface is a COM object, we essentially build a .NET type on the fly to match the mocked VBA/COM interface, so that’s what Moq actually mocks: a .NET interface type Rubberduck makes up at run-time from any COM object. Moq uses Castle Windsor under the hood to spawn instances of proxy types – made-up actual objects that actually implement one or more interfaces. Castle Windsor is excellent at what it does; we use CW to automate dependency injection in Rubberduck (a technique dubbed Inversion of Control, where a single container object is responsible for creating all instances of all objects in the application in the composition root; that’s what’s going on while Rubberduck’s splash screen is being displayed).

There is a problem though: CW seems to be caching types with the reasonable but still implicit assumption that the type isn’t going to change at run-time. In our case however, this means mocking a VBA interface once and then modifying that interface (e.g. adding, removing, or reordering members, or changing a member signature in any way) and re-running the test would still be mocking the old interface, as long as the host process lives. This isn’t a problem for mocking a Range or a Worksheet dependency, but VBA user code is being punished here.

Verifiable Invocations

Going back to the IFileProvider example, the GetFiles method could be configured to return a hard-coded array of bogus test strings, and a test could be made to turn green when IFileProvider.GetFiles is invoked with the same specific Path parameter value that was given to Class1.DoSomething. If you were stubbing IFileProvider, you would perhaps increment a counter every time IFileProvider_GetFiles is invoked, and expose that counter with a property that the test could Assert is equal to an expected value. With Moq, you can make a test fail by invoking a Verify method on the mock itself, that verifies whether the specified method was invoked as configured.

A best practice with mocking would be to only setup the minimal amount of members to make the test work, because of the performance overhead: if a mocked interface has 5 methods and 3 properties but the method under test only needs 2 of these methods and 1 of these properties, then it should only setup these. Verification makes mocking a very valuable tool to test behavior that relies on side-effects and state changes.

The best part is that because VBA is COM, then everything is an interface, so if you don’t have an IFileProvider interface but you’re still passing a FileProvider object as a dependency, then you can mock the FileProvider directly and don’t need to introduce any extra “just-for-testing” IFileProvider interface if you don’t already have one.

I’m going to stop here and just publish, otherwise I’ll be editing this post forever. So much is missing…

Introducing Rubberduck 2.5.2

Version 2.5.1 was released August 22, 2020. Since then, the installer was downloaded over 11,600 times; we are now 420 commits and 650 modified files later, and the time has come to deliver all that work into a convenient little installer package and move on to the next dev/release cycle.

What’s New?

If you’ve kept up with latest pre-release builds (especially in the last few weeks), nothing much. If you’ve been patiently waiting for the next release, you’re in for a treat!

The first thing you’ll probably notice is the shiny new splash screen design:

It’s the same old yellow splash made with Paint.NET, with a tiled reflection distortion effect against the background, a semi-transparent white bottom panel, and a finer font. Do you like it?

Fixed Bugs

50-some issues labelled “bug” were closed between 2020-08-22 and mid-April 2021, many of them thanks to flicking the switch on leveraging our internal ITypeLib API for user code – thanks to earlier invaluable contributions from the amazing Wayne Phillips (vbWatchdog, twinBASIC), Rubberduck is now able to tap into the actual in-memory COM type library compiled from the VBA code and, eventually, fill the remaining the gaps in Rubberduck’s understanding of the code: Rubberduck now understands enough to be able to tell that ThisWorkbook has a _Workbook subtype, and that Sheet1 has a _Worksheet subtype, …and that’s enough to identify the ThisWorkbook module at long last, and as a result Rubberduck’s ImplicitActiveSheetReference and ImplicitActiveWorkbookReference inspections get to work exactly as intended, and the door is now opened for so many interesting things…

New Inspections

A Rubberduck release wouldn’t be a Rubberduck release without at least a handful of new inspections. The IllegalAnnotation inspection is being replaced by InvalidAnnotation, UnrecognizedAnnotation, and together with the new AnnotationInIncompatibleComponentType inspection they allow Rubberduck to better convey exactly what’s wrong with a given “illegal” annotation comment.

Annotation in Incompatible Component Type

Some annotations cannot be used in certain types of modules. For example, attribute-related annotations cannot be used in document modules (because Rubberduck cannot import back the modified modules), and a @TestModule annotation is only meaningful in a standard module.

Note that the @Description, @ModuleDescription and @VariableDescription annotations do work in document modules now, because Rubberduck is now reading docstrings off annotations rather than hidden attributes.

Implicit Containing Workbook Reference

Code in the ThisWorkbook module (Excel) referring to members of the Workbook class, have an implicit Me qualifier. This makes an unqualified Worksheets(1) retrieval in ThisWorkbook refer to ThisWorkbook.Worksheets(1), but an identical statement in any other module would be (implicitly) referring to ActiveWorkbook. By qualifying such member calls with Me, the intent is clarified.

Implicit Containing Worksheet Reference

Code in a worksheet module (Excel) referring to members of the Worksheet class, have an implicit Me qualifier. This makes an unqualified Range member call in the Sheet2 module refer to Sheet2, but an identical statement in any other module would be (implicitly) referring to ActiveSheet. By qualifying such member calls with Me, the intent is clarified.

Invalid Annotation

Flags unbound annotations; that is, annotation comments that were correctly parsed as Rubberduck annotations but that could not be associated with a target element. This would happen when a module annotation is used in local scope, or a member annotation at module level. This inspection only flags annotation comments that parsed as Rubberduck annotations.

Misleading ByRef Parameter

The RHS/Value parameter of a Property Let procedure is always passed by value. As such, an explicit ByRef modifier on such a parameter definition is misleading. From MS-VBAL (VBA language specifications) section Property Declarations:

§ If the <value-param> of a <property-LHS-declaration> does not have a <parameter-mechanism> element or has a <parameter-mechanism> consisting of the keyword ByRef, it has the same meaning as if it instead had a <parameter-mechanism> element consisting of the keyword ByVal.
§ The <value-param> of a <property-LHS-declaration> always has the runtime semantics of a ByVal parameter.

Unrecognized Annotation

This inspection flags comments that parsed like a Rubberduck annotation, but aren’t recognized or supported. It picks up typos in Rubberduck annotations, and annotation-like comments that aren’t Rubberduck annotations but parse as such. Splicing this specific scenario from other invalid annotations is particularly useful when you want to mute inspection results for non-Rubberduck annotations while still validating the supported ones.

New Quick Fixes

This release also introduces a handful of new quick-fixes:


This fix is now available for ProcedureNotUsed inspection results in standard and document modules; it simply annotates a member with the new @EntryPoint annotation which specifically instructs ProcedureNotUsed to ignore that member. Use this quick-fix for UDFs and macro procedures that are attached to document objects and don’t need an Excel hotkey. If your project is hosted in an Excel workbook, macros annotated with @ExcelHotkey are also considered as entry points now.


VariableTypeNotDeclared inspection results could always be “fixed” by making the declared type an explicit Variant; this new quick-fix makes Rubberduck infer the declared type from usage where possible, which is objectively awesome.


This new quick-fix is available for the new implicit containing workbook/worksheet reference inspections, making the reference to the containing module explicit.

Introduce Get Accessor

The Write-Only Property inspection gets a new quick-fix with this release; this iteration does not try to infer the backing field, so further manual edits are needed, but it’s a start.

New UI Language: Italian

Thanks to a timely contribution by @PhilCattivocaratere, we are thrilled to announce that this release introduces Italian as a UI language:

Every single UI string in Rubberduck comes from a localized resource file. Translating all the resources for a new language can take 3-5 hours, and then it’s only a matter of keeping the translations up-to-date by creating a small pull request when new resource strings are added for new features.

In a nutshell

Here’s a quick summary of the most significant pull requests and commits merged this cycle:

  • Encapsulate Field enhancements
  • We are now leveraging our internal ITypeLib API
  • We are now building Rubberduck with the latest version of Visual Studio 2019
  • Precompiler directives now parse correctly with line continuations
  • Internal CodeBuilder API honors indenter settings when generating code
  • Fixed a number of issues with name conflict validation
  • Test methods now support a @TestIgnore annotation to ignore a test
  • Specific projects can now be ignored by the parser
  • Users no longer need to accept the GPLv3 as if it were an End User License Agreement (EULA)
  • Custom templates extensions is changing from .rdt to .template
  • Implicit Variant inspection quick-fix will now infer the best type from usage instead of just making the variable an explicit Variant
  • For...Next loop variables no longer trigger a variable not used inspection
  • Implicit Public Member inspection will now flag Enum types and Type structures
  • Branch “master” was renamed to “main”
  • New Property Group indenter settings
  • Arrays declared with ReDim now correctly resolve the declared type
  • @Description, @VariableDescription, and @ModuleDescription can now be used in document modules (cannot be synchronized)
    • Documentation strings are now read from annotations when missing from attributes
  • Start menu link to website now uses https
  • Fixed context menu positioning
  • New @EntryPoint annotation marks a standard or document module member as invoked from outside the code; as such the Procedure Not Used inspection will no longer flags members annotated with @EntryPoint or @ExcelHotkey (Excel only).
  • Several other opportunistic fixes left & right, improved overall stability.
  • Shiny new splash screen; debug builds now indicate “debug” instead of a meaningless local build number (build version# is controlled by the AppVeyor CI build server; local builds are all .0).
  • Expand/collapse all in Code Explorer
  • Rubberduck CommandBar label will now show the corresponding parameter declaration for a selected argument, and Find all References will now include arguments at call sites for parameter declarations (previous versions would only count named arguments).
  • Find Symbol navigation tool works again.
  • Find all References search results will now highlight the target reference in its context.
  • Added Italian UI resources.

Possible (Silent) Crash on Exit

I haven’t personally experienced it in a long time in Excel, but Rubberduck may run into issues tearing down, sometimes causing an AccessViolationException when it unloads, which can either crash the host process or leave it hanging as a ghosted process that will interfere with reloading: verify that the host process (e.g. ACCESS.EXE) has shut down completely using Task Manager when you close everything, and make sure to kill any such ghosted processes before loading Rubberduck in a new process.

Sounds familiar? If you’ve been following the project all along, you probably remember similar behavior in earlier releases – at one point during this development cycle we thought the problem was finally under control, but the cure was worse than the disease and there was a chance that the host document / project gets completely corrupted and impossible to open in the VBE: because we think it’s much better to sometimes crash on teardown than to corrupt our users’ host documents forever, we have reverted that “fix” and will have to come up with something else.

What’s Next?

Lots of good stuff, including a new peek definition command to the code pane, Code Explorer, and the VBE’s own Project Explorer‘s context menus – the feature was developed too late to make the cut for this release, but will be available in 2.5.2.x pre-release builds very soon:

Peek Definition commands pop a panel that shows you the syntax-highlighted source code for a type or member. The pop-up panel can then be dragged around to keep it in sight while editing.

In the Unit Testing department, a mocking framework is about to debut as an experimental feature with a number of technical limitations.

I’m going to be turning my attention towards code path analysis this cycle; this internal API is needed to implement the more advanced inspection ideas, and an Extract Method refactoring needs it too.

To be continued…

Constructors in twinBASIC

If you haven’t tried it already, download VSCode and get the twinBASIC extension, and be part of the next stage of the Visual Basic revolution. When it goes live (it’s still in preview, and vigorously maintained), twinBASIC will compile 100% VB6/VBA compatible code and completely redefine how VB6 and VBA solutions will be maintained and extended in the foreseeable future.

Among the many mind-blowing language-level enhancements twinBASIC brings to the table, are actual constructors – something Visual Basic developers that haven’t made the leap to VB.NET have only been able to simulate with factory methods.

Object Construction As We Know It

When we create a new instance of a class in VBA like this:

Dim thing As Something
Set thing = New Something

Several things appear to happen all at once, but in reality there’s a very specific sequence of events that unfolds when this Set assignment instruction is evaluated:

  • The right-hand side of the assignment is evaluated first; it’s a <new-expression>, so we’re spawning a New instance of the Something class.
  • As the object gets created and before the New operation returns to the caller, the Class_Initialize handler inside the Something class is invoked.
  • When the Class_Initialize handler returns, the New operation is completed and yields an object reference pointing to the new object.
  • The object reference gets copied to the thing variable, and member calls are now legal against it.

Classes in VBA/VB6 don’t really have a constructor – there’s this Class_Initialize handler where it’s appropriate to initialize private instance state, but it’s essentially a callback invoked from the actual “base class” constructor which is for a COM object and thus, without any parameters.

Default Instances & Factory Methods

Classes in VBA/VB6 have a hidden VB_PredeclaredId attribute that is False by default, but that can be set to True (either manually, or using Rubberduck’s @PredeclaredId annotation). Document modules like ThisWorkbook and Sheet1, but also UserForm modules, have that hidden attribute set to True.

Given a VB_PredeclaredId = True attribute, the runtime automatically creates an instance of the class that is named after the class itself, so the global UserForm1 identifier refers to the default instance of the UserForm1 class when it’s used as an object, and refers to the UserForm1 class type when it’s used as a type.

If you handle Class_Initialize in a class that has VB_PredeclaredId set to True, you’ll notice the handler is invoked the first time the class name is used as an object in code, i.e. just before the first reference to it. And if you handle Class_Terminate too, you’ll find the default instance gets destroyed as soon as it’s no longer needed (i.e. when nothing in-scope references it anymore).

We could treat default instances like global objects – that’s what they are. But globals and OOP don’t quite go hand-in-hand, for many reasons; there’s something icky about having magical implicit global objects spawned from the language runtime itself. However, if we treat this default instance as we would a type, then we can consider the members of a class’ default interface as members that belong to the type, and then we can define an explicit, separate interface that the class can implement to expose its actual intended instance functionality.

In many languages, members that belong to a type (rather than an instance of that type) are called “static”. In C# the static keyword is used for this, but in VBA/VB6 the Static keyword has a different meaning and there isn’t really anything “static” in Visual Basic. In .NET type-level members are identified with the Shared keyword, which was reserved in VB6 but never implemented. twinBASIC might end up changing that.

So by treating the default instance of a VBA/VB6 class as we would a static class (i.e. keeping the default instance stateless, that is, we don’t allow it to hold any state/variables), we can still adhere to OOP principles while leveraging language features that let us simulate static behavior, chiefly so by exposing factory methods that effectively simulate parameterized constructors – here for our Something example class module, with an added SomeProperty value being property-injected:

Option Explicit
Implements ISomething
Private mValue As Long

Public Function Create(ByVal Value As Long) As ISomething
    Dim Result As Something
    Set Result = New Something
    Result.SomeProperty = Value
    Set Create = Result
End Function

Public Property Let SomeProperty(ByVal RHS As Long)
    mValue = RHS
End Property

Private Property Get ISomething_SomeProperty() As Long
    ISomething_SomeProperty = mValue
End Property

The ISomething interface is only exposing SomeProperty with a Property Get accessor, which makes it read-only. That’s great when the code is written against ISomething, but then several things feel wrong:

  • We must expose Property Let (or Property Set) mutators on the class’ default interface to support the property-injection that happens in the factory method.
  • Rubberduck will (appropriately) flag the write-only properties and suggest to also expose a Property Get accessor, because it makes no sense to be able to write to a property when we can’t read back the value we just wrote.
  • Properties visible on the default interface look like mutable state that is accessible from the default instance. If nothing is done to actively prevent it, the default instance can easily become stateful… and then we’re looking at dreadful global state living in some class.
  • In order to have a clean interface without the Create member (and without the Property Let mutator), we must implement an explicit, non-default interface to expose the members we intend the calling code to work with.

Actual Constructors

With twinBASIC we get actual constructors, that can be parameterized (for classes we’re not making visible to COM clients, like VBA or VB6). A constructor is a special procedure named New (like the operator) whose sole purpose is to initialize the state of an object, so that the client code creating the object receives a fully-initialized object: the very same purpose as a default instance factory method.

We don’t need default instance factory methods in twinBASIC because we get to define actual constructors. This has several interesting and snowballing implications we’ll go over in a moment, but first we need to establish certain things about what constructors should and generally shouldn’t do.

  • DO take a constructor parameter for instance state that should be initialized by the caller.
  • DO initialize private instance fields from constructor parameters.
  • DO invoke any private initialization procedures that must be invoked for the object instance to be valid when the constructor returns.
  • DO validate all parameters and raise a run-time error given any invalid parameter value.
  • AVOID doing any kind of non-initialization work in a constructor.
  • AVOID invoking any procedure that performs non-initialization work from a constructor.
  • AVOID raising run-time errors in a constructor (other than from guard clauses validating parameter values).

For example, a DbConnection class might take a ConnectionString constructor parameter; the constructor stores the ConnectionString as instance-level state into a private field, then returns. Another method invoked by the consumer of the object invokes an Open method that reads the ConnectionString and proceeds to open the database connection. The DbConnection constructor could open the connection itself and that would probably be convenient for a lot of use cases… but it also couples constructing a DbConnection object with the action of connecting to a database. Problem is, when most people read this instruction:

Dim db As DbConnection = New DbConnection(connString)

…they expect to have simply instantiated a new DbConnection object – nothing less, nothing more. If merely creating an instance of an object can raise a run-time error because some network cable is unplugged, we’re looking at the consequences of having a badly side-effecting constructor.

Inline initialization notice the initial assignment is on the same line as the declaration? This syntax is legal in VB.NET, and twinBASIC adopted it as well. In VBA/VB6, we must separate the declaration (Dim) from the instruction performing the instantiation and assignment.

When we create a New object, we expect a new object to get created, and we expect that to be a very boring thing: it wouldn’t even occur to us that there’s the slightest chance anything could possibly go wrong with just spawning a new instance of a class.

That is why constructors should adhere as much as possible to the KISS principle: Keep It Stupid Simple. If something more complicated than creating objects and setting their properties happens in a constructor, consider refactoring it so that the actual work is triggered after the object is constructed.


The constructor is operating on the instance that’s in the process of being created. This makes them much simpler to reason about and to implement than, say, a Create factory method on the default interface of the class, because now we have access to the internal state of the object we’re constructing.

The implication of this, is that we no longer need to expose any Property Let mutators to property-inject the parameter values; instead we can now do constructor injection and directly assign the private fields, without needing to pollute the class’ default interface with members we don’t need.

Since we’re no longer polluting the class’ default interface with members we don’t need, we don’t have to extract an explicit interface to hide them anymore. And since constructors are invoked using the New operator, there’s no need to have a predeclared default instance of the class for a Create method to be accessible to the calling code.

Let’s see how tremendously twinBASIC constructors change everything, by contrasting a simple scenario in Classic VB with the same identical scenario in twinBASIC.

Simulating Constructors in Classic VB (VBA/VB6)

Here’s an example of how I’d write a class named Example, simulating a parameterized constructor:

Option Explicit
Implements IExample

Private Type TState
    Value1 As Long
    Value2 As String
End Type

Private This As TState

Public Function Create(ByVal Value1 As Long, ByVal Value2 As String) As IExample
    Dim Result As Example
    Set Result = New Example
    Result.Value1 = Value1
    Result.Value2 = Value2
    Set Create = Result
End Function

Public Property Get Value1() As Long
    Value1 = This.Value1
End Property
Public Property Let Value1(ByVal RHS As Long)
    This.Value1 = RHS
End Property

Public Property Get Value2() As String
    This.Value2 = RHS
End Property
Public Property Let Value2(ByVal RHS As String)
    This.Value2 = RHS
End Property

Private Property Get IExample_Value1() As Long
    IExample_Value1 = This.Value1
End Property

Private Property Get IExample_Value2() As String
    IExample_Value2 = This.Value2
End Property

Where IExample is another class module that only exposes Public Property Get Value1() As Long and Public Property Get Value2() As String. The calling code might look like this:

Dim x As IExample
Set x = Example.Create(42, "Test")
Debug.Print x.Value1, x.Value2

The x variable could legally be cast to an Example, and then x.Value = 10 would be legal too. But we code against abstract interfaces so we get IExample.Value1 and IExample.Value2 as get-only properties, and that’s the standard pattern I’ve now been using for several years in classic VB, to perform dependency injection and initialize objects with properties before they’re returned to the code that consumes them.

It works pretty nicely, with relatively few caveats (like casting to concrete /default interface being allowed, or Example.Value1 = 42 making the default instance stateful unless we actively guard against it) but it’s robust enough and makes a rather clean API that’s very suitable for OOP and testable code.

Are we in the default instance? Using the Is operator together with Me, we can test whether Me Is Example and determine whether we’re currently in the default instance of the Example class. So adding If Me Is Example Then Err.Raise 5 could raise a run-time error as a guard clause in the Property Let members, effectively protecting against misuse of the class/design.

Rubberduck has tooling that makes writing most of this code pretty much entirely automatic, but at the end of the day it’s still quite a lot of code – and the only reason we need it is because we can’t parameterize an actual constructor.

What if we could though?

Constructors in twinBASIC

The legacy-VB example above should compile just fine and work identically in twinBASIC, but the language offers new opportunities and it would be silly to ignore them. Now a twinBASIC executable doesn’t necessarily have the same concerns as a twinBASIC ActiveX DLL; in a standalone .exe project we can do anything we want, but if we’re making a library that’s intended to be used by legacy VB code we have to keep our intended COM-based client in mind.

COM clients (like VBA) don’t support parameterized constructors, so public/exposed classes (with VB_Exposed attribute set to True) should define a parameterless constructor. Either the legacy way, with a Class_Initialize handler:

Private Sub Class_Initialize()
End Sub

Or the twinBASIC way with an explicit, parameterless constructor:

Public Sub New()
End Sub

Similar to VB.NET, a constructor in twinBASIC is a Sub procedure named New in a class module. Ideally you want your constructor near the top of the module, as the first member of the class. Not for any technical reason really, but instinctively that’s where you expect a constructor to be.

A class’ parameterless constructor is dubbed a default constructor, because if no constructor is specified for a class, then an implicit one necessarily exists. If a class defines a parameterized constructor, it is understood as a class that requires the constructor arguments, and there is no implicit default/parameterless constructor then: a COM client could not create a new instance of such a class.

In twinBASIC, I’d write the above Example clas like this – note the absence of an IExample interface:

Class Example

    Private Type TState
        Value1 As Long
        Value2 As String
    End Type

    Private This As TState

    Public Sub New(ByVal Value1 As Long, ByVal Value2 As String)
        This.Value1 = Value1
        This.Value2 = Value2
    End Sub

    Public Property Get Value1() As Long
        Return This.Value1
    End Property

    Public Property Get Value2() As String
        Return This.Value2
    End Property

End Class

The calling code would now look like this:

Dim x As Example = New Example(42, "Test")
Debug.Print x.Value1, x.Value2

And it would have the exact same compile-time restrictions as the code written against the read-only IExample VBA/VB6 interface, only now thanks to parameterized construction we get to constructor-inject values and make the default interface of the Example class read-only, as we intended all along.

With twinBASIC we can still implement interfaces, but here an IExample get-only interface would be redundant. In a sense that brings most useful interfaces in twinBASIC closer to “pure” abstract interfaces, the kind that gets implemented by multiple classes: it would be suspicious to see a Thing class implement an IThing interface, for example, whereas in VBA/VB6 IThing would be an interface to work with a Thing instance when Thing is only used as a type as in myThing = Thing.Create(42).

Constructor Injection

In VBA/VB6 with factory methods we can achieve property injection – that is, using properties to “inject” dependencies into a class instance: the factory method invokes Property Let/Set procedures to do this. An example of property injection is how we set an ADODB.Connection‘s ConnectionString after instantiating the Connection object.

Dim Conn As Connection
Set Conn = New Connection
Conn.ConnectionString = "..."

That works, but then it’s not ideal because it induces temporal coupling in the client code: the caller must remember to set the ConnectionString property before they invoke the Open method.

In VBA/VB6 we can also do method injection by taking dependencies in as Sub or Function parameters. To stick with the ConnectionString example, method injection would be the Open method taking the connection string as a parameter:

Dim Conn As Connection
Set Conn = New Connection
Conn.Open "..."

That’s much better: it’s now impossible for the calling code to “forget” to supply a connection string. The Property Let ConnectionString member becomes somewhat of a wart, and should be removed.

Now method injection is great for something like a connection string and nothing needs it other than an Open method. If many members of a class seem to need the same parameters, there’s a good chance we can remove that parameter from all these members by promoting the dependency to instance level. In VBA/VB6 that would have to be through property injection. Say you have a class and many of its members require a Connection parameter: ask yourself whether it would make sense for that Connection to be a dependency of the class rather than a dependency of each one of its methods.

With twinBASIC we can now do constructor injection, and create objects that are valid as soon as they come into existence:

Dim Conn As Connection = New Connection("...")

If a Connection class takes a ByVal ConnectionString As String constructor argument, then the constructor can store that string in Private instance state, and we only need to expose a ConnectionString property (which would be get-only) if we have a reason to do so. The object is immediately usable, and there’s no temporal coupling anymore.

Eventually, twinBASIC could support ReadOnly modifiers for instance fields, that could enforce and guarantee immutability: the role of a constructor then boils down to assigning all the ReadOnly private instance fields.

By writing classes that take their instance-level dependencies as constructor arguments, we throw consumers of these classes into a pit of success where doing things wrong is much harder than doing them correctly – and that is the single best reason to leverage constructors when we can.

Globals and Ambient Context

Most of the time, we don’t need any global variables. State can usually be neatly encapsulated in an object, and a reference to this object can easily be passed as an argument to any procedure scope that needs it. But global scope is neither a necessary evil, nor necessarily evil. Like many things in programming, it’s a tool, and like many other tools, misusing it can cause pain.

The VBA code and host Excel workbook accompanying this article can be found on GitHub.

What is Global Scope?

When we declare a variable inside a procedure, we call it a “local variable” in reference to its scope being local to the procedure. “Module variables” are accessible within any procedure scope within the module they’re declared in. Public members of private modules (and Friend members of public modules) are only accessible within the project they live in, and Public members of public modules are global and can be accessed from other projects.

The different scopes of VBA: Global, project, module, and local.

Because in VBA class modules are private by default, and a public class is only PublicNotCreatable (as in, a referencing project cannot create a New instance of a class, factory methods must be provided), and also because “actually global” is in reality slightly more complicated than that (the VB_GlobalNamespace attribute is always going to be False for a VBA class), for the sake of simplicity when I talk about “global scope” and “globals” in this article, I’m treating global and project scopes as one and the same – but it’s important to know the difference, especially more so in scenarios where a VBA/Excel add-in/library is being referenced by other VBA projects, where a tidy public API is handy.

Rubberduck recommends using the Dim keyword only in local scope, and to use the Private keyword to declare module-level variables. It also recommends using Public over Global, because nothing is really “global” in VBA and that makes the deprecated keyword potentially confusing. The Global keyword really means Public in VBA, and should be avoided.

Picture the VBA runtime executing some macro procedure and some variable needs to be incremented by 1. Scope determines whether that variable identifier is referring to a local, module, or global declaration. Accessibility is how we use code to restrict scope, using keywords like Private, Public, or Friend: if the variable identifier exists in a public module but is declared with the Private keyword, then it’s inaccessible and not in scope for the procedure we’re in.

So in search for the variable’s declaration we look for a local scope declaration by that name. If there isn’t any, we look for a module scope declaration for that name. Not there? We look at everything we can see in project scope. If we still haven’t found it then, we look for the declaration in the referenced libraries and projects, in priority order (so, the VBA standard library, then the host application’s own object model library, then everything else).

That’s scoping. Scopes and accessibility are closely related, but they’re different things. Think of accessibility as a tool to shape your private and public interfaces and APIs, keeping in mind that in VBA all module members are implicitly Public unless their declaration states otherwise.

Globals and Testability

Global variables are very useful: having a piece of data that is accessible from anywhere in the code does have its advantages. Used wisely, globals can very elegantly address cross-cutting concerns. Instead of having every method responsible for its own logging, or instead of passing a Logger instance to every method, each scope can access a single global Logger object (or invoke the same Log utility procedure), and there really isn’t any problem with that, …until you realize that your unit tests are all needlessly writing logs to some file under C:\Dev\VBA because the global logger is doing its job whether or not the code invoking it is being executed from a test runner… and this is making tests run code that isn’t related to these tests’ purpose: if there’s a bug in the logger code, it’s a test about the logger code that should be failing, not every single other test that couldn’t care less for the logging functionality.

From a testability standpoint, code with global dependencies can be difficult, if not impossible to test. In the case of a global Logger dependency, the logger’s interface would need to expose some kind of “kill switch” that tests can invoke to disable logging… but then modifying an otherwise perfectly good interface for the sake of making the object aware of whether it’s being invoked from a test or not, isn’t ideal at all (we’ll see why in a bit).

This Logger is a good example of a legitimate global service, but it’s “user code” that could always be pragmatically modified to accommodate testing. What about code that depends on global-scope services that aren’t “user code”?

Treating the Excel Object Model as a Dependency

Imagine needing to write tests for user-defined functions (UDF) that store a number of values in a global Dictionary and then schedule a macro that then runs (asynchronously!) and sends these values over to some web API that returns data that then ends up on the worksheet, underneath the calling UDF; the functions have dependencies on Application.Caller and Application.OnTime: we don’t own the Application global object, and we can’t modify its code to accommodate testing – what then?

Writing tests for a UDF is normally trivial: the function takes inputs, computes a result, and then returns it. Tests can supply various inputs and run the function through all kinds of cases and assert that it handles them correctly, by simply comparing its return value with what’s expected, and exceptional edge cases can have tests asserting that the expected error is thrown.

Writing tests for a side-effecting UDF that temporarily stores data in global scope is a lot more challenging, for many reasons. Remember, unit tests:

  • Should reliably produce the same outcome regardless of any external factors;
  • Should be fast, and not involve any I/O or network activity;
  • Should be able to be executed individually or in any given order without affecting outcome;
  • Should be able to be executed concurrently (at least in theory – VBA won’t run concurrent code).

With state shared between the tests, we have to be careful to correctly setup and clean-up that state before & after each test, so that each test gets a fresh canvas in a controlled environment… and then we can live with VBA unit tests that would likely break if executed concurrently, because VBA can’t run them concurrently anyway.

Testing Untestable Things

Back to this not-so-crazy UDF scenario with the Application.OnTime hack: it wouldn’t be acceptable for a test to literally wait for Excel to decide it’s time to invoke a macro, not any more than a test should be sending any actual HTTP requests (although that would be very a good way to actually be testing an API’s rate limits and get acquainted with throttling, I guess), let alone parse and process an actual HTTP response.

Such a user-defined function involves too many moving parts soldered together to be testable: making the code testable involves making the parts moving parts again, and yes it involves a proverbial blowtorch and lots of proverbial sparks flying everywhere.

Refactoring code to make it testable is a lot of fun, but the first step is, ideally, to fully grasp what’s going on and why.

If you aren’t familiar with using Application.OnTime in user-defined functions (only indirectly, because Application.OnTime calls, like run-time errors and many other members in the Excel object model, get “swallowed” when Excel is evaluating a UDF), it’s a pretty cool process that goes like this:

The calling cell contains the UDF’s return value just before the macro gets asynchronously invoked and produces its own output.

So if a UDF stored its arguments as key/value pairs in a global-scope dictionary, if all goes well and according to plan, the macro that runs a moment later gets to consume this data.

By storing the Application.Caller cell object reference in global scope, the side-effecting macro gets to know where to put its results table. There’s always the possibility that a second UDF overwrites this global state during the split-second between the moment a first UDF writes it and the moment the scheduled asynchronous read of this global state actually happens: it’s important to keep in mind that Ambient Context does not inherently address this particular problem; the state is still global and mutable from anywhere in the code, and there is never any guarantee that any scope will run to completion before the VBA runtime decides it’s an asynchronous callback’s turn to run.

The Application.Caller member isn’t going to return a Range reference when it’s not a worksheet cell invoking the function, we can’t afford to wait for Application.OnTime, and we’d like to avoid actually invoking any Win32 API functions during a test. That UDF simply isn’t testable as-is.

The solution is to introduce an abstraction to wrap the Application members we need, and make the side-effecting UDFs depend on that abstraction instead of invoking Application members directly.

AbstractionThe untestable code might look something like this:

Public Function SideEffectingUDF(ByVal FirstParameter As String, ByVal SecondParameter As Long) As Variant
    Set SomeGlobalRange = Application.Caller.Offset(RowOffset:=1)
    With SomeGlobalDictionary
        .Add "FirstParameter", FirstParameter
        .Add "SecondParameter", SecondParameter
    End With
End Function

Where ScheduleMacro involves a Win32 API call to schedule the execution of an Execute procedure that handles the Application.OnTime scheduling of the actual side-effecting procedure.

We want to be able to write a test that invokes this SideEffectingUDF function, and determines whether Application.Caller was invoked: Application.Caller is a dependency here, and for the test to be able to fulfill its purpose we must find a way to inject the dependencies so they can be controlled by the test, from outside the function.

Note how narrow such a test would be: it asserts that the UDF gets the Application.Caller reference, nothing more. Other tests would be similarly narrow, but for other things, and we don’t want a failing Application.Caller member call to interfere with these other tests by throwing error 91 before the test gets to do its thing. Whether or not we need to know if a UDF does or does not invoke Application.Caller, we still need a way to abstract the dependency away, to stub it.

You may be thinking “oh that’s easy” and be tempted go down this path:

Public Function SideEffectingUDF(ByVal FirstParameter As String, ByVal SecondParameter As Long) As Variant
    If TypeOf Application.Caller Is Excel.Range Then
        ' caller is a worksheet cell
        Set ThatGlobalCell = Application.Caller.Offset(RowOffset:=1)
        With ThatGlobalDictionary
            .Add "FirstParameter", FirstParameter
            .Add "SecondParameter", SecondParameter
        End With
        ScheduleMacro "SideEffectingMacro"
        ' caller is a unit test
        Set ThatGlobalCell = Sheet1.Cells(1, 1) ' tests can read as "Application.Caller was invoked"
        With ThatGlobalDictionary
            .Add "FirstParameter", FirstParameter
            .Add "SecondParameter", SecondParameter
        End With
        SideEffectingUDF = True ' tests can read this as "macro was scheduled"
    End If
End Function

While it does solve the problem of avoiding to involve Application.Caller and actually scheduling the macro in tests, there are several reasons why this is a terrible idea:

  • Function now has a higher Cyclomatic Complexity metric by virtue of now needing more execution paths to accomplish the same thing: the code is objectively and measurably more complex now, on top of being repetitive (copying & pasting any code is usually a sign something is off!).
  • Tests are no longer executing the same code as normal execution does, which means tests are now testing code that only exists because there are tests: the normal execution path remains untested, and that makes the tests worthless busy-work.
  • Tests now need to be making assumptions about how the function is implemented, which effectively casts the code into concrete instead of making it simpler & safer to modify.
  • Dependencies should be abstractions, and code should be working with these abstractions without regards to their actual implementation: code that acts differently when the runtime type of an abstraction is X vs when it’s Y, violates the Liskov Substitution Principle, the “L” of “SOLID” that essentially states that all implementations of a given abstraction should be treated the same.

The killer is the second bullet: if the sole purpose of a test is to determine whether Application.Caller was invoked, and the UDF says “oh we’re in a test, here yeah everything is all right, see”, then a UDF that does nothing but returning True would pass that test, and that is why the test is useless, as is the code duplication.

When we write a test whose purpose is to determine whether the Application.Caller dependency was invoked, the test should FAIL when it isn’t, otherwise that test is just as good as a deleted one.

Now picture the UDF looking like this instead:

Public Function SideEffectingUDF(ByVal FirstParameter As String, ByVal SecondParameter As Long) As Variant
    With AppContext.Current
        Set .Target = .Caller.Offset(RowOffset:=1)
        .Property("FirstParameter") = FirstParameter
        .Property("SecondParameter") = SecondParameter
    End With
End Function

The UDF now only has one dependency, AppContext.Current, which is global state by virtue of being accessible from the default instance of the AppContext class; we’re tightly coupled with the AppContext class, but only because we specifically want to access global state in a controlled manner, and the rest of the function is working against the IAppContext abstraction. The state that was formerly a Range and a Dictionary globally-scoped declaration is now properly encapsulated in an object, and the “current” AppContext is coming into existence from outside the UDF scope (but still from within our own code), which is exactly what we want: now unit tests get to inject a TestContext instead of manipulating global state.

So how do we get there?


The basic idea is to pull our dependencies from global scope, encapsulate them in a class module, …and then making an instance of that class an “ambient context” that’s still globally accessible, but that introduces the necessary abstraction needed to make that UDF fully testable.

We want to leverage the default instance of the AppContext class, so we’re going to need an AppContext class with a @PredeclaredId annotation and a Current property getter that returns some IAppContext instance. If you’re familiar with factory methods this will feel a bit like something you’ve already seen:

Option Explicit
Implements IAppContext
Private Type TState
    Factory As IAppContextFactory
    Current As IAppContext
End Type
Private This As TState
'@Description "Gets the current (or default) context."
Public Property Get Current() As IAppContext
    Errors.GuardNonDefaultInstance Me, AppContext, TypeName(Me)
    If This.Current Is Nothing Then
        Set This.Current = This.Factory.Create
        Errors.GuardNullReference This.Factory, TypeName(Me), "IAppContextFactory.Create returned Nothing."
    End If
    Set Current = This.Current
End Property
Private Property Get IsDefaultInstance() As Boolean
    IsDefaultInstance = Me Is AppContext
End Property
Private Sub Class_Initialize()
    If IsDefaultInstance Then
        'must initialize context with sensible defaults:
        Set This.Factory = New AppContextFactory
        Set This.TimerProvider = New TimerProvider
        Set This.Properties = New Scripting.Dictionary
        'we want all instances to have the same provider instance:
        Set This.TimerProvider = AppContext.TimerProvider
    End If
End Sub

We don’t normally want Property Get procedures to be side-effecting, but with an Ambient Context what we want is to yield a cached instance of the context class, so when no instance already exists, the getter caches the created object so it’s readily available next time, making it accessible from anywhere in the project (aka “global”).

Abstract Factory

The default instance of the AppContext class does not know what the actual runtime type of the Current context is, and this polymorphism is the cornerstone making it all work: the Current property getter is responsible for caching the new context instance, but not for actually creating it. That’s the job of an abstract factory (the IAppContextFactory dependency) that we conveniently initialize to a concrete factory type that creates instances of… the AppContext class.

Why involve an abstract factory to create an instance of the class we’re in, you might ask? Because that’s only the default implementation, and with ability to Set the Factory reference from outside the class, tests can inject a different factory implementation, say, this one named TestContextFactory:

'@Folder "Tests.Stubs"
'@ModuleDescription "A factory that creates TestContext instances."
Option Explicit
Implements IAppContextFactory
Private Function IAppContextFactory_Create() As IAppContext
    Set IAppContextFactory_Create = New TestContext
End Function

Meanwhile the actual UDFs would be using this AppContextFactory implementation by default:

'@Folder "AmbientContext"
'@ModuleDescription "A factory that creates AppContext instances."
Option Explicit
Implements IAppContextFactory
Private Function IAppContextFactory_Create() As IAppContext
    Set IAppContextFactory_Create = New AppContext
End Function

The AppContext.Current property will happily cache an instance of any class whatsoever, as long as it implements the IAppContext interface. The abstract factory pattern allows us to spawn an instance of a class at run-time, of which we don’t necessarily know the actual “concrete” type at compile-time.

In other words just by reading the UDF code, there is no way to tell whether AppContext.Current is going to be an AppContext or a TestContext instance, and that is exactly what we want.

What this abstraction achieves, is the decoupling that is necessary for a test to be able to inject a TestContextFactory and take control of everything UDFs can do with an IAppContext object.

Context State

We know the context needs to wrap Application.Caller and Application.OnTime functionality. We know we need a Target cell, we need some Properties in an encapsulated Scripting.Dictionary. If we crammed all that into a single interface, we would get a somewhat crowded IAppContext interface that doesn’t quite adhere to the Interface Segregation Principle and Open/Closed Principle guidelines.

By abstracting away the macro-scheduling functionality into its own IAppTimer interface, and making that interface an abstract dependency of the context class, we can stub that abstract dependency and write tests for the logic of the context class itself. Without this extra step, the context can be stubbed to test the code that uses it, but the macro-scheduling bits would remain untestable.

Treating IAppTimer as a dependency of the context makes the IAppContext interface look like this:

'@Folder "AmbientContext.Abstract"
'@ModuleDescription "Encapsulates the data and macro invocation mechanism for a side-effecting UDF."
Option Explicit
'@Description "Gets the cell that invoked the currently-running user-defined function (UDF), if applicable; Nothing otherwise."
Public Property Get Caller() As Range
End Property
'@Description "Gets or sets the target reference cell that the side-effecting macro shall use."
Public Property Get Target() As Range
End Property
Public Property Set Target(ByVal Value As Range)
End Property
'@Description "Gets or sets a named value representing data passed between the UDF and the side-effecting macro."
Public Property Get Property(ByVal Name As String) As Variant
End Property
Public Property Let Property(ByVal Name As String, ByVal Value As Variant)
End Property
'@Description "Gets an array of all property names."
Public Property Get Properties() As Variant
End Property
'@Description "Gets or sets the IAppTimer dependency."
Public Property Get Timer() As IAppTimer
End Property
Public Property Set Timer(ByVal Value As IAppTimer)
End Property
'@Description "Clears all held state."
Public Sub Clear()
End Sub

Note that we’re not exposing the dictionary itself: rather we expose an indexed property to get/set the key/values, then by exposing the dictionary keys, the calling code gets to do everything it needs to do, without ever directly interacting with a Scripting.Dictionary, a bit as if the AppContext class were a custom collection.

Now, there’s something special about the IAppTimer dependency: we absolutely cannot have each context instance spawn timers willy-nilly, because a leaking Win32 timer is a nice way to send Excel up in flames. Yet, we need each context instance to be able to access the same IAppTimer reference.

A good way to solve this is by introducing a Provider mechanism. The interface looks like this:

'@ModuleDescription "A service that ensures all clients get the same IAppTimer instance."
Option Explicit
'@Description "Gets an IAppTimer instance."
Public Property Get Timer() As IAppTimer
End Property

What I’m calling a “provider” here is exactly the same mechanism that provides the IAppContext instance (a Property Get procedure that gets a cached object or creates the object and caches it), except no abstract factory needs to get involved here. The class also makes a very convenient place to put the name of the Win32 callback macro procedure:

Option Explicit
Implements ITimerProvider
Private Const MacroName As String = "Execute"
Private Property Get ITimerProvider_Timer() As IAppTimer
    Static Instance As AppTimer
    If Instance Is Nothing Then
        Set Instance = New AppTimer
        Instance.MacroName = MacroName
    End If
    Set ITimerProvider_Timer = Instance
End Property

TimerProvider the only object that creates a New AppTimer: as a result, every AppContext instance created from this factory is going to use the same IAppTimer reference, and if we need to write tests for AppContext we can inject a TestTimerProvider that returns a TestTimer.

Note that the “provider” mechanism is an implementation detail of AppContext: the TestContext doesn’t need this, because it just initializes itself with a TestTimer, while AppContext initializes itself with a TimerProvider that gets the IAppTimer instance. Being an implementation detail, there’s no ITimerProvider dependency on the abstract interface.

The Tests

The previously-untestable user-defined functions now look like this:

Public Function TestUDF(ByVal SomeParameter As Double) As Boolean
    On Error GoTo CleanFail
    With AppContext.Current
        Set .Target = .Caller.Offset(RowOffset:=1)
        .Property("Test1") = 42
        .Property("Test2") = 4.25 * SomeParameter
    End With
    TestUDF = True
    Exit Function
    TestUDF = False
    Resume CleanExit
End Function

The code isn’t very far off from the original, but now we can write a test that passes when a UDF invokes the Caller member; when the UDF is invoked from a worksheet cell, IAppContext.Caller returns the Range reference returned by Application.Caller; when the exact same code is invoked from a test, IAppContext.Caller returns a bogus/test cell reference.

Similarly, when a UDF invokes IAppTimer.ExecuteMacroAsync, a Win32 API call schedules the execution of a callback macro that itself invokes Application.OnTime to schedule the execution of a side-effecting macro that can consume the state and alter the target range and worksheet; when the exact same code is invoked from a test, IAppTimer.ExecuteMacroAsync simply notes that it was invoked, …and does nothing else.

This test passes when IAppTimer.ExecuteMacroAsync is invoked from a UDF, and would fail if the UDF didn’t invoke it:

Private Sub TestUDF_SchedulesMacro()
    'inject the test factory:
    Set AppContext.Factory = New TestContextFactory
    'get the test context:
    Dim Context As TestContext
    Set Context = AppContext.Current
    'test factory already stubbed the timer:
    Dim StubTimer As TestTimer
    Set StubTimer = AppContext.Current.Timer
    'run the UDF:
    Dim Result As Boolean
    Result = Functions.TestUDF(0)
    'Assert that the UDF has invoked IAppContext.ScheduleMacro once:
    Const Expected As Long = 1
    Assert.AreEqual Expected, StubTimer.ExecuteMacroAsyncInvokes, "IAppTimer.ExecuteMacroAsync was invoked " & StubTimer.ExecuteMacroAsyncInvokes & " times; expected " & Expected
End Sub


Ambient Context is a fantastic tool to address cross-cutting concerns and leverage global scope in a way that does not hinder testing. It’s also useful for storing state and dependencies that would otherwise be held in global scope, when passing that state and dependencies as normal parameters isn’t possible.

That makes it a somewhat dangerous pattern: one must keep in mind that the state is still global, and globals that don’t need to be global, should not be global. By defining an explicit interface for the context (like IAppContext), we not only end up with neat abstractions: we also make it harder for the context interface to grow new members and for the class to become an over-engineered Globals.bas module.

Interfaces shouldn’t be designed to change. In .NET the IDisposable interface only mandates a parameterless Dispose method; IEquatable is all about an Equals method. A factory interface shouldn’t need more than a carefully parameterized Create method that only takes arguments that can’t be dependencies of the factory instance: we want to avoid modifying existing interfaces as much as possible, and since none of us can really predict the future… the best way to do that is to keep interfaces as slim as possible. Cohesion is what we’re after: a module that is cohesive will feel like everything is exactly where it should be.

If the members of a module don’t feel like they’re a cohesive and complete group of closely related methods, there’s a greater chance that more members need to be added in the future – and you will want to avoid that. Of course the “and complete” part can mean a few growing pains, but in general naming things is a great way to avoid the pitfalls of treating the context as some “state bag” where we just lazily stuff state without thinking it through. In that sense AppContext is probably one of the worst possible names for this: perhaps a FunctionContext that only exposes the Caller member would be a cleaner approach?

In the real world, ambient context is for things like System.Threading.Thread.CurrentThread in .NET: it’s very specialized, with a very specific purpose, and we don’t see it very often. Authorization mechanisms might use it too.

In VBA-land, I’ve never once needed to implement it until I came upon this side-effecting UDF scenario needing unit tests; macros are definitely much simpler to refactor for testability!

WorksheetFunction and Errors

Using Excel worksheet functions taps into the native calculation engine: using Excel’s very own MATCH function instead of writing a lookup loop or otherwise reinventing that wheel every time makes a lot of sense if your project is hosted in Excel in the first place, or if you’re otherwise referencing the Excel type library.

You may have seen it look like this:

Dim result As Variant
result = Application.WorksheetFunction.Match(...)

Or like this:

Dim result As Variant
result = Application.Match(...)

You’ve tested both, confirmed they both work, and might be using them interchangeably in code, and all is well… until it isn’t anymore and you’re facing a cryptic run-time error:

The canned default message for error 1004 is a meaningless “Application-defined or object-defined error”. The message you get for a worksheet function that raises this error is arguably even more confusing: “unable to get the {function name} property of the WorksheetFunction class”.

What could this nonsense possibly mean? First, we need to understand that we’re looking at a templated error message where “property” has to have been mistakenly made part of the templated string – because we’re really looking at a function member here, but even reading the message with the correct kind of member makes no sense… until we read it as simply “the worksheet function returned a worksheet error value“: if we typed that exact same invocation in an actual worksheet cell formula, Excel’s own error-handling would do the same, and the cell would contain an #N/A error:

When MATCH or VLOOKUP fails in a cell, that cell’s error value propagates to any caller/cell that references it. When you invoke these functions from VBA code, it’s into your VBA code that these errors propagate now.

Given bad arguments or a failed lookup, Application.WorksheetFunction.Match and Application.Match will behave very differently. Let us understand why and how. Note I’m going to be using a VLookup function here, but Index or Match wouldn’t be any different, and everything here holds true for any other worksheet function, from the simplest Sum to the most obscure financial function nobody ever used.

The two forms are not interchangeable, and it’s important to understand the difference!

Early Bound: Errors are Raised

When you invoke WorksheetFunction members, errors are raised as VBA run-time errors. This means a failed lookup can be caught with an On Error statement, as would any other run-time error.

  On Error GoTo LookupFailed
  Debug.Print Application.WorksheetFunction.VLookup(...)
  Exit Sub
  Debug.Print "..."
  Resume Next

When you type these member calls, you know you’re typing early-bound code because IntelliSense (its ancestor, anyway) is listing that member in an inline dropdown:

VLookup is a member of the object returned by the WorksheetFunction property of the Application object.

The implication is that the function is assumed to “just work”: if using that same function with these same parameter values in an actual worksheet formula results in a #REF!, #VALUE!, #N/A, #NAME?, or any other Variant/Error value… then the early-bound WorksheetFunction equivalent raises run-time error 1004.

This VBA-like behavior is very useful when any failure of the worksheet function needs to be treated as a run-time error, for example when we are expecting the function to succeed every time and it failing would be a bug: throwing an error puts us on an early path to recovery.

Sometimes though, we don’t know what to expect, and a worksheet function returning an error is just one of the possible outcomes – using error handling in such cases would amount to using error handling for control flow, and that is a design smell: we should be using runtime errors for exceptional things that we’re not expecting. When a worksheet function can fail as part of normal execution, we have other options.

Late Bound: Errors are Values

When you invoke worksheet functions using late-bound member calls against an Excel.Application object, when a function fails, it returns an error code.

Dim result As Variant
result = Application.VLookup(...)

It’s important to understand that the Variant type means nothing in particular until it gets a subtype at runtime; result is a Variant/Empty until the assignment succeeds – when it does result might be a Variant/Double if the value is numeric; if the lookup failed, instead of raising a run-time error result will now be a Variant/Error value.

Operations Involving Variant/Error: Removing Assumptions

Because a failed late-bound WorksheetFunction returns an error value, it’s easy to forget the data type of the result might not be convertible to the declared type, so the first opportunity for things to go wrong materializes if we simply assume a non-error result by declaring a non-Variant data type for the variable that is being assigned with the function’s result:

Dim result As Long 'assumes a successful lookup...
result = Application.VLookup(...) 'runtime error 13 when lookup fails!

So we soon start systematically assigning these results to a Variant:

Dim result As Variant
result = Application.VLookup(...)

…only to find that all we did was moving the type mismatch error further down, here:

If result > 0 Then 'runtime error 13 when result is Variant/Error!

The first thing we should do with a Variant, is to remove any assumptions about its content. The VBA.Information.IsError function returns True given a Variant/Error, and we must use it to correctly remove assumptions about what’s in this result variable:

Dim result As Variant
result = Application.VLookup(...)
If IsError(result) Then
    'lookup failed

    'lookup succeeded

End If

Inside the lookup failed conditional block, result is a Variant/Error value that can only be compared against another Variant/Error value – involving result in an operation with any other runtime type will throw a type mismatch error.

Using the VBA.Conversion.CVErr function, we can convert a Long integer into a Variant/Error value; the Excel object model library includes named constants for each type of worksheet error, so we can use them with the CVErr function to refine our knowledge of what’s in result, if we need anything more detailed than “something went wrong”:

Dim result As Variant
result = Application.VLookup(...)
If IsError(result) Then
    'lookup failed
    Select Case result
        Case CVErr(xlErrNA)
            'result is a #N/A error: value wasn't found in the lookup range

        Case CVErr(xlErrRef)
            'result is a #REF! error: is the lookup range badly defined?

        Case Else
            'result is another type of error value

    End Select

    'lookup succeeded

End If

By systematically treating the result of a late-bound Application.{WorksheetFunction} call as a potential Variant/Error value, we avoid assuming success and handle a bad result without exposing our “happy path” to type mismatch errors; we then use If...Else...Then standard control flow statements to branch execution differently depending on the outcome, using standard On Error statements / error handling for the exceptional situations that could arise beyond these worksheet errors we’re already accounting for.

Other Variant/Error Pitfalls

The IsError function isn’t just useful to determine whether a late-bound WorksheetFunction call returned a usable value or not. The function returns True given any Variant/Error value, which makes it the perfect tool to identify worksheet cells that contain values that aren’t usable either.

Dim cell As Range
Set cell = Sheet1.Range("A1")
If cell.Value > 42 Then 'assumes cell.Value can be compared to 42!
End If

VBA code often assumes cells contain valid values, and whenever that assumption is broken, a type mismatch error occurs. Unless the cell value was written by the same VBA code, it’s never really safe to assume a worksheet cell contains what the code expects it to contain. Using the IsError function we remove such assumptions and make the code more resilient:

Dim cell As Range
Set cell = Sheet1.Range("A1")
If Not IsError(cell.Value) Then
    If cell.Value > 42 Then
    End If
    MsgBox cell.Address(External:=True) & " contains an unexpected value."
End If

A Variant/Error value can spell trouble in many other ways. Sometimes it’s an implicit conversion to String that causes the type mismatch:

Dim cell As Range
Set cell = Sheet1.Range("A1")
MsgBox cell.Value 'assumes cell.Value can be converted to a String!

Implicit conversions can be hard to spot, but if your code is blowing up with a type mismatch error involving the value of a worksheet cell, or a value returned by a worksheet function, then that’s where you need to look.

Synchronizing your VBA project with files in a folder

Sync Project commands in the Code Explorer context menu.

VBA code being embedded in a host document might be very practical for certain aspects of both development and deployment, but let’s face it, it also makes using source control (e.g. git, SVN, mercurial, etc.) with VBA projects rather frustrating. As a developer, committing source code to a repository is usually a very simple task, because the code files live in the file system, and git can track changes and additions. With VBA, we commit the code that’s exported on the file system, the host document may contain different code, and merging remote changes implies exporting your code again, working out any merge conflicts with the exported code, then re-importing the merged changes into the host document.

Which wouldn’t be so bad… if the VBE had a nice way of exporting more than one single file at a time, and if importing files had an option to replace modules when they already exist… instead of importing the module with a “1” suffix as if that were something anyone ever needed to do!

Did You Know?
The VBE’s “Import File…” command doesn’t make it very obvious, but it does support importing multiple files at once. Simply select multiple files when prompted for what file to import!
Another little known feature of the VBE (one of the few Rubberduck hasn’t enhanced yet) is that its Project Explorer toolwindow is a drag-and-drop destination that can accept files you dragged from the Windows Explorer (⊞+E).

Rubberduck’s Export Project… command prompts for a folder, and then proceeds to export all modules there – overwriting any existing files in that folder. By default, the hotkey for that command is Ctrl+Shift+E, but it can be reconfigured to any key combination you like.

The context menu of Rubberduck’s Code Explorer toolwindow has a Sync Project sub-menu that offers two commands:

  • Update Components from Files…
  • Replace Contents from Files…

Rubberduck in general needs more documentation, but exactly what these “Sync Project” commands do is something that goes well beyond just using Rubberduck and they really deserve all the attention they can get, since they exist to facilitate an actual development workflow that looks something like this:

Import source files; make code changes; export source files; commit, push, pull, merge; rinse & repeat!

Update Components

This command prompts for source code files to import into your project.

  • If the project already contains a module with the same name as one of the imported files, the module is considered the same, and replaced with the imported version.
  • If the project does not already contain the imported modules, they’re simply added to the project.
  • If the project contains modules with different names than the imported files, these modules remain in the project.

Replace Contents

This command also prompts for source code files to import into your project, but the selected files will replace everything in the current project. Because this command is potentially destructive, a confirmation is required.

  • The entire project becomes the selected files.
  • If the project contained (non-document) modules before, they are removed before the import is performed.

We have a number of open issues (here, here, and here) about getting the “export project” command to take the @Folder annotations into account, and transpose the virtual folder hierarchy into an actual folder hierarchy on the file system, which would play nicely with version control and would help better organize a VBA repository.