Chapter 23: 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 used 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 anyone can use it without knowing the intimate details of Excel. 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 23-1 shows an amortization schedule generated by the Loan Amortization Wizard.

Figure 23-1: This amortization schedule shows details for a 30-year mortgage.

The Loan Amortization Wizard is available on the book's website in 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 application 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.

The Loan Amortization Wizard uses a single UserForm with a MultiPage control to display the five steps, shown in Figure 23-2.

Figure 23-2: The five steps 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 that 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 on the Ribbon. This code is not visible in Excel.

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 23-3). Clicking this button executes the StartAmortizationWizard procedure, which displays the FormMain UserForm.

Figure 23-3: 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 20 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 23-4 shows the UserForm.

Figure 23-4: This form is displayed when the Loan Amortization Wizard is opened.

To prevent the UserForm from become annoying, I included 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

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 (named cbMessage), 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 Visual Basic Editor to make the UserForm easier to edit.

• It sets the MultiPage control's Value property to 0. This ensures that the UserForm 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. In Excel 2013, a workbook will always be active because of the single-document interface. I left the code in place because it's necessary for previous versions of Excel.

• 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 13 for more information about programming a wizard.

Displaying help in the wizard

You have several options when it comes to displaying help. I chose a technique that displays help text (which is stored in a worksheet) in the UserForm shown in Figure 23-5. 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.

Figure 23-5: User help is presented in a UserForm that copies text stored in a worksheet.

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

For more information about the technique of transferring worksheet text to a UserForm, refer to Chapter 22.

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 23-6).

Figure 23-6: If an invalid entry is made, the focus is set back to the control that contains the error.

• 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.

• The loan parameters (purchase price, down payment information, loan amount, term, and interest rate) are written to the worksheet. I used 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 preceding row. Note that I use named ranges in the formulas. These names 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.

• The code then applies the appropriate number formatting to the cells.

• 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 note 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 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 the VBA 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 23-7 shows how these values appear in the Registry, as displayed by the Windows Registry Editor program.

Figure 23-7: The Windows Registry stores the default values for the wizard.

The following SaveDefaults procedure is similar to the GetDefaults procedure. It uses the VBA 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

Note 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 context-sensitive help to a user

• Naming cells with VBA

• Writing and copying formulas with VBA

• Creating a table 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 bulletproof, I did not do extensive real-world testing, so I wouldn't be surprised if it fails under some conditions.