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.
The accompanying code for this article is in the Rubberduck Examples repository.
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
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.
IHandlePropertyChanged interface is to be implemented by property binding classes, such as this
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.
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
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.
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:
Private Sub ConfigureBindings(ByVal Model As INotifyPropertyChanged) Const EnabledProperty As String = "Enabled" Const ListProperty As String = "List" This.Bindings.Add BindTextBox(Me.BillToNameBox, "BillToName", This.OrderModel) This.Bindings.Add BindTextBox(Me.BillToAddressLine1, "BillToLine1", This.OrderModel) This.Bindings.Add BindTextBox(Me.BillToAddressLine2, "BillToLine2", This.OrderModel) This.Bindings.Add BindTextBox(Me.BillToAddressLine3, "BillToLine3", This.OrderModel) This.Bindings.Add BindTextBox(Me.BillToEmailBox, "EmailAddress", This.OrderModel) This.Bindings.Add BindCheckBox(Me.BillToContributorBox, "IsContributor", This.OrderModel) This.Bindings.Add BindCheckBox(Me.ShipToSameBox, "ShipToBillingAddress", This.OrderModel) This.Bindings.Add BindTextBox(Me.ShipToNameBox, "ShipToName", This.OrderModel) This.Bindings.Add BindTextBox(Me.ShipToAddressLine1, "ShipToLine1", This.OrderModel) This.Bindings.Add BindTextBox(Me.ShipToAddressLine2, "ShipToLine2", This.OrderModel) This.Bindings.Add BindTextBox(Me.ShipToAddressLine3, "ShipToLine3", This.OrderModel) This.Bindings.Add BindProperty(Me.ShipToAddressLabel, EnabledProperty, "ShipToBillingAddress", This.OrderModel, InvertBoolean:=True) This.Bindings.Add BindProperty(Me.ShipToNameLabel, EnabledProperty, "ShipToBillingAddress", This.OrderModel, InvertBoolean:=True) This.Bindings.Add BindProperty(Me.ShipToNameBox, EnabledProperty, "ShipToBillingAddress", This.OrderModel, InvertBoolean:=True) This.Bindings.Add BindProperty(Me.ShipToAddressLine1, EnabledProperty, "ShipToBillingAddress", This.OrderModel, InvertBoolean:=True) This.Bindings.Add BindProperty(Me.ShipToAddressLine2, EnabledProperty, "ShipToBillingAddress", This.OrderModel, InvertBoolean:=True) This.Bindings.Add BindProperty(Me.ShipToAddressLine3, EnabledProperty, "ShipToBillingAddress", This.OrderModel, InvertBoolean:=True) This.Bindings.Add BindProperty(Me.ItemSkuSelectBox, ListProperty, "Value", InventorySheet.Table.ListColumns("SKU").DataBodyRange) This.Bindings.Add BindComboBox(Me.ItemSkuSelectBox, "SKU", This.OrderModel.NewLineItem) This.Bindings.Add BindTextBox(Me.ItemQuantityBox, "Quantity", This.OrderModel.NewLineItem) This.Bindings.Add BindTextBox(Me.ItemPriceBox, "Price", This.OrderModel.NewLineItem) This.Bindings.Add BindProperty(Me.LineItemsList, ListProperty, "LineItems", This.OrderModel) End Sub
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
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.
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’
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.
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.