Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

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.

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 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 Office Excel 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 Office Open command.

The Loan Amortization Wizard application started out as a simple concept and evolved into a relatively complex project. My primary goal was to demonstrate as many development concepts as possible and still have a useful end product. I would like to say that I clearly envisioned the end result before I began developing the application, but I'd be lying.

My basic idea was much less ambitious. I simply wanted to create an application that gathered user input and created a worksheet. But, after I got started, I began thinking of ways to enhance my simple program. I eventually stumbled down several blind alleys. Some folks would consider my wanderings time-wasting, but those false starts became a vital part of the development process.

I completed the entire project in one (long) day, and I spent a few more hours fine-tuning and testing it. I added a few more accouterments for the version included in this edition of the book.

MODIFYING THE USER INTERFACE

Every add-in needs a way to be accessed. I added some simple 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 simply 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>

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. So, in order to make this application as user-friendly as possible, I added a UserForm that is displayed when the workbook is opened. This form simply tells the user how to start the wizard. Figure 25-8 shows the UserForm.

Figure 25-8: This form is displayed when the Loan Amortization Wizard is opened.

Importantly, 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

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, APP-NAME ). 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 will not 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 . 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.

DISPLAYING HELP IN THE WIZARD

You have several options when it comes to displaying online help. I chose a simple technique that employs 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.

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

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

  Figure 25-10: 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. 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. This is accomplished 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 is not 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, from the perspective of the Windows Registry Editor program.

Figure 25-11: The Windows Registry stores the default values for the wizard.

The following SaveDefaults procedure is similar. 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 is to 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 are stored in the Registry. But they are 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_USER\Software\VB 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