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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
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!