33.

Creating an Error-Resistant Loan Payment Calculator

Excel has tools that enable you to make an error-resistant loan payment calculator. By restricting options to valid items, you can make your model less likely to produce erroneous results and also much easier to use. The Control Toolbox contains all kinds of useful ActiveX controls that you can put on a worksheet, such as list boxes, spin buttons, combo boxes, and so on.

Restrict the Years to a Valid Range

Start by making it difficult to enter an invalid number of years. Typically, for car loans you can borrow for up to five years in units of a year. Just to be safe, allow values from 1 to 6 for the number of years. A spin button is an effective way to specify such integer values.

Control Toolbox

  1. Activate the Control Toolbox. (Click the Control Toolbox button on the Visual Basic toolbar.)

  2. Click the Spin Button button on the Control Toolbox.

    Spin Button

  3. Press and hold the Alt key, and click near the upper left corner of cell E6. (Pressing the Alt key as you drag makes the control snap to the cell grid line.)

    An ActiveX control has many properties. For most of the properties, you can simply accept the default values. Change only the properties for which you need a custom value.

  4. Release the Alt key, and drag the lower right corner of the new spin button to the bottom center of cell E6.

    This makes the spin button rotate sideways and fit on the row.

    click to expand

    Properties

  5. Click the Properties button to display the Properties window.

  6. Type 6 as the value of the Max property, and type 1 as the value of the Min property.

    click to expand

    You want the spin button to control the value in cell C6.

  7. For the LinkedCell property, type C6 and press the Enter key.

    click to expand

    Exit Design Mode

  8. Click the Exit Design Mode button, and try clicking the spin button.

    The number in cell C6 changes as you click the spin button, and the payment amount changes accordingly. Now your friend will find it easy to select only valid loan duration values.

Restrict the Down Payment to Valid Values

Unfortunately, your friend can still enter an invalid value for the down payment percentage (-50%, for example, or Dog). You need to help out. A reasonable range of values for the down payment would be anywhere from 0% to 100%, counting in 5% increments. You specify the down payment as a percentage (which is a fraction, not an integer). The minimum change for a spin button is an integer, but you can still use a spin button as long as you utilize an extra cell to hold the intermediate value.

Copying the control makes both controls exactly the same size.

Design Mode

  1. Click the Design Mode button to switch back to design mode.

  2. Press and hold both the Alt key and the Ctrl key, and drag the spin button from cell E6 to cell E3. When you release the mouse button, a copy snaps to the upper left corner of cell E3.

    click to expand

  3. In the Properties window, type 100 as the value of the Max property, 0 (zero) as the value of the Min property, and H3 as the value of the LinkedCell property.

    Cell H3 holds an intermediate-integer-value because the spin button can increment only in integers. Later, you will divide the value in cell H3 by 100 to convert the integer created by the spin button into a percentage suitable for use as a down payment.

  4. As the value of the SmallChange property, type 5 and press the Enter key.

    This property controls how much the number will change each time you click the control.

    click to expand

    Exit Design Mode

  5. Click the Exit Design Mode button, and then click the control.

    The value in cell H3 changes to a multiple of 5 between 0 and 100. Now you need a value in cell C3 that changes between 0% and 100%.

  6. Select cell C3, type =H3/100, and press the Enter key.

    A percentage value appears in the cell.

    click to expand

  7. Click the spin button to see both the integer in cell H3 and the derived value in cell C3 change in tandem.

Restrict the Interest Rate to Valid Values

The interest rate is another input value your friend might enter incorrectly. The interest rate is similar to the down payment rate; both are percentages. You probably want to allow interest rates to vary by as little as 0.25%, and within a range from 0% through about 20%. Because you're allowing so many possible values, you'll have many more steps than with the down payment rate, so you'll use a scroll bar control instead of a spin button. Like a spin button, the scroll bar returns only integers, so you'll link the control to an intermediate cell.

Scroll Bar

  1. Click the Scroll Bar button on the Control Toolbox, and then press and hold the Alt key as you click the upper left corner of cell E5.

  2. Continue to press the Alt key as you drag the lower right corner of the new scroll bar to the lower right corner of cell E5.

    click to expand

  3. In the Properties window, type 2000 as the value of the Max property, 25 as the value of the SmallChange property, 100 as the value of the LargeChange property, and H5 as the value of the LinkedCell property. Press the Enter key.

  4. Click the Exit Design Mode button, and try the scroll bar control by clicking the arrows as well as the area between them. If you click one of the arrows on either end, the number in cell H5 changes by 25 (the SmallChange value). If you click between the box and the end, the number changes by 100 (the LargeChange value).

  5. Select cell C5, type =H5/10000, and press the Enter key. You divide by 100 to turn the number from H5 into a percentage and by another 100 (100 * 100 = 10000 total) to allow for hundredths of a percent.

    click to expand

Now your friend can easily modify the number of years for the loan (using one spin button), the down payment percentage (using the other spin button), or the interest rate (using the scroll bar control).



Microsoft Excel 2002 Visual Basic for Applications Step by Step
FrameMaker 6: Beyond the Basics
ISBN: 735613591
EAN: 2147483647
Year: 2005
Pages: 122
Authors: Lisa Jahred

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net