VBA – The perfect userform in VBA

Somewhere in the summer of 2012 I have started coding in VBA. A little less than 5 years have passed since that very moment when I have pressed Alt+F11 for a first time and I have entered another Excel world. Since then a lot has happened and I have become a much better developer. But still, there are things that I don’t know. If they are worthy, I try to master them and put them into my routine.

Long story short – I was browsing the Documentation section for VBA in stackoverflow and suddenly I have noticed that the idea of building a userform of the author of the section was completely different from mine. He has written as a best practise that one should work with a new instance every time. Definitely the opposite of the last 5 VBA books I have read, where you are thought to use UserForm.Show and to live easily and happily ever after. After reading the example, it really made sense to me. E.g. if the form is a blueprint, one should really not abuse it with UserForm.Show. Then one can follow the famous MVC pattern and anything can be like a standard for a developer outside the VBA world. Anyhow, I somehow realized that the userforms should be threatened somehow better than in all VBA books and thus, I have decided to write the code of the perfect userform in VBA.

Pretty much we have a form, a module and a class. The form is the blueprint, the module is the business logic and the class is what implements the business logic into the form, thus they do not know about each other.

Pretty much, here is how the form looks like:

It has only the following controls:

btnRun
btnExit
lblInfo
frmMain (the class)

The code of the form itself is the following:

Option Explicit

Public Event OnRunReport()
Public Event OnExit()

Public Property Get InformationText() As String

    InformationText = lblInfo.Caption

End Property

Public Property Let InformationText(ByVal value As String)

    lblInfo.Caption = value

End Property

Public Property Get InformationCaption() As String

    InformationCaption = Caption

End Property

Public Property Let InformationCaption(ByVal value As String)

    Caption = value

End Property

Private Sub btnRun_Click()

    RaiseEvent OnRunReport

End Sub

Private Sub btnExit_Click()

    RaiseEvent OnExit

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

    If CloseMode = vbFormControlMenu Then
        Cancel = True
        Hide
    End If

End Sub

The form is with two events, getting caught by the clsSummaryPresenter. The clsSummaryPresenter looks like this:

Option Explicit

Private WithEvents objSummaryForm As frmMain

Private Sub Class_Initialize()
    
    Set objSummaryForm = New frmMain

End Sub

Private Sub Class_Terminate()
    
    Set objSummaryForm = Nothing
    
End Sub

Public Sub Show()

    If Not objSummaryForm.Visible Then
        objSummaryForm.Show vbModeless
        Call ChangeLabelAndCaption("Press Run to Start", "Starting")
    End If

    With objSummaryForm
        .Top = CLng((Application.Height / 2 + Application.Top) - .Height / 2)
        .Left = CLng((Application.Width / 2 + Application.Left) - .Width / 2)
    End With

End Sub

Private Sub Hide()

    If objSummaryForm.Visible Then objSummaryForm.Hide

End Sub

Public Sub ChangeLabelAndCaption(strLabelInfo As String, strCaption As String)

    objSummaryForm.InformationText = strLabelInfo
    objSummaryForm.InformationCaption = strCaption
    objSummaryForm.Repaint

End Sub

Private Sub objSummaryForm_OnRunReport()

    MainGenerateReport
    Refresh

End Sub

Private Sub objSummaryForm_OnExit()

    Hide

End Sub

Public Sub Refresh()
    
    With objSummaryForm
        .lblInfo = "Ready"
        .Caption = "Task performed"
    End With

End Sub

Finally, we have the modMain, which is the so-called business logic of the form:

Option Explicit

Private objPresenter   As clsSummaryPresenter

Public Sub MainGenerateReport()

    objPresenter.ChangeLabelAndCaption "Starting and running...", "Running..."
    GenerateNumbers

End Sub

Public Sub GenerateNumbers()

    Dim lngLong         As Long
    Dim lngLong2        As Long

    tblMain.Cells.Clear

    For lngLong = 1 To 10
        For lngLong2 = 1 To 10
            tblMain.Cells(lngLong, lngLong2) = lngLong * lngLong2
        Next lngLong2
    Next lngLong

End Sub

Public Sub ShowMainForm()

    If (objPresenter Is Nothing) Then
        Set objPresenter = New clsSummaryPresenter
    End If

    objPresenter.Show

End Sub

And the whole party works flawlessly!

Enjoy it! 🙂

Special thanks to Mat’s Mug for writing the form documentation in StackOverflow!

VBA – The perfect userform in VBA

Related posts: