Month: May 2018

A recent comment on UserForm1.Show asked about how to extend that logic to a dialog that would have an “Apply” button. This article walks you through the process – and this time, there’s a download link!

The dialog is a simple UserForm with two textboxes and 3 buttons:

The Model for this dialog is a simple class exposing properties that the two textboxes manipulate – I’ve named the class ExampleModel:

Option Explicit

Private Type TModel
    field1 As String
    field2 As String
End Type

Private this As TModel

Public Property Get field1() As String
    field1 = this.field1
End Property

Public Property Let field1(ByVal value As String)
    this.field1 = value
End Property

Public Property Get field2() As String
    field2 = this.field2
End Property

Public Property Let field2(ByVal value As String)
    this.field2 = value
End Property

I also defined a simple IDialogView interface, which can be implemented by any other dialog, since it passes the model as an Object (i.e. it’s not tightly coupled with the ExampleModel class in any way); the contract is simply “here’s your model, now show me a dialog and tell me if I can proceed to consume the model” – in other words, the caller provides an instance of the model, and the implementation returns True unless the user cancelled the form.

Option Explicit

Public Function ShowDialog(ByVal viewModel As Object) As Boolean
End Function

The form’s code-behind therefore needs to implement the IDialogView interface, and somehow store a reference to the ExampleModel. And since we have cancellation logic but we’re not exposing it (we don’t need to – the IDialogView.ShowDialog interface handles that concern, by returning False if the dialog is cancelled), the IsCancelled flag is just internal state.

As far as the “apply” logic is concerned, the thing to note here is the Public Event ApplyChanges event, which we raise when the user clicks the “apply” button:

Option Explicit

Public Event ApplyChanges(ByVal viewModel As ExampleModel)

Private Type TView
    IsCancelled As Boolean
    Model As ExampleModel
End Type
Private this As TView

Implements IDialogView

Private Sub AcceptButton_Click()
    Me.Hide
End Sub

Private Sub ApplyButton_Click()
    RaiseEvent ApplyChanges(this.Model)
End Sub

Private Sub CancelButton_Click()
    OnCancel
End Sub

Private Sub Field1Box_Change()
    this.Model.field1 = Field1Box.value
End Sub

Private Sub Field2Box_Change()
    this.Model.field2 = Field2Box.value
End Sub

Private Sub OnCancel()
    this.IsCancelled = True
    Me.Hide
End Sub

Private Function IDialogView_ShowDialog(ByVal viewModel As Object) As Boolean
    Set this.Model = viewModel
    Me.Show vbModal
    IDialogView_ShowDialog = Not this.IsCancelled
End Function

Private Sub UserForm_Activate()
    Field1Box.value = this.Model.field1
    Field2Box.value = this.Model.field2
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = VbQueryClose.vbFormControlMenu Then
        Cancel = True
        OnCancel
    End If
End Sub

The Presenter class does all the fun stuff. Here I’ve decided to allow the model’s data to be optionally supplied as parameters to the Show method; the form handles its Activate event to make sure the form controls reflect the model’s initial values when the form is displayed:

Option Explicit
Private WithEvents view As ExampleDialog

Private Property Get Dialog() As IDialogView
    Set Dialog = view
End Property

Public Sub Show(Optional ByVal field1 As String, Optional ByVal field2 As String)

    Set view = New ExampleDialog

    Dim viewModel As ExampleModel
    Set viewModel = New ExampleModel
    viewModel.field1 = field1
    viewModel.field2 = field2

    If Dialog.ShowDialog(viewModel) Then ApplyChanges viewModel
    Set view = Nothing

End Sub

Private Sub view_ApplyChanges(ByVal viewModel As ExampleModel)
    ApplyChanges viewModel
End Sub

Private Sub ApplyChanges(ByVal viewModel As ExampleModel)
    Sheet1.Range("A1").value = viewModel.field1
    Sheet1.Range("A2").value = viewModel.field2
End Sub

So we have a Private WithEvents field that gets assigned in the Show method, and we handle the form’s ApplyChanges event by invoking the ApplyChanges logic, which, for the sake of this example, takes the two fields and writes them to A1 and A2 on Sheet1; if you’ve read There is no worksheet then you know how you can introduce an interface there to decouple the worksheet from the presenter, and then it doesn’t matter if you’re writing to a worksheet, a text file, or a database: the presenter doesn’t need to know all the details.

The calling code in Module1 might look like this:

Option Explicit

Public Sub ExampleMacro()
    With New ExamplePresenter
        .Show "test"
    End With
End Sub

One problem here, is that the View implementation is coupled with the presenter (i.e. the presenter is creating the view): we need the concrete UserForm type in order for VBA to see the events; without further abstraction, we can’t quite pass a IDialogView implementation to the presenter logic without popping up the actual dialog. Pieter Geerkens has a nice answer on Stack Overflow that describes how an Adapter Pattern can be used to solve this problem by introducing more interfaces, but covering this design pattern will be the subject of another article.