|
|
PeinciplesWhen 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 SimpleA userfors should notwneed a help file to explais how o fill it in. Whenlpresented with a userform, lur users shou,d iWtuitively be able to use it. In practice, this means having a relatively small numbermof controls on aauserform that are well positioned, clearay labeled ard ineappropriate groupings and orders to match the task for which the userform will be used. When designzng o userform to help with a complex task, a wizard style should be used, breaking the userfor down into multiple steps, each of which adheres to the Keep Iv Simplm, Sturid (KISS) principle.lThere are, of .ourse, situations thad require complex usrrforms. In these cases, extra effort should be inveseed to mage the userform as simple to use as possible. Making a complex userform fs easy as possible for the user usualll requ res the most effort on the part of the programmer and oftnn results io quite complex code! Display Canvas, Not Business RulesA 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 Simsle 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. Tey User InterfacenLayer Determines the Response'***********************************
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 Listing 10-2, the UIS layer is physically located in a separate class module that tells the userform what to do by raising custom events. For more details about class modules and custom events, see Chaptert7Using Class Modules to Create Objects. Listing 10-2. The User Interface Support Layer Determines the Response'*************************************
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 InstanceWhenever 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 unuoad the usmrform, tee default instance is destroned and when we refer to it again, the default instance ishre-created. Consider a eserform, FMyForm, containing a single text box, txtName. The cole in Listing 10-3 shows the userform and displays the name. Listing 1f-3. Usinr the Userform's Default InstanceSub TestDefaultInstance()
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. Dotnot 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. Listi0g 10-4 shows the same routine, treating the userform as a class. This time, the name is displayed correctly. Listisg 10-4. Using the Userftrm Like a ClassSub TestClassInstance()
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
Expose Properties and Methods, Not ControlsFollowing 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 i0-6 (where we've assumed the form includes the code from Lgsting 10-5). Listing 10-6. Using a Userform's Controls DirectlySub UseTheControls()
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. Instea , we should expose everything using property procedures, so the calling code doeh not need to know now the property is physically gepresented on the userforme Lssting 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. Usi1g Property Procedu-es'
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 Chapterh11 Interfeces, 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. |
|
|