Page 197 - Microsoft Office Excel 2003 Programming Inside Out

Part 3: The Excel Object Model
Ranges and Cells

Note When you first enter this formula, a #DIV/0 error will appear in the cell because
there are no values in the cells used in the formula. Once you enter those values, the
error will disappear.

5 Select the field cell for Effective Rate, and type in the following formula:
=EFFECT(APR,12)

Note When you first enter this formula, a #NUM! error will appear in the cell because
Chapter 8
there is no value in the Years cell. Once you enter a value in that cell, the error will
disappear.

6 Select the field cell for Number of Payments, and type the following formula:
=Years*12
7 After you have typed in the Calculate_Table macro, select the Button button on the
Forms tool bar and draw a button on the worksheet, as shown in Figure 8-3. The Assign
Macro dialog box will appear, allowing you to choose the Calculate_Table macro.
8 With the button still selected, select the text in the button and type in the following:
Draw Amortization Table.

Figure 8-3. Adding the Button Control to the form.
Once you type information in the fields, you can click the Draw Amortization Table button to
have the Calculate_Table macro executed. If you want to run the macro again, you need to
change the name of your bank, which is used as the name of the new worksheet. If you try
to create a worksheet with a duplicate name, the macro will generate an error.

171