I’ve seen a few posts lately on UserForms and I’ve come across some examples at work that are not ideal in their construct. As a result I put together this FAQ on best practices for VBA userforms.
This is very much a first attempt and I would appreciate all your feedback.
The most common issues I see with userforms are
1. Auto-instantiation
2. Encapsulation
3. Business Rules
To illustrate the concepts I’m discussing here I have included a demonstration file. This file includes a form from a Quote system I recently developed. The forms purpose is to allow the user to Goal Seek the final customer price by either rounding up or down to the nearest multiple of a round number supplied by the user or by specifying an exact price. It uses all the concepts discussed below.
Auto-instantiation
When we create a form, VBA automatically creates a default instance of the userform with the same name as the form’s class name.
Using this default instance is quick and easy and we all do it, but it can lead to bugs.
The biggest issue with the default instance is that when we call the unload method and subsequently reference the form, the new reference creates a new instance, as the unload method destroyed the previous instance. The values of any controls that were changed by the user will not be reflected in this new instance.
This problem can be overcome by remembering that a form is just a specialized version of a class module and can be treated in the same way as a class.
Using this approach I always do the following when presenting a form to a user.
1. Declare an object variable As MyForm.
2. Set the object variable to a NEW instance of MyForm.
3. Within MyForm’s code never call the Unload method, always call the Hide method instead. This includes trapping for the user clicking on the ‘x’ in the corner or hitting Alt+F4.
4. Once the user has completed the form and ‘closed’ it, i.e. called the Hide method, I pass any values I need from the form to variables in my calling procedure.
5. Unload the instance of MyForm.
Here is a code snippet from the attached where I follow this approach.
VB Code:
Dim oPriceFrom As FAdjustPrice
Dim uRoundType As dpsRoundType
Dim bExitType As Boolean
Dim lPrecision As Long
'Instantiate a copy of the Adjust Price Form.
Set oPriceFrom = New FAdjustPrice
'Show the form and get all its properties.
'The key point here is that we never
'reference a control on the form directly.
oPriceFrom.Show
uRoundType = oPriceFrom.RoundType
bExitType = oPriceFrom.OK
lPrecision = oPriceFrom.Precision
'We can now get rid of the form.
Unload oPriceFrom
Encapsulation
Too often I’ve seen the following in a procedure.
VB Code:
If myform.SomeControl.Value = …
The problem with this is that the programmer has exposed a particular control outside of the forms module. This means that if the system changes in the future and SomeControl is replaced by SomeOtherControl then the programmer is going to have to find every instance that SomeControl was exposed outside of the forms module.
Again, if we think of a form as, and treat it like, a class we would never expose its controls directly outside of forms module. Instead we would use Properties and Methods to interact with the form. By only ever referring to the properties and methods we are then free to change any controls in the future and feel safe in the knowledge that any code calling the form will continue to work.
In the attached example there are 4 OptionButtons that allow the user to specify a round number to which the Price should be adjusted. Rather than referencing these options buttons from my procedure, I use them to change the value of a Property, called Precision, within the Form. If the business decides to change the number of options to say 10, then I can easily replace my 4 OptionButtons with a single ComboBox and use it to set the value of the Precision Property. As my calling code has no knowledge of the control on the form, this change can be managed entirely with the forms module.
This approach can be taken a step further by using an interface to connect to the form. This is useful when one developer is building the form and others are writing the calling procedures. I will deal with interfaces in a subsequent post.
Business Rules
The purpose of a form is to either present information to, or get information from, the user. That’s it, nothing more, nothing less. There should never be any business rules or processes included in a forms module.
If you have a cmdOK_Click event that does more that perform some validations on the data the user has entered and then hides the form – it’s doing too much.
If you have properly encapsulated the form as described above and have included all required properties, then you should be able to implement all business rules in the calling procedure.
The reason we don’t want to include business rules in the form itself is that in the future we may want to remove the form and receive the user input through a different method. Having the business rules maintained outside the form, and just using the form to gather the inputs, allows us to do this.
In the example procedure the form is called and once closed by the user, its properties are passed back to the calling procedure, which then handles the business logic.
That’s all I’ve got for now. Again, I’d appreciate any feedback
