Making MVVM Work in VBA Part 1 – Testing

I have recently written (100% VBA) a proof-of-concept for a Model-View-ViewModel (MVVM) framework, and since the prototype works exactly as needed (with some rough edges of course)… I’ve decided to explore what Rubberduck can do to make MVVM fully supported, but going down that path poses a serious problem that needs a very good and well thought-out solution.

A Vision of a Framework

When you start a new project in Visual Studio (including 6.0 /VB6), the IDE prompts for a project type, essentially asking “what are we building today?

In VBA the assumption is that you just want to write a bit of script to automate some document manipulation. And then the framework so to speak, is the VBA Standard Library: functions, methods, constants, and actual objects too; all globally-scoped for convenience and quick-and-easy access: a fully spelled-out VBA.Interaction.MsgBox function call is a rare sight! Combined with the nonexistence of namespaces, the flip side is that the global scope is easily polluted, and name collisions are inevitable since anything exposed by any library becomes globally accessible. This makes fully-qualified global function calls appear sporadically sprinkled in the code, which can be confusing. I digress, but what I mean to get at is that this is part of what made Microsoft make the shift to the .NET platform in the early 2000’s, and eventually abandon the Visual Basic Editor to its fate. The COM platform and Win32 API was the framework, and Win32 programming languages built on top of that.

This leaves two approaches for a vision of a “framework” for VBA:

  • Package a type library and ship it.
    • Pros: any COM-visible library will work, can be written in .NET
    • Cons: projects now have a hard dependency on a specific type library; updating is a mess, etc.
  • Embed the framework into VBA projects, pretty much like JavaScript does.
    • Pros: devs are in charge of everything, framework is 100% VBA and inherently open-source, updating is essentially seamless for any non-breaking change, no early-bound dependencies, graceful late-bound degradation, etc.
    • Cons: VBA devs and maintainers that aren’t using Rubberduck will be massively lost in the source code (framework would cleanly leverage @Folder annotations), but then when the host application allows it this could be mitigated by embedding the code into its own separate VBA project and reference it from other projects (e.g. ship an Excel add-in with the framework code your VBA project depends on).

I think I’m slightly biased here, but I think this rules out the type library approach regardless. So we need a way to make this work in VBA, with VBA source code that lives in a GitHub repository with vetted, trusted content.

Where Rubberduck fits in

Like Visual Studio, Rubberduck could prompt VBA devs with “what are we building today?” and offer to pull various “bundles” of modules from this GitHub repository into the active project. Rubberduck would request the available “bundles” from, which would return with “bundle metadata” describing each “package” (is “nugget” forbidden to use as a name for these / play on “nuget” (the package manager for .NET)?), and then list them in a nice little dialog.

The “nugget” metadata would include a name, a description, and the path to each file to download for it. Every package would be the same “version”, but the tool could easily request any particular “tag” or “release” version, and/or pull from “main” or from “next” branches, and the source code / framework itself could then easily be a collaborative effort, with its own features and projects and milestones and collaborators, completely separate from the C# Rubberduck code base.

This complete decoupling from Rubberduck means you don’t need to use Rubberduck to leverage this VBA code in your VBA projects, and new tags / “releases” would be entirely independent of Rubberduck’s own release cycles. That means you’re using, say, future-Rubberduck 2.7.4 and the “nuggets” feature offers “v1.0 [main]” and “v1.1 [next]”; one day you’re still using Rubberduck 2.7.4 but now you get “v1.1 [main]”, “v1.0”, and “v1.2 [next]” to chose from, and if you updated the “nuggets” in your project from v1.0 to v1.1 then Rubberduck inspections would flag uses of any obsolete members that would now be decorated with @Obsolete annotations… it’s almost like this annotation was presciently made for this.

But before we can even think of implementing something like this and make MVVM infrastructure the very first “nugget”, we need a rock-solid framework in the first place.

Unit Tests

I had already written the prototype in a highly decoupled manner, mindful of dependencies and how things could later be tested from the outside. I’m very much not-a-zealot when it comes to things like Test-Driven Development (TDD), but I do firmly believe unit tests provide a solid safety net and documentation for everything that matters – especially if the project is to make any kind of framework, where things need to provably work.

And then it makes a wonderful opportunity to blog about writing unit tests with Rubberduck, something I really haven’t written nearly enough about.

Tests? Why?!

Just by writing these tests, I’ve found and fixed edge-case bugs and improved decoupling and cohesion by extracting (and naming!) smaller chunks of functionality into their own separate class module. The result is quite objectively better, simpler code.

Last but not least, writing testable code (let alone the tests!) in VBA makes a great way to learn these more advanced notions and concepts in a language you’re already familiar with.

If you’re new to VBA and programming in general, or if you’re not a programmer and you’re only interested in making macros, then reading any further may make your head spin a bit (if that’s already under way… I’m sorry!), so don’t hesitate to ask here or on the examples repository on GitHub if you have any questions! This article is covering a rather advanced topic, beyond classes and interfaces, but keep in mind that unit testing does not require OOP! It just so happens that object-oriented code adhering to SOLID principles tends to be easily testable.

This is an ongoing project and I’m still working on the test suite and refactoring things; I wouldn’t want to upload the code to GitHub in its current shape, so I’ll come back here with a link once I have something that’s relatively complete.

Where to Start?

There’s a relatively small but very critical piece of functionality that makes a good place to begin in the MVVM infrastructure code (see previous article): the BindingPath class, which I’ve pulled out of PropertyBinding this week. The (still too large for its own good) PropertyBinding class is no longer concerned with the intricacies of resolving property names and values: both this.Source and this.Target are declared As IBindingPath in a PropertyBinding now, which feels exactly right.

The purpose of a BindingPath is to take a “binding context” object and a “binding path” string (the binding path is always relative to the binding context), and to resolve the member call represented there. For example, this would be a valid use of the class:

Dim Path As IBindingPath
Set Path = BindingPath.Create(Sheet1.Shapes("Shape1").TextFrame.Characters, "Text")

This Path object implements TryReadPropertyValue and TryWritePropertyValue methods that the BindingManager can invoke as needed.

'@Folder MVVM.Infrastructure.Bindings
'@ModuleDescription "An object that can resolve a string property path to a value."
Option Explicit
Implements IBindingPath
Private Type TState
    Context As Object
    Path As String
    Object As Object
    PropertyName As String
End Type
Private This As TState
'@Description "Creates a new binding path from the specified property path string and binding context."
Public Function Create(ByVal Context As Object, ByVal Path As String) As IBindingPath
    GuardClauses.GuardNonDefaultInstance Me, BindingPath, TypeName(Me)
    GuardClauses.GuardNullReference Context, TypeName(Me)
    GuardClauses.GuardEmptyString Path, TypeName(Me)
    Dim Result As BindingPath
    Set Result = New BindingPath
    Set Result.Context = Context
    Result.Path = Path
    Set Create = Result
End Function
'@Description "Gets/Sets the binding context."
Public Property Get Context() As Object
    Set Context = This.Context
End Property
Public Property Set Context(ByVal RHS As Object)
    GuardClauses.GuardDefaultInstance Me, BindingPath, TypeName(Me)
    GuardClauses.GuardNullReference RHS, TypeName(Me)
    GuardClauses.GuardDoubleInitialization This.Context, TypeName(Me)
    Set This.Context = RHS
End Property
'@Description "Gets/Sets a string representing a property path against the binding context."
Public Property Get Path() As String
    Path = This.Path
End Property
Public Property Let Path(ByVal RHS As String)
    GuardClauses.GuardDefaultInstance Me, BindingPath, TypeName(Me)
    GuardClauses.GuardEmptyString RHS, TypeName(Me)
    GuardClauses.GuardDoubleInitialization This.Path, TypeName(Me)
    This.Path = RHS
End Property
'@Description "Gets the bound object reference."
Public Property Get Object() As Object
    Set Object = This.Object
End Property
'@Description "Gets the name of the bound property."
Public Property Get PropertyName() As String
    PropertyName = This.PropertyName
End Property
'@Description "Resolves the Path to a bound object and property."
Public Sub Resolve()
    This.PropertyName = ResolvePropertyName(This.Path)
    Set This.Object = ResolvePropertyPath(This.Context, This.Path)
End Sub
Private Function ResolvePropertyName(ByVal PropertyPath As String) As String
    Dim Parts As Variant
    Parts = Strings.Split(PropertyPath, ".")
    ResolvePropertyName = Parts(UBound(Parts))
End Function
Private Function ResolvePropertyPath(ByVal Context As Object, ByVal PropertyPath As String) As Object
    Dim Parts As Variant
    Parts = Strings.Split(PropertyPath, ".")
    If UBound(Parts) = LBound(Parts) Then
        Set ResolvePropertyPath = Context
        Dim RecursiveProperty As Object
        Set RecursiveProperty = CallByName(Context, Parts(0), VbGet)
        If RecursiveProperty Is Nothing Then Exit Function
        Set ResolvePropertyPath = ResolvePropertyPath(RecursiveProperty, Right$(PropertyPath, Len(PropertyPath) - Len(Parts(0)) - 1))
    End If
End Function
Private Property Get IBindingPath_Context() As Object
    Set IBindingPath_Context = This.Context
End Property
Private Property Get IBindingPath_Path() As String
    IBindingPath_Path = This.Path
End Property
Private Property Get IBindingPath_Object() As Object
    Set IBindingPath_Object = This.Object
End Property
Private Property Get IBindingPath_PropertyName() As String
    IBindingPath_PropertyName = This.PropertyName
End Property
Private Sub IBindingPath_Resolve()
End Sub
Private Function IBindingPath_ToString() As String
    IBindingPath_ToString = StringBuilder _
        .AppendFormat("Context: {0}; Path: {1}", TypeName(This.Context), This.Path) _
End Function
Private Function IBindingPath_TryReadPropertyValue(ByRef outValue As Variant) As Boolean
    If This.Object Is Nothing Then Resolve
    On Error Resume Next
    outValue = VBA.Interaction.CallByName(This.Object, This.PropertyName, VbGet)
    IBindingPath_TryReadPropertyValue = (Err.Number = 0)
    On Error GoTo 0
End Function
Private Function IBindingPath_TryWritePropertyValue(ByVal Value As Variant) As Boolean
    If This.Object Is Nothing Then Resolve
    On Error Resume Next
    VBA.Interaction.CallByName This.Object, This.PropertyName, VbLet, Value
    IBindingPath_TryWritePropertyValue = (Err.Number = 0)
    On Error GoTo 0
End Function

Here’s our complete “system under test” (SUT) as far as the BindingPathTests module goes. We have a Create factory method, Context and Path properties, just like the class we’re testing.

The path object is itself read-only once initialized, but the binding source may resolve to Nothing or to a different object reference over the course of the object’s lifetime: say we want a binding path to SomeViewModel.SomeObjectProperty; when we first create the binding, SomeObjectProperty might very well be Nothing, and then it’s later Set-assigned to a valid object reference. This is why the IBindingPath interface needs to expose a Resolve method, so that IPropertyBinding can invoke it as needed, as the binding is being applied.

We’ll want a test for every guard clause, and each method needs at least one test as well.

So, I’m going to add a new test module and call it BindingPathTests. Rubberduck’s templates are good-enough to depict the mechanics and how things work at a high level, but if you stick to the templates you’ll quickly find your unit tests rather boring, wordy, and repetitive: we must break out of the mold, there isn’t one true way to do this!

Rubberduck discovers unit tests in standard modules annotated with @TestModule. Test methods are any [parameterless, for now] method annotated with a @TestMethod annotation that can have a category string – the Test Explorer can group your tests using these categories. The declarations section of a test module must include a declaration (early or late bound) for an Rubberduck.AssertClass or Rubberduck.PermissiveAssertClass (both implement the same internal interface; the “permissive” one has VBA-like equality semantics, and the default one has stricter type equality requirements (a Long can’t be equal to a Double, for example). The default test template also defines a FakesProvider object, but we’re not going to need it now (if we needed to test logic that involved e.g. branching on the result of a MsgBox function call, we could hook into the MsgBox function and configure it to return what the test needs it to return, which is honestly wicked awesome). So our test module might look something like this at first:

'@Folder Tests.Bindings
Option Explicit
Option Private Module
#Const LateBind = LateBindTests
#If LateBind Then
Private Assert As Object
Private Assert As Rubberduck.AssertClass
#End If

With this conditionally-compiled setup, all we need to toggle between late and early binding is to define a project-scoped conditional compilation argument: bring up the project properties and type LateBindTests=0 or LateBindTests=1 in that box, and just like that you can control conditional compilation project-wide without modifying a single module.

The first thing to do is to get the test state defined, and implement TestInitialize and TestCleanup methods that configure this state – in the case of BindingManagerTests, I’m going to add a private type and a private field to define and hold the current test state:

Private Type TState
    ExpectedErrNumber As Long
    ExpectedErrSource As String
    ExpectedErrorCaught As Boolean
    ConcreteSUT As BindingManager
    AbstractSUT As IBindingManager
    HandlePropertyChangedSUT As IHandlePropertyChanged
    BindingSource As TestBindingObject
    BindingTarget As TestBindingObject
    SourcePropertyPath As String
    TargetPropertyPath As String
    Command As TestCommand
End Type
Private Test As TState

Unit Testing Paradigm

Test modules are special, in the sense that they aren’t (absolutely shouldn’t be anyway) accessible from any code path in the project. Rubberduck invokes them one by one when you run a command like “run all tests” or “repeat last run”. But there’s a little more to it than that, worthy of mention.

VBA being single-threaded, tests are invoked by Rubberduck on the UI/main thread, and uses a bit of trickery to keep its own UI somewhat responsive. Each module runs sequentially, and each test inside each module runs sequentially as well – but the test execution order still shouldn’t be considered deterministic, and each test should be completely independent of every other test, such that executing all tests in any given order always produces the same outcomes.

A test that makes no assertions will be green/successful. When writing unit tests, the first thing you want to see is a test that’s failing (you can’t trust a test you have never seen fail!), and with Rubberduck in order to give a test a reason to fail, you use Assert methods (wiki).

When Rubberduck begins processing a test module, it invokes the methods (again, sequentially but not in an order that should matter) marked @ModuleInitialize in the module – ideally that would be only one method.

This is where the Assert object should be assigned (the default test templates do this):

Private Sub ModuleInitialize()
#If LateBind Then
    'requires HKCU registration of the Rubberduck COM library.
    Set Assert = CreateObject("Rubberduck.PermissiveAssertClass")
    'requires project reference to the Rubberduck COM library.
    Set Assert = New Rubberduck.PermissiveAssertClass
#End If
End Sub

Rubberduck’s test engine will then execute all methods (usually cleaner with only one though) annotated with @TestInitialize before executing each test in the module; that is the best place to put the wordy setup code that would otherwise need to be in pretty much every single test of the module:

Private Sub TestInitialize()
    Dim Context As TestBindingObject
    Set Context = New TestBindingObject
    Set Context.TestBindingObjectProperty = New TestBindingObject
    Test.Path = "TestBindingObjectProperty.TestStringProperty"
    Test.PropertyName = "TestStringProperty"
    Set Test.BindingSource = Context.TestBindingObjectProperty
    Set Test.BindingContext = Context
    Set Test.ConcreteSUT = BindingPath.Create(Test.BindingContext, Test.Path)
    Set Test.AbstractSUT = Test.ConcreteSUT
End Sub

By moving the test state to module level rather than having it local to each test, we already eliminate a lot of code duplication, and the Test module variable makes a rather nifty way to access the current test state, too!

Methods annotated with @TestCleanup are automatically invoked after each test in the module; in order to avoid accidentally sharing state between tests, every object reference should be explicitly set to Nothing, and values of intrinsic data types should be explicitly reset to their respective default value:

Private Sub TestCleanup()
    Set Test.ConcreteSUT = Nothing
    Set Test.AbstractSUT = Nothing
    Set Test.BindingSource = Nothing
    Set Test.BindingContext = Nothing
    Test.Path = vbNullString
    Test.PropertyName = vbNullString
    Test.ExpectedErrNumber = 0
    Test.ExpectedErrSource = vbNullString
    Test.ExpectedErrorCaught = False
End Sub

What Goes Into the Test State?

A number of members should always be in the Test state structure:

  • ConcreteSUT (or just SUT) and AbstractSUT both point to the same object, through the default interface (BindingPath) and the explicit one (IBindingPath), respectively.
  • If the system under test class implements additional interfaces, having a pointer to the SUT object with these interfaces is also useful. For example the TState type for the BindingManager class has a HandlePropertyChangedSUT As IHandlePropertyChanged member, because the class implements this interface.
  • Default property values and dependency setup: we want a basic default SUT configured and ready to be tested (or fine-tuned and then tested).
  • ExpectedErrNumber, ExpectedErrSource, and ExpectedErrorCaught are useful when a test is expecting a given input to produce a particular specific error.

Expecting Errors

The “expected error” test method template works for its purpose, but having this on-error-assert logic duplicated everywhere is rather ugly. Consider pulling that logic into a private method instead (I’m considering adding this into Rubberduck’s test module templates):

Private Sub ExpectError()
    Dim Message As String
    If Err.Number = Test.ExpectedErrNumber Then
        If (Test.ExpectedErrSource = vbNullString) Or (Err.Source = Test.ExpectedErrSource) Then
            Test.ExpectedErrorCaught = True
            Message = "An error was raised, but not from the expected source. " & _
                      "Expected: '" & TypeName(Test.ConcreteSUT) & "'; Actual: '" & Err.Source & "'."
        End If
    ElseIf Err.Number <> 0 Then
        Message = "An error was raised, but not with the expected number. Expected: '" & Test.ExpectedErrNumber & "'; Actual: '" & Err.Number & "'."
        Message = "No error was raised."
    End If
    If Not Test.ExpectedErrorCaught Then Assert.Fail Message
End Sub

With this infrastructure in place, the unit tests for all guard clauses in the module can look like this – it’s still effectively doing Arrange-Act-Assert like the test method templates strongly suggest, only implicitly so (each “A” is essentially its own statement, see comments in the tests below):

Private Sub Create_GuardsNullBindingContext()
    Test.ExpectedErrNumber = GuardClauseErrors.ObjectCannotBeNothing '<~ Arrange
    On Error Resume Next
        BindingPath.Create Nothing, Test.Path '<~ Act
        ExpectError '<~ Assert
    On Error GoTo 0
End Sub
Private Sub Create_GuardsEmptyPath()
    Test.ExpectedErrNumber = GuardClauseErrors.StringCannotBeEmpty '<~ Arrange
    On Error Resume Next
        BindingPath.Create Test.BindingContext, vbNullString '<~ Act
        ExpectError '<~ Assert
    On Error GoTo 0
End Sub
Private Sub Create_GuardsNonDefaultInstance()
    Test.ExpectedErrNumber = GuardClauseErrors.InvalidFromNonDefaultInstance '<~ Arrange
    On Error Resume Next
        With New BindingPath
            .Create Test.BindingContext, Test.Path '<~ Act
            ExpectError '<~ Assert
        End With
    On Error GoTo 0
End Sub

And then similar tests exist for the respective guard clauses of Context and Path members. Having tests that validate that guard clauses are doing their job is great: it tells us exactly how not to use the class… and that doesn’t tell us much about what a BindingPath object actually does.

Testing the Actual Functionality

The methods we’re testing need to be written in a way that makes it possible for a test to determine whether it’s doing its job correctly or not. For functions and properties, the return value is the perfect thing to Assert on. For Sub procedures, you have to Assert on the side-effects, and have verifiable and useful, reliable ways to verify them.

These two tests validate that the BindingPath returned by the Create factory method has resolved the PropertyName and Object properties, respectively.

Private Sub Create_ResolvesPropertyName()
    Dim SUT As BindingPath
    Set SUT = BindingPath.Create(Test.BindingContext, Test.Path)
    Assert.IsFalse SUT.PropertyName = vbNullString
End Sub
Private Sub Create_ResolvesBindingSource()
    Dim SUT As BindingPath
    Set SUT = BindingPath.Create(Test.BindingContext, Test.Path)
    Assert.IsNotNothing SUT.Object
End Sub

I could have made multiple assertions in a test, like this…

Private Sub Create_ResolvesBindingSource()
    Dim SUT As BindingPath
    Set SUT = BindingPath.Create(Test.BindingContext, Test.Path)
    Assert.IsFalse SUT.PropertyName = vbNullString
    Assert.IsNotNothing SUT.Object
End Sub

The Test Explorer would say “IsFalse assert failed” or “IsNotNothing assert failed”, so it’s arguably (perhaps pragmatically so) still useful and clear enough why that test would fail (and if you had multiple Assert.IsFalse calls in a test you could provide a different message for each)… but really as a rule of thumb, tests want to have one reason to fail. If the conditions to meaningfully pass or fail a test aren’t present, use Assert.Inconclusive to report the test as such:

Private Sub Resolve_SetsBindingSource()
    With New BindingPath
        .Path = Test.Path
        Set .Context = Test.BindingContext
        If Not .Object Is Nothing Then Assert.Inconclusive "Object reference is unexpectedly set."
        Assert.AreSame Test.BindingSource, .Object
    End With
End Sub
Private Sub Resolve_SetsBindingPropertyName()
    With New BindingPath
        .Path = Test.Path
        Set .Context = Test.BindingContext
        If .PropertyName <> vbNullString Then Assert.Inconclusive "PropertyName is unexpectedly non-empty."
        Assert.AreEqual Test.PropertyName, .PropertyName
    End With
End Sub

This mechanism is especially useful when the test state isn’t in local scope and there’s a real possibility that the TestInitialize method is eventually modified and inadvertently breaks a test. Such conditional Assert.Inconclusive calls are definitely a form of defensive programming, just like having guard clauses throwing custom meaningful errors.

Note that while we know that the BindingPath.Create function invokes the Resolve method, the tests for Resolve don’t involve Create: the Path and Context are being explicitly spelled out, and the .Resolve method is invoked from a New instance.

And that’s pretty much everything there is to test in the BindingPath class.

There’s one thing I haven’t mentioned yet, that you might have caught in the TState type:

BindingSource As TestBindingObject
BindingTarget As TestBindingObject

This TestBindingObject is a test stub: it’s a dependency of the class (it’s the “binding context” of the test path) and it’s a real object, but it is implemented in a bit of a special way that the BindingPath tests don’t do justice to.

Test Stubs

Eventually Rubberduck’s unit testing framework will feature a COM-visible wrapper around Moq, a popular mocking framework for .NET that Rubberduck already uses for its own unit test requirements. When this happens Rubberduck unit tests will no longer need such “test stubs”. Instead, the framework will generate them at run-time and make them work exactly as specified/configured by a unit test, and “just like that” VBA/VB6 suddenly becomes surprisingly close to being pretty much on par with professional, current-day IDE tooling.

The ITestStub interface simply formalizes the concept:

'@Folder Tests.Stubs
'@ModuleDescription "An object that stubs an interface for testing purposes."
Option Explicit
'@Description "Gets the number of times the specified member was invoked in the lifetime of the object."
Public Property Get MemberInvokes(ByVal MemberName As String) As Long
End Property
'@Description "Gets a string representation of the object's internal state, for debugging purposes (not intended for asserts!)."
Public Function ToString() As String
End Function

A TestStubBase “base class” provides the common implementation mechanics that every class implementing ITestStub will want to use – the idea is to use a keyed data structure to track the number of times each member is invoked during the lifetime of the object:

'@Folder Tests.Stubs
Option Explicit
Private Type TState
    MemberInvokes As Dictionary
End Type
Private This As TState
'@Description "Tracks a new invoke of the specified member."
Public Sub OnInvoke(ByVal MemberName As String)
    Dim newValue As Long
    If This.MemberInvokes.Exists(MemberName) Then
        newValue = This.MemberInvokes.Item(MemberName) + 1
        This.MemberInvokes.Remove MemberName
        newValue = 1
    End If
    This.MemberInvokes.Add MemberName, newValue
End Sub
'@Description "Gets the number of invokes made against the specified member in the lifetime of this object."
Public Property Get MemberInvokes(ByVal MemberName As String) As Long
    If This.MemberInvokes.Exists(MemberName) Then
        MemberInvokes = This.MemberInvokes.Item(MemberName)
        MemberInvokes = 0
    End If
End Property
'@Description "Gets a string listing the MemberInvokes cache content."
Public Function ToString() As String
    Dim MemberNames As Variant
    MemberNames = This.MemberInvokes.Keys
    With New StringBuilder
        Dim i As Long
        For i = LBound(MemberNames) To UBound(MemberNames)
            Dim Name As String
            Name = MemberNames(i)
            .AppendFormat "{0} was invoked {1} time(s)", Name, This.MemberInvokes.Item(Name)
        ToString = .ToString
    End With
End Function
Private Sub Class_Initialize()
    Set This.MemberInvokes = New Dictionary
End Sub

With this small bit of infrastructure, the TestBindingObject class is a full-fledged mock object that can increment a counter whenever a member is invoked, and that can be injected as a dependency for anything that needs an IViewModel:

'@Folder Tests.Stubs
'@ModuleDescription "An object that can stub a binding source or target for unit tests."
Option Explicit
Implements ITestStub
Implements IViewModel
Implements INotifyPropertyChanged
Private Type TState
    Stub As TestStubBase
    Handlers As Collection
    TestStringProperty As String
    TestNumericProperty As Long
    TestBindingObjectProperty As TestBindingObject
    Validation As IHandleValidationError
End Type
Private This As TState
Public Property Get TestStringProperty() As String
    This.Stub.OnInvoke "TestStringProperty.Get"
    TestStringProperty = This.TestStringProperty
End Property
Public Property Let TestStringProperty(ByVal RHS As String)
    This.Stub.OnInvoke "TestStringProperty.Let"
    If This.TestStringProperty <> RHS Then
        This.TestStringProperty = RHS
        OnPropertyChanged Me, "TestStringProperty"
    End If
End Property
Public Property Get TestNumericProperty() As Long
    This.Stub.OnInvoke "TestNumericProperty.Get"
    TestNumericProperty = This.TestNumericProperty
End Property
Public Property Let TestNumericProperty(ByVal RHS As Long)
    This.Stub.OnInvoke "TestNumericProperty.Let"
    If This.TestNumericProperty <> RHS Then
        This.TestNumericProperty = RHS
        OnPropertyChanged Me, "TestNumericProperty"
    End If
End Property
Public Property Get TestBindingObjectProperty() As TestBindingObject
    This.Stub.OnInvoke "TestBindingObjectProperty.Get"
    Set TestBindingObjectProperty = This.TestBindingObjectProperty
End Property
Public Property Set TestBindingObjectProperty(ByVal RHS As TestBindingObject)
    This.Stub.OnInvoke "TestBindingObjectProperty.Set"
    If Not This.TestBindingObjectProperty Is RHS Then
        Set This.TestBindingObjectProperty = RHS
        OnPropertyChanged Me, "TestBindingObjectProperty"
    End If
End Property
Private Sub OnPropertyChanged(ByVal Source As Object, ByVal PropertyName As String)
    Dim Handler As IHandlePropertyChanged
    For Each Handler In This.Handlers
        Handler.OnPropertyChanged Source, PropertyName
End Sub
Private Sub Class_Initialize()
    Set This.Stub = New TestStubBase
    Set This.Handlers = New Collection
    Set This.Validation = ValidationManager.Create
End Sub
Private Sub INotifyPropertyChanged_OnPropertyChanged(ByVal Source As Object, ByVal PropertyName As String)
    OnPropertyChanged Source, PropertyName
End Sub
Private Sub INotifyPropertyChanged_RegisterHandler(ByVal Handler As IHandlePropertyChanged)
    This.Handlers.Add Handler
End Sub
Private Property Get ITestStub_MemberInvokes(ByVal MemberName As String) As Long
    ITestStub_MemberInvokes = This.Stub.MemberInvokes(MemberName)
End Property
Private Function ITestStub_ToString() As String
    ITestStub_ToString = This.Stub.ToString
End Function
Private Property Get IViewModel_Validation() As IHandleValidationError
    Set IViewModel_Validation = This.Validation
End Property

This functionality will be extremely useful when testing the actual property bindings: for example we can assert that a method was invoked exactly once, and fail a test if the method was invoked twice (and/or if it never was).

There’s a lot more to discuss about unit testing in VBA with Rubberduck! I hope this article gives a good idea of how to get the best out of Rubberduck’s unit testing feature.

Hello, Rubberduck 2.5.0

Creating the pull request to merge the current [next] branch into [master] is always thrilling: the incredible amount of work that goes into Rubberduck, release after release, never ceases to amaze me. This time (again!), the pull request is well over 1.2K commits. Green-release version was all the way back on March 25, 2019 – which was the Monday that immediately followed the last MVP Global Summit.

What’s new?

If you’ve been keeping up with pre-release builds, you already know. If you’re still using v2.4.1.0 and have the check for newer version at startup setting enabled, your ducky will be telling you about the new build next time you fire up the VBE.

When you update to v2.5, you’ll notice a new option for the check for newer version at startup setting: there’s a new “check for pre-release builds” option that can let you know not only of a new minor version bump, but also for every pre-release build – which effectively means you now get to keep Rubberduck as up-to-date as possible (every merged pull request), without needing to subscribe to GitHub email notifications.

Splash Screen

But the first thing you’ll notice (assuming you haven’t disabled it) will be the splash screen going back to the 2.4.0 yellow ducky splash – if you didn’t know, v2.4.1 was “ThunderFrame Edition” and all this time the splash screen was a nod to our dear friend Andrew Jackson:

Rubberduck’s repository is still filled with hundreds of Andrew’s ideas, and his impact on the project will remain with us forever. This ducky is based on Andrew’s work, too:

I’m not a fan of the font (it’s the same as on the ThunderSplash), but SHOWCARD GOTHIC was getting old and annoyingly too playful-looking. If a graphic artist is reading this and has a nice idea they’d like to contribute, they’re welcome to do so!

But you’re not here to read about the splash screen, are you?

Website/GitHub Integration

In the past, a new green-release meant Rubberduck needed to be deployed to the project’s website itself, so that the /version/build pages could respond with the assembly version of the Rubberduck.dll file deployed. Today the website only needs a Rubberduck build to support the online indenter page, and we only need to update that build to keep the online indenter preview tool up-to-date: if no indenter changes are made, then nothing needs to be updated – the website uses GitHub’s REST API to get the latest pre-release and official “green release” version numbers, but also to download the latest xml-doc from the Rubberduck.CodeAnalysis project, and with that the website’s /inspections/list page will now start identifying the newer inspections that are only available in a pre-release build, versus those present in the latest “green release” (this hasn’t kicked in yet, only because the [master] branch didn’t have any xml-docs to download). The /inspections/details pages are also entirely generated from the in-code xml documentation, including the many examples: we’ll eventually start linking to these pages in the inspection results toolwindow, with “why am I seeing this?” links/buttons.

New Features?

New inspections and new quickfixes, of course – but mostly lots of bugs fixed, and extremely important enhancements to the resolver logic effectively warrant the minor version bump. As mentioned in What’s Cooking for Rubberduck 2.5.x, special attention was given to the resolution of implicit default member calls and bang notation – and with that there’s very, very little early-bound code (if any) that Rubberduck isn’t understanding.

Self-closing pairs aren’t a new feature, but Rubberduck will now ship with the feature enabled by default (was opt-in before). We have been able to hijack and suppress the annoying “beep” that the VBE sounds when the Parameter Quick-Info command doesn’t have anything to show, and this has unlocked restoring automatic quick-info when typing the argument list of a function or procedure call: before that, using self-closing pairs worked pretty nicely, but parameter quick-info had to be manual, which was rather disturbing.

VBA + Source Control

If you’ve been following the project for some time, you probably remember the defunct source control panel – a toolwindow that essentially implemented Visual Studio’s Team Explorer and let you synchronize your VBA project with the files in a git repository. It would also list modified files and let you commit, push, pull, fetch, create new branches, merge them, etc. It failed and isn’t coming back, but the Code Explorer in v2.5 brings back the ability to synchronize the contents of your VBA project from the file system:

Update Components from Files will update existing modules from files in a selected folder, and Replace Contents from Files will make the VBA project mirror the contents of the selected folder (creating new project components/modules as needed). Because Visual Basic 6.0 already works off the file system, in VB6 we only offer the Update Components from Files command.

Keep in mind that while the contents of document modules can be imported, new document modules can’t be added to the host project by the VBE (the host application owns these modules: see this article): for this reason you will want to minimize the amount of code you have in modules like ThisWorkbook and other Worksheet modules in Excel, or in reports & forms in Access. Implementing the actual functionality in separate modules will make things much easier to work with this feature in conjunction with source control (whether you use git, mercurial, SVN, or any other VCS technology).

Visual Studio 2019

Rubberduck has been built with Visual Studio 2017 for quite some time: we have successfully updated all projects in the solution to the awesome new .csproj format, and until now the WPF (Windows Presentation Foundation – the .NET UI framework we use to design our toolwindows and dialogs) dependencies made it impossible to upgrade our build process to work in Visual Studio 2019 until the release of .NET Core 3 last September. This release marks the milestone where we flip the page, sunset Visual Studio 2017 – the first pull request to be merged after v2.5.0, will be one that updates the build process to work with Visual Studio 2019.

If you have forked or cloned Rubberduck, please note that Rubberduck will no longer build in VS2017, as soon as it builds in VS2019.

What Next?

One of the biggest road blocks that’s currently keeping us from implementing a lot of the amazing inspection ideas (and bringing back a proper Extract Method refactoring!), is the lack of proper code path analysis. With that, we’ll have standard tooling that all these inspections can share and reuse (rather than reinvent a rather complex wheel everytime), and then we can tackle the many open Code Path Analysis issues. I’ll be posting an “Inside Rubberduck” article about the architecture and thinking behind this at some point.

Another road block, that’s currently keeping Rubberduck from fully understanding the interfaces it’s looking at, is flicking the switch for our internal TypeLib API, which taps deep into the VBIDE’s guts and gives us visibility on the internal ITypeLib of the VBA project. Rubberduck is already leveraging some of these capabilities (that’s how unit testing works in every VBA host application), but by flicking that switch we’ll be able to, among many other things, pick up the Workbook interface of the ThisWorkbook module… which unlocks fixing a number of long-standing issues and inspection false positives.

Block Completion is another upcoming feature that will possibly be getting my attention in 2020, but not before Code Path Analysis does.

In order to address the growing concerns of performance and memory consumption (especially in larger projects, which currently work best in 64-bit hosts, and possibly not at all in 32-bit hosts), we are exploring implementing a Language Server to offload parsing & resolution out of the host process, similar to how VSCode & Roslyn works, and possibly also moving a lot of the in-memory storage of referenced type libraries’ declarations to an out-of-process database.

Rubberduck 2.4.1: ThunderFrame Edition

As was shared a week or two ago on social media, Rubberduck contributor and supporter Andrew “ThunderFrame” Jackson passed away recently – but his love for VBA, his awesomely twisted ways of breaking it, his insights, the 464 issues (but mostly ideas, with 215 still open as of this writing) and 30 pull requests he contributed to Rubberduck, have shaped a large part of what this project is all about, and for this release we wanted to honor him with a special little something in Rubberduck, starting with the splash screen.

Andrew joined the project very early on. He gave us the signature spinning duckies and the SVG icon of the project; he once implemented a very creative way to make unit testing work in Outlook (and I know a certain duck that had to eat their hat because of it!), before the feature was made host-agnostic. He gave us the weirdest, most completely evil-but-still-legal VBA code we could possibly test Rubberduck’s parser/resolver with – and we’re very proud to have a ThunderCode-proof parser now!

What’s New?

This isn’t an exhaustive list. See the release notes for more information.

¬°Rubberduck ahora habla espa√Īol!

This release introduces Spanish language support. German, French, and Czech translations have also been updated.

Rubberduck doesn’t speak your language yet? Nothing would make us happier than helping you help us translate Rubberduck! See for all the details, and don’t hesitate to ask any questions you have – go on, fork us!

The project’s many resource files are easily handled with the ResX Manager add-in for Visual Studio.

UI Enhancements

The Test Explorer has had a rather impressive facelift, Inspection Results are now much easier to review, navigate and filter. There is a known issue about the GroupingGrid control expanding/collapsing all groupings together, but we weren’t going to hold back the release for this – we will definitely address it in a near-future release though.

Toggle buttons in the toolbar now allow filtering inspection results by severity, and grouping by inspection type, by module, by individual inspection, or by severity.
Similar toggle buttons in the Test Explorer allow grouping tests by outcome, module, or category. Tests can be categorized by specifying a category name string as an argument to the @TestMethod annotation.

Parser performance has improved, especially for the most common usages of “bang” (foo!bar) notation, which remain a difficult language construct to handle. But they’re also late-bound, implicit, default member calls that would probably be better off made explicit and early-bound.

Self-Closing Pair completion works rather nicely now, with only two known, low-priority edge cases that don’t behave quite as nicely as they should.

Easter Is Coming

And with Easter comes… White Walkers Easter Eggs, so all I’m going to say, is that they’ll be flagging ThunderCode – the kind of code our friend loved to test & push the limits of Rubberduck’s parser with. If your code trips a ThunderCode inspection, …nah, it can’t happen.

Woopsie, might happen after all. We’ll eventually figure out a way to hide them from the settings!

Also it’s apparently not impossible that there’s no way no other Easter Eggs were never not added to Rubberduck. For the record I don’t know if this means what I think I mean it to say, and that’s perfect.

What’s Next?

Some very important changes have been waiting for this release and will be merged in the next few weeks – these changes won’t necessarily be visible from a user standpoint, but they will greatly enhance our internal API – refactorings, COM object management, and we’ll be leveraging more of the TypeLibs API, which in turn should end up translating into greatly enhanced user experience and feature set.

Next release will include a few new inspections, including one that locates obsolete While...Wend loops, and suggests to rewrite them as Do While...Loop blocks, which can be exited with an Exit Do statement, whereas While loops can only be prematurely exited (without throwing an error) by an inelegant GoTo jump.

We really want to tighten our release cycle, so we’ll be shooting for the end of April for what should be version 2.4.2.

Rubberduck v2.4.0

Unlike quite a number of Rubberduck releases, this time we’re not boasting a thousand commits though: we’re looking at well under 300 changes, but if the last you’ve seen of Rubberduck was 2.3.0 or prior, …trying this release you’ll quickly realize why we originally wanted to release it around Christmas.

So, here’s your belated Christmas gift from the Rubberduck dev team!

VBE Project References: CURED!

You may have seen the Introducing the Reference Explorer announcement post last autumn – well, the new feature is now field-tested, works beautifully, instinctively, and is ready for prime time. It’s a beauty!

The add/remove references dialog has seen a number of enhancements since its pre-release: thanks everyone for your constructive feedback!
Quickly locate any type library by name and/or description.
Pin your favorite references, and Rubberduck will keep them handy for all your VBA/VB6 projects.

You’ll never want to use the vanilla-VBE references dialog again!

If you’ve been following the Rubberduck project for quite some time, you may remember something about using annotations together with inspections and quick-fixes to document the presence of module & member attributes. You may also remember when & why the idea was dropped. Keeping in tradition with including new inspections every release… Surprise, it’s coming back!

German, French, and Czech translations have been updated, a number of bugs were fixed in a few inspections, the Code Explorer has seen a number of subtle enhancements, and WPF binding leaks are all but gone.

Code Explorer Enhancements

Adding the Reference Explorer made a perfect opportunity to revisit the Code Explorer toolwindow – our signature navigation feature. Behold, the new Code Explorer:

The new ‘Sync with code pane’ toolbar button (the left/right arrows icon) selects the treeview node closest to the current code pane selection.

There’s a new ‘Library References’ node that shows your project’s library dependencies …whether they’re in use or not:

Find all references can now be used to locate all uses of a given type library – including the built-in standard libraries! Note that rendering lots of search results in a toolwindow will require confirmation if there are too many results to display.

The project reference nodes get new icons:

Classes with a VB_PredeclaredID attribute set to True now have their own icon too (and their names now say (Predeclared) explicitly), and class modules marked with an @Interface annotation now appear with an “interface” icon, like IGameStrategy here:

Annotations & Attributes

They’re back, and this time it does work, and it’s another game changer: Rubberduck users no longer need to export any code file to modify module & member attributes!

Module & Member Annotations

At module level, the @ModuleDescription annotation can be given a string argument that controls the value of the module’s VB_Description attribute; the @Exposed annotation controls the value of the VB_Exposed attribute; the presence of a @PredeclaredId annotation signals a VB_PredeclaredId attribute with a value of True.

At member level, @Description annotations can be given a string argument that controls the value of the member’s VB_Description attribute.

Through inspections, Rubberduck is now able to warn about attributes that don’t have a corresponding annotation, and annotations that don’t have a corresponding attributes. Look for inspection results under the “Rubberduck Opportunities” category.


The months to come will see further enhancements in several areas; there are several pull requests lined up already – stay tuned, and keep up with the pre-release builds by watching releases on GitHub!

Autocomplete Enhancements

I got nerd-sniped. A Rubberduck user put up a feature request on the project’s repository, and I thought “we need this, yesterday”… so I did it, and the result crushed all the expectations I had – the prerelease build is here!

There¬†are¬†a few quirks – but rule of thumb, it’s fairly stable and works pretty well. Did you see it in action?

This feature rather impressively enhances the coding experience in the VBE – be it only with how it honors your Rubberduck/Smart Indenter settings to literally auto-indent code blocks as you type them.

Writing auto-completing VBA code, especially with auto-completing double quotes and parentheses, gives an entirely fresh new feel to the good old VBE… I’m sure you’re going to love it.

And in case you don’t, you could always cherry-pick which auto-completions you want to use, and which ones you want to disable:


Inline Completion

These work with the current line (regardless of whether you’re typing code or a comment, or whether you’re inside a string literal), by automatically inserting a “closing” token as soon as you type an “opening” token – and immediately puts the caret between the two. These include (pipe character¬†|¬†depicts caret position):

  • String literals: "¬†-> "|"
  • Parentheses: (¬†-> (|)
  • Square brackets: [¬†-> [|]
  • Curly braces: {¬†-> {|}

Block Completion

These work with the¬†previous line, immediately after committing it: on top of the previous line’s indentation, a standard indent width (per indenter settings) is automatically added, and the caret is positioned exactly where you want it to be. These include (for now):

  • Do¬†-> Do [Until|While]...Loop
  • Enum¬†-> Enum...End Enum
  • For¬†-> For [Each]...Next
  • If...Then¬†-> If...Then...End If
  • #If...Then¬†-> #If...Then...#End If
  • Select Case¬†-> Select Case...End Select
  • Type¬†-> Type...End Type
  • While¬†-> While...Wend
  • With...End With

On top of these standard blocks, On Error Resume Next automatically completes to ...On Error GoTo 0.

Quirks & Edge Cases

It’s possible that parenthesis completion interferes with e.g. Sub()¬†statements (an additional opening parenthesis is sometimes added). This has been experienced and reproduced, but not consistently. If you use the feature and can reliably reproduce this glitch, please open an issue and share the repro steps with us!

On Error Resume Next¬†will indent its body, but there currently isn’t any indenter setting for this: we need to add an indenter option to allow configuring whether this “block” should be indented or not.

Deleting or back-spacing auto-completed code may trigger the auto-complete again, once.

Line numbers are ignored, and an opening token found on the last line of a line-continuated comment will trigger a block auto-complete.

Lastly, care was taken to avoid completing already-completed blocks, however if you try hard enough to break it, you’ll be able to generate non-compilable code. Auto-completion cannot leverage the parser and only has a very limited string view of the current/committed line of code. The nice flipside of this limitation, is very nice performance and no delays in your typing.

None of these issues outweight the awesomeness of it, so all auto-completions are enabled by default.

OOP VBA pt.1: Debunking Stuff

Ever seen that one?

It’s not a real language

The thing is,¬†object-oriented code can definitively be written in VBA.¬†This series of posts shows how.¬†Let’s first debunk a few myths and misconceptions.


VBA classes don’t have constructors!

What’s a constructor but a tool for instantiating objects?¬†In fact there are many ways to create objects, and¬†in SOLID OOP code there shouldn’t be much Newing-up going on anyway: you would be¬†injecting a factory¬†or an¬†abstract factory instead, to reduce coupling. VBA is COM, and COM¬†loves factories. No constructors? No problem!


VBA code is inherently coupled with a UI or spreadsheet

In OOP, the ideal code has¬†low coupling¬†and¬†high cohesion. This means code that doesn’t directly depend on¬†MsgBox, or any given specific¬†Worksheet or¬†UserForm. Truth is, OOP code written in VB.NET or¬†C# be it with WinForms or WPF UI frameworks,¬†faces the same problems and can easily be written in the same “Smart UI” way that makes the UI run the show and the actual functionality completely untestable:¬†bad code is on the programmer, not the language. And spaghetti code can be written in any language. The very same principles that make well-written VB.NET, C#, or Java code be good¬†code, are perfectly applicable to VBA code.


Writing Object-Oriented VBA code is painful

Okay, point. The VBE’s¬†Project Explorer¬†does make things painful, by listing all class modules alphabetically under the same folder: it’s as if the IDE itself encouraged you to cram as much functionality as possible in as few modules as possible!¬†This is where Rubberduck’s¬†Code Explorer¬†comes in to save the day though: with a simple comment annotation in each class’¬†declarations section, you can easily organize your project into¬†virtual folders, nest them as you see fit, and best of all you can have a form, a standard module and a dozen class modules under the same folder if you want. There’s simply no reason to avoid VBA code with many small specialized class modules anymore.


OOP is overkill for VBA

After all, VBA is just “macros”, right? Procedural code was good enough back then, why even bother with OOP when you’re writing code in a language that was made to “get things done”, right? So we¬†go and implement hundreds¬†of lines of code in a worksheet event handler; we go and implement dialogs and thousands of lines of code in a form’s code-behind; we¬†declare dozens upon dozens of global variables because “that’s how it was made to work”. Right? Nope.

It works, and everyone’s happy. Until¬†something needs to change, and something else¬†needs to change the week after, and then another feature needs to be added the next week, then a bug needs to be fixed in that new feature, and then fixing that bug ripples in unexpected places in the code; the beast eventually grows hair and tentacles, and you’re left sitting in front of a spaghetti mess.

And it’s hard to maintain,¬†not because it’s VBA, but because it was written “to get things done”, but not to be maintained. This “ball of mud” code can happen in any language: it’s not the language, it’s the mentality.¬†Most VBA developers are not programmers – code gets written the way it is because doing things in a SOLID way feels like going to the Moon and back to end up next door with the exact same functionality… and lots simply don’t know better, because nobody ever taught them. At least, that’s how it started for me.

Then there’s the IDE. You would like to¬†refactor the code a bit, but there are no refactoring tools and¬†no unit tests, and every change you make risks breaking something somewhere, because knowing what’s used where is terribly painful… and there’s no integrated source control, so if you make a change that the¬†undo¬†button doesn’t remember, you better remember what it looked like. And eventually you start commenting-out a chunk of code, or start having¬†DoSomething_v2 procedures, and then¬†DoSomething3. Soon you don’t know which code calls which version and you have more comments than live code. Without source control, it’s impossible to revert back to any specific version, and short of always working off a copy of the host document, code changes are done at the risk of losing¬†everything.

No safety net. Pretty much no¬†tooling. The VBE makes it pretty hard to work with legacy code¬†–¬†at least, harder than with a more modern, full-featured IDE.

Rubberduck will change that: Rubberduck wants to make writing object-oriented VBA code as enjoyable as in a modern IDE, and maintaining and refactoring legacy procedural code as easy and safe as possible.

Is OOP overkill for VBA? If it’s not overkill for even the tiniest piece of modern-language code, then I fail to see why it would be overkill for any VBA project. After all, SOLID principles are language-agnostic, and the fact that VBA doesn’t support class inheritance does nothing to affect the quality of the code that’s possible to achieve in VBA.


Wait, how would SOLID even apply to VBA?

The¬†Single Responsibility Principle is a golden rule that’s as hard to follow in VBA as it is in any other language: write small procedures and functions that do one thing, prefer many small specialized modules over fewer, large ones.

The Open/Closed Principle, which leaves classes open for extension, closed for modification is even harder to get right, again regardless of the language. However like the others, if the other 4 principles are followed, then this one is just common sense.

Liskov Substitution Principle¬†involves no wizardry, it’s about writing code so that an implementation of an interface guarantees that it does what the interface says it’s doing, so that any given implementation of an interface can be injected into the code, it will still run correctly.

The Interface Segregation Principle goes hand in hand with the other principles, and keeps your code cohesive, focused. Interfaces should not leak any specific implementation; an interface with too many members sounds like breaking SRP anyway.

The¬†Dependency Inversion Principle is possibly the one that raises eyebrows, especially if you don’t know that VBA classes can implement interfaces. Yet it’s perfectly possible to write code against an¬†IMsgBox¬†interface, inject a¬†MsgBoxImpl class in the production code, and inject a¬†MsgBoxStub class in the test code.

See? Nothing VBA can’t handle. So object-oriented VBA code is¬†theoretically¬†possible. In the next couple of weeks we’ll go over what it means in¬†real-world¬†VBA code, in terms of project architecture, design patterns, and code design in general.

2.0 Beta is here!

A little while ago, we issued an alpha release of Rubberduck 2.0, just because, well, v1.4.3 had been around since July 2015, and we wanted to say “look, this is what we’ve been working on; it’s not nearly stable yet, but we still want to show you what’s coming”.

Time flies. 6 whole weeks, 353¬†commits (plus a few last-minute ones), 142* pull requests from 8 contributors, 143* closed issues, 60* new ones, 129,835¬†additions¬†and 113,388 deletions in 788* files later, Rubberduck still has a number of known issues, some involving COM interop, most involving COM reflection and difficulties in coming up with a host-agnostic way of identifying the exact types we’re dealing with.

It might seem obvious, but knowing that¬†ThisWorkbook is a¬†Workbook object is anything but trivial – at this point we know that¬†Workbook¬†implements a WorkbookEvents¬†interface; we also know what events are exposed: we’re¬†this close to connect all the dots and have a resolver that works the way we need it to.

So what does this mean?

It means a number of false positives for a number of inspections. It means false negatives for a number of others.

Other than that, if the last version you used was 1.4.3, you’re going to be blown away. If the last version you used was 2.0.1a, you’ll appreciate all the work that just went into this beta build.

There are a number of¬†little minor issues here¬†and there, but¬†the major issues we’re having pretty much all revolve around¬†resolving identifier references,¬†but I have to admit I don’t like unit test discovery working off the parser – it just doesn’t feel right and we’re going to fix that soon.

Speaking of unit testing… thanks to @ThunderFrame’s hard work,¬†Rubberduck 2.0 unit tests now work in Outlook, Project, Publisher and Visio.

@Hosch250 If you get unit testing to work in outlook I’ll eat my hat.

– @RubberDuck 2016-05-13

So Chris, how’s¬†the hat?

Stay tuned, things are going to snowball from this point on – we’ll be releasing much more often than we have been.

*From the GitHub “Pulse” page between May 7 and June 7, 2016.

VBA Rubberducking (Part 4)

This post is the fourth in a series of post that walk you through the various features of the Rubberduck open-source VBE add-in.

  • Part 1¬†introduced¬†the¬†navigation¬†features.
  • Part 2¬†covered the¬†code inspections.
  • Part 3 featured the¬†unit testing¬†feature.


At first we were happy to just be able to inspect the code.


Quickly we realized “inspection quick-fixes” could be something else;¬†some of the inspections’ quick-fixes are full-fledged automated refactoring¬†operations. Renaming an identifier – and doing it right¬†– is very different than just Ctrl+H/replace an identifier. Manually removing an uneeded parameter in an existing method breaks all call sites and the code no longer even compiles; Rubberduck sees all call sites, and knows which argument to remove everywhere to keep the code compiling.. and it’s much faster than doing it by hand!

Rubberduck 1.3 had Rename and Extract Method refactorings; v1.4.3 also had Remove Parameters and Reorder Parameters refactorings.

Rubberduck 2.0 introduces a few more.


The context menu commands are enabled depending on context; be it the current parser state, or the current selection.


That’s a pretty well-named refactoring. It deals with the impacts on the rest of the code base, of renaming pretty much any identifier.

Extract Method

Pretty much completely rewritten, v2.0 Extract Method refactoring is becoming pretty solid. Make a valid selection, and take that selection into its own member, replacing it with a call to the extracted code, all parameters and locals figured out for you.

Extract Interface

VBA supports interface inheritance; Rubberduck makes it easy to pull all public members of a module into a class that the original module then¬†Implements. This is VBA’s own way of¬†coding against abstractions. Unit tests love testing code that’s depending on abstractions, not concrete implementations, because then the tests can provide (“inject”)¬†fake dependencies and test the applicative logic without triggering any unwanted side-effects, like displaying a message box, writing to a file, or to a database.

Implement Interface

Implementing all members of an interface (and all members of an interface must be implemented) can be tedious; Rubberduck automatically creates a stub method for every member of the interface specified in an Implements statement.

Remove/Reorder Parameters

Reworking a member’s signature is always annoying, because then you have to cycle through every single call site and update the¬†argument list; Rubberduck knows where every call site is, and updates all call sites for you.

Move Closer to Usage

Variables should have the smallest possible scope. The “scope too wide” inspection uses this refactoring to move a declaration just above its first usage; it also works to rearrange “walls of declarations” at the top of a huge method you’re trying to cut into more manageable pieces.

Encapsulate Field

Fields are internal data, implementation details; objects shouldn’t expose public fields, but rather, encapsulate them and expose them as properties. Rubberduck turns a field into a property with only as much effort as it takes to name the new property.

Introduce Parameter/Field

Pretty much the antagonist of¬†move closer to usage, this refactoring promotes a local variable to a parameter or a field, or a parameter to a field; if a new parameter is created, call sites will be updated with a “TODO” bogus argument that leaves the code uncompilable until an argument is supplied for the new parameter at all call sites.

More refactorings are planned for 2.1 and future versions, including Inline Method (the inverse of Extract Method), to move the body of a small procedure or function into all its call sites. Ideas for more refactorings and inspections? Suggest a feature!


VBA Rubberducking (Part 3)

This post is the third in a series of post that walk you through the various features of the Rubberduck open-source VBE add-in.

  • Part 1¬†introduced¬†the¬†navigation¬†features.
  • Part 2¬†covered the¬†code inspections.

Unit Testing

If you’ve been following Rubberduck since its early days, you already know that this is where and how the project started. Before Rubberduck was a VBE add-in, it was an Excel add-in completely written in VBA, that started with this Code Review post; before Rubberduck was even¬†named “Rubberduck”, it was a C# port of this¬†VBA code – the idea being to enable writing and running unit tests¬†beyond Excel, in Access and Word VBA as well, without having to replicate all that code in multiple add-in projects.

Zero Boilerplate

There¬†are other VBA unit testing solutions out there. A lot require quite a bit of boilerplate setup code; those written in VBA require programmatic access to the VBIDE object model, which may be a security concern (you’re allowing VBA to execute code that can¬†generate and run VBA code after all). Rubberduck unit tests require neither. Because it’s a VBE add-in, Rubberduck¬†already¬†has programmatic access to the code in the IDE, and the ability to scan, modify, generate and execute VBA code – without requiring a dent in your corporate security policy.

Rubberduck requires pretty much zero boilerplate. This is a fully working test module:

 Private Assert As Rubberduck.AssertClass
 Public Sub FooIs42()
     Const expected As Integer = 42
     Dim actual As Integer
     actual = Module1.GetFoo
     Assert.AreEqual expected, actual, "Nope, not 42."
 End Sub

Okay, it’s just an example. But still, it shows how little is¬†required for it to work:

  • A @TestModule annotation in the¬†declarations section of a standard module.
  • A¬†Rubberduck.AssertClass¬†instance, which can be late or early-bound.
  • A¬†@TestMethod annotation to formally identify a test method.

That’s all. And up until recently, the¬†@TestMethod annotation was optional – in Rubberduck 1.x, if you had a public parameterless method with a name that starts with “Test”, in a standard module, Rubberduck treated it as a test method.¬†This is changing in 2.0, as we are¬†making the¬†@TestMethod annotation mandatory, favoring explicitness over implicit naming conventions. Test methods still need to be public and parameterless, and in a standard module though.


Now, let’s say GetFoo¬†returning 42 is a business requirement, and that something needs to change in¬†Module1 or elsewhere and, inadvertently,¬†GetFoo starts returning 0. If you don’t have a unit test that documents and verifies that business requirement, you’ve introduced a bug that may take a while to be discovered. However if you¬†do have a test for it, and that you’ve made it a habit to run your test suite whenever you make a change¬†just to be sure that all the business requirements are still met…


Then you have a failing test, and you know right away that your modification has subtly introduced a change in behavior that will be reported as a bug sooner or later.

If you’ve already written unit tests, I’m probably preaching to the choir here. If you’ve only ever written VBA code, it’s possible you’ve¬†heard¬†of unit testing before, but aren’t quite sure how you could make¬†your code work with it.

Luckily, the key concepts are language-agnostic, and VBA definitely has support for everything you need for full-blown Test-Driven Development.

Code Against Abstractions

Whether you’re writing C#, Java, PHP, Python, Ruby, or VBA, if your code is¬†tightly coupled¬†with a UI, accessing the file system, a Web service, a database, …or a worksheet, then it’s not¬†fit¬†for a unit test, because a¬†unit¬†test…

  • Should be fast
  • Should¬†not have side-effects
  • Should not depend on (or impact) other tests
  • Should have all dependencies under control
  • Should test¬†one thing, and¬†have¬†one reason to fail

Wait. My code¬†is¬†accessing a worksheet. Does that mean I can’t write tests for it?

Yes and no. I’ll tell you a secret. Quite a lot of VBA posts I see on Code Review are asking for tips to get their code to run faster with large data sets. Something I often say in my reviews, is that¬†the single slowest thing you can do in VBA is access a worksheet.

Don’t code your logic against the worksheet, code your logic against¬†an abstraction¬†of the worksheet. An array is often all you need: refactor your logic to work with an array instead of a worksheet, and not only you’ll be able to write a test that gives it any array you want, your code will also perform better!

Encapsulate your logic in class modules, test the public interface; if the logic brings up a UI (even a message box!),¬†extract that piece of code elsewhere¬†– make it the responsibility of¬†something else, get it out of the way so your tests can concentrate on the actual important things that they’re testing for.



A whole book could be written about reducing coupling in code, increasing cohesion, and writing tests in general. Poke around, research a bit. You’ll see where Rubberduck wants to take your VBA code.

The Test Explorer

Rubberduck’s¬†Test Explorer¬†offers two main “sets” of commands: “Run”, and “Add”.

The “Add” menu lets you easily add a¬†test module to your project, and from there you can just as easily add a¬†test method, one of two templates:

  • Test Method is the standard Arrange-Act-Assert deal, with error handling that ensures the test will correctly fail on error¬†and report¬†that error.
  • Test Method (Expected Error) is the same AAA deal, except this template is for writing tests that are¬†expected¬†to raise a specific runtime error; such tests¬†fail if the expected error isn’t raised.

The “Run” menu lets you easily run all, or a subset of the tests – e.g. you might want to only run the tests that failed the last time you ran them.

Results can be regrouped either by outcome or by location (project/module), and again can be copied to the clipboard with a single click.

Test settings let you control the contents of the test module template:


Binding mode determines whether the¬†AssertClass instance is going to be declared “As Object” (late-bound, default) or “As New Rubberduck.AssertClass” (early-bound).

Type safety determines whether the Assert variable is going to be a Rubberduck.AssertClass (strict) or a Rubberduck.PermissiveAssertClass (permissive); the permissive asserts differs with the strict (original and default) version in that equality checks are more closely modeled on VBA equality rules: with a permissive assert, an Integer value of 254 can be compared to a Byte value of 254 and deemed equal. Strict equality requires the types to match, not just the value.

Test Module Template checkboxes determine whether the @TestInitialize, @TestCleanup, @ModuleInitialize and @ModuleCleanup method stubs are going to be generated, and also whether creating a new test module creates a test method by default.

All these settings only affect new test modules, not existing ones.

The Assert Class

Tests¬†assert¬†things. Without assertions, a Rubberduck test can’t have a meaningful result, and will simply pass. The IAssert¬†interface (implemented by both¬†AssertClass and¬†PermissiveAssertClass) exposes a number of members largely inspired by MS-Tests in Visual Studio:

Name Description
AreEqual Verifies that two specified objects are equal. The assertion fails if the objects are not equal.
AreNotEqual Verifies that two specified objects are not equal. The assertion fails if the objects are equal.
AreNotSame Verifies that two specified object variables refer to different objects. The assertion fails if they refer to the same object.
AreSame Verifies that two specified object variables refer to the same object. The assertion fails if they refer to different objects.
Fail Fails the assertion without checking any conditions.
Inconclusive Indicates that the assertion cannot be verified.
IsFalse Verifies that the specified condition is false. The assertion fails if the condition is true.
IsNothing Verifies that the specified object is Nothing. The assertion fails if it is notNothing.
IsNotNothing Verifies that the specified object is not Nothing. The assertion fails if it isNothing.
IsTrue Verifies that the specified condition is true. The assertion fails if the condition is false.

To be continued…

VBA Rubberducking (Part 2)

This post is the second in a series of post that walk you through the various features of the Rubberduck open-source VBE add-in. The first post was about the navigation features.

Code Inspections


Back when the project started, when we started realizing what it meant to¬†parse¬†VBA code, we knew we were going to use that information to tell our users when we’re seeing anything¬†from possibly iffy¬†to this would be a¬†bug¬†in their code.

The first one to be implemented was¬†OptionExplicitInspection. The way Rubberduck works, a variable that doesn’t resolve to a known declaration simply doesn’t exist. Rubberduck is designed around the fact that it’s working against¬†code that VBA compiles; is also needs to assume you’re working with code that declares its variables.

Without ‘Option Explicit’ on, Rubberduck code inspections can yield false positives.

Because it’s best-practice to always declare your variables, and because the rest of Rubberduck won’t work as well as it should if you’re using undeclared variables, this inspection defaults to Error severity level.

OptionExplicitInspection was just the beginning. As of this writing, we have implementations for 35 inspections, most with one or more one-click quick-fixes.

35 inspections?

And there’s a couple more left to implement, too. A lot of inspections rely on successful parsing and processing of the entire project and its references; if there’s a parsing error, then Rubberduck will not produce new inspection results. When parsing succeeds, inspections run automatically and the “status bar” indicates¬†Ready¬†when it’s completed.

  1. AssignedByValParameterInspection looks for parameters passed by value and assigned a new value, suggesting to either extract a local variable, or pass it by reference if the assigned value is intended to be returned to the calling code.
  2. ConstantNotUsedInspection looks for constant declarations that are never referenced. Quick-fix is to remove the unused declaration.
  3. DefaultProjectNameInspection looks for unnamed projects (“VBAProject”), and suggests to refactor/rename it. If you’re using source control, you’ll want to name your project, so we made an inspection for it.
  4. EmptyStringLiteralInspection finds “” empty strings and suggests replacing with vbNullString constants.
  5. EncapsulatePublicFieldInspection looks for public fields and suggests making it private and expose it as a property.
  6. FunctionReturnValueNotUsedInspection locates functions whose result is returned, with none of the call sites doing anything with it. The function is used as a procedure, and Rubberduck suggests implementing it as such.
  7. IdentifierNotAssignedInspection reports variables that are declared, but never assigned.
  8. ImplicitActiveSheetReferenceInspection is Excel-specific, but it warns about code that implicitly refers to the active sheet.
  9. ImplicitActiveWorkbookReferenceInspection is also Excel-specific, warns about code that implicitly refers to the active workbook.
  10. ImplicitByRefParameterInspection parameters are passed by reference by default; a quick-fix makes the parameters be explicit about it.
  11. ImplicitPublicMemberInspection members of a module are public by default. Quick-fix makes the member explicitly public.
  12. ImplicitVariantReturnTypeInspection a function or property getter’s signature doesn’t specify a return type; Rubberduck makes it return a explicit Variant.
  13. MoveFieldCloserToUsageInspection locates module-level variables that are only used in one procedure, i.e. its accessibility could be narrowed to a smaller scope.
  14. MultilineParameterInspection finds parameters in signatures, that are declared across two or more lines (using line continuations), which hurts readability.
  15. MultipleDeclarationsInspection finds instructions containing multiple declarations, and suggests breaking it down into multiple lines. This goes hand-in-hand with declaring variables as close as possible to their usage.
  16. MultipleFolderAnnotationsInspection warns when Rubberduck sees more than one single @Folder annotation in a module; only the first annotation is taken into account.
  17. NonReturningFunctionInspection tells you when a function (or property getter) isn’t assigned a return value, which is, in all likelihood, a bug in the VBA code.
  18. ObjectVariableNotSetInspection tells you when a variable that is known to be an object type, is assigned without the¬†Set keyword – this¬†is¬†a bug in the VBA code, and fires a runtime error 91 “Object or With block variable not set”.
  19. ObsoleteCallStatementInspection locates usages of the Call keyword, which is never required. Modern form of VB code uses the implicit call syntax.
  20. ObsoleteCommentSyntaxInspection locates usages of the Rem keyword, a dinosaurian syntax for writing comments. Modern form of VB code uses a single quote to denote a comment.
  21. ObsoleteGlobalInspection locates usages of the Global keyword, which is deprecated by the Public access modifier. Global cannot compile when used in a class module.
  22. ObsoleteLetStatementInspection locates usages of the Let keyword, which is required in the ancient syntax for value assignments.
  23. ObsoleteTypeHintInspection locates usages of type hints in declarations and identifier references, suggesting to replace them with an explicit value type.
  24. OptionBaseInspection warns when a module uses¬†Option Base 1, which can easily lead to¬†off-by-one bugs, if you’re not careful.
  25. OptionExplicitInspection warns when a module does not set¬†Option Explicit, which can lead to VBA happily compiling code that uses undeclared variables, that are undeclared because there’s a typo in the assignment instruction. Always use Option Explicit.
  26. ParameterCanBeByValInspection tells you when a parameter is passed¬†ByRef (implicitly or explicitly), but never assigned in the body of the member – meaning there’s no reason not to pass the parameter by value.
  27. ParameterNotUsedInspection tells you when a parameter can be safely removed from a signature.
  28. ProcedureCanBeWrittenAsFunctionInspection locates procedures that assign a single ByRef parameter (i.e. treating it as a return value), that would be better off written as a function.
  29. ProcedureNotUsedInspection locates procedures that aren’t called anywhere in user code. Use an¬†@Ignore annotation to remove false positives such as public procedures and functions called by Excel worksheets and controls.
  30. SelfAssignedDeclarationInspection finds local object variables declared¬†As New, which (it’s little known) affects the object’s lifetime and can lead to surprising/unexpected behavior, and bugs.
  31. UnassignedVariableUsageInspection locates usages of variables that are referred to before being assigned a value, which is usually a bug.
  32. UntypedFunctionUsageInspection recommends using String-returning functions available, instead of the Variant-returning ones (e.g. Mid$ vs. Mid).
  33. UseMeaningfulNamesInspection finds identifiers with less than 3 characters, without vowels, or post-fixed with a number – and suggests renaming them. Inspection settings will eventually allow “white-listing” common names.
  34. VariableNotAssignedInspection locates variables that are never assigned a value (or reference), which can be a bug.
  35. VariableNotUsedInspection locates variables that might be assigned a value, but are never referred to and could be safely removed.
  36. VariableTypeNotDeclaredInspection finds variable declarations that don’t explicitly specify a type, making the variable implicitly¬†Variant.
  37. WriteOnlyPropertyInspection finds properties that expose a setter (Property Let or Property Set), but no getter. This is usually a design flaw.

Oops, looks like I miscounted them… and there are even more coming up, including host-specific ones that only run when the VBE is hosted in Excel, or Access, or whatever.

The Inspection Results toolwindow

If you bring up the VBE in a brand new Excel workbook, and then bring up the inspection results toolwindow (Ctrl+Shift+I by default) you could be looking at something like this:


Most inspections provide one or more “quick-fixes”, and sometimes a quick-fix can be applied to all inspection results at once, within a module, or even within a project. In this case¬†Option Explicit can be automatically added to all modules that don’t have it in the project, using the blue Fix all occurrences in project¬†link at the bottom.

Or, use the Fix drop-down menu in the top toolbar to apply a quick-fix to the selected inspection result:


Each inspection has its own set of “quick-fixes”¬†in¬†the¬†Fix menu. A common one is¬†Ignore once; it inserts an¬†@Ignore annotation that instructs the specified inspection to skip a declaration or identifier reference..


The bottom panel contains information about the selected inspection result, and fix-all links that always use the first quick-fix in the “Fix” menu.¬†Disable this inspection turns the inspection’s “severity” to¬†DoNotShow, which effectively disables the inspection.

You can access inspection settings from the Rubberduck | Settings menu in the main commandbar, or you can click the settings button in the inspection results toolwindow to bring up the settings dialog:


If you like using the Call keyword, you can easily switch off the inspection for it from there.

The Copy toolbar button sends inspection results into the clipboard so they can be pasted into a text file or an Excel worksheet.

As with similar dockable toolwindows in Rubberduck, the way the grid regroups inspection results can be controlled using the Grouping menu:


The¬†refresh button causes a re-parse of any modified module; whenever parser state reaches “ready”, the inspections run and the grid refreshes – just as it would if you refreshed from the¬†Rubberduck¬†command bar, or from the¬†Code Explorer toolwindow.

To be continued…