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
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
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
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 DoSomethingmacro procedure
- the command is the
- the concrete command is the
SomeDbServicedependency 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.
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
Booleanvalue that indicates whether the command can currently be executed.
Executeprocedure 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
'@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.
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
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.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.
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.