A little while ago already, I went and explored dynamic UI with MSForms in VBA through a lens tinted with Windows Presentation Foundation (WPF) concepts, and ended up implementing a working prototype Model-View-ViewModel (MVVM) framework for VBA… across a hundred and some modules covering everything from property and command bindings to input and model validation. I’m still planning to build an actual COM library for it one day – for now I’m entirely focused on everything around Rubberduck3.
Although… the last month or so has actually been mostly about publishing the new website and setting up the Ko-fi shop: the new website is not without issues (search links are broken, for one), but the source code ownership has been transferred to the rubberduck-vba organization on GitHub and I’m satisfied enough with it to move on.
But then there’s operating the shop. When an order comes in, there’s a worksheet (duh!) with a Sales table where I enter the invoice line items sold using a Stock Keeping Unit (SKU) code that identifies each item sold; the Inventory table picks up the sale and calculates a new Available to Sell figure.
But tracking items sold isn’t the whole picture: an Invoice table tracks the actual totals including the shipping charges and actual shipping costs (currently 24% underwater, but I’ve since adjusted the shipping charges to better reflect reality), computing the Cost of Goods Sold, and ultimately a profit margin.
So for each invoice, I know I need:
Invoice number and date
Billing/shipping information (name, address, etc.)
The number of units sold per SKU, with the amount paid by the customer
The shipping charge paid by the customer
And then I manually prepare the invoice document. Such a waste of time, right? Of course I couldn’t leave it at that – all I needed was a UserForm to enter all that, and a command that would update the merchandise planning workbook and prepare the invoice document for me.
Thing is, I wanted that form to use property bindings and some extent of MVVM, but I wasn’t going to import the 100+ modules of the old MVVM prototype code. So instead, I made a “lite” version.
Bindings and the propagation of property value changes are the core mechanics that make MVVM work, and we don’t need dozens of classes for that.
We do need INotifyPropertyChanged and IHandlePropertyChanged interfaces:
Option Explicit
Public Sub OnPropertyChanged(ByVal Source As Object, ByVal Name As String)
End Sub
Public Sub RegisterHandler(ByVal Handler As IHandlePropertyChanged)
End Sub
Option Explicit
Public Sub OnPropertyChanged(ByVal Source As Object, ByVal Name As String)
End Sub
These interfaces are important, because the bindings need to handle property changed events; the View Model needs to invoke the registered callbacks. This is used in place of actual events, because interfaces in VBA don’t expose events, and we want an abstraction around property changes, so that everything that needs to notify about property changes can do so in a standardized way.
The IHandlePropertyChanged interface is to be implemented by property binding classes, such as this TextBoxValueBinding class:
Option Explicit
Implements IHandlePropertyChanged
Private WithEvents UI As MSForms.TextBox
Private Type TBinding
Source As Object
SourceProperty As String
End Type
Private This As TBinding
Public Sub Initialize(ByVal Control As MSForms.TextBox, ByVal Source As Object, ByVal SourceProperty As String)
Set UI = Control
Set This.Source = Source
This.SourceProperty = SourceProperty
If TypeOf Source Is INotifyPropertyChanged Then RegisterPropertyChanges Source
End Sub
Private Sub RegisterPropertyChanges(ByVal Source As INotifyPropertyChanged)
Source.RegisterHandler Me
End Sub
Private Sub IHandlePropertyChanged_OnPropertyChanged(ByVal Source As Object, ByVal Name As String)
If Source Is This.Source And Name = This.SourceProperty Then
UI.Text = VBA.Interaction.CallByName(This.Source, This.SourceProperty, VbGet)
End If
End Sub
Private Sub UI_Change()
VBA.Interaction.CallByName This.Source, This.SourceProperty, VbLet, UI.Value
End Sub
A binding has a source and a target object and property; the source is a ViewModel object, and the target is a MSForms control, in this case a TextBox. The binding must handle the control’s events to update the source whenever the value of the target changes. In this limited version we’re only going to handle the Change event, but if we wanted we could go further and handle KeyDown here to implement input validation. Some error handling wouldn’t hurt, either.
Because everything that involves notifying about property changes is standardized through interfaces, we can make a PropertyChangeNotification helper class to register the handlers:
Option Explicit
Private Handlers As VBA.Collection
Public Sub AddHandler(ByVal Handler As IHandlePropertyChanged)
Handlers.Add Handler
End Sub
Public Sub Notify(ByVal Source As Object, ByVal Name As String)
Dim Handler As IHandlePropertyChanged
For Each Handler In Handlers
Handler.OnPropertyChanged Source, Name
Next
End Sub
Private Sub Class_Initialize()
Set Handlers = New VBA.Collection
End Sub
This class is responsible for holding a reference to a collection of handlers, and a Notify method invokes the OnPropertyChange method on each registered handler.
ViewModel
The OrderHeaderModel class is the binding source, so it exposes a property representing the value of each field in the form. The Property Let procedures are all structured as follows:
If current encapsulated value is not equal to the new value…
Set the current value to the new value
Notify of a property change
ViewModel classses need to implement INotifyPropertyChange, and the implementation simply uses an instance of the helper class above to do its thing:
Option Explicit
Implements INotifyPropertyChanged
Private Notification As New PropertyChangeNotification
'...
Private Sub OnPropertyChanged(ByVal Name As String)
INotifyPropertyChanged_OnPropertyChanged Me, Name
End Sub
Private Sub INotifyPropertyChanged_OnPropertyChanged(ByVal Source As Object, ByVal Name As String)
Notification.Notify Source, Name
End Sub
Private Sub INotifyPropertyChanged_RegisterHandler(ByVal Handler As IHandlePropertyChanged)
Notification.AddHandler Handler
End Sub
The private OnPropertyChanged method further simplifies the notification by providing the Source argument, which needs to be an instance of the ViewModel, so that’s always Me. So the properties all look more or less like this:
Public Property Get OrderNumber() As Long
OrderNumber = This.OrderNumber
End Property
Public Property Let OrderNumber(ByVal Value As Long)
If This.OrderDate <> Value Then
This.OrderNumber = Value
OnPropertyChanged "OrderNumber"
End If
End Property
The ViewModel is inherently domain-specific, so for a form that collects information about an order we’re going to be looking at properties like OrderNumber, OrderDate, BillToName, ShipToAddress, etc.; in another application, a ViewModel could be a completely different thing – it all really depends on what the thing is meant to do. But no matter what the domain is, a ViewModel will be implementing INotifyPropertyChanged as shown above.
View
Implementing the View (the form’s code-behind module) boils down to setting up all the necessary bindings, and we do this using a PropertyBindings helper module:
Option Explicit
'@Description "Binds a MSForms.Control property to a source property"
Public Function BindProperty(ByVal Control As MSForms.Control, ByVal ControlProperty As String, ByVal SourceProperty As String, ByVal Source As Object, Optional ByVal InvertBoolean As Boolean = False) As OneWayPropertyBinding
Dim Binding As OneWayPropertyBinding
Set Binding = New OneWayPropertyBinding
Binding.Initialize Control, ControlProperty, Source, SourceProperty, InvertBoolean
Set BindProperty = Binding
End Function
'@Description "Binds the Text/Value of a MSForms.TextBox to a source property"
Public Function BindTextBox(ByVal Control As MSForms.TextBox, ByVal SourceProperty As String, ByVal Source As Object) As TextBoxValueBinding
Dim Binding As TextBoxValueBinding
Set Binding = New TextBoxValueBinding
Binding.Initialize Control, Source, SourceProperty
Set BindTextBox = Binding
End Function
'@Description "Binds the Text of a MSForms.ComboBox to a String source property"
Public Function BindComboBox(ByVal Control As MSForms.ComboBox, ByVal SourceProperty As String, ByVal Source As Object) As ComboBoxValueBinding
Dim Binding As ComboBoxValueBinding
Set Binding = New ComboBoxValueBinding
Binding.Initialize Control, Source, SourceProperty
Set BindComboBox = Binding
End Function
'@Description "Binds the Value of a MSForms.CheckBox to a Boolean source property"
Public Function BindCheckBox(ByVal Control As MSForms.CheckBox, ByVal SourceProperty As String, ByVal Source As Object) As CheckBoxValueBinding
Dim Binding As CheckBoxValueBinding
Set Binding = New CheckBoxValueBinding
Binding.Initialize Control, Source, SourceProperty
Set BindCheckBox = Binding
End Function
As you can see each MSForms control gets its Binding class, and a OneWayPropertyBinding binds a source property to a target property without notifying for target changes (so without listening for control events) – this is useful for binding labels, ListBox/ComboBox contents, and anything else that doesn’t involve control events.
The form has a private ConfigureBindings method (invoked from the UserForm_Initialize handler) where we essentially map each one of the form controls to corresponding ViewModel properties:
This rather straightforward configuration completely replaces event handlers. That’s right: the bindings take care of the control events for us, so checking the ShipToSameBox checkbox automatically disables the ShipToNameLabel, ShipToAddressLabel, ShipToAddressLine1, ShipToAddressLine2, and ShipToAddressLine3 controls on the form, and un-checking it automatically enables them, and we don’t need to explicitly handle any control events to achieve this. Small note: here the View is accessing a table in InventorySheet directly, and it shouldn’t be doing that, because what SKUs are available belongs in the Model, not the View: I should instead implement a service that accesses the worksheet for me and supplies the available SKU codes.
With the form controls effectively abstracted away by the ViewModel, we never need to directly interact with MSForms to affect the View, because the property bindings do this automatically for us. This means commands can affect just the ViewModel, and doing that will automatically keep the View in sync.
Commands
This lite version of MVVM doesn’t (yet?) have command bindings, but UI commands are still abstracted behind an ICommand interface. In my case I needed a command to add a new order line item, so I implemented it like this:
Option Explicit
Implements ICommand
Private Function ICommand_CanExecute(ByVal Parameter As Object) As Boolean
ICommand_CanExecute = TypeOf Parameter Is OrderHeaderModel
End Function
Private Sub ICommand_Execute(ByVal Parameter As Object)
If Not TypeOf Parameter Is OrderHeaderModel Then Err.Raise 5
Dim Model As OrderHeaderModel
Set Model = Parameter
Dim Item As OrderLineItemModel
Set Item = New OrderLineItemModel
Item.SKU = Model.NewLineItem.SKU
Item.Quantity = Model.NewLineItem.Quantity
Item.Price = Model.NewLineItem.Price
Model.AddLineItem Item
End Sub
This code is completely oblivious of any form or form controls: it only knows about the OrderHeaderModel and OrderLineItemModel classes, and what it needs to do with them. Why bother implementing this in a separate class, rather than in the form’s code-behind?
Without command bindings, we do need to handle command buttons’ Click event:
Private Sub AddLineItemButton_Click()
CmdAddLineItem.Execute OrderModel
End Sub
I don’t like having logic in event handlers, so this one-liner is perfect. Without a command class the View would need to have more code, code that isn’t directly related to the View itself, and then the commands’ dependencies would become the View‘s dependencies, and that would be wrong: if I made a “Save to Database” button, I’d want the ADODB stuff anywhere but in the form’s code-behind; command classes can have their own dependencies, so pulling commands into their own classes keeps the View cohesive and focused on its purpose.
I’m finding that MVVM works best with relatively complex forms such as this one, where some fields’ enabled state might depend on some checkbox control’s value, for example. There’s something oddly satisfying typing something in a textbox and seeing another (disabled!) textbox get updated with the same content, knowing zero event handling is going on in the form.
Viability
If the full-featured MVVM framework isn’t viable in VBA, a more lightweight version of the UI paradigm certainly is: this particular VBA project doesn’t have dozens of class modules, and yet still manages to leverage what makes Model-View-ViewModel such a compelling architecture.
One of the objectively coolest features in Rubberduck is the Fakes API. Code that pops a MsgBox for example, needs a way to work without actually popping that message box, otherwise that code cannot be unit tested… without somehow hijacking the MsgBox function. The Fakes API does exactly that: it hooks into the VBA runtime, intercepts specific internal function calls, and makes it return exactly what your test setup …set up.
This API can stop time, or Now can be told to return 1:59AM on first invocation, 1:00AM on the next, and then we can test and assert that some time-sensitive logic survives a daylight savings time toggle, or how Timer-dependent code behaves at midnight.
Let’s take a look at the members of the IFakesProvider interface.
Fakes Provider
Fakes for many of the internal VBA standard library functions exist since the initial release of the feature, although some providers wouldn’t always play nicely together – thanks to a recent pull request from @tommy9 these issues have been resolved, and a merry bunch of additional implementations are now available in pre-release builds:
Name
Description
Parameter names
MsgBox
Configures VBA.Interaction.MsgBox calls
Fakes.Params.MsgBox
InputBox
Configures VBA.Interaction.InputBox calls
Fakes.Params.InputBox
Beep
Configures VBA.Interaction.Beep calls
Environ
Configures VBA.Interaction.Environ calls
Fakes.Params.Environ
Timer
Configures VBA.DateTime.Timer calls
DoEvents
Configures VBA.Interaction.DoEvents calls
Shell
Configures VBA.Interaction.Shell calls
Fakes.Params.Shell
SendKeys
Configures VBA.Interaction.SendKeys calls
Fakes.Params.SendKeys
Kill
Configures VBA.FileSystem.Kill calls
Fakes.Params.Kill
MkDir
Configures VBA.FileSystem.MkDir calls
Fakes.Params.MkDir
RmDir
Configures VBA.FileSystem.RmDir calls
Fakes.Params.RmDir
ChDir
Configures VBA.FileSystem.ChDir calls
Fakes.Params.ChDir
ChDrive
Configures VBA.FileSystem.ChDrive calls
Fakes.Params.ChDrive
CurDir
Configures VBA.FileSystem.CurDir calls
Fakes.Params.CurDir
Now
Configures VBA.DateTime.Now calls
Time
Configures VBA.DateTime.Time calls
Date
Configures VBA.DateTime.Date calls
Rnd*
Configures VBA.Math.Rnd calls
Fakes.Params.Rnd
DeleteSetting*
Configures VBA.Interaction.DeleteSetting calls
Fakes.Params.DeleteSetting
SaveSetting*
Configures VBA.Interaction.SaveSetting calls
Fakes.Params.SaveSetting
Randomize*
Configures VBA.Math.Randomize calls
Fakes.Params.Randomize
GetAllSettings*
Configures VBA.Interaction.GetAllSettings calls
SetAttr*
Configures VBA.FileSystem.SetAttr calls
Fakes.Params.SetAttr
GetAttr*
Configures VBA.FileSystem.GetAttr calls
Fakes.Params.GetAttr
FileLen*
Configures VBA.FileSystem.FileLen calls
Fakes.Params.FileLen
FileDateTime*
Configures VBA.FileSystem.FileDateTime calls
Fakes.Params.FileDateTime
FreeFile*
Configures VBA.FileSystem.FreeFile calls
Fakes.Params.FreeFile
IMEStatus*
Configures VBA.Information.IMEStatus calls
Dir*
Configures VBA.FileSystem.Dir calls
Fakes.Params.Dir
FileCopy*
Configures VBA.FileSystem.FileCopy calls
Fakes.Params.FileCopy
*Members marked with an asterisk are only available in pre-release builds for now.
Parameter Names
The IVerify.ParameterXyz members make a unit test fail if the specified parameter wasn’t given a specified value, but the parameter names must be passed as strings. This is a UX issue: the API essentially requires hard-coded magic string literals in its users’ code; this is obviously error-prone and feels a bit arcane to use. The IFakesProvider interface has been given a Params property that gets an instance of a class that exposes the parameter names for each of the IFake implementations, as shown in the list above, and the screenshot below:
Picking the correct parameter name from a drop-down completion list beats risking a typo, doesn’t it?
Note: the PR for this feature has not yet been merged at the time of this writing.
Testing Without Fakes (aka Testing with Stubs)
Unit tests have a 3-part structure: first we arrange the test, then we act by invoking the method we want to test; lastly, we assert that an actual result matches the expectations. When using fakes, we configure them in the arrange part of the test, and in the assert part we can verify whether (and/or how many times) a particular method was invoked with a particular parameterization.
Let’s say we had a procedure we wanted to write some tests for:
Public Sub TestMe()
If MsgBox("Print random number?", vbYesNo + vbQuestion, "Test") = vbYes Then
Debug.Print Now & vbTab & Rnd * 42
Else
Debug.Print Now
End If
End Sub
If we wanted to make this logic fully testable without the Fakes API, we would need to inject (likely as parameters) abstractions for MsgBox, Now, and Debug dependencies: instead of invoking MsgBox directly, the procedure would be invoking the Prompt method of an interface/class that wraps the MsgBox functionality. Unit tests would need a stub implementation of that interface in order to allow some level of configuration setup – an invocation counter, for example. A fully testable version of the above code might then look like this:
Public Sub TestMe(ByVal MessageBox As IMsgBox, ByVal Random As IRnd, ByVal DateTime As IDateTime, ByVal Logger As ILogger)
If MessageBox.Prompt("Print random number?", "Test") = vbYes Then
Logger.LogDebug DateTime.Now & vbTab & Random.Next * 42
Else
Logger.LogDebug DateTime.Now
End If
End Sub
The method is testable, because the caller controls all the dependencies. We’re probably injecting an IMsgBox that pops a MsgBox, an IRnd that wraps Rnd, a DateTime parameter that returns VBA.DateTime.Now and an ILogger that writes to the debug pane, but we don’t know any of that. I fact, we could very well run this method with an ILogger that writes to some log file or even to a database; the IRnd implementation could consistently be returning 0.4 on every call, IDateTime.Now could return Now adjusted to UTC, and IMsgBox might actually display a fancy custom modal UserForm dialog – either way, TestMe doesn’t need to change for any of that to happen: it does what it needs to do, in this case fetching the next random number and outputting it along with the current date/time if a user prompt is answered with a “Yes”, otherwise just output the current date/time. It’s the interfaces that provide the abstraction that’s necessary to decouple the dependencies from the logic we want to test. We could implement these interfaces with stubs that simply count the number of times each member is invoked, and the logic we’re testing would still hold.
We could then write tests that validate the conditional logic:
'@TestMethod
Public Sub TestMe_WhenPromptYes_GetsNextRandomValue()
' Arrange
Dim MsgBoxStub As StubMsgBox ' implements IMsgBox, but we want the stub functionality here
Set MsgBoxStub = New StubMsgBox
MsgBoxStub.Returns vbYes
Dim RndStub As StubRnd ' implements IRnd, but we want the stub functionality here too
Set RndStub = New StubRnd
' Act
Module1.TestMe MsgBoxStub, RndStub, New DateTimeStub, New LoggerStub
' Assert
Assert.Equals 1, RndStub.InvokeCount
End Sub
'@TestMethod
Public Sub TestMe_WhenPromptNo_DoesNotGetNextRandomValue()
' Arrange
Dim MsgBoxStub As StubMsgBox
Set MsgBoxStub = New StubMsgBox
MsgBoxStub.Returns vbNo
Dim RndStub As StubRnd
Set RndStub = New StubRnd
' Act
Module1.TestMe MsgBoxStub, RndStub, New DateTimeStub, New LoggerStub
' Assert
Assert.Equals 0, RndStub.InvokeCount
End Sub
These stub implementations are class modules that need to be written to support such tests. StubMsgBox would implement IMsgBox and expose a public Returns method to configure its return value; StubRnd would implement IRnd and expose a public InvokeCount property that returns the number of times the IRnd.Next method was called. In other words, it’s quite a bit of boilerplate code that we’d usually rather not need to write.
Let’s see how using the Fakes API changes that.
Using Rubberduck.FakesProvider
The standard test module template defines Assert and Fakes private fields. When early-bound (needs a reference to the Rubberduck type library), the declarations and initialization look like this:
'@TestModule
Option Explicit
Option Private Module
Private Assert As Rubberduck.AssertClass
Private Fakes As Rubberduck.FakesProvider
'@ModuleInitialize
Public Sub ModuleInitialize()
Set Assert = CreateObject("Rubberduck.AssertClass")
Set Fakes = CreateObject("Rubberduck.FakesProvider")
End Sub
The Fakes API implements three of the four stubs for us, so we still need an implementation for ILogger, but now the method remains fully testable even with direct MsgBox, Now and Rnd calls:
Public Sub TestMe(ILogger Logger)
If MsgBox("Print random number?", vbYesNo + vbQuestion, "Test") = vbYes Then
Logger.LogDebug Now & vbTab & Rnd * 42
Else
Logger.LogDebug Now
End If
End Sub
With an ILogger stub we could write a test that validates what’s being logged in each conditional branch (or we could decide that we don’t need an ILogger interface and we’re fine with tests actually writing to the debug pane, and leave Debug.Print statements in place), but let’s just stick with the same two tests we wrote above without the Fakes API. They look like this now:
'@TestMethod
Public Sub TestMe_WhenPromptYes_GetsNextRandomValue()
' Arrange
Fakes.MsgBox.Returns vbYes
' Act
Module1.TestMe New LoggerStub ' ILogger is irrelevant for this test
' Assert
Fakes.Rnd.Verify.Once
End Sub
'@TestMethod
Public Sub TestMe_WhenPromptNo_DoesNotGetNextRandomValue()
' Arrange
Fakes.MsgBox.Returns vbNo
' Act
Module1.TestMe New LoggerStub ' ILogger is irrelevant for this test
' Assert
Fakes.Rnd.Verify.Never
End Sub
We configure the MsgBox fake to return the value we need, we invoke the method under test, and then we verify that the Rnd fake was invoked once or never, depending on what we’re testing. A failed verification will fail the test the same as a failed Assert call.
The fakes automatically track invocations, and remember what parameter values each invocation was made with. Setup can optionally supply an invocation number (1-based) to configure specific invocations, and verification can be made against specific invocation numbers as well, and we could have a failing test that validates whether Randomize is invoked when Rnd is called.
API Details
The IFake interface exposes members for the setup/configuration of fakes:
Name
Description
AssignsByRef
Configures the fake such as an invocation assigns the specified value to the specified ByRef argument.
Passthrough
Gets/sets whether invocations should pass through to the native call.
RaisesError
Configures the fake such as an invocation raises the specified run-time error.
Returns
Configures the fake such as the specified invocation returns the specified value.
ReturnsWhen
Configures the fake such as the specified invocation returns the specified value given a specific parameter value.
Verify
Gets an interface for verifying invocations performed during the test. See IVerify.
The members of Rubberduck.IFake
The IVerify interface exposes members for verifying what happened during the “Act” phase of the test:
Name
Description
AtLeast
Verifies that the faked procedure was called a specified minimum number of times.
AtLeastOnce
Verifies that the faked procedure was called one or more times.
AtMost
Verifies that the faked procedure was called a specified maximum number of times.
AtMostOnce
Verifies that the faked procedure was not called or was only called once.
Between
Verifies that the number of times the faked procedure was called falls within the supplied range.
Exactly
Verifies that the faked procedure was called a specified number of times.
Never
Verifies that the faked procedure was called exactly 0 times.
Once
Verifies that the faked procedure was called exactly one time.
Parameter
Verifies that the value of a given parameter to the faked procedure matches a specific value.
ParameterInRange
Verifies that the value of a given parameter to the faked procedure falls within a specified range.
ParameterIsPassed
Verifies that an optional parameter was passed to the faked procedure. The value is not evaluated.
ParameterIsType
Verifies that the passed value of a given parameter was of a type that matches the given type name.
The members of Rubberduck.IVerify
There’s also an IStub interface: it’s a subset of IFake, without the Returns setup methods. Thus, IStub is used for faking Sub procedures, and IFake for Function and Property procedures.
When to Stub Standard Library Members
Members of VBA.FileSystem not covered include EOF and LOF functions, Loc, Seek, and Reset. VBA I/O keywords Name, Open, and Close operate at a lower level than the standard library and aren’t covered, either. VBA.Interaction.CreateObject and VBA.Interaction.GetObject, VBA.Interaction.AppActivate, VBA.Interaction.CallByName, and the hidden VBA.Interaction.MacScript function, aren’t implemented.
Perhaps CreateObject and GetObject calls belong behind an abstract factory and a provider interface, respectively, and perhaps CallByName doesn’t really need hooking anyway. In any case there are a number of file I/O operations that cannot be faked and demand an abstraction layer between the I/O work and the code that commands it: that’s when you’re going to want to write stub implementations.
If you’re writing a macro that makes an HTTP request and processes its response, consider abstracting the HttpClient stuff behind an interface (something like Function HttpGet(ByVal Url As String)): the macro code will gain in readability and focus, and then if you inject that interface as a parameter, then a unit test can inject a stub implementation for it, and you can write tests that handle (or not?) an HTTP client error, or process such or such JSON or HTML payload – without hitting any actual network and making any actual HTTP requests.
Until we can do mocking with Rubberduck, writing test stubs for our system-boundary interfaces is going to have to be it. Mocking would remove the need to explicitly implement most test stubs, by enabling the same kind of customization as with fakes, but with your own interfaces/classes. Or Excel’s. Or anything, in theory.
As Rubberduck started to beef up its static code analysis capabilities in late 2015, it became evident that writing VBA (or VB6) code with Rubberduck loaded up in the Visual Basic Editor (VBE) would inevitably change not only how we work in VBA, but also how we write our VBA code in the first place.
“Rubberduck is essentially providing a bridge between VBA land where people just get in and have a go and the VS land where if you don’t know a great deal about software development, you just waste your time and burn. Rubberduck will put a lot of people on a big learning curve and this will result in a lot of questions.” – AndrewM-commented on Oct 9, 2015
There’s an old issue (#823, still opened as of this writing) about having a coding style guide somewhere, that would enshrine the philosophy behind what Rubberduck is, in a way, trying to make your code-writing be/become; I think that was a great idea and I’m hoping this post captures the essence of it, at least as far as thinking code goes.
About Code Inspections
If you fire up Rubberduck on any legacy VBA project with any significant amount of code, there’s a very high probability that static code analysis generates tons of inspection results, for various mundane little things. Should your goal be to quick-fix all the things and have code that doesn’t spawn any Rubberduck inspection results?
Perhaps surprisingly, the answer is a resounding “no”.
Severity Levels
In Rubberduck each inspection has a configurable “severity level” that defaults to Warning for most inspections (it’s the default-unless-specified-otherwise for all Rubberduck inspections):
Error level indicates a potential problem you likely want to pay immediate attention to, because it could be (or cause) a bug. If inspection results rendered in the code pane, these would be red squiggly underlines.
Warning level indicates a potential issue you should be aware of.
Suggestion level is usually used for various opportunities, not necessarily problems.
Hint level is also for various non-problematic opportunities. If inspection results rendered in the code pane, these would be a subtle dotted underline with a hover text.
DoNotShow disables the inspection: not only its results won’t show, they won’t even be generated.
By default, Rubberduck is configured to run all (that’s currently over 110, counting the hidden/Easter egg ones) inspections, with a handful of cherry-picked exceptions for inspections that would be flagging the exact opposite situation that another enabled inspection is already flagging – for example we ship implicit ByRef modifier enabled (as a Hint), but redundant ByRef modifier is disabled unless you give it a severity level that’s anything other than DoNotShow. This avoids “fixing” one inspection result only to get a new one flagging the exact opposite, which would be understandably confusing for users that aren’t familiar with static code analysis tooling.
Are inspections somehow imbued with the knowledge of whether you should treat them as errors, warnings, or mere hints and suggestions? Sometimes, yes. Missing Option Explicit should make a clear consensus at Error level. On the flipside, whether an implicit default member call or the use of an empty string literal should be a Warning, a Hint, or shown at all probably depends more on how comfortable or experienced you are with VBA/VB6 as a language, or could be just a personal preference; what matters is that the static code analysis tooling is letting you know something about the code, that the code alone isn’t necessarily saying.
Philosophy
One of the very first inspection to be implemented in Rubberduck was the Option Explicit inspection. Okay, part of it was just because it was a trivial one to implement even before we had an actual parser… but the basic idea was (and still is) that nobody knows everything, and it’s with our combined knowledge that we make a mighty bunch, and that is why static code analysis in Rubberduck explains the reasoning behind each inspection result: there are quite many things Rubberduck warns of, that I had no idea about 10 or 15 years ago. That never stopped me (and won’t stop you either) from writing VBA code that worked perfectly fine (except when it didn’t), but whether we realize and accept it or not… a macro written in VBA code is a set of executable instructions, which makes it a program, which makes the act of writing it programming, which makes us programmers.
Being programmers that write and maintain VBA code does set us apart, mostly because the language isn’t going anywhere and the IDE is becoming more and more severely outdated and under-featured as years pass. Yet if the volume of VBA questions on Stack Overflow means anything, VBA is still very much alive, still very much being learned, and this is where Rubberduck and static code analysis comes in.
When I started learning about .NET and C# over a decade ago, there was this exciting new language feature they called LINQ for Language-INtegrated-Query where you could start querying object collections pretty much literally like you would a database, and it was awesome (still is!). In order to make this possible, the C# compiler and the .NET framework and runtime itself had to undergo some very interesting changes Jon Skeet covers in details, but the point is… the new syntax was a bit off-putting at first, and came with new and important implications (closures, deferred execution), and the company I worked for gave us all a ReSharper license, and that is how and when I discovered that thorough & accurate static code analysis tooling could be a formidable educational tool.
Maybe we don’t agree about Hungarian Notation, and that’s fine: Rubberduck wants you to be able to find it and rename it if that’s what you want to do, but you can mute that particular inspection anytime. But I believe the tool should tell you what Systems Hungarian notation is when it calls it out, and perhaps it should even explain what Apps Hungarian is and give examples, because Apps Hungarian notation absolutely is useful and meaningful (think o-for-OneBased, or src-for-Source and dst-for-Destination prefixes). But str-for-String, lng-for-Long, o-for-Object is different, in a bad kind of way.
Rubberduck flags obsolete code constructs and keywords, too. Global declarations, On Local Error statements, explicit Call statements, While...Wend loops, all have no reason to exist in brand new, freshly-written VBA code, and quick-fixes can easily turn them into Public declarations, On Error statements, implicit Call statements (without the Call keyword!), and Do While...Loop structures.
Rubberduck wants to push your programming towards objectively, quantitatively better code.
About Code Metrics
Rubberduck could count the number of lines in a procedure, and issue an inspection result when it’s above a certain configurable threshold. In fact, things are slowly falling into place for it to eventually happen. But we wouldn’t want you to just arbitrarily cut a procedure scope at 20 lines because an inspection said so! Rubberduck can measure line count, nesting levels, and cyclomatic complexity. These metrics can be used to identify problematic areas in a code base and methodically split up large complex problems into measurably much smaller and simpler ones.
Line Countsimply counts the number of lines. Eventually this would expand into Statements and Comments counts, perhaps with percentages; 10% comments is probably considered a good sign, for example. But no tool is going to tell you that ' increments i is a useless comment, and even the best tools would probably not tell the difference between a huge ' the following chunk of code does XYZ banner comment and an actually valuable comment. Common wisdom is to keep this line count metric down as much as possible, but one should not do this at the expense of readability.
Nesting Levels counts the number of… well, nesting levels. While nesting two For...Next loops to iterate a 2D array (or a Range of cells) down and across is probably reasonable, further nesting is probably better off made implicit through a procedure call. Rule of thumb, it’s always good idea to pull the body of a loop into its own parameterized procedure scope. Arrow-shaped code gets flattened, line count gets lower, and procedures become more specialized and have fewer reasons to fail that way.
Cyclomatic Complexity essentially calculates the number of independent execution paths in a given procedure (wikipedia). A procedure with a cyclomatic complexity above 5 is harder to follow than one with a complexity of 1 or 2, but it’s not uncommon for a “God procedure” with nested loops and conditionals to measure in the high 40s or above.
The code metrics feature will eventually get all the attention it deserves, but as with inspections the general idea is to highlight procedures that could be harder to maintain than necessary, and nudge our users towards:
Writing more, smaller, more specialized procedure scopes.
Passing parameters between procedures instead of using global variables.
Having more, smaller, more cohesive modules.
Navigating the VBE
You may or may not have noticed, but the Visual Basic Editor is nudging you in the exact opposite direction, because…
Having fewer, larger, more general-purpose procedures puts you in a scripting mindset.
Using globals instead of passing parameters around is perhaps a simpler thing to do.
Having fewer, larger, more general-purpose modules makes it simpler to share the code between projects, and arguably easier to find things in the Project Explorer.
If you’re actually writing a small script, you can and probably should absolutely do that.
But if you’re like me then you’ve been pushing VBA to do things it wasn’t really meant to do, and you’re maintaining actual applications that could just as well be written in any other language out there, but you’re doing it in VBA because [your reasons are valid, whatever they are].
And that’s kind of a problem, because the VBE seems to actively not want you to write proper object-oriented code: its navigation tooling indeed makes it very hard to work in a project with many small modules, let alone an OOP project involving explicit interfaces and high abstraction levels.
Rubberduck lifts pretty much all the IDE limitations that hinder treating a VBA project as more than just an automation script. Now you can have a project with 135 class modules, all neatly organized by functionality into folders that can contain any module type, so a UserForm can appear right next to the classes that use it, without needing to resort to any kind of ugly prefixing schemes. You can right-click on an abstract interface (or one of its members) and quickly find all classes that implement it. You get a Find symbol command that lets you quickly navigate to literally anything that has a name, anywhere in the project. Curious about the definition of a procedure, but don’t want to break your flow by navigating to it? Peek definition (currently only in pre-release builds) takes you there without leaving where you’re at.
The Peek Definition command pops a floating panel conveniently showing the source code for the user-defined module or member you’ve selected.Find all References shows all the places a given identifier is being used, and shows it in context so you can easily locate the specific usage you’re looking for – and then a double-click takes you there.The Find all Implementations command is incredibly useful in object-oriented projects that leverage polymorphism through abstract interfaces: quickly locate and navigate to any implementation of any interface (class or member).
The VBE’s Project Explorer aims to give you a bird’s eye view of your project, regrouping modules by module type which is great for a small script that can get away with a small number of components, but that makes it very hard to manage larger projects. With Rubberduck’s Code Explorer you get to drill down to member level, and regroup modules by functionality using an entirely customizable folder hierarchy:
The Code Explorer leaves the VBE’s Project Explorer in the dust, fair & square.
These navigational enhancements greatly simplify moving around a project of any size, although some of them might feel a bit overkill in a smaller project, and some of them are only useful in more advanced OOP scenarios. Still, having more than just a text-based search to look for things is very useful.
Guidelines
If there’s one single over-arching principle guiding everything else, it would have to be write code that does what it says, and says what it does. Everything else seems to stem from this. These are warmly recommended guidelines, not dogma.
Naming
Use PascalCase if you like. Use camelCase if you like. Consistency is what you want to shoot for, and in a case-insensitive language that only stores a single version of any identifier name it’s much easier and simpler to just use PascalCase everywhere and move on to more interesting things, like tabs vs spaces.
Avoid _ underscores in identifier names, especially in procedure/member names.
Causes compile errors with Implements.
Use meaningful names that can be pronounced.
Avoid disemvoweling (arbitrarily stripping vowels) and Systems Hungarian prefixing schemes.
A series of variables with a numeric suffix is a missed opportunity to use an array.
A good identifier name is descriptive enough that it doesn’t need an explainer comment.
Use a descriptive name that begins with a verb for Sub and Function procedures.
Use a descriptive name (a noun) for Property procedures and modules.
For object properties, consider naming them after the object type they’re returning, like Excel.Worksheet.Range returns a Range object, or like ADODB.Recordset.Fields returns a Fields object.
Appropriately name everything the code must interact with: if a rounded rectangle shape is attached to a DoSomething macro, the default “Rounded Rectangle 1” name should be changed to “DoSomethingButton” or something that tells us about its purpose. This includes all controls on a UserForm designer, too. CommandButton12 is useless; SearchButton is much better. Consider also naming the controls that don’t necessarily interact with code, too: future code might, and the author of that future code will appreciate that the bottom panel is named BottomPanel and not Label34.
Renaming
Naming is hard enough, renaming things should be easy. With Rubberduck’s Rename refactoring (Ctrl+Shift+R) you can safely rename any identifier once, and all references to that identifier automatically get updated. Without a refactoring tool, renaming a form control can only be done from the Properties toolwindow (F4), and doing this instantly breaks any event handlers for it; renaming a variable by hand can be tedious, and renaming a single-letter variable like a or i with a local-scope find/replace (Ctrl+H) can get funny if the scope has any comments. Rubberduck knows the exact location of every reference to every identifier in your project, so if you have a module with two procedures that each declare a localThing, when you rename the local variable localThing in the first procedure, you’re not going to be affecting the localThing in the other procedure. But if you rename CommandButton1 to OkButton, then CommandButton1_Click() becomes OkButton_Click().
Parameters & Arguments
Prefer passing values as parameters instead of bumping the scope of a variable to module-level, or instead of declaring global variables.
Pass parameters ByVal whenever possible.
Arrays and User-Defined Type structures cannot and should not be passed by value.
Objects are never passed anywhere no matter the modifier: it’s only ever (ByVal: a copy of) a pointer that gets passed around – and most of the time the intent of the author is to pass that pointer by value. A pointer is simply a 32-bit or 64-bit integer value, depending on the bitness of the process; passing that pointer ByRef (explicitly or not) leaves more opportunities for programming errors.
Use an explicit ByRef modifier whenever passing parameters by reference.
Consider specifying an out prefix to name ByRef return parameters.
Consider using named arguments for out-prefixed ByRef return parameters.
Comments
Use the single quote ' character to denote a comment.
Avoid line-continuing comments; use single quotes at position 1 of each line instead.
Consider having a @ModuleDescription annotation at the top of each module.
Consider having a @Description annotation for each Public member of a module.
Remove comments that describe what an instruction does, replace with comments that explain why an instruction needs to do what it does.
Remove comments that summarize what ablock of code does; replace with a call to a new procedure with a nice descriptive name.
Avoid cluttering a module with banner comments that state the obvious. We know they’re variables, or properties, or public methods: no need for a huge green comment banner to tell us.
Avoid cluttering a procedure scope with banner comments that split up the different responsibilities of a procedure: the procedure is doing too many things, split it up and appropriately name the new procedure instead.
Variables
Declare all variables, always. Option Explicit should be enabled at all times.
Declare an explicit data type, always. If you mean As Variant, make it say As Variant.
Consider using a Variant to pass arrays between scopes, instead of typed arrays (e.g. String()).
Pluralize these identifier names: it signals a plurality of elements/items much more elegantly than Pirate Notation (arr*) does.
Avoid Public fields in class modules; encapsulate them with a Property instead.
Consider using a backing user-defined Private Typestructure for the backing fields of class properties; doing so eliminates the need for a prefixing scheme, lets a property be named exactly as per its corresponding backing field, and cleans up the locals toolwindow by grouping the fields under a single module variable.
Limit the scope of variables as much as possible. Prefer passing parameters and keeping the value in local scope over promoting the variable to a larger scope.
Declare variables where you’re using them, as you need them. You should never need to scroll anywhere to see the declaration of a variable you’re looking at.
Late Binding
Late binding has precious little to do with CreateObject and whether or not a library is referenced. In fact, late binding happens implicitly rather easily, and way too often. Strive to remain in early-bound realm all the time: when the compiler / IntelliSense doesn’t know what you’re doing, you’re on your own, and even Option Explicit can’t save you from a typo (and error 438).
Avoid making a member call against Object or Variant. If a compile-time type exists that’s usable with that object, a local variable of that data type should be assigned (Set) the Object reference and the member call made early-bound against this local variable.
Taking an object presenting one interface and assigning it to another data type is called “casting”.
Of course explicit late binding is OK (As Object, no library reference, create objects with CreateObject instead of the New operator). Late binding is very useful and has many legitimate uses, but generally not when the object type is accessible at compile-time through a library reference.
Avoid the dictionary-access (aka “bang”) operator !, it is late-bound by definition, and makes what’s actually a string literal read like a member name, and any member call chained to it is inevitably late-bound too. Rubberduck can parse and resolve these, but they’re much harder to process than standard method calls.
Explicitness
Use explicit modifiers everywhere (Public/Private, ByRef/ByVal).
Declare an explicit data type, even (especially!) if it’s Variant.
Note: some object models define a hidden default member (e.g. Range.[_Default]) that redirects to another member depending on its parameterization. In such cases it’s best to invoke that member directly; for example use Range.Value as appropriate, but the hidden [_Default] member is better off not being invoked at all, for both readability and performance reasons.
Invoke parameterized default members implicitly when they are indexers that get a particular item in an object collection, for example the Item property of a Collection. Invoking them explicitly doesn’t hurt, but could be considered rather verbose.
Call is not a keyword that needs to be in your program’s vocabulary when you use expressive, descriptive procedure names that imply an action taking place.
Consider explicitly qualifying standard module member calls with the project (and module) name, including for standard and referenced libraries, especially in VBA projects that reference multiple object models.
Structured Programming (Procedural)
One macro/script per module. Do have it in a module rather than a worksheet’s code-behind.
Public procedure first, followed by parameterized Private procedures, in decreasing abstraction level order such that the top reads like a summary and the bottom like boring, small but specific operations.
You know it’s done right when you introduce a second macro/module and get to pull the small, low-abstraction, specific operations into Public members of a utility module, and reuse them.
Don’t Repeat Yourself (DRY).
Consider passing the relevant state to another procedure when entering a block of code. Code is simpler and easier to follow when the body of a loop or a conditional block is pulled into its own scope.
Avoid using error handling to control the flow of execution: the best error handling is no error handling at all, because assumptions are checked and things are validated. For example instead of opening a file from a parameter value, first verify that the file exists instead of handling a file not found error… but still handle errors, for any exceptional situations that might occur while accessing the file.
When it’s not possible to avoid error handling, consider extracting a Boolean function that swallows the expected error and returns False on failure, to simplify the logic.
Handle errors around all file and network I/O.
Never trust user inputs to be valid or formatted as expected.
Object Oriented Programming
In VBA/VB6 we get to go further than mere scripting and apply Object-Oriented Programming principles, probably more relevantly so in VB6 and larger VBA projects. For many years it has been drilled into our heads that VBA/VB6 cannot do “real” OOP because it doesn’t support inheritance. The truth is that there is much, much more to OOP than inheritance, and if you want to learn and apply OOP principles in your VBA/VB6 code, you absolutely can, and you absolutely should, and Rubberduck will absolutely help you do that.
Adhere to standard OOP best practices, they are general, language-agnostic concepts that couldn’t care less about the capabilities of VBA/VB6:
Single Responsibility Principle – each abstraction should be responsible for one thing.
Open/Closed Principle – write code that doesn’t need to change unless the purpose of the abstraction itself needs to change.
Liskov Substitution Principle – code should run the exact same execution paths regardless of the concrete implementation of a given abstraction.
Interface Segregation Principle – keep interfaces small and specialized, avoid a design that constantly needs new members to be added to an interface.
Dependency Inversion Principle – depend on abstractions, not concrete implementations.
Leverage composition where inheritance would be needed.
You cannot have parameterized constructors, but you still can leverage property injection in factory methods to inject instance-level dependencies.
Leverage method injection to inject method-level dependencies.
Avoid New-ing dependencies in-place, it couples a class with another, which hinders testability; inject the dependencies instead.
Use the New keyword in your composition root, as close as possible to an entry point.
The Workbook_Open event handler (Excel) is a possible entry point.
Macros (Sub procedures invoked from outside the code) are also valid entry points.
Let go of the idea that a module must control every last one of its dependencies: let something else deal with creating or dereferencing these objects.
Inject an abstract factory when a dependency cannot or should not be created at the composition root, for example if you needed to connect to a database and wish to keep the connection object as short-lived and tightly-scoped as possible.
Keep the default instance of a class stateless as much as possible. Actively protect/guard against accidental misuse by throwing a run-time error as necessary.
Use standard modules instead of a utility class with a @PredeclaredId, that never gets explicitly instantiated or used as an actual object.
User Interfaces
UI code is inherently object-oriented, and thus a UserForm should be treated as the object it wants to be. The responsibilities of a user interface are simple: display and collect data to/from the user, and/or offer a way to execute commands (which typically consume or otherwise manipulate the data).
Avoid working directly with the form’s default instance. New it up instead.
Form module / code-behind should be strictly concerned with presentation concerns.
Do implement UI logic in form’s code-behind, e.g. enable this control when this command says it can be executed, or show this label when the model isn’t valid, etc.
Consider creating a model class to encapsulate the form’s state/data.
Expose a read/write property for each editable field on the form.
Expose a read-only property for data needed by the controls (e.g. the items of a ListBox).
Controls’ Change handlers manipulate the model properties.
Expose additional methods and properties as needed for data/input validation.
Consider having an IsValid property that returns True when all required values are supplied and valid, False otherwise; use this property to enable or disable the form’s Accept button.
Avoid implementing any kind of side-effecting logic in a CommandButton‘s Click handler. A CommandButton should invoke a command, right?
In procedural code the command might be a Public Sub procedure in a standard module named after the form, e.g. a SomeDialogCommands module for a SomeDialog form.
In OOP the command might be a property-injected instance of a DoSomethingCommand class; the Click handler invokes the command’s Execute method and could pass the model as a parameter.
Consider implementing a presenter object that is responsible for owning and displaying the form instance; the Model-View-Presenter UI pattern is well documented, and like everything OOP, its concepts aren’t specific to any language or platform.
Caveat: Microsoft Access Data-Bound UI
VBA projects hosted in Microsoft Access can absolutely use UserForm modules too, but without Rubberduck you need to hunt down the IDE command for it because it’s hidden. Instead, in Access you mostly create Access Forms, which (being document modules owned by the host application) have much more in common with a Worksheet module in Excel than with a UserForm.
The paradigm is different in an Access form, because of data bindings: a data-bound form is inherently coupled with the underlying database storage, and any effort to decouple the UI from the database is working directly against everything Access is trying to make easier for you.
Treating an Access form the way one would treat a worksheet UI in Excel puts you in a bit of a different mindset. Imagine the Battleship worksheet UI implemented as an Access form: the game would be updating game state records in the underlying database, and instead of having code to pull the game state into the UI there would only need to be code to re-query the game state, and the data bindings would take care of updating the actual UI – and then the game could easily become multi-player, with two clients connecting to the database and sharing the same game state.
This is very fundamentally different than how one would go about getting the data into the controls without such data bindings. Binding the UI directly to a data source is perfectly fine when that data source happens to be running in the very same process your VBA code is hosted in: Access’ Rapid Application Development (RAD) approach is perfectly valid in this context, and its global objects and global state make a nice beginner-friendly API to accomplish quite a lot, even with only a minimal understanding of the programming language (and probably a bit of Access-SQL).
If we’re talking about unbound MS-Access forms, then it’s probably worth exploring Model-View-Presenter and Model-View-Controller architectures regardless: in such exploratory OOP scenarios the above recommendations can all hold.
UI Design
I’m not going to pretend to be a guru of UI design, but over the years I’ve come to find myself consistently incorporating the same elements in my modal forms, and it has worked very well for me so here we go turning that into general guidelines.
TopPanel is a Label control with a white background that is docked at the top and tall enough to comfortably fit short instructions.
BottomPanel is also a Label control, with a dark gray background, docked at the bottom and no more than 32 pixels in height.
DialogTitle is another Label control with a bold font, overlapping the TopPanel control.
DialogInstructions is another Label control overlapping the TopPanel control.
DialogIcon is an Image control for a 16×16 or 24×24 .bmp icon aligned left, at the same Top coordinate as the DialogTitle control.
OkButton, CancelButton, CloseButton, ApplyButton would be CommandButton controls overlapping the BottomPanel control, right-aligned.
The actual client area content layout isn’t exactly free-for-all, and I doubt it’s possible to come up with a set of “rules” that can apply universally, but we can try, yeah?
Identify each field with a label; align all fields to make it look like an implicit grid.
Seek visual balance; ensure a relatively constant margin on all sides of the client area, space things out but not too much. Use Frame controls to group ComboBox options.
Avoid making a complex form with too many responsibilities and, inevitably, too many controls. Beyond a certain complexity level, consider making separate forms instead of tabs.
Use Segoe UI for a more modern font than MS Sans Serif.
Do not bold all the labels.
Have a ToolTip string for the label of every field the user must interact with. If a field is required or demands a particular format/pattern, indicate it.
Consider toggling the visibility of a 16×16 icon next to (or even inside, right-aligned) input fields, to clearly indicate any data validation errors (have a tooltip string on the image control with the validation error message, e.g. “this field is required”, or “value cannot be greater than 100”).
Name. All. The. Things.
Use background colors in input controls only to strongly signal something to the user, like a validation error that must be corrected in order to move on. Dark red text over a light pink background makes a very strong statement.
Keep a consistent color scheme/palette and style across all of your application’s UI components.
This pretty much concludes the “guidelines” section (although I’ll quite probably be adding more to it), but since discussing unit testing and testability lines up with everything above…
Unit Testing
A unit test is a small, simple procedure that is responsible for 3 things:
Arrange dependencies and set expectations.
Act, by invoking the method or function under test.
Assert that the expected result matches the actual one.
When a unit test runs, Rubberduck tracks Assert.Xxxx method calls and their outcome; if a single Assert call fails, the test fails. Such automated tests are very useful to document the requirements of a particular model class, or the behavior of a given utility function with multiple optional parameters. With enough coverage, tests can actively prevent regression bugs from being inadvertently introduced as the code is maintained and modified: if a tweak breaks a test, you know exactly what functionality you broke, and if all tests are green you know the code is still going to behave as intended.
Have a test module per unit/class you’re testing, and consider naming the test methods following a MethodUnderTest_GivenAbcThenXyz, where MethodUnderTest is the name of the method you’re testing, Abc is a particular condition, and Xyz is the outcome. For tests that expect an error, consider following a MethodUnderTest_GivenAbc_Throws naming pattern. Rubberduck will not warn about underscores in test method names, and these underscores are safe because Rubberduck test modules are standard modules, and unit test naming recommendations usually heavily favor being descriptive over being concise.
What to test?
You want to test each object’s public interface, and treat an object’s private members as implementation details. You do NOT want to test implementation details. For example if a class’ default interface only exposes a handful of Property Get members and a Create factory method that performs property-injection and a handful of properties, then there should be tests that validate that each of the parameters of the Create method correspond to an injected property. If one of the parameters isn’t allowed to be Nothing, then there should be a guard clause in the Create method for it, and a unit test that ensures a specific error is being raised when the Create method is invoked with Nothing for that parameter.
Below is one such simple example, where we have 2 properties and a method; note how tests for the private InjectDependencies function would be redundant if the public Create function is already covered – the InjectDependencies function is an implementation detail of the Create function:
'@PredeclaredId
Option Explicit
Implements IClass1
Private Type TState
SomeValue As String
SomeDependency As Object
End Type
Private This As TState
Public Function Create(ByVal SomeValue As String, ByVal SomeDependency As Object) As IClass1
If SomeValue = vbNullString Then Err.Raise 5
If SomeDependency Is Nothing Then Err.Raise 5
Dim Result As Class1
Set Result = New Class1
InjectProperties Result, SomeValue, SomeDependency
Set Create = Result
End Function
Private Sub InjectProperties(ByVal Instance As Class1, ByVal SomeValue As String, ByVal SomeDependency As Object)
Instance.SomeValue = SomeValue
Set Instance.SomeDependency = SomeDependency
End Sub
Public Property Get SomeValue() As String
SomeValue = This.SomeValue
End Property
Public Property Let SomeValue(ByVal RHS As String)
This.SomeValue = RHS
End Property
Public Property Get SomeDependency() As Object
SomeDependency = This.SomeDependency
End Property
Public Property Set SomeDependency(ByVal RHS As Object)
Set This.SomeDependency = RHS
End Property
Private Property Get IClass1_SomeValue() As String
IClass1_SomeValue = This.SomeValue
End Property
Private Property Get IClass1_SomeDependency() As Object
IClass1_SomeDependency = This.SomeDependency
End Property
Note: the property injection mechanism doesn’t need a Property Get member on the Class1 interface, however not exposing a Property Get member for a property that has a Property Let (and/or Property Set) procedure, would leave the property as write-only on the Class1 interface. Write-only properties would be flagged as a design smell, so there’s a conundrum here: either we expose a Property Get that nothing is calling (except unit tests, perhaps), or we expose a write-only property (with a comment that explains its property injection purpose). There is no right or wrong, only a consistent design matters.
If we were to write unit tests for this class, we would need at least:
One test that invokes Class1.Create with an "" empty string for the first argument and fails if error 5 isn’t raised by the procedure call.
One test that invokes Class1.Create with Nothing for the second argument and fails if error 5 isn’t raised by the procedure call.
One test that invokes Class1.Create with valid arguments and fails if the returned object is Nothing.
One test that invokes Class1.Create with valid arguments and fails if the Class1.SomeValue property doesn’t return the value of the first argument.
One test that invokes Class1.Create with valid arguments and fails if the Class1.SomeDependency property doesn’t return the very same object reference as was passed for the second argument.
One test that invokes Class1.Create with valid arguments and fails if the IClass1.SomeValue property doesn’t return the same value as Class1.SomeValue does.
One test that invokes Class1.Create with valid arguments and fails if the IClass1.SomeDependency property doesn’t return the same object reference as Class1.SomeDependency does.
Obviously that’s just a simplified example, but it does perfectly illustrate the notion that the answer to “what to test?” is simply “every single execution path”… of every public member (private members are implementation details that are invoked from the public members; if they specifically need tests, then they deserve to be their own concern-addressing class module).
What is testable?
Without the Property Get members of Class1 and/or IClass1, we wouldn’t be able to test that the Create method is property-injecting SomeValue and SomeDependency, because the object’s internal state is encapsulated (as it should be). Therefore, there’s an implicit assumption that a Property Get member for a property-injected dependency is returning the encapsulated value or reference, and nothing more: by writing tests that rely on that assumption, we are documenting it.
Now SomeDependency might be an instance of another class, and that class might have its own encapsulated state, dependencies, and testable logic. A more meaty Class1 module might have a method that invokes SomeDependency.DoSomething, and the tests for that method would have to be able to assert that SomeDependency.DoSomething has been invoked once.
If Class1 wasn’t property-injecting SomeDependency (for example if SomeDependency was being New‘d it up instead), we would not be able to write such a test, because the outcome of the test might be dependent on a method being called against that dependency.
A simple example would be Class1 newing up a FileSystemObject to iterate the files of a given folder. In such a case, FileSystemObject is a dependency, and if Class1.DoSomething is newing it up directly then every time Class1.DoSomething is called, it’s going to try and iterate the files of a given folder, because that’s what a FileSystemObject does, it hits the file system. And that’s slow. I/O (file, network, …and user) is dependent on so many things that can go wrong for so many reasons, having it interfere with tests is something you want to avoid.
The way to avoid having user, network, and file inputs and outputs interfere with the tests of any method, is to completely let go of the “need” for a method to control any of its dependencies. The method doesn’t need to create a new instance of a FileSystemObject; what it really needs is actually a much simpler any object that’s capable of returning a list of files or file names in a given folder.
So instead of this:
Public Sub DoSomething(ByVal Path As String)
With CreateObjet("Scripting.FileSystemObject")
' gets the Path folder...
' iterates all files...
' ...
End With
End Sub
We would do this:
Public Sub DoSomething(ByVal Path As String, ByVal FileProvider As IFileProvider)
Dim Files As Variant
Files = FileProvider.GetFiles(Path)
' iterates all files...
' ...
End Sub
Where IFileProvider would be an interface/class module that might look like this:
Option Explicit
'@Interface
'@Description "Returns an array containing the file names under the specified folder."
Public Function GetFiles(ByVal Path As String) As Variant
End Function
That interface might very well be implemented in a class module named FileProvider that uses a FileSystemObject to return the promised array.
It could also be implemented in another class module, named TestFileProvider, that uses a ParamArray parameter so that unit tests can take control of the IFileProvider dependency and inject (here by method injection) a TestFileProvider instance. The DoSomething method doesn’t need to know where the file names came from, only that it can expect an array of existing, valid file names from IFileProvider.GetFiles(String). If the DoSomething method indeed doesn’t care where the files came from, then it’s adhering to pretty much all OOP design principles, and now a test can be written that fails if DoSomething is doing something wrong – as opposed to a test that might fail if some network drive happens to be dismounted, or works locally when working from home but only with a VPN.
The hard part is obviously identifying the dependencies in the first place. If you’re refactoring a procedural VBA macro, you must determine what the inputs and outputs are, what objects hold the state that’s being altered, and devise a way to abstract them away and inject these dependencies from the calling code – whether that caller is the original entry point macro procedure, or a new unit test.
Mocking
In the above example, the TestFileProvider implementation of the IFileProvider dependency is essentially a test stub: you actually write a separate implementation for the sole purpose of being able to run the code with fake dependencies that don’t incur any file, network, or user I/O. Reusing these stubs in “test” macros that wire up the UI by injecting the test stubs instead of the actual implementations, should result in the application running normally… without hitting any file system or network.
With mocks, you don’t need to write a “test” implementation. Instead, you configure an object provided by a mocking framework to behave as the method/test needs, and the framework implements the mocked interface with an object that can be injected, that verifiably behaves as configured.
Sounds like magic? A lot of it actually is, from a VBA/VB6 standpoint. Many tests in Rubberduck leverage a very popular mocking framework called Moq. What we’re going to be releasing as an experimental feature is not only a COM-visible wrapper around Moq. The fun part is that the Moq methods we need to use are generic methods that take lambda expressions as parameters, so our wrapper needs to expose an API VBA code can use, and then “translate” it into member calls into the Moq API, but because they’re generic methods and the mocked interface is a COM object, we essentially build a .NET type on the fly to match the mocked VBA/COM interface, so that’s what Moq actually mocks: a .NET interface type Rubberduck makes up at run-time from any COM object. Moq uses Castle Windsor under the hood to spawn instances of proxy types – made-up actual objects that actually implement one or more interfaces. Castle Windsor is excellent at what it does; we use CW to automate dependency injection in Rubberduck (a technique dubbed Inversion of Control, where a single container object is responsible for creating all instances of all objects in the application in the composition root; that’s what’s going on while Rubberduck’s splash screen is being displayed).
There is a problem though: CW seems to be caching types with the reasonable but still implicit assumption that the type isn’t going to change at run-time. In our case however, this means mocking a VBA interface once and then modifying that interface (e.g. adding, removing, or reordering members, or changing a member signature in any way) and re-running the test would still be mocking the old interface, as long as the host process lives. This isn’t a problem for mocking a Range or a Worksheet dependency, but VBA user code is being punished here.
Verifiable Invocations
Going back to the IFileProvider example, the GetFiles method could be configured to return a hard-coded array of bogus test strings, and a test could be made to turn green when IFileProvider.GetFiles is invoked with the same specific Path parameter value that was given to Class1.DoSomething. If you were stubbing IFileProvider, you would perhaps increment a counter every time IFileProvider_GetFiles is invoked, and expose that counter with a property that the test could Assert is equal to an expected value. With Moq, you can make a test fail by invoking a Verify method on the mock itself, that verifies whether the specified method was invoked as configured.
A best practice with mocking would be to only setup the minimal amount of members to make the test work, because of the performance overhead: if a mocked interface has 5 methods and 3 properties but the method under test only needs 2 of these methods and 1 of these properties, then it should only setup these. Verification makes mocking a very valuable tool to test behavior that relies on side-effects and state changes.
The best part is that because VBA is COM, then everything is an interface, so if you don’t have an IFileProvider interface but you’re still passing a FileProvider object as a dependency, then you can mock the FileProvider directly and don’t need to introduce any extra “just-for-testing” IFileProvider interface if you don’t already have one.
I’m going to stop here and just publish, otherwise I’ll be editing this post forever. So much is missing…
If you haven’t tried it already, download VSCode and get the twinBASIC extension, and be part of the next stage of the Visual Basic revolution. When it goes live (it’s still in preview, and vigorously maintained), twinBASIC will compile 100% VB6/VBA compatible code and completely redefine how VB6 and VBA solutions will be maintained and extended in the foreseeable future.
Among the many mind-blowing language-level enhancements twinBASIC brings to the table, are actual constructors – something Visual Basic developers that haven’t made the leap to VB.NET have only been able to simulate with factory methods.
Object Construction As We Know It
When we create a new instance of a class in VBA like this:
Dim thing As Something
Set thing = New Something
Several things appear to happen all at once, but in reality there’s a very specific sequence of events that unfolds when this Set assignment instruction is evaluated:
The right-hand side of the assignment is evaluated first; it’s a <new-expression>, so we’re spawning a New instance of the Something class.
As the object gets created and before the New operation returns to the caller, the Class_Initialize handler inside the Something class is invoked.
When the Class_Initialize handler returns, the New operation is completed and yields an object reference pointing to the new object.
The object reference gets copied to the thing variable, and member calls are now legal against it.
Classes in VBA/VB6 don’t really have a constructor – there’s this Class_Initialize handler where it’s appropriate to initialize private instance state, but it’s essentially a callback invoked from the actual “base class” constructor which is for a COM object and thus, without any parameters.
Default Instances & Factory Methods
Classes in VBA/VB6 have a hidden VB_PredeclaredId attribute that is False by default, but that can be set to True (either manually, or using Rubberduck’s @PredeclaredId annotation). Document modules like ThisWorkbook and Sheet1, but also UserForm modules, have that hidden attribute set to True.
Given a VB_PredeclaredId = True attribute, the runtime automatically creates an instance of the class that is named after the class itself, so the global UserForm1 identifier refers to the default instance of the UserForm1 class when it’s used as an object, and refers to the UserForm1class type when it’s used as a type.
If you handle Class_Initialize in a class that has VB_PredeclaredId set to True, you’ll notice the handler is invoked the first time the class name is used as an object in code, i.e. just before the first reference to it. And if you handle Class_Terminate too, you’ll find the default instance gets destroyed as soon as it’s no longer needed (i.e. when nothing in-scope references it anymore).
We could treat default instances like global objects – that’s what they are. But globals and OOP don’t quite go hand-in-hand, for many reasons; there’s something icky about having magical implicit global objects spawned from the language runtime itself. However, if we treat this default instance as we would a type, then we can consider the members of a class’ default interface as members that belong to the type, and then we can define an explicit, separate interface that the class can implement to expose its actual intended instance functionality.
In many languages, members that belong to a type (rather than an instance of that type) are called “static”. In C# the static keyword is used for this, but in VBA/VB6 the Static keyword has a different meaning and there isn’t really anything “static” in Visual Basic. In .NET type-level members are identified with the Shared keyword, which was reserved in VB6 but never implemented. twinBASIC might end up changing that.
So by treating the default instance of a VBA/VB6 class as we would a static class (i.e. keeping the default instance stateless, that is, we don’t allow it to hold any state/variables), we can still adhere to OOP principles while leveraging language features that let us simulatestatic behavior, chiefly so by exposing factory methods that effectively simulate parameterized constructors – here for our Something example class module, with an added SomeProperty value being property-injected:
'@PredeclaredId
Option Explicit
Implements ISomething
Private mValue As Long
Public Function Create(ByVal Value As Long) As ISomething
Dim Result As Something
Set Result = New Something
Result.SomeProperty = Value
Set Create = Result
End Function
Public Property Let SomeProperty(ByVal RHS As Long)
mValue = RHS
End Property
Private Property Get ISomething_SomeProperty() As Long
ISomething_SomeProperty = mValue
End Property
The ISomething interface is only exposing SomeProperty with a Property Get accessor, which makes it read-only. That’s great when the code is written against ISomething, but then several things feel wrong:
We must expose Property Let (or Property Set) mutators on the class’ default interface to support the property-injection that happens in the factory method.
Rubberduck will (appropriately) flag the write-only properties and suggest to also expose a Property Get accessor, because it makes no sense to be able to write to a property when we can’t read back the value we just wrote.
Properties visible on the default interface look like mutable state that is accessible from the default instance. If nothing is done to actively prevent it, the default instance can easily become stateful… and then we’re looking at dreadful global state living in some class.
In order to have a clean interface without the Create member (and without the Property Let mutator), we must implement an explicit, non-default interface to expose the members we intend the calling code to work with.
Actual Constructors
With twinBASIC we get actual constructors, that can be parameterized (for classes we’re not making visible to COM clients, like VBA or VB6). A constructor is a special procedure named New (like the operator) whose sole purpose is to initialize the state of an object, so that the client code creating the object receives a fully-initialized object: the very same purpose as a default instance factory method.
We don’t need default instance factory methods in twinBASIC because we get to define actual constructors. This has several interesting and snowballing implications we’ll go over in a moment, but first we need to establish certain things about what constructors should and generally shouldn’t do.
DO take a constructor parameter for instance state that should be initialized by the caller.
DO initialize private instance fields from constructor parameters.
DO invoke any private initialization procedures that must be invoked for the object instance to be valid when the constructor returns.
DO validate all parameters and raise a run-time error given any invalid parameter value.
AVOID doing any kind of non-initialization work in a constructor.
AVOID invoking any procedure that performs non-initialization work from a constructor.
AVOID raising run-time errors in a constructor (other than from guard clauses validating parameter values).
For example, a DbConnection class might take a ConnectionString constructor parameter; the constructor stores the ConnectionString as instance-level state into a private field, then returns. Another method invoked by the consumer of the object invokes an Open method that reads the ConnectionString and proceeds to open the database connection. The DbConnection constructor could open the connection itself and that would probably be convenient for a lot of use cases… but it also couples constructing a DbConnection object with the action of connecting to a database. Problem is, when most people read this instruction:
Dim db As DbConnection = New DbConnection(connString)
…they expect to have simply instantiated a new DbConnection object – nothing less, nothing more. If merely creating an instance of an object can raise a run-time error because some network cable is unplugged, we’re looking at the consequences of having a badly side-effecting constructor.
Inline initialization notice the initial assignment is on the same line as the declaration? This syntax is legal in VB.NET, and twinBASIC adopted it as well. In VBA/VB6, we must separate the declaration (Dim) from the instruction performing the instantiation and assignment.
When we create a New object, we expect a new object to get created, and we expect that to be a very boring thing: it wouldn’t even occur to us that there’s the slightest chance anything could possibly go wrong with just spawning a new instance of a class.
That is why constructors should adhere as much as possible to the KISS principle: Keep It Stupid Simple. If something more complicated than creating objects and setting their properties happens in a constructor, consider refactoring it so that the actual work is triggered after the object is constructed.
Implications
The constructor is operating on the instance that’s in the process of being created. This makes them much simpler to reason about and to implement than, say, a Create factory method on the default interface of the class, because now we have access to the internal state of the object we’re constructing.
The implication of this, is that we no longer need to expose any Property Let mutators to property-inject the parameter values; instead we can now do constructor injection and directly assign the private fields, without needing to pollute the class’ default interface with members we don’t need.
Since we’re no longer polluting the class’ default interface with members we don’t need, we don’t have to extract an explicit interface to hide them anymore. And since constructors are invoked using the New operator, there’s no need to have a predeclared default instance of the class for a Create method to be accessible to the calling code.
Let’s see how tremendously twinBASIC constructors change everything, by contrasting a simple scenario in Classic VB with the same identical scenario in twinBASIC.
Simulating Constructors in Classic VB (VBA/VB6)
Here’s an example of how I’d write a class named Example, simulating a parameterized constructor:
'@PredeclaredId
Option Explicit
Implements IExample
Private Type TState
Value1 As Long
Value2 As String
End Type
Private This As TState
Public Function Create(ByVal Value1 As Long, ByVal Value2 As String) As IExample
Dim Result As Example
Set Result = New Example
Result.Value1 = Value1
Result.Value2 = Value2
Set Create = Result
End Function
Public Property Get Value1() As Long
Value1 = This.Value1
End Property
Public Property Let Value1(ByVal RHS As Long)
This.Value1 = RHS
End Property
Public Property Get Value2() As String
This.Value2 = RHS
End Property
Public Property Let Value2(ByVal RHS As String)
This.Value2 = RHS
End Property
Private Property Get IExample_Value1() As Long
IExample_Value1 = This.Value1
End Property
Private Property Get IExample_Value2() As String
IExample_Value2 = This.Value2
End Property
Where IExample is another class module that only exposes Public Property Get Value1() As Long and Public Property Get Value2() As String. The calling code might look like this:
Dim x As IExample
Set x = Example.Create(42, "Test")
Debug.Print x.Value1, x.Value2
The x variable could legally be cast to an Example, and then x.Value = 10 would be legal too. But we code against abstract interfaces so we get IExample.Value1 and IExample.Value2 as get-only properties, and that’s the standard pattern I’ve now been using for several years in classic VB, to perform dependency injection and initialize objects with properties before they’re returned to the code that consumes them.
It works pretty nicely, with relatively few caveats (like casting to concrete /default interface being allowed, or Example.Value1 = 42 making the default instance stateful unless we actively guard against it) but it’s robust enough and makes a rather clean API that’s very suitable for OOP and testable code.
Are we in the default instance? Using the Is operator together with Me, we can test whether Me Is Example and determine whether we’re currently in the default instance of the Example class. So adding If Me Is Example Then Err.Raise 5 could raise a run-time error as a guard clause in the Property Let members, effectively protecting against misuse of the class/design.
Rubberduck has tooling that makes writing most of this code pretty much entirely automatic, but at the end of the day it’s still quite a lot of code – and the only reason we need it is because we can’t parameterize an actual constructor.
What if we could though?
Constructors in twinBASIC
The legacy-VB example above should compile just fine and work identically in twinBASIC, but the language offers new opportunities and it would be silly to ignore them. Now a twinBASIC executable doesn’t necessarily have the same concerns as a twinBASIC ActiveX DLL; in a standalone .exe project we can do anything we want, but if we’re making a library that’s intended to be used by legacy VB code we have to keep our intended COM-based client in mind.
COM clients (like VBA) don’t support parameterized constructors, so public/exposed classes (with VB_Exposed attribute set to True) should define a parameterless constructor. Either the legacy way, with a Class_Initialize handler:
Private Sub Class_Initialize()
End Sub
Or the twinBASIC way with an explicit, parameterless constructor:
Public Sub New()
End Sub
Similar to VB.NET, a constructor in twinBASIC is a Sub procedure named New in a class module. Ideally you want your constructor near the top of the module, as the first member of the class. Not for any technical reason really, but instinctively that’s where you expect a constructor to be.
A class’ parameterless constructor is dubbed a default constructor, because if no constructor is specified for a class, then an implicit one necessarily exists. If a class defines a parameterized constructor, it is understood as a class that requires the constructor arguments, and there is no implicit default/parameterless constructor then: a COM client could not create a new instance of such a class.
In twinBASIC, I’d write the above Example clas like this – note the absence of an IExample interface:
Class Example
Private Type TState
Value1 As Long
Value2 As String
End Type
Private This As TState
Public Sub New(ByVal Value1 As Long, ByVal Value2 As String)
This.Value1 = Value1
This.Value2 = Value2
End Sub
Public Property Get Value1() As Long
Return This.Value1
End Property
Public Property Get Value2() As String
Return This.Value2
End Property
End Class
The calling code would now look like this:
Dim x As Example = New Example(42, "Test")
Debug.Print x.Value1, x.Value2
And it would have the exact same compile-time restrictions as the code written against the read-only IExample VBA/VB6 interface, only now thanks to parameterized construction we get to constructor-inject values and make the default interface of the Example class read-only, as we intended all along.
With twinBASIC we can still implement interfaces, but here an IExample get-only interface would be redundant. In a sense that brings most useful interfaces in twinBASIC closer to “pure” abstract interfaces, the kind that gets implemented by multiple classes: it would be suspicious to see a Thing class implement an IThing interface, for example, whereas in VBA/VB6 IThing would be an interface to work with a Thing instance when Thing is only used as a type as in myThing = Thing.Create(42).
Constructor Injection
In VBA/VB6 with factory methods we can achieve property injection – that is, using properties to “inject” dependencies into a class instance: the factory method invokes Property Let/Set procedures to do this. An example of property injection is how we set an ADODB.Connection‘s ConnectionStringafter instantiating the Connection object.
Dim Conn As Connection
Set Conn = New Connection
Conn.ConnectionString = "..."
Conn.Open
That works, but then it’s not ideal because it induces temporal coupling in the client code: the caller must remember to set the ConnectionString property before they invoke the Open method.
In VBA/VB6 we can also do method injection by taking dependencies in as Sub or Function parameters. To stick with the ConnectionString example, method injection would be the Open method taking the connection string as a parameter:
Dim Conn As Connection
Set Conn = New Connection
Conn.Open "..."
That’s much better: it’s now impossible for the calling code to “forget” to supply a connection string. The Property Let ConnectionString member becomes somewhat of a wart, and should be removed.
Now method injection is great for something like a connection string and nothing needs it other than an Open method. If many members of a class seem to need the same parameters, there’s a good chance we can remove that parameter from all these members by promoting the dependency to instance level. In VBA/VB6 that would have to be through property injection. Say you have a class and many of its members require a Connection parameter: ask yourself whether it would make sense for that Connection to be a dependency of the class rather than a dependency of each one of its methods.
With twinBASIC we can now do constructor injection, and create objects that are valid as soon as they come into existence:
Dim Conn As Connection = New Connection("...")
Conn.Open
If a Connection class takes a ByVal ConnectionString As String constructor argument, then the constructor can store that string in Private instance state, and we only need to expose a ConnectionString property (which would be get-only) if we have a reason to do so. The object is immediately usable, and there’s no temporal coupling anymore.
Eventually, twinBASIC could support ReadOnly modifiers for instance fields, that could enforce and guarantee immutability: the role of a constructor then boils down to assigning all the ReadOnly private instance fields.
By writing classes that take their instance-level dependencies as constructor arguments, we throw consumers of these classes into a pit of success where doing things wrong is much harder than doing them correctly – and that is the single best reason to leverage constructors when we can.
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).
'@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 targetCommandButton 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.
Bindings are what makes Model-View-ViewModel feel a little bit like magic. Consider this example code, which creates the dynamic controls for an example UI and showcases how much of a game-changer having a MVVM framework could be for VBA code:
Private Sub InitializeView()
Dim Layout As IContainerLayout
Set Layout = ContainerLayout.Create(Me.Controls, TopToBottom)
With DynamicControls.Create(This.Context, Layout)
With .LabelFor("All controls on this form are created at run-time.")
.Font.Bold = True
End With
.TextBoxFor BindingPath.Create(This.ViewModel, "StringProperty"), _
Validator:=New RequiredStringValidator, _
TitleSource:="Some String:" '<~ also accepts an IBindingPath
.TextBoxFor BindingPath.Create(This.ViewModel, "CurrencyProperty"), _
FormatString:="{0:C2}", _
TitleSource:="Some Amount:" '<~ also accepts an IBindingPath
.CommandButtonFor CancelCommand.Create(Me), This.ViewModel, "Close"
End With
End Sub
This VBA code generates a dynamic UI layout with controls automatically bound to ViewModel properties, and buttons automatically bound to commands. In a project that leverages the MVVM infrastructure code, that’s the only code needed to produce this:
The RequiredStringValidator makes it impossible to leave the ‘StringProperty’ TextBox empty; valid values are automatically applied to the corresponding ViewModel property.
There’s a lot to be written about this DynamicControls API, but that part isn’t very polished yet, and the secret sauce is that it builds (mostly anyway) on top of Property Bindings: they are what makes this sorcery possible… even with a classic drag-and-drop designer UI.
I just couldn’t resist having [at least basic, bare-bones but still extensible] support for a working .LabelFor / .TextBoxFor syntax in VBA code, for the MSForms UI library! I’ll save that for another day though, the layout stuff isn’t where it needs to be yet.
I’m missing about a million unit tests so there’s a good chance something somewhere isn’t working the way it should, but what’s there should be close enough to be worth talking about, and what matters the most is that the code is testable.
Let’s dissect how property bindings work. This time I did not push code to the examples repository, because this is an actual project in its own right, with its own need for examples. I have uploaded everything to https://github.com/rubberduck-vba/MVVM.
Property Bindings
In the context of this MVVM infrastructure code, a property binding is an object responsible for binding a source property path to a target property path; the source points to a ViewModel property, and the target to a property of a UI element (control).
ViewModel?
A ViewModel can be any object that implements the INotifyPropertyChanged interface, as long as that class has:
Public properties for everything the View needs to bind to.
Property Let procedures should invoke OnPropertyChanged, but only when the property value actually changed: avoid signaling a changed property when its current value was merely overwritten with the same.
Property Get procedures are required for all binding modes; Property Let procedures are only needed for TwoWay and OneWayToSource bindings.
ICommand public properties can be exposed to avoid coupling the view with any particular specific command (other than AcceptCommand and/or CancelCommand).
Note that a View could use multiple ViewModels as needed; ViewModel classes should never know anything about any View.
INotifyPropertyChanged
This interface is central in the event propagation mechanics: in design patterns terms, a class that implement it is the subject in an Observer Pattern where the registered handlers are the observers. The reason a ViewModel needs to implement this interface, is because creating a property binding registers the binding as an observer – and it handles the ViewModel telling it about a property change by applying the binding(s) for that property.
The ExampleViewModel class illustrates how to properly implement this interface:
Public Property Get SomeProperty() As String
SomeProperty = This.SomeProperty
End Property
Public Property Let SomeProperty(ByVal RHS As String)
If This.SomeProperty <> RHS Then
This.SomeProperty = RHS
OnPropertyChanged "SomeProperty"
End If
End Property
Private Sub OnPropertyChanged(ByVal PropertyName As String)
This.Notifier.OnPropertyChanged Me, PropertyName
End Sub
Private Sub Class_Initialize()
Set This.Notifier = New PropertyChangeNotifierBase
'...
End Sub
The OnPropertyChanged method is only invoked when the RHS assigned value is different than the current value, and we don’t need to worry about tracking/adding observers or invoking them, because everything we need is already encapsulated in the PropertyChangeNotifierBase class, so we implement the interface by simply passing the parameters over to this “notifier” instance:
Private Sub INotifyPropertyChanged_OnPropertyChanged(ByVal Source As Object, ByVal PropertyName As String)
This.Notifier.OnPropertyChanged Source, PropertyName
End Sub
Private Sub INotifyPropertyChanged_RegisterHandler(ByVal Handler As IHandlePropertyChanged)
This.Notifier.RegisterHandler Handler
End Sub
Now we know the interfaces involved in keeping source and target in sync, let’s look at everything else – starting with the binding paths.
IBindingPath
The documentation calls it “An object that can resolve a string property path to a value”, and that’s really all it does. The properties may need some explaining though:
Context refers to the base object for resolving the path, i.e. your ViewModel (for the source), or a UI control (for the target).
Path refers to the property path string; usually just a ViewModel or control property name, but this string is resolved recursively so you could bind to “ViewModel.SomeObject.SomeProperty” if you needed to.
Object is the object that owns the property we’re referring to. If the path is just a property name, then this is the same reference as the Context.
PropertyName is the resolved property name. In the example path above, that would be “SomeProperty”.
The interface also exposes Resolve, as well as TryReadPropertyValue, TryWritePropertyValue, and ToString methods; these members are invoked by the MVVM infrastructure internals.
IBindingPath is implemented by the BindingPath class, which exposes a Createfactory method that property-injects the Context and Path values and invokes the Resolve method before returning the created object, so creating a binding path really just looks like this:
Dim Path As IBindingPath
Set Path = BindingPath.Create(ViewModel, "PropertyName")
And with that we’re ready to create an IPropertyBinding.
PropertyBindingBase
The IPropertyBinding interface is mostly only useful internally. There’s little of interest here that isn’t more appropriately covered by looking at the factory method for the PropertyBindingBase class. You ready? It’s quite a mouthful…
Public Function Create(ByVal BindingManager As IBindingManager, ByVal CommandManager As ICommandManager, ByVal TargetContext As Object, ByVal SourceContext As Object, ByVal SourcePropertyPath As String, _
Optional ByVal TargetPropertyPath As String = DefaultTargetControlProperty, _
Optional ByVal Mode As BindingMode = BindingMode.TwoWayBinding, _
Optional ByVal UpdateSource As BindingUpdateSourceTrigger = BindingUpdateSourceTrigger.OnExit, _
Optional ByVal Converter As IValueConverter, _
Optional ByVal StringFormat As IStringFormatter, _
Optional ByVal Validator As IValueValidator, _
Optional ByVal ValidationAdorner As IDynamicAdorner, _
Optional ByVal CancelExitOnValidationError As Boolean = True) As IPropertyBinding
The factory method creates the IBindingPath objects from the given context and property path strings, which makes it simpler for the calling code. Note that the target property path is Optional, how is that possible?
If you’ve spent a bit of time with the MVVM prototype code, you probably noticed the PropertyBinding class was pretty much out of control, and extending it to support more target types would only make it worse. So what I did is, I pulled the common code into a new PropertyBindingBase class, then moved the control-specific code into its own specialized control-specific property binding implementation, and now there’s a strategy pattern that’s responsible for fetching the correct implementation – so that’s how binding a TextBox target creates a TextBoxPropertyBinding, and how binding a Label target creates a OneWayPropertyBinding. Each control-specific binding class can handle that control’s events and implement control-specific behavior accordingly.
IBindingManager
The binding manager is the object that knows about all the property bindings; each property binding needs a reference to the binding manager that owns it, in order to invoke data validation and trigger command bindings to evaluate whether commands can be executed. This object is automatically created when you create an AppContext instance, but the AppContext can be injected with any IBindingManager implementation as needed.
ICommandManager
This “manager” guy knows about all the command bindings, which isn’t something I’ve talked about much yet. Next article about the Command Pattern will dive into more details; this object is automatically created when you create an AppContext instance, but the AppContext can be inject with any ICommandManager implementation as needed.
TargetContext
Typically, that’s just a reference to the target MSForms control. Technically, it could really be any object that has any number of public properties.
SourceContext
Typically, that’s just a reference to the source ViewModel object. Technically, it could really be any object that has any number of public properties [and ideally, that implements INotifyPropertyChanged to support 2-way bindings].
SourcePropertyPath
The last required parameter is a string representing a path (relative to the SourceContext) to the property that holds the value we want the binding target to use; see IBindingPath.
TargetPropertyPath
Each binding type has a default target property that is automatically inferred from the type of target (and in some cases, from the data type of the source property). For example, binding to a TextBox control automatically wires up the control’s Text property, such that providing a TargetPropertyPath is only needed when binding to another target property.
Mode
This enum value determines whether the binding synchronizes the target, the source, or both. Note that binding modes OneWayBinding and TwoWayBinding both require the source object to implement INotifyPropertyChanged.
UpdateSource
This enum value determines when the binding gets to update its source. When the Mode is OneWayBinding or OneTimeBinding, value UpdateSourceTrigger.Never is used automatically.
OnKeyPress gets to validate each individual keypress. Useful for TextBox bindings that need a key validator.
OnExit and OnPropertyChanged are still somewhat ambiguously defined, but OnExit gets to keep the target control focused if there’s a validation error, and OnPropertyChanged is [currently] firing for every keypress in a TextBox, after the control’s Text property udpates. Disambiguating the terms would mean breaking with MSForms’ meaning of “updated”… which may actually be a good thing: OnPropertyChanged would basically fire on exit but without a possible navigation-blocking validation error, and then OnKeyPress mode would still need to behave like OnPropertyChanged as far as updating the source goes.
Converter
Each property binding can use an IValueConverter to “convert” a value midway between the source and the target (or between the target and the source). For example we can bind a CheckBox control to a Boolean property, but if we need the checkbox checked when the property value is False, we can use an InverseBooleanConverter to convert True to False as the binding is applied.
StringFormat
The IAppContext.StringFormatterFactory property (can be property-injected from AppContext.Create) determines which formatter class type is going to be injected here. Supply a VBStringFormatterFactory to use VB6/VBA Strings.Format format string syntax, or supply a StringFormatterFactory (or just leave the defaults alone) to use the much more flexible .NET String.Format syntax.
Validator
When a binding is given an IValueValidator, it gets to start issuing validation errors, which propagate to the ViewModel and can be used to pop a warning banner with the validation error message. Note: the AcceptCommand class’ implementation of ICommand.CanExecute makes it return False when the ViewModel has validation errors.
ValidationAdorner
Having validation errors is good, letting the user know about them is even better. Supply an IDynamicAdorner implementation by invoking ValidationErrorAdorner.Create and use the default error formatters or supply custom ones.
Order of Operations
Several objects get involved whenever something happens in a UI control. Let’s look at what happens when we type something in a TextBox with a standard two-way property binding to some ViewModel property.
Control Events
If the TextBox control has a Change event handler in the form’s code-behind (it really shouldn’t though, if we actually follow MVVM), that code probably gets to run first. The IPropertyBinding implementation would be a TextBoxPropertyBinding object, which handles MouseUp and KeyPress, but these handlers don’t trigger anything. What actually triggers the propagation of the new TextBox.Text value to the ViewModel property, is the BeforeUpdate and Exit events, both of which are initially handled in the ControlEventsPunk class, an object that leverages the Win32 IConnectionPoint API to hook up event handlers for the MSForms.Controls interface of our TextBox control (we covered that in the previous post).
So the first thing to run is the ControlEventsPunk.OnBeforeUpdate callback, which promptly iterates all registered handlers (“observers”) and invokes their HandleBeforeUpdate method.
So the ControlEventsPunk.OnBeforeUpdate callback propagates the event to the TextBoxPropertyBinding, and the IHandleControlEvents_HandleBeforeUpdate procedure runs as a result… which proceeds to forward it to the PropertyBindingBase class with a call to OnBindingUpdateSourceOpportunity, a method with the responsibility of conditionally invoking the ApplyToSource method.
ApplyToSource
The method’s job is to read the value from the binding target, and then write that value to the binding source. If the binding’s Mode is OneTimeBinding or OneWayBinding, we can already bail out because these modes only ever write to the binding target.
The first thing that needs to happen is a call to Resolve against the target (an IBindingPath). Typically the Target path would resolve IBindingPath.Object to a MSForms UI control, and IBindingPath.PropertyName accordingly resolves to Text for a TextBoxPropertyBinding, or Value for a CheckBoxPropertyBinding, or Caption for a CaptionPropertyBinding given a Frame or Label target – unless a TargetPropertyPath string was provided, in which case all bets are off and we might be binding the ForeColor or Font.Bold properties of a Label-like control, or what’s stopping us from binding its Width property (time to revisit that progress indicator, I guess).
And that’s just the tip of the iceberg, because the binding can use an IValueConverter implementation, such that you could conceivably implement, say, a converter that takes some Enum constant and maps each value to some Picture icon, and then use that converter in the binding of a ViewModel property of that Enum type to some MSForms.Image control’s Picture property… but I digress. Converters can also do boring things, like making sure the input value 2 becomes 0.02 before it gets written to that PercentValue ViewModel property, and then string formats can make sure that looks like 2.0% before it gets written to TextBox.Text, but we’ll get to that.
So the next thing that needs to happen is a call to IBindingPath.TryReadPropertyValue, and if we can’t do that we need to bail out, but this time ApplyResult.BindingSkipped won’t be good enough, so we explicitly return a BindingFailed result.
Once we know what value is currently in TextBox.Text (when the source update trigger is OnKeyPress, we have the KeyAscii value for it), we need to invoke IValueConverter.ConvertBack if a converter was specified for the binding; if the conversion fails, we return ApplyResult.BindingConversionError.
If conversion succeeded, we’re ready to validate the converted value (or the original one, if there is no converter). If the update trigger is OnKeyPress, then the validator operates on the single keypress – otherwise we validate the entire value. Things get interesting when there’s a validation error now: we’re returning ApplyResult.BindingValidationError, but then if there’s a ValidationAdorner, its Show method gets invoked and the validation error for that property is propagated to an IValidationManager object.
If validation passes, we try to read the source property value. If we can’t read it, we bail with a BindingFailed result. Otherwise we compare the source value with the target value, and if they are different then we proceed to clear any validation errors for that property, and then we finally write the new value to the source property; if that final step succeeds, we return ApplyResult.BindingSuccess result.
ApplyToTarget
When it’s a ViewModel property that changes, a similar sequence of events unfolds: the Property Let procedure invokes INotifyPropertyChanged.NotifyPropertyChanged, the property binding was registered as an observer, so IHandlePropertyChanged_HandlePropertyChanged is invoked; whenever the provided Source is the binding source and the provided PropertyName is the source property name, ApplyToTarget gets invoked.
When the binding mode is OneWayToSource or OneTimeBinding, we know we can immediately bail out, because these states don’t write to the binding target. Now, it’s entirely possible that we still need to supply a TextBox with a Text value even if we can’t yet resolve the binding Source (e.g. IBindingPath.Object resolves to Nothing). In such cases, we attempt to get a sensible default target value depending on the name of the target property:
“Text” and “Caption” target properties default to vbNullString;
“Enabled” and “Visible” target properties default to False;
“Value” property defaults to False when the target is a CheckBox or OptionButton.
If the source object is Nothing and we don’t have a fallback default, we bail out. Otherwise we try to read the source (ViewModel) value, then we validate it, then we convert it, then we read the target property value, compare with the source, and overwrite it if they’re different… but just before doing that, we run it through an IStringFormatter if we have one.
StringFormat
An MVVM application might need to use, say, a Date value somewhere. The application needs the data type to be Date, such that the program doesn’t need to worry about a malformed or ambiguous date string and works with the actual underlying Date value. Such an application would define a ViewModel class with a Date property (say, OrderDate), and then there can be a TextBox control showing that Date value to the user.
If we don’t do anything, the content of that TextBox control would be whatever VBA decides a Date value needs to look like when represented as a String, and that would be the (sorry, American readers) utterly nonsensical en-US format (mm-dd-yyyy). If your application’s users are happy with such a format, more power to them – but I like my date strings unambiguous and boringly standard, so I’d want the TextBox content to say “yyyy-mm-dd” instead. By providing a FormatString argument to the property binding, we can make it do exactly that. Or we can just as easily make it say “Thursday, October 22 2020” if we wanted to, and with a StringToDateConverter we could round-trip that value back to a proper Date.
Or maybe our ViewModel has a Currency property because our application needs to get some dollar amount, and having that displayed in a TextBox control as 1234567.89 is bare-bones good enough, but we could provide a FormatString argument to the property binding and have our ViewModel’s Currency property hold the numeric value 1234567.89 all while the bound TextBox control says $1,234,567.89.
Without MVVM property bindings doing this for us, implementing this kind of functionality is such a pain in the neck that it’s hardly ever done at all! Nobody wants to deal with parsing dates and formatted currency amounts off a TextBox control, and for a reason: when TextBox.Textis the value you’re working with, you are working with a String and you do need to parse its content.
With MVVM, we’re completely elsewhere: the TextBox.Text is just a receptacle for displaying whatever the real underlying value is (i.e. the ViewModel property), and is completely separated from it – and this tremendously simplifies everything.
The MVVM infrastructure code comes with two implementations for the IStringFormatter interface:
So in order to make a Date ViewModel property look like YYYY-MM-DD we could:
Use a VBStringFormatter with a “YYYY-MM-DD” format string (case insensitive)
Use a StringFormatter with a “yyyy-MM-dd” format string (note: lowercase-“m” refers to the “minute” part of the datetime here – use uppercase-“M” for the month!)
And in order to make a Currency ViewModel property look like money we could:
Use a VBStringFormatter with a “Currency” (or a culture-invariant “$#,##0.00”) format string
Use a StringFormatter with a “{0:C2}” format string (if we want 2 decimals)
Creating an IStringFormatter every time we want to use one would be annoying, so the binding manager uses the abstract factory from the IAppContext to spawn it automatically. A nice side-effect of this, is that the string formatters for the bindings of a given context are guaranteed to all use the same syntax. So if we wanted to use VB format strings, we would create the app context like this:
Dim Context As IAppContext
Set Context = AppContext.Create(FormatterFactory:=New VBStringFormatterFactory)
Note that if you use a format string that results in a TextBox.Text value that can’t be automatically (and correctly) parsed back into the data type of the bound ViewModel property (if that isn’t a String), updating the binding source will likely fail with a conversion error: you will need to implement an IValueConverter and inject it into the binding along with the format string in order to correctly convert the formatted string back to a value that is usable by the binding; a StringToDateConverter class exists in the MVVM infrastructure code to work with Date properties and standard format strings, but the implementation may need adjustments to handle formats that don’t automatically round-trip back to a Date value.
Validation
Another key aspect of property bindings, is that they simplify validating user inputs. If a program needs to work with some numeric value provided by the user and the user interface doesn’t validate its inputs, there’s a type mismatch error written in the sky there, or worse. As a general rule of thumb, it’s a good idea for code to assume that a value coming from the user is anything but what the program needs to work as expected.
The best way to handle an error is always to avoid throwing that error in the first place, and validating user inputs contributes to exactly this.
If you need the digits of a phone number and present the user with a control that only accepts a certain number of digit characters and then uses a format string to prettify the value on exit, you ensure that your PhoneNumber string value only ever contains the meaningful characters, leaving the “what a phone number looks like” concern separate from the “phone number” data itself, which means every phone number in your list/table ultimately gets to look exactly the same, as opposed to the free-form nightmares I presume we’re all well too familiar with.
The MVVM infrastructure addresses validation concerns through the IValidationManager interface. The object that implements this interface is responsible for managing validation errors across binding sources (ViewModels) in the context of an IApplicationContext.
ValidationManager
The role of the validation manager is to encapsulate the validation state and expose methods to add and clear validation errors; the IsValidindexed property returns a Boolean given a context (the ViewModel) and an optional property name: in order to know whether the entire context is valid, omit the PropertyName argument.
OnValidationError and ClearValidationError respectively add and remove a validation error for a particular source property, and the validation manager keeps validation errors in a dictionary keyed with the ViewModel object (a string representation of its object pointer), such that each ViewModel can be deemed “valid” or “invalid” individually/separately.
The “manager” class isn’t responsible for doing anything with a validation error: it just holds the state, so that other components can query it and retrieve the IValidationError for SomeViewModel.SomeProperty.
An IValidationError is a simple object that gives us a Message (from the IValueValidator that caused the binding to fail validation) and the IPropertyBinding that couldn’t be applied.
So, that dynamic UI stuff?
It works good-enough to make a good-enough screenshot, but the IContainerLayout stuff needs more thinking-through and more fiddling to get everything just right. See, as of this writing the layout API stacks controls horizontally or vertically, and well, that’s about it.
I want a docking panel, a layout container that can resize its children as needed and that’s a truly fascinating topic… For now there’s an IDynamicControlBuilder interface that looks like this:
'@Folder MVVM.Infrastructure.Abstract
'@ModuleDescription "Builds dynamic MSForms UI components from a binding source."
'@Interface
'@Exposed
Option Explicit
'@Description "Creates a multiline MSForms.TextBox control for the spercified String property binding path."
Public Function TextAreaFor(ByVal SourceValue As IBindingPath, Optional ByVal Converter As IValueConverter, Optional ByVal Validator As IValueValidator, Optional ByVal ErrorAdorner As IDynamicAdorner, Optional ByVal TitleSource As Variant) As MSForms.TextBox
End Function
'@Description "Creates a MSForms.TextBox control for the specified String property binding path."
Public Function TextBoxFor(ByVal SourceValue As IBindingPath, Optional ByVal FormatString As String, Optional ByVal Converter As IValueConverter, Optional ByVal Validator As IValueValidator, Optional ByVal ErrorAdorner As IDynamicAdorner, Optional ByVal TitleSource As Variant) As MSForms.TextBox
End Function
'@Description "Creates a MSForms.Label control for the specified Caption string or String property binding path."
Public Function LabelFor(ByVal SourceCaption As Variant, Optional ByVal FormatString As String, Optional ByVal Converter As IValueConverter) As MSForms.Label
End Function
'@Description "Creates a MSForms.ComboBox control for the specified Value property binding path; SourceItems should be an array property."
Public Function ComboBoxFor(ByVal SourceValue As IBindingPath, ByVal SourceItems As IBindingPath, Optional ByVal FormatString As String, Optional ByVal Converter As IValueConverter, Optional ByVal Validator As IValueValidator, Optional ByVal ErrorAdorner As IDynamicAdorner, Optional ByVal TitleSource As Variant) As MSForms.ComboBox
End Function
'@Description "Creates a MSForms.ListBox control for the specified Value property binding path; SourceItems should be an array property."
Public Function ListBoxFor(ByVal SourceValue As IBindingPath, ByVal SourceItems As IBindingPath, Optional ByVal TitleSource As Variant) As MSForms.ListBox
End Function
'@Description "Creates a MSForms.OptionButton control for the specified Value (Boolean) property binding path; uses the specified Caption string or String property binding path for the control's Caption."
Public Function OptionButtonFor(ByVal SourceValue As IBindingPath, ByVal SourceCaption As Variant) As MSForms.OptionButton
End Function
'@Description "Creates a MSForms.CheckBoxButton control for the specified Value (Boolean) property binding path; uses the specified Caption string or String property binding path for the control's Caption."
Public Function CheckBoxFor(ByVal SourceValue As IBindingPath, ByVal SourceCaption As Variant) As MSForms.CheckBox
End Function
'@Description "Creates a MSForms.CommandButton control for the specified ICommand, using the specified ViewModel context and Caption string or String property binding path."
Public Function CommandButtonFor(ByVal Command As ICommand, ByVal BindingContext As Object, ByVal SourceCaption As Variant) As MSForms.CommandButton
End Function
…and I haven’t even tested all of it yet, and small little things that actually matter, like OptionButton groups, aren’t being considered. I still need to think of how this API can get where it wants to be, but I really like where it’s going.
Thoughts?
To be honest, I’m having a blast with this, and writing actual working MVVM code in VBA is completely surreal, in a very awesome way.
I think it’s in itself a nice deep-dive into OOP+VBA – whether the MVVM architecture it enables ends up being the backbone of any production app or not.
I have recently written (100% VBA) a proof-of-concept for a Model-View-ViewModel (MVVM) framework, and since the prototype works exactly as needed (with some rough edges of course)… I’ve decided to explore what Rubberduck can do to make MVVM fully supported, but going down that path poses a serious problem that needs a very good and well thought-out solution.
A Vision of a Framework
When you start a new project in Visual Studio (including 6.0 /VB6), the IDE prompts for a project type, essentially asking “what are we building today?“
In VBA the assumption is that you just want to write a bit of script to automate some document manipulation. And then the framework so to speak, is the VBA Standard Library: functions, methods, constants, and actual objects too; all globally-scoped for convenience and quick-and-easy access: a fully spelled-out VBA.Interaction.MsgBox function call is a rare sight! Combined with the nonexistence of namespaces, the flip side is that the global scope is easily polluted, and name collisions are inevitable since anything exposed by any library becomes globally accessible. This makes fully-qualified global function calls appear sporadically sprinkled in the code, which can be confusing. I digress, but what I mean to get at is that this is part of what made Microsoft make the shift to the .NET platform in the early 2000’s, and eventually abandon the Visual Basic Editor to its fate. The COM platform and Win32 API was the framework, and Win32 programming languages built on top of that.
This leaves two approaches for a vision of a “framework” for VBA:
Package a type library and ship it.
Pros: any COM-visible library will work, can be written in .NET
Cons: projects now have a hard dependency on a specific type library; updating is a mess, etc.
Embed the framework into VBA projects, pretty much like JavaScript does.
Pros: devs are in charge of everything, framework is 100% VBA and inherently open-source, updating is essentially seamless for any non-breaking change, no early-bound dependencies, graceful late-bound degradation, etc.
Cons: VBA devs and maintainers that aren’t using Rubberduck will be massively lost in the source code (framework would cleanly leverage @Folder annotations), but then when the host application allows it this could be mitigated by embedding the code into its own separate VBA project and reference it from other projects (e.g. ship an Excel add-in with the framework code your VBA project depends on).
I think I’m slightly biased here, but I think this rules out the type library approach regardless. So we need a way to make this work in VBA, with VBA source code that lives in a GitHub repository with vetted, trusted content.
Where Rubberduck fits in
Like Visual Studio, Rubberduck could prompt VBA devs with “what are we building today?” and offer to pull various “bundles” of modules from this GitHub repository into the active project. Rubberduck would request the available “bundles” from api.rubberduckvba.com, which would return with “bundle metadata” describing each “package” (is “nugget” forbidden to use as a name for these / play on “nuget” (the package manager for .NET)?), and then list them in a nice little dialog.
The “nugget” metadata would include a name, a description, and the path to each file to download for it. Every package would be the same “version”, but the tool could easily request any particular “tag” or “release” version, and/or pull from “main” or from “next” branches, and the source code / framework itself could then easily be a collaborative effort, with its own features and projects and milestones and collaborators, completely separate from the C# Rubberduck code base.
This complete decoupling from Rubberduck means you don’t need to use Rubberduck to leverage this VBA code in your VBA projects, and new tags / “releases” would be entirely independent of Rubberduck’s own release cycles. That means you’re using, say, future-Rubberduck 2.7.4 and the “nuggets” feature offers “v1.0 [main]” and “v1.1 [next]”; one day you’re still using Rubberduck 2.7.4 but now you get “v1.1 [main]”, “v1.0”, and “v1.2 [next]” to chose from, and if you updated the “nuggets” in your project from v1.0 to v1.1 then Rubberduck inspections would flag uses of any obsolete members that would now be decorated with @Obsolete annotations… it’s almost like this annotation was presciently made for this.
But before we can even think of implementing something like this and make MVVM infrastructure the very first “nugget”, we need a rock-solid framework in the first place.
Unit Tests
I had already written the prototype in a highly decoupled manner, mindful of dependencies and how things could later be tested from the outside. I’m very much not-a-zealot when it comes to things like Test-Driven Development (TDD), but I do firmly believe unit tests provide a solid safety net and documentation for everything that matters – especially if the project is to make any kind of framework, where things need to provably work.
And then it makes a wonderful opportunity to blog about writing unit tests with Rubberduck, something I really haven’t written nearly enough about.
Tests? Why?!
Just by writing these tests, I’ve found and fixed edge-case bugs and improved decoupling and cohesion by extracting (and naming!) smaller chunks of functionality into their own separate class module. The result is quite objectively better, simpler code.
Last but not least, writing testable code (let alone the tests!) in VBA makes a great way to learn these more advanced notions and concepts in a language you’re already familiar with.
If you’re new to VBA and programming in general, or if you’re not a programmer and you’re only interested in making macros, then reading any further may make your head spin a bit (if that’s already under way… I’m sorry!), so don’t hesitate to ask here or on the examples repository on GitHub if you have any questions! This article is covering a rather advanced topic, beyond classes and interfaces, but keep in mind that unit testing does not requireOOP! It just so happens that object-oriented code adhering to SOLID principles tends to be easily testable.
This is an ongoing project and I’m still working on the test suite and refactoring things; I wouldn’t want to upload the code to GitHub in its current shape, so I’ll come back here with a link once I have something that’s relatively complete.
Where to Start?
There’s a relatively small but very critical piece of functionality that makes a good place to begin in the MVVM infrastructure code (see previous article): the BindingPath class, which I’ve pulled out of PropertyBinding this week. The (still too large for its own good) PropertyBinding class is no longer concerned with the intricacies of resolving property names and values: both this.Source and this.Target are declared As IBindingPath in a PropertyBinding now, which feels exactly right.
The purpose of a BindingPath is to take a “binding context” object and a “binding path” string (the binding path is always relative to the binding context), and to resolve the member call represented there. For example, this would be a valid use of the class:
Dim Path As IBindingPath
Set Path = BindingPath.Create(Sheet1.Shapes("Shape1").TextFrame.Characters, "Text")
This Path object implements TryReadPropertyValue and TryWritePropertyValue methods that the BindingManager can invoke as needed.
'@Folder MVVM.Infrastructure.Bindings
'@ModuleDescription "An object that can resolve a string property path to a value."
'@PredeclaredId
Option Explicit
Implements IBindingPath
Private Type TState
Context As Object
Path As String
Object As Object
PropertyName As String
End Type
Private This As TState
'@Description "Creates a new binding path from the specified property path string and binding context."
Public Function Create(ByVal Context As Object, ByVal Path As String) As IBindingPath
GuardClauses.GuardNonDefaultInstance Me, BindingPath, TypeName(Me)
GuardClauses.GuardNullReference Context, TypeName(Me)
GuardClauses.GuardEmptyString Path, TypeName(Me)
Dim Result As BindingPath
Set Result = New BindingPath
Set Result.Context = Context
Result.Path = Path
Result.Resolve
Set Create = Result
End Function
'@Description "Gets/Sets the binding context."
Public Property Get Context() As Object
Set Context = This.Context
End Property
Public Property Set Context(ByVal RHS As Object)
GuardClauses.GuardDefaultInstance Me, BindingPath, TypeName(Me)
GuardClauses.GuardNullReference RHS, TypeName(Me)
GuardClauses.GuardDoubleInitialization This.Context, TypeName(Me)
Set This.Context = RHS
End Property
'@Description "Gets/Sets a string representing a property path against the binding context."
Public Property Get Path() As String
Path = This.Path
End Property
Public Property Let Path(ByVal RHS As String)
GuardClauses.GuardDefaultInstance Me, BindingPath, TypeName(Me)
GuardClauses.GuardEmptyString RHS, TypeName(Me)
GuardClauses.GuardDoubleInitialization This.Path, TypeName(Me)
This.Path = RHS
End Property
'@Description "Gets the bound object reference."
Public Property Get Object() As Object
Set Object = This.Object
End Property
'@Description "Gets the name of the bound property."
Public Property Get PropertyName() As String
PropertyName = This.PropertyName
End Property
'@Description "Resolves the Path to a bound object and property."
Public Sub Resolve()
This.PropertyName = ResolvePropertyName(This.Path)
Set This.Object = ResolvePropertyPath(This.Context, This.Path)
End Sub
Private Function ResolvePropertyName(ByVal PropertyPath As String) As String
Dim Parts As Variant
Parts = Strings.Split(PropertyPath, ".")
ResolvePropertyName = Parts(UBound(Parts))
End Function
Private Function ResolvePropertyPath(ByVal Context As Object, ByVal PropertyPath As String) As Object
Dim Parts As Variant
Parts = Strings.Split(PropertyPath, ".")
If UBound(Parts) = LBound(Parts) Then
Set ResolvePropertyPath = Context
Else
Dim RecursiveProperty As Object
Set RecursiveProperty = CallByName(Context, Parts(0), VbGet)
If RecursiveProperty Is Nothing Then Exit Function
Set ResolvePropertyPath = ResolvePropertyPath(RecursiveProperty, Right$(PropertyPath, Len(PropertyPath) - Len(Parts(0)) - 1))
End If
End Function
Private Property Get IBindingPath_Context() As Object
Set IBindingPath_Context = This.Context
End Property
Private Property Get IBindingPath_Path() As String
IBindingPath_Path = This.Path
End Property
Private Property Get IBindingPath_Object() As Object
Set IBindingPath_Object = This.Object
End Property
Private Property Get IBindingPath_PropertyName() As String
IBindingPath_PropertyName = This.PropertyName
End Property
Private Sub IBindingPath_Resolve()
Resolve
End Sub
Private Function IBindingPath_ToString() As String
IBindingPath_ToString = StringBuilder _
.AppendFormat("Context: {0}; Path: {1}", TypeName(This.Context), This.Path) _
.ToString
End Function
Private Function IBindingPath_TryReadPropertyValue(ByRef outValue As Variant) As Boolean
If This.Object Is Nothing Then Resolve
On Error Resume Next
outValue = VBA.Interaction.CallByName(This.Object, This.PropertyName, VbGet)
IBindingPath_TryReadPropertyValue = (Err.Number = 0)
On Error GoTo 0
End Function
Private Function IBindingPath_TryWritePropertyValue(ByVal Value As Variant) As Boolean
If This.Object Is Nothing Then Resolve
On Error Resume Next
VBA.Interaction.CallByName This.Object, This.PropertyName, VbLet, Value
IBindingPath_TryWritePropertyValue = (Err.Number = 0)
On Error GoTo 0
End Function
Here’s our complete “system under test” (SUT) as far as the BindingPathTests module goes. We have a Create factory method, Context and Path properties, just like the class we’re testing.
The path object is itself read-only once initialized, but the binding source may resolve to Nothing or to a different object reference over the course of the object’s lifetime: say we want a binding path to SomeViewModel.SomeObjectProperty; when we first create the binding, SomeObjectProperty might very well be Nothing, and then it’s later Set-assigned to a valid object reference. This is why the IBindingPath interface needs to expose a Resolve method, so that IPropertyBinding can invoke it as needed, as the binding is being applied.
We’ll want a test for every guard clause, and each method needs at least one test as well.
So, I’m going to add a new test module and call it BindingPathTests. Rubberduck’s templates are good-enough to depict the mechanics and how things work at a high level, but if you stick to the templates you’ll quickly find your unit tests rather boring, wordy, and repetitive: we must break out of the mold, there isn’t one true way to do this!
Rubberduck discovers unit tests in standard modules annotated with @TestModule. Test methods are any [parameterless, for now] method annotated with a @TestMethod annotation that can have a category string – the Test Explorer can group your tests using these categories. The declarations section of a test module must include a declaration (early or late bound) for an Rubberduck.AssertClass or Rubberduck.PermissiveAssertClass (both implement the same internal interface; the “permissive” one has VBA-like equality semantics, and the default one has stricter type equality requirements (a Long can’t be equal to a Double, for example). The default test template also defines a FakesProvider object, but we’re not going to need it now (if we needed to test logic that involved e.g. branching on the result of a MsgBox function call, we could hook into the MsgBox function and configure it to return what the test needs it to return, which is honestly wicked awesome). So our test module might look something like this at first:
'@Folder Tests.Bindings
'@TestModule
Option Explicit
Option Private Module
#Const LateBind = LateBindTests
#If LateBind Then
Private Assert As Object
#Else
Private Assert As Rubberduck.AssertClass
#End If
With this conditionally-compiled setup, all we need to toggle between late and early binding is to define a project-scoped conditional compilation argument: bring up the project properties and type LateBindTests=0 or LateBindTests=1 in that box, and just like that you can control conditional compilation project-wide without modifying a single module.
The first thing to do is to get the test state defined, and implement TestInitialize and TestCleanup methods that configure this state – in the case of BindingManagerTests, I’m going to add a private type and a private field to define and hold the current test state:
Private Type TState
ExpectedErrNumber As Long
ExpectedErrSource As String
ExpectedErrorCaught As Boolean
ConcreteSUT As BindingManager
AbstractSUT As IBindingManager
HandlePropertyChangedSUT As IHandlePropertyChanged
BindingSource As TestBindingObject
BindingTarget As TestBindingObject
SourcePropertyPath As String
TargetPropertyPath As String
Command As TestCommand
End Type
Private Test As TState
Unit Testing Paradigm
Test modules are special, in the sense that they aren’t (absolutely shouldn’t be anyway) accessible from any code path in the project. Rubberduck invokes them one by one when you run a command like “run all tests” or “repeat last run”. But there’s a little more to it than that, worthy of mention.
VBA being single-threaded, tests are invoked by Rubberduck on the UI/main thread, and uses a bit of trickery to keep its own UI somewhat responsive. Each module runs sequentially, and each test inside each module runs sequentially as well – but the test execution order still shouldn’t be considered deterministic, and each test should be completely independent of every other test, such that executing all tests in any given order always produces the same outcomes.
A test that makes no assertions will be green/successful. When writing unit tests, the first thing you want to see is a test that’s failing (you can’t trust a test you have never seen fail!), and with Rubberduck in order to give a test a reason to fail, you use Assert methods (wiki).
When Rubberduck begins processing a test module, it invokes the methods (again, sequentially but not in an order that should matter) marked @ModuleInitialize in the module – ideally that would be only one method.
This is where the Assert object should be assigned (the default test templates do this):
'@ModuleInitialize
Private Sub ModuleInitialize()
#If LateBind Then
'requires HKCU registration of the Rubberduck COM library.
Set Assert = CreateObject("Rubberduck.PermissiveAssertClass")
#Else
'requires project reference to the Rubberduck COM library.
Set Assert = New Rubberduck.PermissiveAssertClass
#End If
End Sub
Rubberduck’s test engine will then execute all methods (usually cleaner with only one though) annotated with @TestInitializebefore executing each test in the module; that is the best place to put the wordy setup code that would otherwise need to be in pretty much every single test of the module:
'@TestInitialize
Private Sub TestInitialize()
Dim Context As TestBindingObject
Set Context = New TestBindingObject
Set Context.TestBindingObjectProperty = New TestBindingObject
Test.Path = "TestBindingObjectProperty.TestStringProperty"
Test.PropertyName = "TestStringProperty"
Set Test.BindingSource = Context.TestBindingObjectProperty
Set Test.BindingContext = Context
Set Test.ConcreteSUT = BindingPath.Create(Test.BindingContext, Test.Path)
Set Test.AbstractSUT = Test.ConcreteSUT
End Sub
By moving the test state to module level rather than having it local to each test, we already eliminate a lot of code duplication, and the Test module variable makes a rather nifty way to access the current test state, too!
Methods annotated with @TestCleanup are automatically invoked after each test in the module; in order to avoid accidentally sharing state between tests, every object reference should be explicitly set to Nothing, and values of intrinsic data types should be explicitly reset to their respective default value:
'@TestCleanup
Private Sub TestCleanup()
Set Test.ConcreteSUT = Nothing
Set Test.AbstractSUT = Nothing
Set Test.BindingSource = Nothing
Set Test.BindingContext = Nothing
Test.Path = vbNullString
Test.PropertyName = vbNullString
Test.ExpectedErrNumber = 0
Test.ExpectedErrSource = vbNullString
Test.ExpectedErrorCaught = False
End Sub
What Goes Into the Test State?
A number of members should always be in the Test state structure:
ConcreteSUT (or just SUT) and AbstractSUT both point to the same object, through the default interface (BindingPath) and the explicit one (IBindingPath), respectively.
If the system under test class implements additional interfaces, having a pointer to the SUT object with these interfaces is also useful. For example the TState type for the BindingManager class has a HandlePropertyChangedSUT As IHandlePropertyChanged member, because the class implements this interface.
Default property values and dependency setup: we want a basic default SUT configured and ready to be tested (or fine-tuned and then tested).
ExpectedErrNumber, ExpectedErrSource, and ExpectedErrorCaught are useful when a test is expecting a given input to produce a particular specific error.
Expecting Errors
The “expected error” test method template works for its purpose, but having this on-error-assert logic duplicated everywhere is rather ugly. Consider pulling that logic into a private method instead (I’m considering adding this into Rubberduck’s test module templates):
Private Sub ExpectError()
Dim Message As String
If Err.Number = Test.ExpectedErrNumber Then
If (Test.ExpectedErrSource = vbNullString) Or (Err.Source = Test.ExpectedErrSource) Then
Test.ExpectedErrorCaught = True
Else
Message = "An error was raised, but not from the expected source. " & _
"Expected: '" & TypeName(Test.ConcreteSUT) & "'; Actual: '" & Err.Source & "'."
End If
ElseIf Err.Number <> 0 Then
Message = "An error was raised, but not with the expected number. Expected: '" & Test.ExpectedErrNumber & "'; Actual: '" & Err.Number & "'."
Else
Message = "No error was raised."
End If
If Not Test.ExpectedErrorCaught Then Assert.Fail Message
End Sub
With this infrastructure in place, the unit tests for all guard clauses in the module can look like this – it’s still effectively doing Arrange-Act-Assert like the test method templates strongly suggest, only implicitly so (each “A” is essentially its own statement, see comments in the tests below):
'@TestMethod("GuardClauses")
Private Sub Create_GuardsNullBindingContext()
Test.ExpectedErrNumber = GuardClauseErrors.ObjectCannotBeNothing '<~ Arrange
On Error Resume Next
BindingPath.Create Nothing, Test.Path '<~ Act
ExpectError '<~ Assert
On Error GoTo 0
End Sub
'@TestMethod("GuardClauses")
Private Sub Create_GuardsEmptyPath()
Test.ExpectedErrNumber = GuardClauseErrors.StringCannotBeEmpty '<~ Arrange
On Error Resume Next
BindingPath.Create Test.BindingContext, vbNullString '<~ Act
ExpectError '<~ Assert
On Error GoTo 0
End Sub
'@TestMethod("GuardClauses")
Private Sub Create_GuardsNonDefaultInstance()
Test.ExpectedErrNumber = GuardClauseErrors.InvalidFromNonDefaultInstance '<~ Arrange
On Error Resume Next
With New BindingPath
.Create Test.BindingContext, Test.Path '<~ Act
ExpectError '<~ Assert
End With
On Error GoTo 0
End Sub
And then similar tests exist for the respective guard clauses of Context and Path members. Having tests that validate that guard clauses are doing their job is great: it tells us exactly how not to use the class… and that doesn’t tell us much about what a BindingPath object actually does.
Testing the Actual Functionality
The methods we’re testing need to be written in a way that makes it possible for a test to determine whether it’s doing its job correctly or not. For functions and properties, the return value is the perfect thing to Assert on. For Sub procedures, you have to Assert on the side-effects, and have verifiable and useful, reliable ways to verify them.
These two tests validate that the BindingPath returned by the Create factory method has resolved the PropertyName and Object properties, respectively.
'@TestMethod("Bindings")
Private Sub Create_ResolvesPropertyName()
Dim SUT As BindingPath
Set SUT = BindingPath.Create(Test.BindingContext, Test.Path)
Assert.IsFalse SUT.PropertyName = vbNullString
End Sub
'@TestMethod("Bindings")
Private Sub Create_ResolvesBindingSource()
Dim SUT As BindingPath
Set SUT = BindingPath.Create(Test.BindingContext, Test.Path)
Assert.IsNotNothing SUT.Object
End Sub
I could have made multiple assertions in a test, like this…
'@TestMethod("Bindings")
Private Sub Create_ResolvesBindingSource()
Dim SUT As BindingPath
Set SUT = BindingPath.Create(Test.BindingContext, Test.Path)
Assert.IsFalse SUT.PropertyName = vbNullString
Assert.IsNotNothing SUT.Object
End Sub
The Test Explorer would say “IsFalse assert failed” or “IsNotNothing assert failed”, so it’s arguably (perhaps pragmatically so) still useful and clear enough why that test would fail (and if you had multiple Assert.IsFalse calls in a test you could provide a different message for each)… but really as a rule of thumb, tests want to have one reason to fail. If the conditions to meaningfully pass or fail a test aren’t present, use Assert.Inconclusive to report the test as such:
'@TestMethod("Bindings")
Private Sub Resolve_SetsBindingSource()
With New BindingPath
.Path = Test.Path
Set .Context = Test.BindingContext
If Not .Object Is Nothing Then Assert.Inconclusive "Object reference is unexpectedly set."
.Resolve
Assert.AreSame Test.BindingSource, .Object
End With
End Sub
'@TestMethod("Bindings")
Private Sub Resolve_SetsBindingPropertyName()
With New BindingPath
.Path = Test.Path
Set .Context = Test.BindingContext
If .PropertyName <> vbNullString Then Assert.Inconclusive "PropertyName is unexpectedly non-empty."
.Resolve
Assert.AreEqual Test.PropertyName, .PropertyName
End With
End Sub
This mechanism is especially useful when the test state isn’t in local scope and there’s a real possibility that the TestInitialize method is eventually modified and inadvertently breaks a test. Such conditional Assert.Inconclusive calls are definitely a form of defensive programming, just like having guard clauses throwing custom meaningful errors.
Note that while we know that the BindingPath.Create function invokes the Resolve method, the tests for Resolve don’t involve Create: the Path and Context are being explicitly spelled out, and the .Resolve method is invoked from a New instance.
And that’s pretty much everything there is to test in the BindingPath class.
There’s one thing I haven’t mentioned yet, that you might have caught in the TState type:
BindingSource As TestBindingObject
BindingTarget As TestBindingObject
This TestBindingObject is a test stub: it’s a dependency of the class (it’s the “binding context” of the test path) and it’s a real object, but it is implemented in a bit of a special way that the BindingPath tests don’t do justice to.
Test Stubs
Eventually Rubberduck’s unit testing framework will feature a COM-visible wrapper around Moq, a popular mocking framework for .NET that Rubberduck already uses for its own unit test requirements. When this happens Rubberduck unit tests will no longer need such “test stubs”. Instead, the framework will generate them at run-time and make them work exactly as specified/configured by a unit test, and “just like that” VBA/VB6 suddenly becomes surprisingly close to being pretty much on par with professional, current-day IDE tooling.
The ITestStub interface simply formalizes the concept:
'@Exposed
'@Folder Tests.Stubs
'@ModuleDescription "An object that stubs an interface for testing purposes."
'@Interface
Option Explicit
'@Description "Gets the number of times the specified member was invoked in the lifetime of the object."
Public Property Get MemberInvokes(ByVal MemberName As String) As Long
End Property
'@Description "Gets a string representation of the object's internal state, for debugging purposes (not intended for asserts!)."
Public Function ToString() As String
End Function
A TestStubBase “base class” provides the common implementation mechanics that every class implementing ITestStub will want to use – the idea is to use a keyed data structure to track the number of times each member is invoked during the lifetime of the object:
'@Folder Tests.Stubs
Option Explicit
Private Type TState
MemberInvokes As Dictionary
End Type
Private This As TState
'@Description "Tracks a new invoke of the specified member."
Public Sub OnInvoke(ByVal MemberName As String)
Dim newValue As Long
If This.MemberInvokes.Exists(MemberName) Then
newValue = This.MemberInvokes.Item(MemberName) + 1
This.MemberInvokes.Remove MemberName
Else
newValue = 1
End If
This.MemberInvokes.Add MemberName, newValue
End Sub
'@Description "Gets the number of invokes made against the specified member in the lifetime of this object."
Public Property Get MemberInvokes(ByVal MemberName As String) As Long
If This.MemberInvokes.Exists(MemberName) Then
MemberInvokes = This.MemberInvokes.Item(MemberName)
Else
MemberInvokes = 0
End If
End Property
'@Description "Gets a string listing the MemberInvokes cache content."
Public Function ToString() As String
Dim MemberNames As Variant
MemberNames = This.MemberInvokes.Keys
With New StringBuilder
Dim i As Long
For i = LBound(MemberNames) To UBound(MemberNames)
Dim Name As String
Name = MemberNames(i)
.AppendFormat "{0} was invoked {1} time(s)", Name, This.MemberInvokes.Item(Name)
Next
ToString = .ToString
End With
End Function
Private Sub Class_Initialize()
Set This.MemberInvokes = New Dictionary
End Sub
With this small bit of infrastructure, the TestBindingObject class is a full-fledged mock object that can increment a counter whenever a member is invoked, and that can be injected as a dependency for anything that needs an IViewModel:
'@Folder Tests.Stubs
'@ModuleDescription "An object that can stub a binding source or target for unit tests."
Option Explicit
Implements ITestStub
Implements IViewModel
Implements INotifyPropertyChanged
Private Type TState
Stub As TestStubBase
Handlers As Collection
TestStringProperty As String
TestNumericProperty As Long
TestBindingObjectProperty As TestBindingObject
Validation As IHandleValidationError
End Type
Private This As TState
Public Property Get TestStringProperty() As String
This.Stub.OnInvoke "TestStringProperty.Get"
TestStringProperty = This.TestStringProperty
End Property
Public Property Let TestStringProperty(ByVal RHS As String)
This.Stub.OnInvoke "TestStringProperty.Let"
If This.TestStringProperty <> RHS Then
This.TestStringProperty = RHS
OnPropertyChanged Me, "TestStringProperty"
End If
End Property
Public Property Get TestNumericProperty() As Long
This.Stub.OnInvoke "TestNumericProperty.Get"
TestNumericProperty = This.TestNumericProperty
End Property
Public Property Let TestNumericProperty(ByVal RHS As Long)
This.Stub.OnInvoke "TestNumericProperty.Let"
If This.TestNumericProperty <> RHS Then
This.TestNumericProperty = RHS
OnPropertyChanged Me, "TestNumericProperty"
End If
End Property
Public Property Get TestBindingObjectProperty() As TestBindingObject
This.Stub.OnInvoke "TestBindingObjectProperty.Get"
Set TestBindingObjectProperty = This.TestBindingObjectProperty
End Property
Public Property Set TestBindingObjectProperty(ByVal RHS As TestBindingObject)
This.Stub.OnInvoke "TestBindingObjectProperty.Set"
If Not This.TestBindingObjectProperty Is RHS Then
Set This.TestBindingObjectProperty = RHS
OnPropertyChanged Me, "TestBindingObjectProperty"
End If
End Property
Private Sub OnPropertyChanged(ByVal Source As Object, ByVal PropertyName As String)
Dim Handler As IHandlePropertyChanged
For Each Handler In This.Handlers
Handler.OnPropertyChanged Source, PropertyName
Next
End Sub
Private Sub Class_Initialize()
Set This.Stub = New TestStubBase
Set This.Handlers = New Collection
Set This.Validation = ValidationManager.Create
End Sub
Private Sub INotifyPropertyChanged_OnPropertyChanged(ByVal Source As Object, ByVal PropertyName As String)
OnPropertyChanged Source, PropertyName
End Sub
Private Sub INotifyPropertyChanged_RegisterHandler(ByVal Handler As IHandlePropertyChanged)
This.Handlers.Add Handler
End Sub
Private Property Get ITestStub_MemberInvokes(ByVal MemberName As String) As Long
ITestStub_MemberInvokes = This.Stub.MemberInvokes(MemberName)
End Property
Private Function ITestStub_ToString() As String
ITestStub_ToString = This.Stub.ToString
End Function
Private Property Get IViewModel_Validation() As IHandleValidationError
Set IViewModel_Validation = This.Validation
End Property
This functionality will be extremely useful when testing the actual property bindings: for example we can assert that a method was invoked exactly once, and fail a test if the method was invoked twice (and/or if it never was).
There’s a lot more to discuss about unit testing in VBA with Rubberduck! I hope this article gives a good idea of how to get the best out of Rubberduck’s unit testing feature.
We’ve seen in UserForm1.Show what makes a Smart UI solution brittle, and how to separate the UI concerns from rest of the logic with the Model-View-Presenter (MVP) UI pattern. MVP works nicely with the MSForms library (UserForms in VBA), just like it does with its .NET Windows Forms successor. While the pattern does a good job of enhancing the testability of application logic, it also comes with its drawbacks: the View’s code-behind (that is, the code module “behind” the form designer) is still littered with noisy event handlers and boilerplate code, and the back-and-forth communication between the View and the Presenter feels somewhat clunky with events and event handlers.
Rubberduck’s UI elements are made with the Windows Presentation Foundation (WPF) UI framework, which completely redefines how everything about UI programming works, starting with the XML/markup-based (XAML) design, but the single most compelling element is just how awesome its data binding capabilities are.
We can leverage in VBA what makes Model-View-ViewModel (MVVM) awesome in C# without going nuts and writing a whole UI framework from scratch, but we’re still going to need a bit of an abstract infrastructure to work with. It took the will to do it and only costed a hair or two, but as far as I can tell this works perfectly fine, at least at the proof-of-concept stage.
This article is the first in a series that revolves around MVVM in VBA as I work (very much part-time) on the rubberduckdb content admin tool. There’s quite a bit of code to make this magic happen, so let’s kick this off with what it does and how to use it – subsequent articles will dive into how the MVVM infrastructure internals work. As usual the accompanying code can be found in theexamples repository on GitHub (give it a star, and fork it, then make pull requests with your contributions during Hacktoberfest next month and you can get a t-shirt, stickers, and other free stuff, courtesy of Digital Ocean!).
Overview
The code in the examples repository isn’t the reason I wrote this: I mentioned in the previous post that I was working on an application to maintain the website content, and decided to explore the Model-View-ViewModel pattern for that one. Truth be told, MVVM is hands-down my favorite UI pattern, by far. This is simply the cleanest UI code I’ve ever written in VBA, and I love it!
The app is work in progress, but the property and command bindings work!
The result is an extremely decoupled, very extensible, completely testable architecture where every user action (“command”) is formally defined, can be programmatically simulated/tested with real, stubbed, or faked dependencies, and can be bound to multiple UI elements and programmatically executed as needed.
MVVM Quick Checklist
These would be the rules to follow as far a relationships go between the components of the MVVM pattern:
View (i.e. the UserForm) knows about the ViewModel, but not the Model;
ViewModel knows about commands, but nothing about a View;
Exactly what the Model actually is/isn’t/should/shouldn’t be, is honestly not a debate I’m interested in – I’ll just call whatever set of classes is responsible for hydrating my ViewModel with data my “model” and sleep at night. What matters is that whatever you call the Model knows nothing of a View or ViewModel, it exists on its own.
Before we dive into bindings and the infrastructure code, we need to talk about the command pattern.
Commands
A command is an object that implements an ICommand interface that might look like this:
'@Folder MVVM.Infrastructure
'@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
In the case of a CommandBinding the Context parameter is always the DataContext / ViewModel (for now anyway), but manual invokes could supply other kinds of parameters. Not all implementations need to account for the ViewModel, a CanExecute function that simply returns True is often perfectly fine. The Description is used to set a tooltip on the target UI element of the command binding.
The implementation of a command can be very simple or very complex, depending on the needs. A command might have one or more dependencies, for example a ReloadCommand might want to be injected with some IDbContext object that exposes a SelectAllTheThings function and the implementation might pull them from a database, or make them up from hard-coded strings: the command has no business knowing where the data comes from and how it’s acquired.
Each command is its own class, and encapsulates the logic for enabling/disabling its associated control and executing the command. This leaves the UserForm module completely devoid of any logic that isn’t purely a presentation concern – although a lot can be achieved solely with property bindings and validation error formatters.
The infrastructure code comes with AcceptCommand and CancelCommand implementations, both useful to wire up [Ok], [Cancel], or [Close] dialog buttons.
AcceptCommand
The AcceptCommand can be used as-is for any View that can be closed with a command involving similar semantics. It is implemented as follows:
'@Exposed
'@Folder MVVM.Infrastructure.Commands
'@ModuleDescription "A command that closes (hides) a View."
'@PredeclaredId
Option Explicit
Implements ICommand
Private Type TState
View As IView
End Type
Private this As TState
'@Description "Creates a new instance of this command."
Public Function Create(ByVal View As IView) As ICommand
Dim result As AcceptCommand
Set result = New AcceptCommand
Set result.View = View
Set Create = result
End Function
Public Property Get View() As IView
Set View = this.View
End Property
Public Property Set View(ByVal RHS As IView)
GuardClauses.GuardDoubleInitialization this.View, TypeName(Me)
Set this.View = RHS
End Property
Private Function ICommand_CanExecute(ByVal Context As Object) As Boolean
Dim ViewModel As IViewModel
If TypeOf Context Is IViewModel Then
Set ViewModel = Context
If Not ViewModel.Validation Is Nothing Then
ICommand_CanExecute = ViewModel.Validation.IsValid
Exit Function
End If
End If
ICommand_CanExecute = True
End Function
Private Property Get ICommand_Description() As String
ICommand_Description = "Accept changes and close."
End Property
Private Sub ICommand_Execute(ByVal Context As Object)
this.View.Hide
End Sub
CancelCommand
This command is similar to the AcceptCommand in that it simply invokes a method in the View. This implementation could easily be enhanced by making the ViewModel track “dirty” (modified) state and prompting the user when they are about to discard unsaved changes.
'@Folder MVVM.Infrastructure.Commands
'@ModuleDescription "A command that closes (hides) a cancellable View in a cancelled state."
'@PredeclaredId
'@Exposed
Option Explicit
Implements ICommand
Private Type TState
View As ICancellable
End Type
Private this As TState
'@Description "Creates a new instance of this command."
Public Function Create(ByVal View As ICancellable) As ICommand
Dim result As CancelCommand
Set result = New CancelCommand
Set result.View = View
Set Create = result
End Function
Public Property Get View() As ICancellable
Set View = this.View
End Property
Public Property Set View(ByVal RHS As ICancellable)
GuardClauses.GuardDoubleInitialization this.View, TypeName(Me)
Set this.View = RHS
End Property
Private Function ICommand_CanExecute(ByVal Context As Object) As Boolean
ICommand_CanExecute = True
End Function
Private Property Get ICommand_Description() As String
ICommand_Description = "Cancel pending changes and close."
End Property
Private Sub ICommand_Execute(ByVal Context As Object)
this.View.OnCancel
End Sub
This gives us very good indications about how the pattern wants user actions to be implemented:
Class can have a @PredeclaredId annotation and expose a factory method to property-inject any dependencies; here a IView object, but a custom SaveChangesCommand would likely get injected with some DbContext service class.
All commands need a description; that description is user-facing as a tooltip on the binding target (usually a CommandButton).
CanExecute can be as simple as an unconditional ICommand_CanExecute = True, or as complex as needed (it has access to the ViewModel context); keep in mind that this method can be invoked relatively often, and should perform well and return quickly.
It’s a simple interface with a simple purpose: attach a command to a button. The EvaluateCanExecute method invokes the command’s CanExecute function and accordingly enables or disables the Target control.
By implementing all UI commands as ICommand objects, we keep both the View and the ViewModel free of command logic and Click handlers. By adopting the command pattern, we give ourselves all the opportunities to achieve low coupling and high cohesion. That is, small and specialized modules that depend on abstractions that can be injected from the outside.
Property Bindings
In XAML we use a special string syntax (“markup extensions”) to bind the value of, say, a ViewModel property, to that of a UI element property:
As long as the ViewModel implements INotifyPropertyChanged and the property fires the PropertyChanged event when its value changes, WPF can automatically keep the UI in sync with the ViewModel and the ViewModel in sync with the UI. WPF data bindings are extremely flexible and can also bind to static and dynamic resources, or other UI elements, and they are actually slightly more complex than that, but this captures the essence.
Obviously MVVM with MSForms in VBA isn’t going to involve any kind of special string syntax, but the concept of a PropertyBinding can very much be encapsulated into an object (and XAML compiles down to objects and methods, too). At its core, a binding is a pretty simple thing: a source, a target, and a method to update them.
Technically nothing prevents binding a target to any object type (although with limitations, since non-user code won’t be implementing INotifyPropertyChanged), but for the sake of clarity:
The binding Source is the ViewModel
The SourcePropertyPath is the name of a property of the ViewModel
The binding Target is the MSForms control
The binding TargetProperty is the name of a property of the MSForms control
Note that the SourcePropertyPath resolves recursively and can be a property of a property…of a property – as long as the string ultimately resolves to a non-object member.
The IBindingManager.BindPropertyPath method is pretty flexible and accepts a number of optional parameters while implementing sensible defaults for common MSForms controls’ “default property binding”. For example, you don’t need to specify a TargetProperty when binding a ViewModel property to a MSForms.TextBox: it will automatically binds to the Text property, but will accept to bind any other property.
The optional arguments are especially useful for custom data validation, but some of them also control various knobs that determine what and how the binding updates.
Value
Behavior
TwoWayBinding
Binding will update the source when the target changes, and will update the target when the source changes.
OneWayBinding
Binding will update the target when the source changes.
OneWayToSource
Binding will update the source when the target changes.
OneTimeBinding
Binding will only update the target once.
The BindingMode enum values
Value
Behavior
OnPropertyChanged
Binding will update when the bound property value changes.
OnKeyPress
Binding will update the source at each keypress. Only available for TextBox controls. Data validation may prevent the keypress from reaching the UI element.
OnExit
Binding will update the source just before target loses focus. Data validation may cancel the exit and leave the caret inside. This update source trigger is the most efficient since it only updates bindings when the user has finished providing a value.
The UpdateSourceTriggerenum values
Property Paths
The binding manager is able to recursively resolve a member path, so if your ViewModel has a ThingSection property that is itself a ViewModel with its own bindings and commands, that itself has a Thing property, know that the binding path can legally be “ThingSection.Thing“, and as long as the Source is the ViewModel object where a ThingSection property exists, and that the ThingSection porperty yields an object that has a Thing property, then all is good and the binding works. If ThingSection were to be Nothing when the binding is updated, then the target is assigned with a default value depending on the type. For example if ThingSection.Thing was bound to some TextBox1 control and the ThingSection property of the ViewModel was Nothing, then the Text property would end up being an empty string – note that this default value may be illegal, depending on what data validation is in place.
Data Validation
Every property binding can attach any IValueValidator implementation that encapsulates specialized, bespoke validation rules. The infrastructure code doesn’t include any custom validator, but the example show how one can be implemented. The interface mandates an IsValid function that returns a Boolean (True when valid), and a user-friendly Message property that the ValidationManager uses to create tooltips.
'@Folder MVVM.Example
Option Explicit
Implements IValueValidator
Private Function IValueValidator_IsValid(ByVal Value As Variant, ByVal Source As Object, ByVal Target As Object) As Boolean
IValueValidator_IsValid = Len(Trim$(Value)) > 0
End Function
Private Property Get IValueValidator_Message() As String
IValueValidator_Message = "Value cannot be empty."
End Property
The IsValid method provides you with the Value being validated, the binding Source, and the binding Target objects, which means every validator has access to everything exposed by the ViewModel; note that the method being a Function strongly suggests that it should not have side-effects. Avoid mutating ViewModel properties in a validator, but the message can be constructed dynamically if the validator is made to hold module-level state… although I would really strive to avoid making custom validators stateful.
While the underlying data validation mechanics are relatively complex, believe it or not there is no other step needed to implement custom validation for your property bindings: IBindingManager.BindPropertyPath is happy to take in any validator object, as long as it implements the IValueValidator interface.
Presenting Validation Errors
Without taking any steps to format validation errors, commands that can only execute against a valid ViewModel will automatically get disabled, but the input field with the invalid value won’t give the user any clue. By providing an IValidationErrorFormatter implementation when registering the binding, you get to control whether hidden UI elements should be displayed when there’s a validation error.
The ValidationErrorFormatter class meets most simple scenarios. Use the factory method to create an instance with a specific target UI element, then chain builder method calls to configure the formatting inline with a nice, fluent syntax:
Set Formatter = ValidationErrorFormatter.Create(Me.PathBox) _
.WithErrorBackgroundColor(vbYellow) _
.WithErrorBorderColor
Method
Purpose
Create
Factory method, ensures every instance is created with a target UI element.
WithErrorBackgroundColor
Makes the target have a different background color given a validation error. If no color is specified, a default “error background color” (light red) is used.
WithErrorBorderColor
Makes the target have a different border color given a validation error. If no color is specified, a default “error border color” (dark red) is used. Method has no effect if the UI control isn’t “flat style” or if the border style isn’t “fixed single”.
WithErrorForeColor
Makes the target have a different fore (text) color given a validation error. If no color is specified, a default “error border color” (dark red) is used.
WithErrorFontBold
Makes the target use a bold font weight given a validation error. Method has no effect if the UI element uses a bolded font face without a validation error.
WithTargetOnlyVisibleOnError
Makes the target UI element normally hidden, only to be made visible given a validation error. Particularly useful with aggregated formatters, to bind the visibility of a label and/or an icon control to the presence of a validation error.
The factory and builder methods of the ValidationErrorFormatter class.
The example code uses an AggregateErrorFormatter to tie multiple ValidationErrorFormatter instances (and thus possibly multiple different target UI controls) to the the same binding.
Value Converters
IBindingManager.BindPropertyPath can take an optional IValueConverter parameter when a conversion is needed between the source and the target, or between the target and the source. One useful value converter can be one like the InverseBooleanConverter implementation, which can be used in a binding where True in the source needs to bind to False in the target.
The interface mandates the presence of Convert and ConvertBack functions, respectively invoked when the binding value is going to the target and the source. Again, pure functions and performance-sensitive implementations should be preferred over side-effecting code.
'@Folder MVVM.Infrastructure.Bindings.Converters
'@ModuleDescription "A value converter that inverts a Boolean value."
'@PredeclaredId
'@Exposed
Option Explicit
Implements IValueConverter
Public Function Default() As IValueConverter
GuardClauses.GuardNonDefaultInstance Me, InverseBooleanConverter
Set Default = InverseBooleanConverter
End Function
Private Function IValueConverter_Convert(ByVal Value As Variant) As Variant
IValueConverter_Convert = Not CBool(Value)
End Function
Private Function IValueConverter_ConvertBack(ByVal Value As Variant) As Variant
IValueConverter_ConvertBack = Not CBool(Value)
End Function
Converters used in single-directional bindings don’t need to necessarily make both functions return a value that makes sense: sometimes a value can be converted to another but cannot round-trip back to the original, and that’s fine.
String Formatting
One aspect of property bindings I haven’t tackled yet, is the whole StringFormat deal. Once that is implemented and working, the string representation of the target control will be better separated from its actual value. And a sensible default format for some data types (Date, Currency) can even be inferred from the type of the source property!
Another thing string formatting would enable, is the ability to interpolate the value within a string. For example there could be a property binding defined like this:
And the NetAmountBox would read “USD$ 1,386.77” given the value 1386.77, and the binding would never get confused and would always know that the underlying value is a numeric value of 1386.77 and not a formatted string. Now, until that is done, string formatting probably needs to involve custom value converters. When string formatting works in property bindings, any converter will get invoked before: it’s always going to be the converted value that gets formatted.
ViewModel
Every ViewModel class is inherently application-specific and will look different, but there will be recurring themes:
Every field in the View wants to bind to a ViewModel property, and then you’ll want extra properties for various other things, so the ViewModel quickly grows more properties than comfort allows. Make smaller “ViewModel” classes by regrouping related properties, and bind with a property path rather than a plain property name.
Property changes need to propagate to the “main” ViewModel (the “data context”) somehow, so making all ViewModel classes fire a PropertyChanged event as appropriate is a good idea. Hold a WithEvents reference to the “child” ViewModel, and handle propagation by raising the “parent” ViewModel’s own PropertyChanged event, all the way up to the “main” ViewModel, where the handler nudges command bindings to evaluate whether commands can execute. One solution could be to register all command bindings with some CommandManager object that would have to implement IHandlePropertyChanged and would relieve the ViewModel of needing to do this.
Each ViewModel should implement at least two interfaces:
IViewModel, because we need a way to access the validation error handler and this interface makes a good spot for it.
INotifyPropertyChanged, to notify data bindings when a ViewModel property changes.
Here is the IViewModel implementation for the example code – the idea is really to expose properties for the view to bind, and we must not forget to notify handlers when a property value changes – notice the RHS-checking logic in the Property Let member:
'@Folder MVVM.Example
'@ModuleDescription "An example ViewModel implementation for some dialog."
'@PredeclaredId
Implements IViewModel
Implements INotifyPropertyChanged
Option Explicit
Public Event PropertyChanged(ByVal Source As Object, ByVal PropertyName As String)
Private Type TViewModel
'INotifyPropertyChanged state:
Handlers As Collection
'CommandBindings:
SomeCommand As ICommand
'Read/Write PropertyBindings:
SourcePath As String
SomeOption As Boolean
SomeOtherOption As Boolean
End Type
Private this As TViewModel
Private WithEvents ValidationHandler As ValidationManager
Public Function Create() As IViewModel
GuardClauses.GuardNonDefaultInstance Me, ExampleViewModel, TypeName(Me)
Dim result As ExampleViewModel
Set result = New ExampleViewModel
Set Create = result
End Function
Public Property Get Validation() As IHandleValidationError
Set Validation = ValidationHandler
End Property
Public Property Get SourcePath() As String
SourcePath = this.SourcePath
End Property
Public Property Let SourcePath(ByVal RHS As String)
If this.SourcePath <> RHS Then
this.SourcePath = RHS
OnPropertyChanged "SourcePath"
End If
End Property
Public Property Get SomeOption() As Boolean
SomeOption = this.SomeOption
End Property
Public Property Let SomeOption(ByVal RHS As Boolean)
If this.SomeOption <> RHS Then
this.SomeOption = RHS
OnPropertyChanged "SomeOption"
End If
End Property
Public Property Get SomeOtherOption() As Boolean
SomeOtherOption = this.SomeOtherOption
End Property
Public Property Let SomeOtherOption(ByVal RHS As Boolean)
If this.SomeOtherOption <> RHS Then
this.SomeOtherOption = RHS
OnPropertyChanged "SomeOtherOption"
End If
End Property
Public Property Get SomeCommand() As ICommand
Set SomeCommand = this.SomeCommand
End Property
Public Property Set SomeCommand(ByVal RHS As ICommand)
Set this.SomeCommand = RHS
End Property
Public Property Get SomeOptionName() As String
SomeOptionName = "Auto"
End Property
Public Property Get SomeOtherOptionName() As String
SomeOtherOptionName = "Manual/Browse"
End Property
Public Property Get Instructions() As String
Instructions = "Lorem ipsum dolor sit amet, consectetur adipiscing elit."
End Property
Private Sub OnPropertyChanged(ByVal PropertyName As String)
RaiseEvent PropertyChanged(Me, PropertyName)
Dim Handler As IHandlePropertyChanged
For Each Handler In this.Handlers
Handler.OnPropertyChanged Me, PropertyName
Next
End Sub
Private Sub Class_Initialize()
Set this.Handlers = New Collection
Set ValidationHandler = ValidationManager.Create
End Sub
Private Sub INotifyPropertyChanged_OnPropertyChanged(ByVal Source As Object, ByVal PropertyName As String)
OnPropertyChanged PropertyName
End Sub
Private Sub INotifyPropertyChanged_RegisterHandler(ByVal Handler As IHandlePropertyChanged)
this.Handlers.Add Handler
End Sub
Private Property Get IViewModel_Validation() As IHandleValidationError
Set IViewModel_Validation = ValidationHandler
End Property
Private Sub ValidationHandler_PropertyChanged(ByVal Source As Object, ByVal PropertyName As String)
OnPropertyChanged PropertyName
End Sub
Nothing much of interest here, other than the INotifyPropertyChanged implementation and the fact that a ViewModel is really just a fancy word for a class that exposes a bunch of properties that magically keep in sync with UI controls!
View
In a Smart UI, that module is, more often than not, a complete wreck. In Model-View-Presenter it quickly gets cluttered with many one-liner event handlers, and something just feels clunky about the MVP pattern. Now, I’m trying really hard, but I can’t think of a single reason to not want UserForm code-behind to look like this all the time… this is absolutely all of it, there’s no cheating going on:
'@Folder MVVM.Example
'@ModuleDescription "An example implementation of a View."
Implements IView
Implements ICancellable
Option Explicit
Private Type TView
'IView state:
ViewModel As ExampleViewModel
'ICancellable state:
IsCancelled As Boolean
'Data binding helper dependency:
Bindings As IBindingManager
End Type
Private this As TView
'@Description "A factory method to create new instances of this View, already wired-up to a ViewModel."
Public Function Create(ByVal ViewModel As ExampleViewModel, ByVal Bindings As IBindingManager) As IView
GuardClauses.GuardNonDefaultInstance Me, ExampleView, TypeName(Me)
GuardClauses.GuardNullReference ViewModel, TypeName(Me)
GuardClauses.GuardNullReference Bindings, TypeName(Me)
Dim result As ExampleView
Set result = New ExampleView
Set result.Bindings = Bindings
Set result.ViewModel = ViewModel
Set Create = result
End Function
Private Property Get IsDefaultInstance() As Boolean
IsDefaultInstance = Me Is ExampleView
End Property
'@Description "Gets/sets the ViewModel to use as a context for property and command bindings."
Public Property Get ViewModel() As ExampleViewModel
Set ViewModel = this.ViewModel
End Property
Public Property Set ViewModel(ByVal RHS As ExampleViewModel)
GuardClauses.GuardExpression IsDefaultInstance, TypeName(Me)
GuardClauses.GuardNullReference RHS
Set this.ViewModel = RHS
InitializeBindings
End Property
'@Description "Gets/sets the binding manager implementation."
Public Property Get Bindings() As IBindingManager
Set Bindings = this.Bindings
End Property
Public Property Set Bindings(ByVal RHS As IBindingManager)
GuardClauses.GuardExpression IsDefaultInstance, TypeName(Me)
GuardClauses.GuardDoubleInitialization this.Bindings, TypeName(Me)
GuardClauses.GuardNullReference RHS
Set this.Bindings = RHS
End Property
Private Sub BindViewModelCommands()
With Bindings
.BindCommand ViewModel, Me.OkButton, AcceptCommand.Create(Me)
.BindCommand ViewModel, Me.CancelButton, CancelCommand.Create(Me)
.BindCommand ViewModel, Me.BrowseButton, ViewModel.SomeCommand
'...
End With
End Sub
Private Sub BindViewModelProperties()
With Bindings
.BindPropertyPath ViewModel, "SourcePath", Me.PathBox, _
Validator:=New RequiredStringValidator, _
ErrorFormat:=AggregateErrorFormatter.Create(ViewModel, _
ValidationErrorFormatter.Create(Me.PathBox).WithErrorBackgroundColor.WithErrorBorderColor, _
ValidationErrorFormatter.Create(Me.InvalidPathIcon).WithTargetOnlyVisibleOnError("SourcePath"), _
ValidationErrorFormatter.Create(Me.ValidationMessage1).WithTargetOnlyVisibleOnError("SourcePath"))
.BindPropertyPath ViewModel, "Instructions", Me.InstructionsLabel
.BindPropertyPath ViewModel, "SomeOption", Me.OptionButton1
.BindPropertyPath ViewModel, "SomeOtherOption", Me.OptionButton2
.BindPropertyPath ViewModel, "SomeOptionName", Me.OptionButton1, "Caption", OneTimeBinding
.BindPropertyPath ViewModel, "SomeOtherOptionName", Me.OptionButton2, "Caption", OneTimeBinding
'...
End With
End Sub
Private Sub InitializeBindings()
If ViewModel Is Nothing Then Exit Sub
BindViewModelProperties
BindViewModelCommands
Bindings.ApplyBindings ViewModel
End Sub
Private Sub OnCancel()
this.IsCancelled = True
Me.Hide
End Sub
Private Property Get ICancellable_IsCancelled() As Boolean
ICancellable_IsCancelled = this.IsCancelled
End Property
Private Sub ICancellable_OnCancel()
OnCancel
End Sub
Private Sub IView_Hide()
Me.Hide
End Sub
Private Sub IView_Show()
Me.Show vbModal
End Sub
Private Function IView_ShowDialog() As Boolean
Me.Show vbModal
IView_ShowDialog = Not this.IsCancelled
End Function
Private Property Get IView_ViewModel() As Object
Set IView_ViewModel = this.ViewModel
End Property
Surely some tweaks will be made over the next couple of weeks as I put the UI design pattern to a more extensive workout with the Rubberduck website content maintenance app – but having used MVVM in C#/WPF for many years, I already know that this is how I want to be coding VBA user interfaces going forward.
I really love how the language has had the ability to make this pattern work, all along.
Note: this article was updated 2021-04-13 with screenshots from the latest v2.5.1.x pre-release build; the extract interface enhancements shown will green-release with v2.5.2.
We’ve seen how to leverage the default instance of a class module to define a stateless interface that’s perfect for a factory method. At the right abstraction level, most objects will not require more than just a few parameters. Often, parameters are related and can be abstracted/regrouped into their own object. Sometimes that makes things expressive enough. Other times, there’s just nothing we can do to work around the fact that we need to initialize a class with a dozen or more values.
The example code for this article can be found in our Examples repository.
A class with many properties
Such classes are actually pretty common; any entity object representing a database record would fit the bill. Let’s make a User class. We’re using Rubberduck, so this will be quick!
We start with a public field for each property we want:
Option Explicit
Public Id As String
Public UserName As String
Public FirstName As String
Public LastName As String
Public Email As String
Public EmailVerified As Boolean
Public TwoFactorEnabled As Boolean
Public PhoneNumber As String
Public PhoneNumberVerified As Boolean
Public AvatarUrl As String
Now we hit Ctrl+` to trigger a parse, right-click any of the variables and select Encapsulate Field from the Refactor menu (or Ctrl+Shift+F if you haven’t tweaked the default hotkeys):
The command will be disabled if the caret/selection isn’t on a public field, or if the module has been modified since the last time Rubberduck parsed it.
Check the wrap fields in private type box, then click the Select all button and hit OK.
The Encapsulate Field refactoring also lets you rename the properties and their respective backing field.
Now the module looks like this, and all you had to do was to declare a bunch of public fields:
Option Explicit
Private Type TUser
Id As String
UserName As String
FirstName As String
LastName As String
Email As String
EmailVerified As Boolean
TwoFactorEnabled As Boolean
PhoneNumber As String
PhoneNumberVerified As Boolean
AvatarUrl As String
End Type
Private this As TUser
Public Property Get Id() As String
Id = this.Id
End Property
Public Property Let Id(ByVal value As String)
this.Id = value
End Property
Public Property Get UserName() As String
UserName = this.UserName
End Property
Public Property Let UserName(ByVal value As String)
this.UserName = value
End Property
Public Property Get FirstName() As String
FirstName = this.FirstName
End Property
Public Property Let FirstName(ByVal value As String)
this.FirstName = value
End Property
Public Property Get LastName() As String
LastName = this.LastName
End Property
Public Property Let LastName(ByVal value As String)
this.LastName = value
End Property
Public Property Get Email() As String
Email = this.Email
End Property
Public Property Let Email(ByVal value As String)
this.Email = value
End Property
Public Property Get EmailVerified() As Boolean
EmailVerified = this.EmailVerified
End Property
Public Property Let EmailVerified(ByVal value As Boolean)
this.EmailVerified = value
End Property
Public Property Get TwoFactorEnabled() As Boolean
TwoFactorEnabled = this.TwoFactorEnabled
End Property
Public Property Let TwoFactorEnabled(ByVal value As Boolean)
this.TwoFactorEnabled = value
End Property
Public Property Get PhoneNumber() As String
PhoneNumber = this.PhoneNumber
End Property
Public Property Let PhoneNumber(ByVal value As String)
this.PhoneNumber = value
End Property
Public Property Get PhoneNumberVerified() As Boolean
PhoneNumberVerified = this.PhoneNumberVerified
End Property
Public Property Let PhoneNumberVerified(ByVal value As Boolean)
this.PhoneNumberVerified = value
End Property
Public Property Get AvatarUrl() As String
AvatarUrl = this.AvatarUrl
End Property
Public Property Let AvatarUrl(ByVal value As String)
this.AvatarUrl = value
End Property
I love this feature! Rubberduck has already re-parsed the module, so next we right-click anywhere in the module and select the Extract Interface refactoring, and check the box to select all Property Get accessors (skipping Property Let):
Extract Interface can automatically implement the extracted interface for you, and you can extract a public interface out of a private class.
Having a read-only interface for client code that doesn’t need the Property Let accessors makes an objectively cleaner API: assignments are recognized as invalid at compile time.
We get a read-only IUser interface for our efforts (!), and now the User class has an Implements IUser instruction at the top, …and these new members at the bottom:
Private Property Get IUser_ThingId() As String
IUser_ThingId = ThingId
End Property
Private Property Get IUser_UserName() As String
IUser_UserName = UserName
End Property
Private Property Get IUser_FirstName() As String
IUser_FirstName = FirstName
End Property
Private Property Get IUser_LastName() As String
IUser_LastName = LastName
End Property
Private Property Get IUser_Email() As String
IUser_Email = Email
End Property
Private Property Get IUser_EmailVerified() As Boolean
IUser_EmailVerified = EmailVerified
End Property
Private Property Get IUser_TwoFactorEnabled() As Boolean
IUser_TwoFactorEnabled = TwoFactorEnabled
End Property
Private Property Get IUser_PhoneNumber() As String
IUser_PhoneNumber = PhoneNumber
End Property
Private Property Get IUser_PhoneNumberVerified() As Boolean
IUser_PhoneNumberVerified = PhoneNumberVerified
End Property
Private Property Get IUser_AvatarUrl() As String
IUser_AvatarUrl = AvatarUrl
End Property
The scary part is that it feels as though if Extract Interface accounted for the presence of a Private Type in a similar way Encapsulate Field does, then even the TODO placeholder bits could be fully automated. Might be something to explore there… Update: automagic implementation completed!
Now we have our read-only interface worked out, if we go by previous posts’ teachings, , that is where we make our User class have a predeclared instance, and expose a factory method that I’d typically name Create:
'@Description "Creates and returns a new user instance with the specified property values."
Public Function Create(ByVal Id As String, ByVal UserName As String, ...) As IUser
'...
End Function
Without Rubberduck, in order to have a predeclared instance of your class you would have to export+remove the class module, locate the exported .cls file, open it in Notepad++, edit the VB_PredeclaredId attribute value to True, save+close the file, then re-import it back into your VBA project.
With Rubberduck, there’s an annotation for that: simply add '@PredeclaredId at the top of the class module, parse, and there will be a result for the AttributeValueOutOfSync inspection informing you that the class’ VB_PredeclaredId attribute value disagrees with the @PredeclaredId annotation, and then you apply the quick-fix you want, and you just might have synchronized hidden attributes across the with a single click.
'@PredeclaredId
Option Explicit
When it’s a factory method for a service class that takes in dependencies, 2-3 parameters is great, 5+ is suspicious. But here we’re taking in values, pure data – not some IFileWriter or other abstraction. And we need quite a lot of them (here 10, but who knows how many that can be!), and that’s a problem, because this is very ugly:
Set identity = User.Create("01234", "Rubberduck", "contact@rubberduckvba.com", False, ...)
But the resulting code still feels pretty loaded, and that’s with consistent line breaks. Problem is, that limits the number of factory method parameters to 20-ish (if we’re nice and stick to one per line), since that’s how many line continuations the compiler will handle for a single logical line of code.
Surely there’s a better way.
Building the Builder
I wrote about this pattern in OOP Design Patterns: The Builder, but in retrospect that article was really just a quick overview. Let’s explore the builder pattern.
I like to design objects from the point of view of the code that will be consuming them. In this case what we want to end up with, is something like this:
This solves a few problems that the factory method doesn’t:
Optional arguments become explicitly optional member calls; long argument lists are basically eliminated.
Say Id and UserName are required, i.e. a User object would be invalid without these values; the builder’s own Create factory method can take these required values as arguments, and that way any User instance that was built with a UserBuilder is guaranteed to at least have these values.
If we can provide a value for EmailVerified but not for Email, or for PhoneVerified but not for Phone, and neither are required… then with individual properties the best we can do is raise some validation error after the fact. With a UserBuilder, we can have WithEmail and WithPhone methods that take a Verified Boolean parameter along with the email/phone, and guarantee that if EmailVerified is supplied, then Email is supplied as well.
I like to start from abstractions, so let’s add a new class module – but don’t rename it just yet, otherwise Rubberduck will parse it right away. Instead, copy the IUser interface into the new Class1 module, select all, and Ctrl+H to replace “Property Get ” (with the trailing space) with “Function With” (without the trailing space). Still with the whole module selected, we replace “String” and “Boolean” with “IUserBuilder”. The result should look like this:
'@Interface
Option Explicit
Public Function WithId() As IUserBuilder
End Function
Public Function WithUserName() As IUserBuilder
End Function
Public Function WithFirstName() As IUserBuilder
End Function
Public Function WithLastName() As IUserBuilder
End Function
Public Function WithEmail() As IUserBuilder
End Function
Public Function WithEmailVerified() As IUserBuilder
End Function
Public Function WithTwoFactorEnabled() As IUserBuilder
End Function
Public Function WithPhoneNumber() As IUserBuilder
End Function
Public Function WithPhoneNumberVerified() As IUserBuilder
End Function
Public Function WithAvatarUrl() As IUserBuilder
End Function
We’re missing a Build method that returns the IUser we’re building:
Public Function Build() As IUser
End Function
Now we add the parameters and remove the members we don’t want, merge the related ones into single functions – this is where we define the shape of our builder API: if we want to make it hard to create a User with a LastName but without a FirstName, or one with TwoFactorEnabled and PhoneNumberVerified set to True but without a PhoneNumber value… then with a well-crafted builder interface we can make it do exactly that.
Once we’re done, we can rename the class module to IUserBuilder, and that should trigger a parse. The interface might look like this now:
'@Interface
'@ModuleDescription("Incrementally builds a User instance.")
Option Explicit
'@Description("Returns the current object.")
Public Function Build() As IUser
End Function
'@Description("Builds a user with a first and last name.")
Public Function WithName(ByVal FirstName As String, ByVal LastName As String) As IUserBuilder
End Function
'@Description("Builds a user with an email address.")
Public Function WithEmail(ByVal Email As String, Optional ByVal Verified As Boolean = False) As IUserBuilder
End Function
'@Description("Builds a user with SMS-based 2FA enabled.")
Public Function WithTwoFactorAuthentication(ByVal PhoneNumber As String, Optional ByVal Verified As Boolean = False) As IUserBuilder
End Function
'@Description("Builds a user with an avatar at the specified URL.")
Public Function WithAvatar(ByVal Url As String) As IUserBuilder
End Function
Then we can add another class module, and type Implements IUserBuilder under Option Explicit, then hit Ctrl+` to parse. Unless you disabled the “check if code compiles before parsing” setting (it’s enabled by default), you should be seeing this warning:
The project can’t compile, because the interface isn’t implemented.
Click Yes to parse anyway (normally we only want compilable code, but in this case we know what we’re doing, I promise), then right-click somewhere in the Implements IUserBuilder statement, and select the Implement Interface refactoring:
Creating all these method stubs manually, or… letting Rubberduck create them all at once in a split-second?
The result is as follows, and makes a good starting point:
Option Explicit
Implements IUserBuilder
Private Function IUserBuilder_Build() As IUser
Err.Raise 5 'TODO implement interface member
End Function
Private Function IUserBuilder_WithName(ByVal FirstName As String, ByVal LastName As String) As IUserBuilder
Err.Raise 5 'TODO implement interface member
End Function
Private Function IUserBuilder_WithEmail(ByVal Email As String, Optional ByVal Verified As Boolean = False) As IUserBuilder
Err.Raise 5 'TODO implement interface member
End Function
Private Function IUserBuilder_WithTwoFactorAuthentication(ByVal PhoneNumber As String, Optional ByVal Verified As Boolean = False) As IUserBuilder
Err.Raise 5 'TODO implement interface member
End Function
Private Function IUserBuilder_WithAvatar(ByVal Url As String) As IUserBuilder
Err.Raise 5 'TODO implement interface member
End Function
We’re “building” an IUser object. So we have a module-level User object (we need the class’ default interface here, so that we can access the Property Let members), and each With method sets one property or more and then returns the current object (Me). That last part is critical, it’s what makes the builder methods chainable. We’ll need a Build method to return an encapsulated IUser object. So the next step will be to add a @PredeclaredId annotation and implement a Create factory method that takes the required values and injects the IUser object into the IUserBuilder instance we’re returning; then we can remove the members for these required values, leaving only builder methods for the optional ones. We will also add a value parameter of the correct type to each builder method, and make them all return the current object (Me). Once the class module looks like this, we can rename it to UserBuilder, and Rubberduck parses the code changes – note the @PredeclaredId annotation (needs to be synchronized to set the hidden VB_PredeclaredId attribute to True:
'@PredeclaredId
'@ModuleDescription("Builds a User object.")
Option Explicit
Implements IUserBuilder
Private internal As User
'@Description("Creates a new UserBuilder instance.")
Public Function Create(ByVal Id As String, ByVal UserName As String) As IUserBuilder
Dim result As UserBuilder
Set result = New UserBuilder
'@Ignore UserMeaningfulName FIXME
Dim obj As User
Set obj = New User
obj.Id = Id
obj.UserName = UserName
Set result.User = internal
Set Create = result
End Function
'@Ignore WriteOnlyProperty
'@Description("For property injection of the internal IUser object; only the Create method should be invoking this member.")
Friend Property Set User(ByVal value As IUser)
If Me Is UserBuilder Then Err.Raise 5, TypeName(Me), "Member call is illegal from default instance."
If value Is Nothing Then Err.Raise 5, TypeName(Me), "'value' argument cannot be a null reference."
Set internal = value
End Property
Private Function IUserBuilder_Build() As IUser
If internal Is Nothing Then Err.Raise 91, TypeName(Me), "Builder initialization error: use UserBuilder.Create to create a UserBuilder."
Set IUserBuilder_Build = internal
End Function
Private Function IUserBuilder_WithName(ByVal FirstName As String, ByVal LastName As String) As IUserBuilder
internal.FirstName = FirstName
internal.LastName = LastName
Set IUserBuilder_WithName = Me
End Function
Private Function IUserBuilder_WithEmail(ByVal Email As String, Optional ByVal Verified As Boolean = False) As IUserBuilder
internal.Email = Email
internal.EmailVerified = Verified
Set IUserBuilder_WithEmail = Me
End Function
Private Function IUserBuilder_WithTwoFactorAuthentication(ByVal PhoneNumber As String, Optional ByVal Verified As Boolean = False) As IUserBuilder
internal.TwoFactorEnabled = True
internal.PhoneNumber = PhoneNumber
internal.PhoneNumberVerified = Verified
Set IUserBuilder_WithTwoFactorAuthentication = Me
End Function
Private Function IUserBuilder_WithAvatar(ByVal Url As String) As IUserBuilder
internal.AvatarUrl = Url
Set IUserBuilder_WithAvatar = Me
End Function
Now, when I said default instances and factory methods (here too) are some kind of fundamental building block, I mean we’re going to be building on top of that, starting with this builder pattern; the Create method is intended to be invoked off the class’ default instance, like this:
Set builder = UserBuilder.Create(internalId, uniqueName)
The advantages are numerous, starting with the possibility to initialize the builder with everything it needs (all the required values), so that the client code can call Build and consume a valid User object right away.
Side note about this FIXME comment – there’s more to it than it being a signpost for the reader/maintainer:
'@Ignore UserMeaningfulName FIXME
Dim obj As User
By default only TODO, BUG, and NOTE markers are picked up, but you can easily configure Rubberduck to find any marker you like in comments, and then the ToDo Explorer lets you easily navigate them all:
Rubberduck has a ToDo Explorer toolwindow that can be configured (click the cogwheel icon) to pick up “FIXME” anywhere in comments, anywhere in the project. Or “HERE BE DRAGONS”.
Another noteworthy observation:
'@Ignore WriteOnlyProperty
'@Description("For property injection of the internal IUser object; only the Create method should be invoking this member.")
Friend Property Set User(ByVal value As IUser)
If Me Is UserBuilder Then Err.Raise 5, TypeName(Me), "Member call is illegal from default instance."
If value Is Nothing Then Err.Raise 5, TypeName(Me), "'value' argument cannot be a null reference."
Set internal = value
End Property
Me is always the current object, as in, an instance of this class module, presenting the default interface of this class module: the If Me Is UserBuilder condition evaluates whether Me is the object known as UserBuilder – and right now there’s no such thing and the code doesn’t compile.
Synchronizing Attributes & Annotations
Rubberduck knows we mean that class to have a VB_PredeclaredId attribute value of True because of the @PredeclaredId annotation, but it’s still just a comment at this point. Bring up the inspection results toolwindow, and find the results for the MissingAttribute inspection under Rubberduck Opportunities:
Clicking Fix all occurrences in project will automatically add all the missing attributes.
That didn’t fix the VB_PredeclaredId attributes! Why?! The reason is that the attribute isn’t missing, only its value is out of sync. We’ll have to change this (pull requests welcome!), but for now you’ll find the AttributeValueOutOfSync inspection results under the Code Quality Issues group. If you group results by inspection, its miscategorization doesn’t matter though:
When attributes and annotations contradict each other, the AttributeValueOutOfSync inspection starts issuing results.
Adjust the attribute value accordingly (right-click the inspection result, or select “adjust attribute value(s)” from the “Fix” dropdown menu), and now your UserBuilder is ready to use:
Dim identity As IUser
Set identity = UserBuilder.Create(uniqueId, uniqueName) _
.WithName(first, last) _
.WithEmail(emailAddress) _
.Build
…and misuse:
Set UserBuilder.User = New User '<~ runtime error, illegal from default instance
Debug.Print UserBuilder.User.AvatarUrl '<~ compile error, invalid use of property
Set builder = New UserBuilder
Set identity = builder.Build '<~ runtime error 91, builder state was not initialized
Set builder = New UserBuilder
Set builder = builder.WithEmail(emailAddress) '<~ runtime error 91
Conclusions
Model classes with many properties are annoying to write, and annoying to initialize. Sometimes properties are required, other times properties are optional, others are only valid if another property has such or such value. This article has shown how effortlessly such classes can be created with Rubberduck, and how temporal coupling and other state issues can be solved using the builder creational pattern.
Using this pattern as a building block in the same toolbox as factory methods and other creational patterns previously discussed, we can now craft lovely fluent APIs that can chain optional member calls to build complex objects with many properties without needing to take a gazillion parameters anywhere.
Unless you’re hosted in Access, your VBA project doesn’t have access to a database engine. If you’re in Excel, it’s easy to treat the host workbook as a database and each worksheet as a table. While we can build an application that uses Excel worksheets to store data, we probably shouldn’t do that. The reasons are many, but primarily (pun …yeah, intended), we want to be able to establish bullet-proof referential integrity between records/tables; while Excel is great for many things, it’s useless for that: it’s the job of a relational database system (RDBMS), not that of any worksheet software, no matter how powerful. Power Query is very much worth looking into, but if you’re building a small CRUD (Create/Read/Update/Delete) application in VBA, you’ll want VBA code responsible for the data access – enter ADODB, …and every pitfall that comes with it.
Querying a database with ADODB is easy: just set up a connection, open it, then execute whatever SQL statement you need through the Connection, and you get the results in a Recordset object:
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.Open "ConnectionString"
Dim rs As ADODB.Recordset
Set rs = conn.Execute("SELECT Field1, Field2 FROM Table1")
'...
rs.Close
conn.Close
That is great for one-timer, ad-hoc queries: things quickly get messy when you start needing multiple queries, or when your SQL statement needs to be invoked repeatedly with different values:
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.Open "ConnectionString"
Dim i As Long
For i = 1 To 10
Dim rs As ADODB.Recordset
Set rs = conn.Execute("SELECT Field1, Field2 FROM Table1 WHERE Field3 = " & i)
'...
rs.Close
Next
conn.Close
This right here – WHERE SomeField = " & i, is making the database engine work harder than it needs to… and it’s costing server-side performance, because as far as the engine knows, it’s getting a different query every time – and thus computes the same execution plan over and over, every time… when it could just be reusing it. Databases are smart. Like, wicked smart… but yeah we still need to ask for the right thing!
Compare to something like this:
Const sql As String = "SELECT Field1, Field2 FROM Table1 WHERE Field3 = ?"
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.Open "ConnectionString"
Dim i As Long
For i = 1 To 10
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
cmd.CommandType = adCmdText
cmd.CommandText = sql
cmd.Parameters.Append cmd.CreateParameter(Type:=adInteger, Value:= i)
Dim rs As ADODB.Recordset
Set rs = cmd.Execute
'...
rs.Close
Next
conn.Close
Oh my, so much more code, so little gain – right?
Using ADODB.Command when queries involve a WHERE (and/or VALUES) clause and user-provided (directly or not) values is not only more efficient (the cached execution plan is reused because the command string is identical every time), it’s also more secure. Concatenating user inputs into SQL command strings is a common rookie mistake, and it’s a practice that is way more widespread than it should be (regardless of the language, paradigm, or platform); your code becomes vulnerable to SQL Injection Attacks – something that may or may not be in your threat model, but that inevitably turns into… easily avoidable bugs: think of what might happen if a user entered O'Connor in that LastName field. If you’re thinking “oh that’s easy! I’ll just double-up single quotes, and fixed!“, then you’re playing a needlessly exhausting game of cat-and-mouse with the next thing that will break your clever escaping: the mouse wins.
Abstract thoughts
Much simpler to just use an ADODB.Command every time, and when you need it parameterized, to Append any number of ADODB.Parameter objects to its Parameters collection. Except, it does make a lot of code to write, every time.
What do we do when we see repetitive patterns in code? If you’re thinking “we put it in a function!” then you’re thinking abstraction and that’s exactly the right train of thought.
We’re just going to take this abstraction… and make it an object. Then think of what objects it needs in order to do its job, and abstract these objects behind interfaces too, and take these abstractions in as constructor parameters of our Create “static” factory method. Rinse & repeat until all dependencies are property-injected and all responsibilities are nicely encapsulated into their own classes. It was fun!
I wrote an original version of this functionality little while ago – you can find the original version on Code Review, and see how different/similar it is to this simplified/improved version in our Examples repositoryon GitHub.
The original was just an ADODB wrapper class though, couldn’t really be unit-tested, and was annoying to maintain because it felt very repetitive. This version is separating the type mappings from the parameter-providing logic, which makes configuring these mappings is done through an object that’s solely responsible for these mappings; it also separates the command from the connection, and abstracts away that connection enough to enable unit testing and cover quite a large part of the API – but most importantly, this version exposes adequate abstractions for the calling code to use and stub in its own unit tests.
VBA code written with this API (and the principles it demonstrates) can easily be fully testable, without ever actually hitting any database.
I can do this in the immediate pane:
?UnitOfWork.FromConnectionString("connection string").Command.GetSingleValue("SELECT Field1 FROM Table1 WHERE Id=?", 1)
I mean, it’s a contrived example, but with a valid connection string, query, and arguments, that’s all we need to get an actual parameterized ADODB command sending that 1 as an actual ADODB parameter, …and the following debug output:
Begin connect...
Connect completed. Status: 1
Begin transaction completed.
Begin execute...
Execute completed, -1 record(s) affected.
{whatever value was in Field1}
Rollback transaction completed.
Disconnect completed. Status: 1
I made DbConnection listen in on whatever events the ADODB connection is firing, pending the implementation of an adapter to expose some IDbConnectionEvents members – the idea is to end up with client code that can inject its own callbacks and do things like log such messages. In the meantime Debug.Print statements are producing this debug output, but that’s it’s an implementation detail: it doesn’t publicly expose any of these events. It couldn’t, either: the rest of the code needs to work with the IDbConnection interface, and interfaces unfortunately can’t expose events in VBA.
SecureADODB
Some might call it layered spaghetti. Others call it lasagna. I call it well-abstracted code that reads and maintains like a charm and provably works as intended. There is nothing, absolutely nothing wrong with having many class modules in a VBA project: the only problem is… well, the VBE itself:
Project Explorer is making OOP rather painful. In fact it makes any kind of modularization painful.
Code Explorer makes the VBE more OOP-friendly: now you can have folders regrouping modules by functionality rather than just by module type.
Nice, rich APIs involve many related objects, interfaces, methods – members that make up the object model the API’s client code will be working with. As long as we can keep all these classes organized, there’s no problem having many of them.
Before we look at the implementation, let’s review the interfaces and the overall structure.
Only two interfaces aren’t being stubbed for unit tests. IUnitOfWork because as the top-level object nothing in the object model consumes it. It is needed though, because client code can inject it as a dependency of some FooRepository class, and then tests can provide it with a StubUnitOfWork that implements IUnitOfWork.
The other “façade” interface is ITypeMap. This one isn’t really needed (neither is the predeclared instance of AdoTypeMappings or its Default factory method), something felt wrong with the client code without it. While the class is essentially just a dictionary / literally a map, there’s something rather elegant about depending on an ITypeMap rather than some Scripting.Dictionary.
The two dark blue interfaces are abstract factory interfaces, each with a “real” and a “stub” implementation for tests: these are very simple classes whose entire purpose is to create an object of a particular type.
If we consider IParameterProvider an implementation detail of IDbCommandBase, that leaves us with only the core stuff: IDbCommandBase, IDbCommand, and IDbConnection – everything else just revolves around these.
DbCommandBase
The old SqlCommand code had two sets of commands: “Execute” for methods you could pass a Connection to, and “QuickExecute” for methods that created a connection on-the-spot. I decided to split the two behaviors into two distinct implementation of the same interface, and that’s how I ended up with DefaultDbCommand and AutoDbCommand. As I was cleaning up the two new classes, I had to notice these two classes needed a number of common bits of functionality… as would any other implementation of IDbCommand.
In a language that supports inheritance, I would probably make the two classes inherit a third abstract “base” class where I’d implement the IDbCommand interface. In VBA, we can’t derive a class from another, or inherit members from another class: inheritance is flat-out unavailable. There’s an alternative though, and it’s arguably even better than inheritance: composition. We can put the common functionality in a third class, and then have the two implementations take an instance of that “base” class as we would any other dependency – effectively achieving what we wanted out of inheritance, but through composition.
Code is said to be “decoupled” when none of its concrete components are inter-dependent, as is apparent with the solid black “depends on” arrows here. Decoupled components can easily be swapped for other implementations, like …test stubs.
What’s wrong with inheritance?
Don’t get me wrong, inheritance is very cool: with an abstract class you can have templated methods, where a method in the base class (typically a method that implements some interface member) invokes an abstract or virtual method (typically with protected scope) that the inherited class must override and provide an implementation for. Rubberduck uses this pattern in quite a few places (inspections, notably). Without inheritance, it’s just not something that’s possible.
Inheritance is described as a “is a” relationship, while composition is more of a “has a” relationship. This is important, because when the only consideration weighting in favor of inheritance is the need for two classes to share some functionality, it’s exactly why inheritance should not be used.
Decoupling FTW
The “base” class appeared as a need to have a place for IDbCommand implementations to access shared functionality. I wanted to return disconnected recordsets, and retrieving the value of the first field of the first record of a recordset isn’t something that’s glaringly implementation-specific. The other piece of functionality I needed, was a function that creates the ADODB.Command object and adds the parameters.
Because I wanted this class to create the ADODB.Command, I needed it to be able to turn a Variant into an ADODB.Parameter through some mapping, and since I didn’t want my class to be necessarily coupled with that mapping, or anything remotely related to configuring ADODB parameters… I’m property-injecting an IParameterProvider dependency:
Public Function Create(ByVal provider As IParameterProvider) As IDbCommandBase
Errors.GuardNonDefaultInstance Me, DbCommandBase
Errors.GuardNullReference provider
Dim result As DbCommandBase
Set result = New DbCommandBase
Set result.ParameterProvider = provider
Set Create = result
End Function
Validating the command string / arguments
Since the commands are given an SQL command string to execute, and a ParamArray array of arguments that should have the same number of items as there are ? ordinal parameters in the SQL command string, we have an opportunity to catch a missing or extraneous argument before we even send the command string to the database server. And because this validation logic would have to be the same regardless of what IDbCommand implementation we’re looking at, DbCommandBase makes the best place to put it.
This implementation is probably too naive for a number of edge cases, but sufficient for most: we’re simply counting the number of ? characters in the sql string, and comparing that with the number of elements in the args array. We need to handle errors here, because if the args array is empty, evaluating UBound(args) and/or LBound(args) will throw a “subscript out of range” run-time error 9.
Public Function ValidateOrdinalArguments(ByVal sql As String, ByRef args() As Variant) As Boolean
On Error GoTo CleanFail
Dim result As Boolean
Dim expected As Long
expected = Len(sql) - Len(Replace(sql, "?", vbNullString))
Dim actual As Long
On Error GoTo CleanFail 'if there are no args, LBound/UBound are both out of bounds
actual = UBound(args) + (1 - LBound(args))
CleanExit:
result = (expected = actual)
ValidateOrdinalArguments = result
Exit Function
CleanFail:
actual = 0
Resume CleanExit
End Function
Getting a disconnected Recordset
If we created a database connection, issued a command against it, and received the recordset from ADODB.Command.Execute, and then we close the connection and return that recordset, then the calling code can’t use the data anymore: a connected recordset only works if the calling code owns the connection. So we need a way to issue a disconnected recordset, while still using an ADODB.Command. The way to do this, is to pass the command as the Source argument to Recordset.Open, and to use a static, client-side cursor:
Private Function GetDisconnectedRecordset(ByVal cmd As ADODB.Command) As ADODB.Recordset
Errors.GuardNullReference cmd
Errors.GuardNullReference cmd.ActiveConnection
Dim result As ADODB.Recordset
Set result = New ADODB.Recordset
result.CursorLocation = adUseClient
result.Open Source:=cmd, CursorType:=adOpenStatic
Set result.ActiveConnection = Nothing
Set GetDisconnectedRecordset = result
End Function
Getting a single value result
With functions to validate the parameters, create commands and get a disconnected recordset, we have everything we need for IDbCommand implementations to do their job, but if we leave it like this, we’ll end up with all implementations copying the logic of IDbCommand.GetSingleValue: best have that logic in DbCommandBase and avoid as much repetition as possible.
Private Function GetSingleValue(ByVal db As IDbConnection, ByVal sql As String, ByRef args() As Variant) As Variant
Errors.GuardEmptyString sql
Dim cmd As ADODB.Command
Set cmd = CreateCommand(db, adCmdText, sql, args)
Dim results As ADODB.Recordset
Set results = GetDisconnectedRecordset(cmd)
GetSingleValue = results.Fields.Item(0).value
End Function
Creating the command
A few things can go wrong when creating the ADODB.Command object: we need an ADODB.Connection that’s open, and the parameters must be valid. Since we’re not executing the command just yet, we don’t have to worry about everything that could go wrong actually executing the command string and processing the parameters on the server. So the strategy here is to guard against invalid inputs as much as possible, and then to handle errors when we add the parameters, and return the Command object with whatever parameters were successfully added. We don’t need to try salvaging the rest of the parameters if one blows up, since that failing parameter will fail command execution anyway, but there isn’t much we can do about it, other than perhaps throw an error and have the caller not even try to run the command – but here I decided that the server-side errors would be more useful than any custom “invalid parameter” error.
Note that the ADODB.Command object is actually created by the method-injected IDbConnection dependency. This creates a seam between the class and ADODB, despite the inherent coupling with the ADODB.Command type: it makes the command’s ActiveConnection an implementation detail of IDbConnection.CreateCommand, and that’s all I needed to make this method work with a stub connection that isn’t actually connecting to anything:
Private Function CreateCommand(ByVal db As IDbConnection, ByVal commandType As ADODB.CommandTypeEnum, ByVal sql As String, ByRef args() As Variant) As ADODB.Command
Errors.GuardNullReference db
Errors.GuardEmptyString sql
Errors.GuardExpression db.State <> adStateOpen, message:="Connection is not open."
Errors.GuardExpression Not ValidateOrdinalArguments(sql, args), message:="Arguments supplied are inconsistent with the provided command string parameters."
Dim cmd As ADODB.Command
Set cmd = db.CreateCommand(commandType, sql)
On Error GoTo CleanFail
Dim arg As ADODB.Parameter
For Each arg In this.ParameterProvider.FromValues(args)
cmd.parameters.Append arg
Next
CleanExit:
Set CreateCommand = cmd
Exit Function
CleanFail:
Resume CleanExit
End Function
DbCommand
As mentioned before, there are two implementations for the IDbCommand interface: one that creates and owns its own IDbConnection, the other that takes it in as a dependency.
This abstraction represents an object that can take an SQL statement and parameters, and return the result(s) to its caller.
DefaultDbCommand receives its IDbConnection dependency through property injection in its Create factory method.
AutoDbCommand takes a connection string and an IDbConnectionFactory instead.
UnitOfWork uses a DefaultDbCommand because the unit of work needs to own the connection, but AutoDbCommand could be used instead of a unit of work, if we just need a quick SELECT and no transaction.
Abstract Factory
IDbConnectionFactory is an Abstract Factory here. This is needed, because unit tests need to be able to inject a stub factory that produces stub connections: an abstract factory is a factory interface that creates objects of a type that is also an abstraction – in this case, IDbConnectionFactory.Create returns an IDbConnection object. Implementing this factory class is exactly as simple as you’d think – here’s DbConnectionFactory:
'@Exposed
'@Folder("SecureADODB.DbConnection")
'@ModuleDescription("An implementation of an abstract factory that creates DbConnection objects.")
Option Explicit
Implements IDbConnectionFactory
Private Function IDbConnectionFactory_Create(ByVal connString As String) As IDbConnection
Set IDbConnectionFactory_Create = DbConnection.Create(connString)
End Function
And here’s StubDbConnectionFactory:
'@Folder("Tests.Stubs")
'@ModuleDescription("A stub acting as a IDbConnectionFactory implementation.")
Option Explicit
Implements IDbConnectionFactory
Private Type TInvokeState
CreateConnectionInvokes As Long
End Type
Private this As TInvokeState
Private Function IDbConnectionFactory_Create(ByVal connString As String) As IDbConnection
this.CreateConnectionInvokes = this.CreateConnectionInvokes + 1
Set IDbConnectionFactory_Create = New StubDbConnection
End Function
Public Property Get CreateConnectionInvokes() As Long
CreateConnectionInvokes = this.CreateConnectionInvokes
End Property
The test stub is more “complex” because it tracks method invocations, so that tests can know whether & how many times any given member was invoked during a test run.
The Abstract Factory pattern is very useful with Dependency Injection: it gives us an abstraction to inject when a class needs a dependency that just cannot be injected when the object is created – the alternative would be tight coupling: if we weren’t injecting a connection factory, then the command class would’ve had to be the one invoking DbConnection.Create – tightly coupling it with the DbConnection class and instantly making unit testing impossible. An abstract factory removes the coupling and allows unit tests to inject an alternative/stub implementation of the factory that creates StubDbConnection objects.
Wrapping it all up
AutoDbConnection can very well be consumed as-is by the client code:
Dim results As ADODB.Recordset
Set results = AutoDbConnection.Create(connString, New DbConnectionFactory, DbCommandBase.Create(AdoParameterProvider.Create(AdoTypeMappings.Default))).Execute(sql)
The only problem is that, well, the dependencies need to be resolved somehow, and that means the client code is now responsible for wiring everything up. While each component has a clear purpose, explicitly creating all these objects quickly gets old and redundant: we need an object that simplifies this – enter IUnitOfWork, and now we can use this much simpler code:
Dim results As ADODB.Recordset
Set results = UnitOfWork.FromConnectionString(connString).Command.Execute(sql)
Unit of Work is a design pattern that encapsulates a transaction: each individual operation can succeed or fail, and the unit of work either succeeds or fails as a whole. These notions are abstracted in the IUnitOfWork interface:
'@Folder("SecureADODB.UnitOfWork")
'@ModuleDescription("Represents an object encapsulating a database transaction.")
'@Interface
'@Exposed
Option Explicit
'@Description("Commits the transaction.")
Public Sub Commit()
End Sub
'@Description("Rolls back the transaction.")
Public Sub Rollback()
End Sub
'@Description("Creates a new command to execute as part of the transaction.")
Public Function Command() As IDbCommand
End Function
When a UnitOfWork is created, it initiates a database transaction. When it is destroyed before the transaction is committed, the transaction gets rolled back and from the database’s point of view, it’s like nothing happened.
Transaction?
If you’re unfamiliar with database transactions, there’s an easy example to illustrate what they do: imagine you have an Accounts table, and you’re processing a transfer – you need to UPDATE the record for the source account to deduct the transfer amount, then UPDATE the record for the destination account to add the transferred amount. In a happy world where everything goes well that would be the end of it… but the world is a cruel place, and assuming the 1st command goes through, nothing guarantees nothing will blow up when sending the 2nd command. Without transactions, the funds would simply vanish: they’re gone from the first account, and they were never added to the second account. With a transaction, we can rollback everything when the 2nd operation completes, no funds vanish and the data is exactly the way it was before the transaction started.
Again, the implementation is pretty straightforward – the only peculiarity is that the class has two factory methods – one named Create that takes all the dependencies in, and another named FromConnectionString that conveniently wires up a default set of dependencies (and then passes them to the Create method to avoid duplicating code).
'@Folder("SecureADODB.UnitOfWork")
'@ModuleDescription("An object that encapsulates a database transaction.")
'@PredeclaredId
'@Exposed
Option Explicit
Implements IUnitOfWork
Private Type TUnitOfWork
Committed As Boolean
RolledBack As Boolean
Connection As IDbConnection
CommandFactory As IDbCommandFactory
End Type
Private this As TUnitOfWork
'@Description("Creates a new unit of work using default configurations.")
'@Ignore ProcedureNotUsed
Public Function FromConnectionString(ByVal connString As String) As IUnitOfWork
Dim db As IDbConnection
Set db = DbConnection.Create(connString)
Dim provider As IParameterProvider
Set provider = AdoParameterProvider.Create(AdoTypeMappings.Default)
Dim baseCommand As IDbCommandBase
Set baseCommand = DbCommandBase.Create(provider)
Dim factory As IDbCommandFactory
Set factory = DefaultDbCommandFactory.Create(baseCommand)
Set FromConnectionString = UnitOfWork.Create(db, factory)
End Function
'@Inject: just an idea.. see #https://github.com/rubberduck-vba/Rubberduck/issues/5463
Public Function Create(ByVal db As IDbConnection, ByVal factory As IDbCommandFactory) As IUnitOfWork
Errors.GuardNonDefaultInstance Me, UnitOfWork
Errors.GuardNullReference factory
Errors.GuardNullReference db
Errors.GuardExpression db.State <> adStateOpen, message:="Connection should be open."
Dim result As UnitOfWork
Set result = New UnitOfWork
Set result.CommandFactory = factory
Set result.Connection = db
Set Create = result
End Function
'@Inject: this member should only be invoked by Me.Create, where Me is the class' default/predeclared instance.
'@Ignore ProcedureNotUsed: false positive with v2.5.0.5418
Friend Property Set Connection(ByVal value As IDbConnection)
Errors.GuardDoubleInitialization this.Connection
Errors.GuardNullReference value
Set this.Connection = value
this.Connection.BeginTransaction
End Property
'@Inject: this member should only be invoked by Me.Create, where Me is the class' default/predeclared instance.
'@Ignore ProcedureNotUsed: false positive with v2.5.0.5418
Friend Property Set CommandFactory(ByVal value As IDbCommandFactory)
Errors.GuardDoubleInitialization this.CommandFactory
Errors.GuardNullReference value
Set this.CommandFactory = value
End Property
Private Sub Class_Terminate()
On Error Resume Next
If Not this.Committed Then this.Connection.RollbackTransaction
On Error GoTo 0
End Sub
Private Sub IUnitOfWork_Commit()
Errors.GuardExpression this.Committed, message:="Transaction is already committed."
Errors.GuardExpression this.RolledBack, message:="Transaction was rolled back."
On Error Resume Next ' not all providers support transactions
this.Connection.CommitTransaction
this.Committed = True
On Error GoTo 0
End Sub
Private Function IUnitOfWork_Command() As IDbCommand
Set IUnitOfWork_Command = this.CommandFactory.Create(this.Connection)
End Function
Private Sub IUnitOfWork_Rollback()
Errors.GuardExpression this.Committed, message:="Transaction is already committed."
On Error Resume Next ' not all providers support transactions
this.Connection.RollbackTransaction
this.RolledBack = True
On Error GoTo 0
End Sub
Errors
If you paid close attention to the code listings so far, you likely already noticed the many Errors.GuardXxxxx member calls scattered throughout the code. There are probably as many ways to deal with custom errors as there are VBA classes out there, this is one way. Probably not the best way, but it feels “just right” for me in this case and I think I like it enough to keep using it until the problems it creates become clearer (there’s always something). Errors is a standard private module in the project, that defines custom error codes. Okay I was lazy and deemed SecureADODBCustomError all I needed, but it could also have been an Enum with descriptive names for each custom error code.
The module simply exposes a small number of very simple Sub procedures that make it easy for the rest of the code to raise meaningful custom errors:
'@Folder("SecureADODB")
'@ModuleDescription("Global procedures for throwing common errors.")
Option Explicit
Option Private Module
Public Const SecureADODBCustomError As Long = vbObjectError Or 32
'@Description("Re-raises the current error, if there is one.")
Public Sub RethrowOnError()
With VBA.Information.Err
If .Number <> 0 Then
Debug.Print "Error " & .Number, .Description
.Raise .Number
End If
End With
End Sub
'@Description("Raises a run-time error if the specified Boolean expression is True.")
Public Sub GuardExpression(ByVal throw As Boolean, _
Optional ByVal Source As String = "SecureADODB.Errors", _
Optional ByVal message As String = "Invalid procedure call or argument.")
If throw Then VBA.Information.Err.Raise SecureADODBCustomError, Source, message
End Sub
'@Description("Raises a run-time error if the specified instance isn't the default instance.")
Public Sub GuardNonDefaultInstance(ByVal instance As Object, ByVal defaultInstance As Object, _
Optional ByVal Source As String = "SecureADODB.Errors", _
Optional ByVal message As String = "Method should be invoked from the default/predeclared instance of this class.")
Debug.Assert TypeName(instance) = TypeName(defaultInstance)
GuardExpression Not instance Is defaultInstance, Source, message
End Sub
'@Description("Raises a run-time error if the specified object reference is already set.")
Public Sub GuardDoubleInitialization(ByVal instance As Object, _
Optional ByVal Source As String = "SecureADODB.Errors", _
Optional ByVal message As String = "Object is already initialized.")
GuardExpression Not instance Is Nothing, Source, message
End Sub
'@Description("Raises a run-time error if the specified object reference is Nothing.")
Public Sub GuardNullReference(ByVal instance As Object, _
Optional ByVal Source As String = "SecureADODB.Errors", _
Optional ByVal message As String = "Object reference cannot be Nothing.")
GuardExpression instance Is Nothing, Source, message
End Sub
'@Description("Raises a run-time error if the specified string is empty.")
Public Sub GuardEmptyString(ByVal value As String, _
Optional ByVal Source As String = "SecureADODB.Errors", _
Optional ByVal message As String = "String cannot be empty.")
GuardExpression value = vbNullString, Source, message
End Sub
Most of these procedures are invoked as the first executable statement in a given scope, to raise an error given invalid parameters or internal state, such as these:
Private Sub IUnitOfWork_Commit()
Errors.GuardExpression this.Committed, message:="Transaction is already committed."
Errors.GuardExpression this.RolledBack, message:="Transaction was rolled back."
On Error Resume Next ' not all providers support transactions
this.Connection.CommitTransaction
this.Committed = True
On Error GoTo 0
End Sub
Consistently raising such errors is the single best way to ensure our objects are always in a known and usable state, because we outright forbid them to be invalid. These validation clauses are called guard clauses, hence the GuardXxxxx procedure names.
A lot of the unit tests simply verify that, given the specified conditions, the expected error is raised:
'@TestMethod("Factory Guard")
Private Sub Create_ThrowsIfNotInvokedFromDefaultInstance()
On Error GoTo TestFail
With New AutoDbCommand
On Error GoTo CleanFail
Dim sut As IDbCommand
Set sut = .Create("connection string", New StubDbConnectionFactory, New StubDbCommandBase)
On Error GoTo 0
End With
CleanFail:
If Err.Number = ExpectedError Then Exit Sub
TestFail:
Assert.Fail "Expected error was not raised."
End Sub
If each guard clause has a unit test, then the tests are effectively documenting how the objects are meant to be used. With more specific custom errors, the tests would be more accurate, but there’s a point where you need to look at what you’ve got and say “I think I can work with that”, and move on.
Audience
Obviously, one doesn’t import 20 classes into their VBA project just to send one ADODB command to a database server. However if you’re maintaining a VB6 application that uses ADODB all over the place, leaks connections, leaves recordsets dangling, …then importing this API can really help tighten up the data access code in that legacy app. Or maybe you’re writing a complex data-driven system in VBA for Excel because that’s all you’ve got, and a UnitOfWork abstraction makes sense for you.
The goal here is mostly to 1) demonstrate proper usage of ADODB.Command for secure, parameterized queries, and 2) demonstrate that Classic VB (VB6/VBA) has always had everything everyone ever needed to write full-blown object-oriented code that leverages abstraction, encapsulation, and polymorphism – making it possible to write clean and fully unit-tested code.
…and of course, it makes a great practical application of the OOP concepts discussed in many other articles on this blog. Studying the code in this project gives you insight on…
Rubberduck News 