Are there disadvantages in putting code into a VBA Userform instead of into a "normal" module?
This might be a simple question but I have not found a conclusive answer to it while searching the web and stackoverflow.
Background: I am developing a Front-End Application of a database in Excel-VBA. To select different filters I have different userforms. I ask what general program design is better: (1) putting the control structure into a separate module OR (2) putting the code for the next userform or action in the userform.
Lets make an example. I have a Active-X Button which triggers my filters and my forms.
Variant1: Modules
In the CommandButton:
Private Sub CommandButton1_Click() call UserInterfaceControlModule End Sub
In the Module:
Sub UserInterfaceControllModule() Dim decisionInput1 As Boolean Dim decisionInput2 As Boolean UserForm1.Show decisionInput1 = UserForm1.decision If decisionInput1 Then UserForm2.Show Else UserForm3.Show End If End Sub
In Variant 1 the control structure is in a normal module. And decisions about which userform to show next are separated from the userform. Any information needed to decide about which userform to show next has to be pulled from the userform.
Variant2: Userform
In the CommadButton:
Private Sub CommandButton1_Click() UserForm1.Show End Sub
In Userform1:
Private Sub ToUserform2_Click() UserForm2.Show UserForm1.Hide End Sub Private Sub UserForm_Click() UserForm2.Show UserForm1.Hide End Sub
In Variant 2 the control structure is directly in the userforms and each userform has the instructions about what comes after it.
I have started development using method 2. If this was a mistake and there are some serious drawbacks to this method I want to know it rather sooner than later.
A UserForm object is a window or dialog box that makes up part of an application's user interface. The UserForms collection is a collection whose elements represent each loaded UserForm in an application. The UserForms collection has a Count property, an Item method, and an Add method.
Sure, you can duplicate an Excel UserForm manually: Rename the UserForm to the desired new UserForm name. Export the UserForm. Rename the UserForm back to its original name.
Disclaimer I wrote the article Victor K linked to. I own that blog, and manage the open-source VBIDE add-in project it's for.
Neither of your alternatives are ideal. Back to basics.
To select different filters I have differnt (sic) userforms.
Your specifications demand that the user needs to be able to select different filters, and you chose to implement a UI for it using a UserForm
. So far, so good... and it's all downhill from there.
Making the form responsible for anything other than presentation concerns is a common mistake, and it has a name: it's the Smart UI [anti-]pattern, and the problem with it is that it doesn't scale. It's great for prototyping (i.e. make a quick thing that "works" - note the scare quotes), not so much for anything that needs to be maintained over years.
You've probably seen these forms, with 160 controls, 217 event handlers, and 3 private procedures closing in on 2000 lines of code each: that's how badly Smart UI scales, and it's the only possible outcome down that road.
You see, a UserForm
is a class module: it defines the blueprint of an object. Objects usually want to be instantiated, but then someone had the genius idea of granting all instances of MSForms.UserForm
a predeclared ID, which in COM terms means you basically get a global object for free.
Great! No? No.
UserForm1.Show decisionInput1 = UserForm1.decision If decisionInput1 Then UserForm2.Show Else UserForm3.Show End If
What happens if UserForm1
is "X'd-out"? Or if UserForm1
is Unload
ed? If the form isn't handling its QueryClose
event, the object is destroyed - but because that's the default instance, VBA automatically/silently creates a new one for you, just before your code reads UserForm1.decision
- as a result you get whatever the initial global state is for UserForm1.decision
.
If it wasn't a default instance, and QueryClose
wasn't handled, then accessing the .decision
member of a destroyed object would give you the classic run-time error 91 for accessing a null object reference.
UserForm2.Show
and UserForm3.Show
both do the same thing: fire-and-forget - whatever happens happens, and to find out exactly what that consists of, you need to dig it up in the forms' respective code-behind.
In other words, the forms are running the show. They're responsible for collecting the data, presenting that data, collecting user input, and doing whatever work needs to be done with it. That's why it's called "Smart UI": the UI knows everything.
There's a better way. MSForms is the COM ancestor of .NET's WinForms UI framework, and what the ancestor has in common with its .NET successor, is that it works particularly well with the famous Model-View-Presenter (MVP) pattern.
That's your data. Essentially, it's what your application logic need to know out of the form.
UserForm1.decision
let's go with that.Add a new class, call it, say, FilterModel
. Should be a very simple class:
Option Explicit Private Type TModel SelectedFilter As String End Type Private this As TModel Public Property Get SelectedFilter() As String SelectedFilter = this.SelectedFilter End Property Public Property Let SelectedFilter(ByVal value As String) this.SelectedFilter = value End Property Public Function IsValid() As Boolean IsValid = this.SelectedFilter <> vbNullString End Function
That's really all we need: a class to encapsulate the form's data. The class can be responsible for some validation logic, or whatever - but it doesn't collect the data, it doesn't present it to the user, and it doesn't consume it either. It is the data.
Here there's only 1 property, but you could have many more: think one field on the form => one property.
The model is also what the form needs to know from the application logic. For example if the form needs a drop-down that displays a number of possible selections, the model would be the object exposing them.
That's your form. It's responsible for knowing about controls, writing to and reading from the model, and... that's all. We're looking at a dialog here: we bring it up, user fills it up, closes it, and the program acts upon it - the form itself doesn't do anything with the data it collects. The model might validate it, the form might decide to disable its Ok button until the model says its data is valid and good to go, but under no circumstances a UserForm
reads or writes from a worksheet, a database, a file, a URL, or anything.
The form's code-behind is dead simple: it wires up the UI with the model instance, and enables/disables its buttons as needed.
The important things to remember:
Hide
, don't Unload
: the view is an object, and objects don't self-destruct.QueryClose
, again, to avoid a self-destructing object ("X-ing out" of the form would otherwise destroy the instance).In this case the code-behind might look like this:
Option Explicit Private Type TView Model As FilterModel IsCancelled As Boolean End Type Private this As TView Public Property Get Model() As FilterModel Set Model = this.Model End Property Public Property Set Model(ByVal value As FilterModel) Set this.Model = value Validate End Property Public Property Get IsCancelled() As Boolean IsCancelled = this.IsCancelled End Property Private Sub TextBox1_Change() this.Model.SelectedFilter = TextBox1.Text Validate End Sub Private Sub OkButton_Click() Me.Hide End Sub Private Sub Validate() OkButton.Enabled = this.Model.IsValid End Sub Private Sub CancelButton_Click() OnCancel End Sub Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) If CloseMode = VbQueryClose.vbFormControlMenu Then Cancel = True OnCancel End If End Sub Private Sub OnCancel() this.IsCancelled = True Me.Hide End Sub
That's literally all the form does. It isn't responsible for knowing where the data comes from or what to do with it.
That's the "glue" object that connects the dots.
Option Explicit Public Sub DoSomething() Dim m As FilterModel Set m = New FilterModel With New FilterForm Set .Model = m 'set the model .Show 'display the dialog If Not .IsCancelled Then 'how was it closed? 'consume the data Debug.Print m.SelectedFilter End If End With End Sub
If the data in the model needed to come from a database, or some worksheet, it uses a class instance (yes, another object!) that's responsible for doing just that.
The calling code could be your ActiveX button's click handler, New
-ing up the presenter and calling its DoSomething
method.
This isn't everything there is to know about OOP in VBA (I didn't even mention interfaces, polymorphism, test stubs and unit testing), but if you want objectively scalable code, you'll want to go down the MVP rabbit hole and explore the possibilities truly object-oriented code bring to VBA.
Code ("business logic") simply doesn't belong in forms' code-behind, in any code base that means to scale and be maintained across several years.
In "variant 1" the code is hard to follow because you're jumping between modules and the presentation concerns are mixed with the application logic: it's not the form's job to know what other form to show given button A or button B was pressed. Instead it should let the presenter know what the user means to do, and act accordingly.
In "variant 2" the code is hard to follow because everything is hidden in userforms' code-behind: we don't know what the application logic is unless we dig into that code, which now purposely mixes presentation and business logic concerns. That is exactly what the "Smart UI" anti-pattern does.
In other words variant 1 is slightly better than variant 2, because at least the logic isn't in the code-behind, but it's still a "Smart UI" because it's running the show instead of telling its caller what's happening.
In both cases, coding against the forms' default instances is harmful, because it puts state in global scope (anyone can access the default instances and do anything to its state, from anywhere in the code).
Treat forms like the objects they are: instantiate them!
In both cases, because the form's code is tightly coupled with the application logic and intertwined with presentation concerns, it's completely impossible to write a single unit test that covers even one single aspect of what's going on. With the MVP pattern, you can completely decouple the components, abstract them behind interfaces, isolate responsibilities, and write dozens of automated unit tests that cover every single piece of functionality and document exactly what the specifications are - without writing a single bit of documentation: the code becomes its own documentation.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With