Tag: macros

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