From Macros to Objects: The Command Pattern

In procedural code, a macro might be implemented in some Public Sub DoSomething procedure that proceeds to do whatever it is that it needs do, usually by dereferencing a number of library-defined objects and invoking their members in a top-to-bottom sequence of executable instructions. Clean, nicely written and well-modularized procedural code would have that be a small, high-abstraction public procedure at the top of some SomethingMacro standard module, with increasingly lower-abstraction private procedures underneath.

Looking only at scope names (the private procedures might be Function, and they would likely take parameters), the module for a MakeSalesReport macro might roughly look something like this:

Like “making coffee”, the phrase “make the sales report” is abstracting away quite a lot of smaller sub-steps.

Breaking down a problem into smaller and simpler steps and sub-steps is how we begin to achieve separation of concerns: maybe one of these sub-steps is going to require prompting the user for a filename – if that’s implemented in a separate PromptFileName function that’s only responsible for prompting the user for a filename, then it’s much easier to later (as needed) reuse that function by pulling it into its own, say, Files module, and making it Public.

If programming is a lot like writing a story, then procedures have to be the verbs we use to express the actions carried by our code. The smaller a procedure, the less it can do; the fewer things a procedure does, the easier it is to give it a name that accurately, precisely describes what it does.

Public Sub DoSomething()
    'do stuff:
    '...
    
    'get the filename:
    Dim FileName As String
    FileName = ...

    'do more stuff:
    '...

End Sub

Any chunk of code that can be isolated inside a procedure scope and described with a comment that essentially says “this chunk of code reticulates splines” (whatever that is – maybe it’s “get the filename:”, or a much less subtle “======= GET FILENAME =======”), is a chunk of code that could be extracted into its own ReticulateSplines named procedure scope, and then doing this replaces a comment that says “this chunk of code reticulates splines” and the entire code block that goes with it, with a higher-abstraction single procedure call that plainly says ReticulateSplines: by properly naming the things we abstract away, we can make our code expressive and [for the most part] self-explanatory.

Option Explicit

Public Sub DoSomething()
    DoStuff
    
    Dim FileName As String
    FileName = ...

    DoMoreStuff FileName

End Sub

Private Sub DoStuff()
'...
End Sub

Private Sub DoMoreStuff(ByVal FileName As String)
'...
End Sub

And that’s glorious already.

With object-oriented programming (OOP), we get to further increase the abstraction level, such a Public Sub DoSomething macro procedure might belong to some Macros or EntryPoints standard module, painting an abstract broad-brush big picture… with all the spline-reticulating gory details in Private procedures of a separate class module.

Like procedures in procedural code, classes in OOP become another building block to tell our story: with class modules we get to use nouns: procedures do things, objects are things. So we could have a SomeMacro class that encapsulates everything “do something” needs to do, and when we need a DoSomethingElse macro we can implement it in its own dedicated class module too, leaving the Macros module (or EntryPoints, or whatever… just not Module1!) a high-abstraction, broad-brush picture of what’s going on.

This boils down to 1) create the dependencies of the macro class module we want to create; 2) create and initialize the “macro” object, and 3) invoke a Run method to, well, run the macro.

A standard module doing that, might look like this:

Option Explicit
Private Const ConnectionString As String = "..."

Public Sub DoSomething()
    ' create the dependencies...
    Dim DbService As IDbService
    Set DbService = SomeDbService.Create(ConnectionString)

    ' create the macro object, pass/inject the dependencies;
    ' we know SomeMacro needs a Worksheet and an IDbService
    ' because its Create factory method takes them as parameter:
    With SomeMacro.Create(Sheet1, DbService)
        .Run ' runs the macro
    End With
End Sub

Public Sub DoSomethingElse()
    'we could have another macro here...
    '..if that other macro is in another class...
    '...does it have a .Run method?
End Sub

This does effectively roughly demonstrate Dependency Injection and Inversion of Control in VBA (glossing over the required predeclared ID hidden attributes here), but in the context of this article, the point of interest is the .Run member call: if we make an object that encapsulates the notion of running a macro, it makes sense for that object to have a Run method. However if we don’t formalize this concept with an interface, we could have a SomeMacro.Run, then we could have AnotherMacro.Execute, and why not SomeOtherMacro.DoSomething: nothing is structuring things and telling the compiler and future maintainers “see this class is a macro and it has a method that runs it”, so while it’s nice that we’ve nicely cleaned up the Macros module by moving most of the code into class modules, it’s still chaos out there – unless there’s a way to get all macros to agree on exactly how we run them.

How do we tell the compiler “this class is a macro and it has a method that runs it”?

Interfaces and the Implements keyword, of course!

We can do this by adding a new class module (call it IMacro – I’m really not a prefix guy, but abstract interfaces in COM traditionally have that I prefix, and the tradition carried into C# and .NET, so here we are – if this were Java I would have just called it Macro; it’s all just conventions), and then adding a Run method with an empty body – this class shall remain abstract, and the implementation(s) shall be provided by other class modules:

'@ModuleDescription "Represents an executable macro."
'@Interface
Option Explicit

'@Description "Runs the macro."
Public Sub Run()
End Sub

The implementation(s) would be class modules with Implements IMacro and a Private Sub IMacro_Run procedure that invokes a Run procedure which… would break down into smaller, lower-abstraction private procedures underneath, and would delegate the more specialized work to more specialized objects (which would thus become that class’ dependencies). Sounds familiar?

Yep. You’re looking at your standard procedural macro, with the only difference being that instead of a standard module it’s now inside a class module that Implements IMacro.

Is this… a command pattern (macro in a class module)? Turns out, it pretty much actually is!

Of course, that’s not the whole story. But yes, it’s indeed a command pattern, however minimal – in design pattern abstraction terminology:

  • the caller is the Public Sub DoSomething macro procedure
  • the command is the IMacro interface
  • the concrete command is the SomeMacro class (implements IMacro)
  • the SomeDbService dependency would be a receiver, I think

What makes a “macro in a classs module” a command pattern, is the IMacro interface and how it abstracts the notion of “running a macro”. It represents the abstract concept of “something that can run”, and this right there, is the command pattern in a nutshell.

Let’s dig a little deeper though, because VBA can do much more than just macros, and commands are everywhere in software.

Divide & Conquer

Say we’re writing a user interface that can add, delete, and update records in a table. We might have a form featuring a ListBox control, and then CommandButton controls to create a new record, delete the selected one(s), and modify an existing one.

In a clean design without the command pattern, code might be written and organized with a “divide & conquer” attitude, and would look something like this (lower-abstraction details omitted, they’re not the point):

Option Explicit

'...

Public Property Get Model() As SomeModel
    'gets an object holding the data needed for this form.
End Property

Private Sub CreateNewItem()
    With New ItemEditorForm ' new form instance
        .Show 
        If .Cancelled Then Exit Sub
        AddToSource .Model ' implies the form has a Model As Something property.
    End With
End Sub

Private Sub AddToSource(ByVal Thing As Something)
    Model.AddThing Thing ' the Something class needs an AddThing method for this.
End Sub

Private Sub RemoveFromSource(ByVal Thing As Something)
    Model.RemoveThing Thing ' the Something class needs a RemoveThing method for this.
End Sub

Private Sub DeleteSelectedItems()
    Dim i As Long
    For i = Me.ItemsBox.ListCount - 1 To 0 Step -1 ' assumes an ItemsBox listbox
        If Me.ItemsBox.Selected(i) Then ' does not assume single-item selections
            Dim Item As Something
            ' assumes a ListSource collection of Something objects
            Set Item = ListSource(Me.ItemsBox.ListIndex)
            If Not Item Is Nothing Then
                RemoveFromSource Item  ' <~ do this work at a lower abstraction level
            End If
        End If
    Next
End Sub

Private Sub EditSelectedItem()
    Dim Item As Something
    Set Item = ListSource(Me.ItemsBox.ListIndex)
    If Item Is Nothing Then Exit Sub

    With New ItemEditorForm ' pop a modal with fields for an item...
        Set .Model = Item ' <~ this item. (assumes a Model As Something property)
        .Show
        If .Cancelled Then Exit Sub
        UpdateSourceItem .Model ' <~ do this work at a lower abstraction level
    End With
End Sub

Private Sub CreateButton_Click()
    CreateNewItem ' <~ do this work at a lower abstraction level
End Sub

Private Sub DeleteButton_Click()
    DeleteSelectedItems ' <~ do this work at a lower abstraction level
End Sub

Private Sub EditButton_Click()
    EditSelectedItem ' <~ do this work at a lower abstraction level
End Sub


'...

By factoring each button action into its own dedicated procedure, we get to name things and clearly split things up by functionality. The job of a Click handler becomes to fork execution elsewhere, so they [often] become simple one-liners invoking a private method, painting a broad-brush picture of what’s going on.

We could just as well implement the functionality in the body of the Click handler, but I personally find extracting these private methods worthwhile, because they make it easier to restructure things later (you can cut/move the entire scope), versus leaving that code in event handlers where the refactoring is more tedious. Event handlers are entry points in a way, enough so that having them at a high abstraction level feels exactly right for me.

Now what if we wanted the EditButton to only be enabled when only one item is selected, and then make the DeleteButton only enabled when at least one item is selected? We would have to start handling the ItemsBox.Change event, and would need additional code that might look like this:

Private Sub SetButtonsEnabledState()
    Me.EditButton.Enabled = (Model.SelectedItems.Count = 1)
    Me.DeleteButton.Enabled = (Model.SelectedItems.Count > 0)
    '...
End Sub

Private Sub ItemsBox_Change()
    SetModelSelectedItems
    SetButtonsEnabledState
End Sub

Imagine a form with many more controls – each with their own “is enabled” rules and a Change event handler procedure: boilerplate… boilerplate code everywhere!

Each command button has its own associated actions implemented in its own set of procedures, and that creates a lot of noise and reduces the signal when we’re reading the code, and that’s a clear sign the abstraction level needs to go up a bit.

Abstraction Levels
Think of the steps involved in making a cup of coffee, in maybe 3-5 steps. Think of a descriptive verb for each step, then think of how each step could be broken down into another 3-5 steps, and then use descriptive names for these steps, too. The names at the top level are necessarily going to be more abstract than those in the lower level(s): that’s what abstraction levels refers to. Now imagine doing all that in one giant procedure scope and you can see the benefits of balancing abstraction and indirection in programming 🙂

Moving that boilerplate to Public procedures in standard modules would “work” to clean up the form module… but then it would also pretty much defeat the purpose of encapsulating things into objects… and then when (not if) one such procedure needs any state, then that state soon becomes global state, and that is absolutely not something we want to have to resort to.

Command & Conquer

Using the command pattern (even without MVVM command bindings), a CreateButton_Click handler would still be responsible for kicking the “create a new item” logic into action… but now that logic would be living in some ICommand implementation, encapsulating its dependencies and state (and thus moving these outside of the form’s code-behind but not into global scope now).

The MVVM infrastructure defines an ICommand interface that looks like this:

'@Folder MVVM.Infrastructure.Abstract
'@ModuleDescription "An object that represents an executable command."
'@Interface
'@Exposed
Option Explicit

'@Description "Returns True if the command is enabled given the provided binding context (ViewModel)."
Public Function CanExecute(ByVal Context As Object) As Boolean
End Function

'@Description "Executes the command given the provided binding context (ViewModel)."
Public Sub Execute(ByVal Context As Object)
End Sub

'@Description "Gets a user-friendly description of the command."
Public Property Get Description() As String
End Property

This makes a command as an abstraction that has:

  • A user-friendly description of what the command does.
  • A function that takes a context object and returns a Boolean value that indicates whether the command can currently be executed.
  • An Execute procedure that takes a context object and, well, executes the command.

The mysterious Context parameter is an object that encapsulates the state, the data we’re working with. In MVVM that would be the ViewModel instance.

MVVM command bindings use the Description property to set the ControlToolTip string of a binding’s target CommandButton object, and automatically invokes the CanExecute method as property bindings update, which automatically enables or disables the bound command button control: the command pattern works very, very well with Model-View-ViewModel, but nothing says we cannot use the command pattern without it.

So let’s strip the interface of its Description property, leaving only the CanExecute and Execute methods:

'@Folder CommandPattern.Example
'@ModuleDescription "An object that represents an executable command."
'@Interface
'@Exposed
Option Explicit

'@Description "Returns True if the command is enabled given the provided context."
Public Function CanExecute(ByVal Context As Object) As Boolean
End Function

'@Description "Executes the command given the provided context."
Public Sub Execute(ByVal Context As Object)
End Sub

We’re still going to need a Click handler in the code-behind for each CommandButton on a form, but now that we have an ICommand abstraction to code against, we can already go back to the Divide & Conquer form’s code-behind and watch it melt:

Private CreateNewItem As ICommand
Private DeletedSelectedItems As ICommand
Private EditSelectedItem As ICommand

Public Property Get Model() As Object
    'gets an object holding the data needed for this form
End Property

Private Sub CreateButton_Click()
    CreateNewItem.Execute Me.Model
End Sub

Private Sub DeleteButton_Click()
    DeleteSelectedItems.Execute Me.Model
End Sub

Private Sub EditButton_Click()
    EditSelectedItem.Execute Me.Model
End Sub

That of course is again just simplified illustrative code, but the lower-abstraction implementation details that were omitted for brevity in the “divide & conquer” code no longer need to find a place to call home, and no longer even need to be omitted either: that lower-abstraction code is simply gone from the code-behind now, and lives in a handful of distinct objects that implement the ICommand interface, such that the only thing a button’s Click handler needs to do now is to invoke a high-abstraction method that does whatever it needs to do.

At a glance, such a one-liner CreateNewItem.Execute instruction looks very similar to another one-liner CreateNewItem instruction (both involve a procedure call against an object – but only one of them is a command); the difference is that now the form is [blissfully] unaware of how that activity is going to happen, and a maintainer looking for the code that creates a new item will find it in a CreateNewItemCommand class, instead of somewhere in the middle of other specialized procedure scopes all in the same module.

Embracing Changes

Code changes, code evolves, it’s inevitable: code lives. When we code against abstractions, we reduce the code’s resistance to change. You want your code to embrace changes, you want it to welcome changes and extensions.

By coding against an ICommand interface, the only thing we commit to is that clicking a button will do something; we don’t know what and we don’t even need to care, and that’s what not resisting change means: we aren’t saying “run procedure X in module Y” anymore, we’re saying “run X implemented by any class whatsoever“. The actual code that runs the command is bound at run-time and doesn’t even need to exist for the code to compile, and the form is still fully-functional given no-op stub “commands” – we just need to get more abstract about what “to be functional” means for a form (meaning, if we click a button and ICommand.Execute is invoked, then we’re good – that’s all we need the form to do here).

The hypothetical example code above implies a separate CreateItemCommand class; it might look something like this:

Option Explicit
Implements ICommand

Private Function ICommand_CanExecute(ByVal Context As Object) As Boolean
    ICommand_CanExecute = True
End Function

Private Sub ICommand_Execute(ByVal Context As Object)
    With New ItemEditorForm
        .Show
        If .Cancelled Then Exit Sub
        AddToSource .Model, Context
    End With
End Sub

Private Sub AddToSource(ByVal Thing As Something, ByVal Context As Object)
    Context.AddThing Thing
End Sub

Note that this is again really just moving private methods from one place into their own class, so AddToSource would be the same code as before, only now the “source” collection that needs an item added to, would live in the Context object, which we’re accessing late-bound here for simplicity’s sake, but a command implementation that works with a particular specific type of Context object should validate that, and cast the parameter into a local variable declared with the appropriate type, so as to avoid such unnecessary late binding, like this:

Private Sub DoSomething(ByVal Context As Object)
    Debug.Assert TypeOf Context Is Class1
    Dim LocalContext As Class1
    Set LocalContext = Context '<~ type mismatch here if the assert fails
    'carry on using LocalContext with early-bound member calls
End Sub

By moving the implementation out of the button’s Click handler, we make it much easier to later repurpose that button, or to make a future button elsewhere that invokes the same command. The form module doesn’t need to know about any concrete implementation of the ICommand interface: a button can be wired-up to any command, swapping SomeCommand for a SomeOtherCommand implementation is all that’s needed.


One Step Further

We’ve seen how to pull functionality from a form’s code-behind and refactor it into specialized command objects that can be invoked from a button’s Click handler. The nicest thing about such commands, is that they are full-fledged objects, which means they can be passed around as parameters – and Model-View-ViewModel (MVVM) leverages that.

In the MVVM object model, you have a top-level AppContext object that exposes an ICommandManager object: this manager is responsible for holding a reference to all command bindings in your MVVM application, and there’s an IBindingManager that notifies it whenever a property binding updates in a way that may require commands’ CanExecute method to be evaluated.

When coding against the MVVM object model, you no longer wire-up event handlers: the MVVM infrastructure automatically does it for you – so the only code that remains (that actually does anything) in a form’s code-behind, is code that wires up form controls to property and command bindings – the rest is just implementations for IView and ICancellable interfaces (as applicable), and then a factory method can initialize a bunch of properties (or the properties can be Set from outside the module, but a Create factory method works very well with UserForm classes for property injection):

Option Explicit
Implements IView
Implements ICancellable

Private Type TState
    Context As MVVM.IAppContext
    ViewModel As ExampleViewModel '<~ any class implementing INotifyPropertyChanged
    IsCancelled As Boolean
    CreateNewItem As ICommand
    DeletedSelectedItems As ICommand
    EditSelectedItem As ICommand
End Type

Private This As TState

'...properties...

Public Property Get ViewModel() As ExampleViewModel
    Set ViewModel = This.ViewModel
End Property

Private Sub InitializeView()
    With This.Context.Commands
        .BindCommand ViewModel, Me.CreateButton, ViewModel.CreateNewItem
        .BindCommand ViewModel, Me.DeleteButton, ViewModel.DeleteSelectedItems
        .BindCommand ViewModel, Me.EditButton, ViewModel.EditSelectedItem
        .BindCommand ViewModel, Me.CancelButton, CancelCommand.Create(Me)
    End With
End Sub

'...interface implementations...

The UI controls are still referred to as Me.CreateButton, Me.DeleteButton, and Me.EditButton (added Me.CancelButton for good measure), but now instead of handling their Click event we bind them to ICommand objects – whose references we conveniently expose as Property Get members of our ViewModel, but we can also bind a command that we create inline, like this CancelCommand instance. Shame the QueryClose event isn’t exposed, because then binding a CancelCommand to a UserForm would be all you’d need to do for it to automagically properly close/cancel a dialog.

Note that the form doesn’t even need to know what specific ICommand implementations it’s given to work with, at all: here the form is coupled with the CancelCommand, but all other commands (create, delete, edit) are binding to public ICommand properties that live on the ViewModel object.

Full Circle: EventCommand (MVVM)

Not all commands are created equal: a command like CancelCommand is generic enough that it can work with any ICancellable object, and an AcceptCommand can work with any implementation of the IView interface. On the other hand, something feels wrong about systematically implementing any & all commands in their own classes.

Having each command neatly factored into its own class module is a great way to implement complex commands, but can be overkill when things are relatively trivial – very often the ViewModel class already has access to every object a command needs, and having a way to make the ViewModel itself implement the command would solve this.

I’m going to introduce an EventCommand class into the MVVM infrastructure code, to do exactly this:

'@Folder MVVM.Common.Commands
'@ModuleDescription "A command that allows the ViewModel to supply the implementation."
'@PredeclaredId
'@Exposed
Option Explicit
Implements ICommand

Private Type TState
    Description As String
End Type

Private This As TState

Public Event OnCanExecute(ByVal Context As Object, ByRef outResult As Boolean)
Public Event OnExecute(ByVal Context As Object)

'@Description "Creates a new instance of this ICommand class. Set the returned reference to a WithEvents variable."
Public Function Create(ByVal Description As String) As ICommand
    Dim Result As EventCommand
    Set Result = New EventCommand
    Result.Description = Description
    Set Create = Result
End Function

'@Description "Gets/sets the command's Description."
Public Property Get Description() As String
    Description = This.Description
End Property

Friend Property Let Description(ByVal RHS As String)
    This.Description = RHS
End Property

Private Function ICommand_CanExecute(ByVal Context As Object) As Boolean
    Dim outResult As Boolean
    outResult = True
    RaiseEvent OnCanExecute(Context, outResult)
    ICommand_CanExecute = outResult
End Function

Private Property Get ICommand_Description() As String
    ICommand_Description = This.Description
End Property

Private Sub ICommand_Execute(ByVal Context As Object)
    RaiseEvent OnExecute(Context)
End Sub

In VBA we can’t pass functions around like we can with delegates in C#, but events are a nice language feature we can still leverage for this purpose. Code like this could be in any ViewModel class:

Private WithEvents PseudoDelegateCommand As EventCommand

'...

Private Sub Class_Initialize()
    Set PseudoDelegateCommand = EventCommand.Create("Full circle!")
End Sub

'...

Private Sub PseudoDelegateCommand_OnCanExecute(ByVal Context As Object, outResult As Boolean)
'supply the ICommand.CanExecute implementation here.
'assign outResult to False to disable the command (it's True by default).
'in principle, the Context *is* the ViewModel instance, so this assertion should hold:
    Debug.Assert Me Is Context
'it also means the Context parameter should probably be ignored.
End Sub

Private Sub PseudoDelegateCommand_OnExecute(ByVal Context As Object)
'supply the ICommand.Execute implementation here.
'in principle, the Context *is* the ViewModel instance, so this assertion should hold:
    Debug.Assert Me Is Context
'it also means the Context parameter should probably be ignored.
'EventCommand is useful for commands that are specific to a particular ViewModel,
'and don't really need to have their implementation extracted into their own class.
End Sub

And now we’ve gone full circle and essentially moved the Click handlers out of the View …and into the ViewModel – except these aren’t Click handlers now, although they will run when a user clicks the associated button (mind-boggling, right?): we’re essentially looking at callbacks here, invoked from within the MVVM infrastructure in response to control events… and/or INotifyPropertyChanged notifications from the ViewModel.

From a testability standpoint, it’s important to understand the implications: if you intend to have your ViewModel under a thorough suite of unit tests, then an EventCommand becomes somewhat of a liability. The OnExecute handler (or OnCanExecute, for that matter) shouldn’t require dependencies that the ViewModel doesn’t already have, so that tests can property-inject stub dependencies. In other words, unless the ViewModel already depends on an abstraction to access, say, a database connection or the file system, then the handlers of an EventCommand in that class shouldn’t connect to a database or access the file system.


You’re in command

Whether it’s for a workbook with many simple (-ish) macros, or for a full-fledged MVP, MVC, or MVVM application, implementing the command pattern lets you move the code that contains your actual functionality wherever it makes the most sense to have it. Unless you’re writing a Smart UI, that place is pretty much never the code-behind of the View module. By implementing an ICommand interface directly, you can move all that code from the UI to a command class whose sole purpose is to provide that particular piece of functionality.

Using an EventCommand with MVVM, you can even move that code from the UI to literally anywhere you want, as long as that is a class module (only class modules can have a WithEvents instance variable). It’s not uncommon to see a ViewModel class include somewhat high-abstraction code that provides commands’ implementations.

See and follow github.com/rubberduck-vba/MVVM for the Model-View-ViewModel infrastructure code that makes command bindings a thing in VBA, as well as examples (including a Smart UI!) and additional documentation.

Dependency Injection + Inversion of Control + VBA

Whether VBA can do serious OOP isn’t a question – it absolutely can: none of the SOLID principles have implications that disqualify VBA as a language, and this means we can implement dependency injection and inversion of control. This article will go over the general principles, and then subsequent articles will dive into various dependency injection techniques you can use in VBA code.

A quick summary of these fundamental guidelines, before we peek at DI and IoC:

SOLID

Single Responsibility Principle

Split things up, and then some. Write loop bodies in another procedure, extract if/else blocks into other small specialized procedures. Do as little as possible, aim for each procedure to have a well-defined single responsibility.

Open/Closed Principle

Designing classes that are “open for extension, but closed for modification” is much easier said than done, but definitely worth striving for; by adhering to the other SOLID principles, this one just naturally falls into place. In a nutshell, you’ll want to be able to add features by extending a class rather than modifying it (and risk breaking something) – the only code you need to think about is the code for the new feature… and how you’re going to be testing it.

Liskov Substitution Principle

Say you write a procedure that takes an IFooRepository parameter. Whether you invoke it with some SqlFooRepository, MySqlFooRepository, or FakeFooRepository, should make no difference whatsoever: each implementation fulfills the interface’s contract, each implementation could be swapped for another without altering the logic of the procedure.

Interface Segregation Principle

Write small, specialized interface with a clear purpose, that won’t likely need to grow new members in the future: IFooRepository.GetById is probably fine, but IFooRepository.GetByName looks like someone had a specific or particular implementation in mind when they designed the interface, and now you need to implement a GetByName method for a repository where that makes no sense.

Dependency Inversion Principle

Depend on abstractions, not concrete implementations – your code has dependencies, and you want them abstracted away behind interfaces that you receive as parameters.


What is a dependency?

You’re writing a procedure, and you need to invoke a method that belongs to another object or module – say, MsgBox: with it your procedure can warn the user of an error, or easily get a yes/no answer. But this ability comes with a cost: now there’s no way to invoke that procedure without popping a message box and stopping execution until it’s dismissed. Hard-wired dependencies make unit testing difficult (if not impossible), so we inject them instead, as abstractions.

And dependency injection?

MsgBox is a bad example – Rubberduck’s FakesProvider already lets you configure MsgBox calls any way your testing requires, and no pop-up ..pops up. But let’s say the procedure needs to do things to a Worksheet.

We could make the procedure take a Worksheet parameter, and that would be method injection.

Since we’re in a class module (right?), we could have a Property Set member that takes a Worksheet value argument and assigns it to a Worksheet instance field that our method can work with, and that would be property injection.

We could have a factory method on our class’ default instance, that receives a Worksheet argument and property-injects it to a New instance of the class, then returns an instance of the class that’s ready to use (behind an interface that doesn’t expose any Property Set accessor for the injected dependencies), and that would be as close to the ideal constructor injection as you could get in a language without constructors.

What “control” is inverted, and why?

When a method News up all its dependencies, it’s a control freak that doesn’t let the outside world know anything about what objects it needs to do its job: it’s a black box that other code needs to take as “it just works”, and we can’t do much to alter how it works.

With inversion of control (IoC), you give up that control and let something else New things up for you, and that’s why Dependency Injection (DI) goes hand-in-hand with it. IoC implies completely reversing the dependency graph. Take a UserForm that reads from / writes to a worksheet, with code-behind that implements every little bit of what needs to happen in CommandButton1_Click handlers – a “Smart UI” – reversing the dependency graph means the form’s code-behind is now only concerned about the data it needs to present to the user, and the data it needs to collect from the user; the CommandButton1 button was renamed to AcceptButton, and its Click handler does one single thing: it invokes a SaveChangesCommand object’s Execute method, and everything that was in that click handler is now in that ICommand implementation. The command knows nothing of any userform; it works with the model, that it receives in an Object parameter to its Execute method.

It all comes down to one thing: testability. You want to test your commands and what they do, how they manipulate the model – so you pull as much as possible out of UI-dependent code and into specialized classes that only know as much as they need to know. The form’s code-behind (aka the view) knows about the model, the commands; the model knows about nothing but itself; the commands know about the model; a presenter would know about both the view and the model, but shouldn’t need to care for commands.

If none of the components create their dependencies / if all components have their dependencies injected, then if we follow the dependency chain we arrive to an entry point: in VB6 that would be some Public Sub Main(); in VBA, that could be any Public Sub procedure / “macro” in a standard module, or any Worksheet or Workbook event handler. These entry points all need to New up (or otherwise provide) everything in the dependency graph (e.g. class1 depends on class2 which depends on class3 and class4, …), and then invoke the desired functionality.

What is testable code?

Testable code is code for which you can fully control/inject all the dependencies of that code. This is where coding against abstractions pays off: you can leverage polymorphism and implement test doubles / stubs / fakes as needed. The presence of a New keyword in a method is a rather obvious indicator of a dependency; it’s the implicit dependencies that are harder to spot. These could be a MsgBox prompt, a UserForm dialog, but also Open, Close, Write, Kill, Name keywords, or maybe ActiveSheet, or ActiveWorkbook implicit member calls against a hidden global object; even the current Date can be a hidden dependency if it’s involved in behavior you want to cover with one or more unit tests. The Rnd function is definitely a dependency as well.

SOLID code is inherently testable code. If you write the tests first (Test-Driven Development / TDD), you could even conceivably end up with SOLID-compliant code out of necessity.

Say you want to bring up a dialog that collects some inputs, and one of these inputs needs to be a decimal value greater than or equal to 0 but less than 1 – what are the odds that such validation logic ends up buried in some TextBox12_Change handler (and duplicated in 3 places) in the UserForm module if the problem is tackled from a testability standpoint? That’s right: exactly none.

If the first thing you do is create a MyViewModelTests module with a MySpecialDecimal_InvalidIfGreaterThanOne test method, there’s a good chance your next move could be to add a MyViewModel class with a MySpecialDecimal property – be it only so that the test method can compile:

'@TestMethod("ValidationTests")
Public Sub MySpecialDecimal_InvalidIfGreaterThanOne()
    Dim sut As MyViewModel
    Set sut = New MyViewModel
    sut.MySpecialDecimal = 42
    Assert.IsFalse sut.IsValid
End Sub

So we need this MyViewModel.IsValid member now:

Public Property Get IsValid() As Boolean
End Property

At this point we can run the test in Rubberduck’s Test Explorer, and see it fail. Never trust a test you’ve never seen fail! The next step is to write just enough code to make the test pass:

Public Property Get IsValid() As Boolean
    IsValid = MySpecialDecimal < 1
End Property

This prompts us to write another test that we know would fail:

'@TestMethod("ValidationTests")
Public Sub MySpecialDecimal_InvalidIfNegative()
    Dim sut As MyViewModel
    Set sut = New MyViewModel
    sut.MySpecialDecimal = -1
    Assert.IsFalse sut.IsValid
End Sub

So we tweak the code to make it pass:

Public Property Get IsValid() As Boolean
    IsValid = MySpecialDecimal >= 0 And MySpecialDecimal < 1
End Property

We then run the whole test suite, to validate that this change didn’t break any green test, which would mean a regression bug was introduced – and the red test is telling you exactly which input scenario broke.


In a vanilla VBE, OOP quickly gets out of hand, for any decently-sized project: wading through many class modules in the legacy editor, locating implementations of the interfaces you’re coding against – things that you would seamlessly deal with in a modern IDE, become excruciatingly painful when modules are all listed alphabetically under one single “classes” folder, and when Ctrl+F “Implements {name}” is the only thing that can help you locate interface implementations.

Rubberduck not only addresses the organization of your OOP project (with “@Folder” annotations that let you organize & regroup modules by functionality) and enhances navigation tooling (“find all implementations”, “find all references”, “find symbol”, etc.), it also provides a unit testing framework, so that testing your VBA code is done the same way it’s done in other languages and modern IDEs, with Assert expressions that make or break a green test.

But if you write unit tests for your object-oriented VBA code, you’ll quickly notice that when your tests need to inject a fake implementation of a dependency, a consequence is that you often end up with a lot of “test fake” classes whose sole purpose is to support unit testing. This is double-edged, because you need to be careful that you’re testing the right thing (i.e. the actual object/method under test) and not whether your test fake/stub is behaving correctly.

Rubberduck has well over 5K unit tests, and most of them would be very hard to implement without the ability to setup proper mocking. Using the popular Moq framework, we are able to create and configure these “test fakes” without actually writing a class that implements the interface we need to inject into the component we’re testing.

Soon, these capabilities will land in the VBA landscape, with Rubberduck’s unit testing tools wrapping up Moq to let VBA code do exactly that.