Month: April 2023

Lightweight MVVM in VBA

Rubberduck VBA6 Comments

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.

Property Bindings

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:

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

Rubberduck Swag Shop is Open!

Featured Rubberduck VBA7 Comments

Back in the summer of 2017, we ran a rather successful GoFundMe campaign to collect funds to get some swag made, and shipped to the project’s contributors. It was great to see all that support pouring in, but it was bittersweet… for I would have loved to have been able to materially thank back our supporters. Alas, making custom printed gear quickly gets expensive, and crazy shipping fees essentially doubled it up.

So this time around I decided to bite the bullet and make a more significant initial investment, and actually start an online shop and keep an inventory in stock that can be shipped to supporters whenever, without needing to wait for prints, or paying too much for too few items being made, severely limiting profitability and driving prices up.

I guess I’ll know soon enough if I still have my old merchandise planner’s touch! If the inventory turns too fast, I’ll miss opportunities and may have to delay shipments to replenish. But if the markup is too high and stock isn’t moving quickly enough, I’ll probably need to take markdowns.

I’ll be writing an article about the particular workbook I’m using to track all that next – I want a bit of VBA code to simplify tracking sales and invoicing.. but for now I’m just thrilled to start shipping all the goodies!

I’m very happy with how everything turned out. That beautiful yellow ducky mug will probably only ever be printed in small batches, for they are quite expensive and I’m unfortunately not going to be able to have a better price for them unless I make them in very large batches. Everything else (even t-shirts, to a degree) has the potential to eventually get to a very decent price point – the goal is obviously to collect some funds to help finance the costs of hosting and securing the blog and website, but I want this inventory to move and don’t mind taking a lower margin if there’s enough volume to drive enough sales to just keep the shop afloat – time will tell!

Rubberduck Swag Shop on Ko-fi