Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA® نسخه متنی

Principles

When we design and code userforms, we strive to adhere to a small set of basic principles whenever possible, explained below. We have found that following these principles has resulted in userforms that are easy to use, easy to code and easy to maintain. Although some of these principles may seem a little artificial at first, experience has shown that sticking to them gives long-term rewards.

Keep It Simple

A userform should not need a help file to explain how to fill it in. When presented with a userform, our users should intuitively be able to use it. In practice, this means having a relatively small number of controls on a userform that are well positioned, clearly labeled and in appropriate groupings and orders to match the task for which the userform will be used. When designing a userform to help with a complex task, a wizard style should be used, breaking the userform down into multiple steps, each of which adheres to the Keep It Simple, Stupid (KISS) principle. There are, of course, situations that require complex userforms. In these cases, extra effort should be invested to make the userform as simple to use as possible. Making a complex userform as easy as possible for the user usually requires the most effort on the part of the programmer and often results in quite complex code!

Display Canvas, Not Business Rules

A userform is a user interface element, not a place to implement business logic. The user interaction should always be separated from the business response to that interaction, at least logically if not physically. In practice, this means that the only code that should be included in controls' event procedures is either (a) changing another control's properties, or (b) calling functions in the business logic layer. Conversely, the code in the business logic layer should never refer directly to controls on the userform and ideally should not assume that a specific display mechanism is being used (such as a set of option buttons vs. a list box).

So what is business logic in this context? Figure 10-1 shows a simple userform with a combo box to select a region and a two-column list to show sales by product.

Figure 10-1. A Simple Userform

If the code in the combobox_change event procedure identifies the region, retrieves the products for the region, retrieves the total sales for each product and adds them to the list box, it's implementing business logic and is doing too much.

When the user selects a new region, there are two things we need to specify: (1) the appropriate response, and (2) the data required to satisfy that response. In our example, the appropriate response is to populate the list of products and the data required to satisfy the response is the list of products and the total sales for each.

At a minimum, the data required to satisfy the response should be obtained from the business logic layer. In this case, we would have a function in the business logic layer that takes a region as a parameter and returns an array of products and total sales. It does this by retrieving the underlying data from the data access layer and populating the array. Code in the combobox_change event would read the selected region from the combo box, call the business logic layer function to get the array of products and sales and write that array to the list box. Listing 10-1 shows an example of this mechanism.

Listing 10-1. The User Interface Layer Determines the Response

'***********************************
'* User Interface Layer, FSimpleForm
'***********************************
'Handle selecting a different region
Private Sub cboRegion_Change()
Dim vaProductSales As Variant
'Get the Product/Sales array for the selected region
'from the business logic layer
vaProductSales = GetProductSalesForRegion(cboRegion.Value)
'Populate the list box
lstProducts.List = vaProductSales
End Sub

At the extreme, we introduce a new user interface support (UIS) layer that contains the code to determine the appropriate response for each user action. The event procedure would then contain a single line that calls a procedure in the UIS layer, passing the selected region. The UIS layer calls the function in the business logic layer to retrieve the array of products and total sales, then tells the userform to populate the list with the array. This mechanism treats the userform as nothing more than a drawing and interaction layer and is an extremely useful way to handle complex forms. An example of this technique can be found in the UISLayer.xls workbook on the CD. In Chapter 7Using Class Modules to Create Objects.

Listing 10-2. The User Interface Support Layer Determines the Response

'*************************************
'* User Interface Layer in
'* userform FComplexForm
'*************************************
'UIS Event handler
Dim WithEvents mclsUISComplexForm As CUISComplexForm
'Initialize our UIS class
Private Sub UserForm_Initialize()
Set mclsUISComplexForm = New CUISComplexForm
End Sub
'
' Control events, to handle the user telling us
' to do something. In most cases, we just pass it
' on to the UIS class.
'
'Handle selecting a different region
Private Sub cboRegion_Change()
'Tell the UIS layer that the user
'just selected a different region
mclsUISComplexForm.RegionSelected cboRegion.Value
End Sub
'
' UIS class events, to handle the UIS layer
' telling us to do something
'
'Populate the Product Sales List
Private Sub mclsUISComplex_PopulateProductList( _
vaProductSales As Variant)
lstProducts.List = vaProductSales
End Sub
'************************************************
'* User Interface Support Layer
'* in class CUISComplexForm
'************************************************
'Events to tell the userform what to do
Public Event PopulateProductList(vaProductSales As Variant)
'The user selected a different region.
Public Sub RegionSelected(ByVal sRegion As String)
Dim vaProductSales As Variant
'Get the Product/Sales array from the business logic layer
vaProductSales = GetProductSalesForRegion(sRegion)
'Tell the userform to populate the products list
RaiseEvent PopulateProductList(vaProductSales)
End Sub

There is obviously more overhead in using an intermediate UIS layer, but it reduces most of the userform event procedures to one-line calls into the UIS layer, enabling us to concentrate on the detail of the user experience within the userform module. This makes the userform itself much easier to maintain. Notice also that the UIS class has no knowledge of how the information is obtained or displayedall it knows is that when it's given a region, it should tell the userform that there's a new list of products. The UIS class could be used by multiple versions of the same userformperhaps with each one optimized for different screen resolutions.

Use Classes, Not the Default Instance

Whenever we add a userform to a project, we automatically get a default instance of the userform created for us. This is effectively a global variable that has the same name as the userform's class name, declared As New. This means that as soon as we refer to the userform, Excel creates the default instance for us (this is known as auto-instantiation). When we unload the userform, the default instance is destroyed and when we refer to it again, the default instance is re-created. Consider a userform, FMyForm, containing a single text box, txtName. The code in Listing 10-3 shows the userform and displays the name.

Listing 10-3. Using the Userform's Default Instance

Sub TestDefaultInstance()
'Show the userform
FMyForm.Show
'Show the contents of the text box
MsgBox "The name is: " & FMyForm.txtName.Text
End Sub

Run the procedure, type a name into the text box and close the userform using the [x] in the top-right corner. The procedure runs without any errors, but the message box doesn't show us the name we typed in! This is because when the [x] was clicked, the userform was unloaded and anything entered into it was lost. Within the MsgBox line, the reference to FMyForm caused Excel to create a new instance of the userform, in which the name is blank.

Do not use default instances.
Userforms are just a special type of class module and they should be treated like class modules. By doing so, we gain control over when the userform is created and destroyed, preventing the type of bug demonstrated in Listing 10-3. Listing 10-4 shows the same routine, treating the userform as a class. This time, the name is displayed correctly.

Listing 10-4. Using the Userform Like a Class

Sub TestClassInstance()
'Define our object variable
Dim frmMyForm As FMyForm
'Set our object variable to be a new instance of the userform
Set frmMyForm = New FMyForm
'Show the userform
frmMyForm.Show
'Show the contents of the text box
MsgBox "The name is: " & frmMyForm.txtName.Text
'If showing the userform modeless, we have to unload it
Unload frmMyForm
End Sub

Unfortunately, using a userform like this gives us a minor problem that we need to be aware of and workaround: If the userform is unloaded while our object variable is referring to it, we will often get an automation error. This is easily avoided by ensuring our userform is only ever hidden instead of being unloaded. The code in Listing 10-5 can be added to any userforms that have the standard OK and Cancel buttons.

Listing 10-5. Hiding Instead of Unloading a Form

'Store whether the user OK'd or Cancel'd
Dim mbOK As Boolean
'Handle the OK button
Private Sub cmdOK_Click()
mbOK = True
Me.Hide
End Sub
'Handle the Cancel button
Private Sub cmdCancel_Click()
mbOK = False
Me.Hide
End Sub
'Make the [x] behave the same as Cancel
Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
cmdCancel_Click
Cancel = True
End If
End Sub
'Return whether the OK or Cancel button was clicked
Public Property Get OK() As Boolean
OK = mbOK
End Property

Expose Properties and Methods, Not Controls

Following the philosophy of treating a userform like a class, we should only interact with the userform via properties and methods that we add to the userform's class module; we should never refer to individual controls from outside the userform's module, nor should we set any properties of the userform itself. Proper encapsulation dictates that everything to do with a userform should be contained within the userform. By adding properties and methods to isolate a userform's controls from external code, we gain the ability to rename or change the style of any of the controls, knowing that we won't be breaking any code that uses the userform.

Imagine a userform with a set of three option buttons to select a level of detail for a report. If we were to allow external code to directly access the controls, we might be tempted to write code like Listing 10-6 (where we've assumed the form includes the code from Listing 10-5).

Listing 10-6. Using a Userform's Controls Directly

Sub UseTheControls()
Dim frmOptions As FOptions
Set frmOptions = New FOptions
'Show the userform
frmOptions.Show
If frmOptions.OK Then
'Which option was selected?
If frmOptions.optDetailed.Value Then
RunDetailedReport
ElseIf frmOptions.optNormal.Value Then
RunNormalReport
ElseIf frmOptions.optSummary.Value Then
RunSummaryReport
End If
End If
End Sub

The result of doing this is that the calling code is very tightly bound to the physical layout of the userform, so if we want to change the userform's layoutsuch as to use a combo box instead of the three option buttonswe have to check wherever the userform is used and change that code as well as the code within the userform's module.

Instead, we should expose everything using property procedures, so the calling code does not need to know how the property is physically represented on the userform. Listing 10-7 adds a DetailLevel property to the userform, returning the level of detail as an enumeration, which the calling code uses to decide which report to run.

Listing 10-7. Using Property Procedures

'
'Within the userform FOptions
'
'Enum for the levels of detail
Public Enum odlOptionDetailLevel
odlDetailLevelDetailed
odlDetailLevelNormal
odlDetailLevelSummary
End Enum
'Property to return the level of detail
Public Property Get DetailLevel() As odlOptionDetailLevel
'Which option was selected?
If optDetailed.Value Then
DetailLevel = odlDetailLevelDetailed
ElseIf optNormal.Value Then
DetailLevel = odlDetailLevelNormal
ElseIf optSummary.Value Then
DetailLevel = odlDetailLevelSummary
End If
End Property
'
'The calling code
'
Sub UseAProperty()
Dim frmOptions As FOptions
Set frmOptions = New FOptions
'Show the userform
frmOptions.Show
If frmOptions.OK Then
'Which option was selected?
If frmOptions.DetailLevel = odlDetailLevelDetailed Then
RunDetailedReport
ElseIf frmOptions.DetailLevel = odlDetailLevelNormal Then
RunNormalReport
ElseIf frmOptions.DetailLevel = odlDetailLevelSummary Then
RunSummaryReport
End If
End if
End Sub

Now if we want to change the option buttons to a combo box, all of the changes are contained within the userform and its code module, making maintenance much easier and much less prone to introducing new bugs. Unfortunately, all the controls on a userform and all the userform's properties are always exposed to external code, so any properties and methods we add get lost in the IntelliSense list. In Chapter 11 Interfaces, we explain how to define and use our own interfaces, which allow us to expose only the properties and methods that we want to be called.