VBA User Forms
Best Practices
A UserForm is a class module with a designer and a default instance. The designer can be accessed by pressing Shift + F7 while viewing the code-behind, and the code-behind can be accessed by pressing F7 while viewing the designer.
Work with a new instance every time.
Being a class module, a form is therefore a blueprint for an object. Because a form can hold state and data, it's a better practice to work with a new instance of the class, rather than with the default/global one:
With New UserForm1
.Show vbModal
If Not .IsCancelled Then
'...
End If
End WithInstead of:
UserForm1.Show vbModal
If Not UserForm1.IsCancelled Then
'...
End IfWorking with the default instance can lead to subtle bugs when the form is closed with the red "X" button and/or when Unload Me is used in the code-behind.
Implement the logic elsewhere.
A form should be concerned with nothing but presentation: a button Click handler that connects to a database and runs a parameterized query based on user input, is doing too many things.
Instead, implement the applicative logic in the code that's responsible for displaying the form, or even better, in dedicated modules and procedures.
Write the code in such a way that the UserForm is only ever responsible for knowing how to display and collect data: where the data comes from, or what happens with the data afterwards, is none of its concern.