Chapter 25: Developing User-Oriented Applications
IN THIS CHAPTER
• Describing a user-oriented application
• Looking at the Loan Amortization Wizard, which generates a worksheet with an amortization schedule for a fixed-rate loan
• Demonstrating application development concepts and techniques by the Loan Amortization Wizard
• Reviewing an application development checklist
What is a User-Oriented Application?
I use the term user-oriented application for an Excel application that someone with minimal training can use. These applications produce useful results even for users who know virtually nothing about Excel.
The Loan Amortization Wizard discussed in this chapter qualifies as a user-oriented application because it's designed in such a way that the end user doesn't need to know the intimate details of Excel to use it. Replying to a few simple prompts produces a useful and flexible worksheet complete with formulas.
The Loan Amortization Wizard
The Loan Amortization Wizard generates a worksheet that contains an amortization schedule for a fixed-rate loan. An amortization schedule projects month-by-month details for a loan. The details include the monthly payment amount, the amount of the payment that goes toward interest, the amount that goes toward reducing the principal, and the new loan balance.
An alternative to creating an amortization schedule using a wizard is to create a template file. As you'll see, this wizard approach offers several advantages.
Figure 25-1 shows an amortization schedule generated by the Loan Amortization Wizard.
FIGURE 25-1: This amortization schedule shows details for a 30-year mortgage.
The Loan Amortization Wizard is available on the CD-ROM that accompanies this book. It's an unprotected add-in named loan amortization wizard.xlam.
Using the Loan Amortization Wizard
The Loan Amortization Wizard consists of a five-step dialog box sequence that collects information from the user. Typical of a wizard, this enables the user to go forward and backward through the steps. Clicking the Finish button creates the new worksheet. If all the steps haven't been completed when the user clicks Finish, default values are used. Clicking the Cancel button closes the UserForm, and no action is taken.
This application uses a single UserForm with a MultiPage control to display the five steps, shown in Figures 25-2 through 25-6.
FIGURE 25-2: Step 1 of the Loan Amortization Wizard.
FIGURE 25-3: Step 2 of the Loan Amortization Wizard.
FIGURE 25-4: Step 3 of the Loan Amortization Wizard.
FIGURE 25-5: Step 4 of the Loan Amortization Wizard.
FIGURE 25-6: Step 5 of the Loan Amortization Wizard.
The Loan Amortization Wizard workbook structure
The Loan Amortization Wizard consists of the following components:
• FormMain: A UserForm that serves as the primary user interface.
• FormHelp: A UserForm that displays online help.
• FormMessage: A UserForm that displays a message when the add-in is opened. The user can disable this display.
• HelpSheet: A worksheet that contains the text used in the online help.
• ModMain: A VBA module that contains a procedure that displays the main UserForm.
• ThisWorkbook: The code module for this object contains the Workbook_Open event-handler procedure.
In addition, the workbook file contains some simple RibbonX XML code that creates the Loan Amortization Wizard button in the Insert tab of the Ribbon.
How the Loan Amortization Wizard works
The Loan Amortization Wizard is an add-in, so you should install it by using the Add-Ins dialog box. To display this dialog box, choose File⇒Options⇒Add-Ins. Then, in the Excel Options dialog box, choose Excel Add-Ins from the Manage drop-down list and click Go. Use the Browse button to locate the add-in file. After it's installed, an add-in remains installed across Excel sessions. The add-in works perfectly well, however, if it's opened with the File⇒Open command.
Modifying the user interface
Every add-in needs a way to allow the user to access the procedures. I added some RibbonX code to the file that adds a button to a new group in the Insert tab (see Figure 25-7). Clicking this button executes the StartAmortizationWizard procedure, which displays the FormMain UserForm.
FIGURE 25-7: A new group on the Insert tab contains one control.
The RibbonX code that creates the Ribbon control is
<customUI xmlns=”http://schemas.microsoft.com/office/2006/01/customui”>
<ribbon>
<tabs>
<tab idMso=”TabInsert”>
<group id=”gpUtils” label=”Loan Amortization”>
<button id=”b1”
size=”large”
imageMso=”CreateQueryFromWizard”
label=”Loan Amortization Wizard”
supertip=”Click here to create an amortization schedule.”
onAction=”StartAmortizationWizard”/>
</group>
</tab>
</tabs>
</ribbon>
</customUI>
Refer to Chapter 22 for information about modifying the Ribbon.
Displaying an initial message
I've installed many Excel add-ins over the years, and I've found that many of them don't provide a clue as to how to access the add-in. To make this application as user-friendly as possible, I added a UserForm that is displayed when the workbook is opened. This form tells the user how to start the wizard. Figure 25-8 shows the UserForm.
To prevent it from become annoying, this UserForm includes an option to turn off the message in the future.
Following is the Workbook_Open procedure that displays the dialog box:
Private Sub Workbook_Open()
If GetSetting(APPNAME, “Defaults”, “ShowMessage”, “Yes”) = “Yes” Then
FormMessage.Show
End If
End Sub
FIGURE 25-8: This form is displayed when the Loan Amortization Wizard is opened.
The user's choice regarding the future display of the UserForm is stored in the Windows Registry. The Registry key is specified by the application's name (a global constant, APPNAME). The default value is “Yes,” so the UserForm will display at least one time.
Following is the code that is executed when the user clicks the OK button:
Private Sub OKButton_Click()
If cbMessage Then
SaveSetting APPNAME, “Defaults”, “ShowMessage”, “No”
Else
SaveSetting APPNAME, “Defaults”, “ShowMessage”, “Yes”
End If
Unload Me
End Sub
If the user checks the check box control, then the registry setting is set to “No,” and the UserForm won't be displayed again.
Initializing FormMain for the wizard
The UserForm_Initialize procedure for FormMain does quite a bit of work:
• It sets the MultiPage control's Style property to fmTabStyleNone. The tabs are present in the Visual Basic Editor to make the UserForm easier to edit.
• It sets the MultiPage control's Value property to 0. This ensures that it displays the first page, regardless of its value when the workbook was last saved.
• It adds items to three ComboBox controls used on the form.
• It calls the GetDefaults procedure, which retrieves the most recently used setting from the Windows Registry (see the upcoming section “Saving and retrieving default settings”).
• It checks whether a workbook is active. If no workbook is active, the code disables the OptionButton that enables the user to create the new worksheet in the active workbook.
• If a workbook is active, an additional check determines whether the workbook's structure is protected. If so, the procedure disables the OptionButton that enables the user to create the worksheet in the active workbook.
Processing events while the UserForm is displayed
The code module for the FormMain UserForm contains several event-handler procedures that respond to the Click and Change events for the controls on the UserForm.
Clicking the Back and Next buttons determines which page of the MultiPage control is displayed. The MultiPage1_Change procedure adjusts the UserForm's caption and enables and disables the Back and Next buttons as appropriate. See Chapter 15 for more information about programming a wizard.
Displaying help in the wizard
You have several options when it comes to displaying online help. I chose a technique that displays help text in the UserForm shown in Figure 25-9 to display text stored in a worksheet. You'll notice that this help is context-sensitive. When the user clicks the Help button, the Help topic displayed is relevant to the current page of the MultiPage control.
Worksheets in an add-in aren't visible. To view the worksheet that contains the help text for this add-in, you need to temporarily set the workbook's IsAddin property to False. One way to accomplish this is to select the project in the Project window and execute this statement in the Immediate window:
ThisWorkbook.IsAddin = False
FIGURE 25-9: User help is presented in a UserForm that copies text stored in a worksheet.
For more information about the technique of transferring worksheet text to a UserForm, refer to Chapter 24.
Creating the new worksheet
When the user clicks the Finish button, the action begins. The Click event-handler procedure for this button performs the following actions:
• It calls a function named DataIsValid, which checks the user's input to ensure that it's valid. If all the entries are valid, the function returns True, and the procedure continues. If an invalid entry is encountered, DataIsValid sets the focus to the control that needs to be corrected and returns a descriptive error message (see Figure 25-10).
• If the user's responses are valid, the procedure creates a new worksheet either in the active workbook or in a new workbook, per the user's request.
FIGURE 25-10: If an invalid entry is made, the focus is set back to the control that contains the error.
• The loan parameters (purchase price, down payment information, loan amount, term, and interest rate) are written to the worksheet. This requires the use of some If statements because the down payment can be expressed as a percentage of the purchase price or as a fixed amount.
• The column headers are written to the worksheet.
• The first row of formulas is written below the column headers. The first row is different from the remaining rows because its formulas refer to data in the loan parameters section. The other formulas all refer to the previous row. Notice that I use named ranges in the formulas. These are sheet-level names, so the user can store more than one amortization schedule in the same workbook.
• For unnamed references, I use row number and column number notation, which is much easier than trying to determine actual cell addresses.
• The second row of formulas is written to the worksheet and then copied down one row for each month.
• If the user requested annual totals as opposed to simply monthly data, the procedure uses the Subtotal method to create subtotals. This, by the way, is an example of how using a native feature in Excel can save lots of coding.
• Because subtotaling the Balance column isn't appropriate, the procedure replaces formulas in the Balance column with a formula that returns the year-end balance.
• When Excel adds subtotals, it also creates an outline. If the user didn't request an outline, the procedure uses the ClearOutline method to remove it. If an outline was requested, the procedure hides the outline symbols.
• Next, the procedure applies formatting to the cells: number formatting, plus an AutoFormat if the user requested color output.
• The amortization schedule is then converted to a table, and a style is applied based on the user's choice of black-and-white or color.
• The procedure then adjusts the column widths, freezes the titles just below the header row, and protects the formulas and a few other key cells that shouldn't be changed.
• If the Protect Sheet option is specified in Step 5, the sheet is protected (but not with a password).
• Finally, the SaveDefaults procedure writes the current values of the UserForm's controls to the Windows registry. These values will be the new default settings the next time the user creates an amortization schedule. (See the following section.)
Saving and retrieving default settings
If you run this application, you'll notice that the FormMain UserForm always displays the setting that you most recently used. In other words, it remembers your last choices and uses them as the new default values. This step makes it very easy to generate multiple what-if amortization schedules that vary in only a single parameter. The code remembers the user input by storing the values in the Windows Registry and then retrieving them when the UserForm is initialized. When the application is used for the first time, the Registry doesn't have any values, so it uses the default values stored in the UserForm controls.
The following GetDefaults procedure loops through each control on the UserForm. If the control is a TextBox, ComboBox, OptionButton, CheckBox, or SpinButton, it calls VBA's GetSetting function and reads the value to the Registry. Note that the third argument for GetSetting is the value to use if the setting isn't found. In this case, it uses the value of the control specified at design time. APPNAME is a global constant that contains the name of the application.
Sub GetDefaults()
‘ Reads default settings from the registry
Dim ctl As Control
Dim CtrlType As String
For Each ctl In Me.Controls
CtrlType = TypeName(ctl)
If CtrlType = “TextBox” Or _
CtrlType = “ComboBox” Or _
CtrlType = “OptionButton” Or _
CtrlType = “CheckBox” Or _
CtrlType = “SpinButton” Then
ctl.Value = GetSetting _
(APPNAME, “Defaults”, ctl.Name, ctl.Value)
End If
Next ctl
End Sub
Figure 25-11 shows how these values appear in the Registry, as displayed by the Windows Registry Editor program.
FIGURE 25-11: The Windows Registry stores the default values for the wizard.
The following SaveDefaults procedure is similar to the GetDefaults procedure. It uses VBA's SaveSetting statement to write the current values to the Registry:
Sub SaveDefaults()
‘ Writes current settings to the registry
Dim ctl As Control
Dim CtrlType As String
For Each ctl In Me.Controls
CtrlType = TypeName(ctl)
If CtrlType = “TextBox” Or _
CtrlType = “ComboBox” Or _
CtrlType = “OptionButton” Or _
CtrlType = “CheckBox” Or _
CtrlType = “SpinButton” Then
SaveSetting APPNAME, “Defaults”, ctl.Name, CStr(ctl.Value)
End If
Next ctl
End Sub
Notice that the code uses the CStr function to convert each setting to a string. This function helps avoid problems for those who use non-English regional settings. Without the string conversion, True and False are translated to the user's language before they're stored in the Registry. But they're not translated back to English when the setting is retrieved — which causes an error.
The SaveSetting statement and the GetSetting function always use the following Registry key:
HKEY_CURRENT_USERSoftwareVB and VBA Program Settings
Potential enhancements for the Loan Amortization Wizard
It's been said that you never finish writing an application — you just stop working on it. Without even thinking too much about it, I can come up with several enhancements for the Loan Amortization Wizard:
• An option to display cumulative totals for interest and principal
• An option to work with adjustable-rate loans and make projections based on certain interest rate scenarios
• More formatting options (for example, no decimal places, no dollar signs, and so on)
• Options to enable the user to specify page headers or footers
Application Development Concepts
Following the logic in an application developed by someone other than yourself is often difficult. To help you understand my work, I included lots of comments in the code and described the general program flow in the preceding sections. But, if you really want to understand this application, I suggest that you use the Debugger to step through the code.
At the very least, the Loan Amortization Wizard demonstrates some useful techniques and concepts that are important for Excel developers:
• Modifying the Ribbon.
• Using a wizard-like UserForm to gather information.
• Setting the Enabled property of a control dynamically.
• Linking a TextBox control and a SpinButton control.
• Displaying online help to a user.
• Naming cells with VBA.
• Writing and copying formulas with VBA.
• Reading from and writing to the Windows Registry.
Developing user-oriented applications in Excel isn't easy. You must be keenly aware of how people will use (and abuse) the application in real life. Although I tried to make this application completely bulletproof, I did not do extensive real-world testing, so I wouldn't be surprised if it fails under some conditions.