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:

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
.

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 (implementsIMacro
) - 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.