Page 318 - Excel Workbook for Dummies

33_798452 ch23.qxp 3/13/06 7:51 PM Page 301
Chapter 23: Using the Visual Basic Editor 301

Try It

Exercise 23-3: Constructing a Custom Function in the Visual Basic Editor

Open the Exercise23-3.xls workbook file in your Chapter 23 folder in the My Practice
Spreadsheets folder on your hard disk or in the Excel Workbook folder on the work-
book CD-ROM. This worksheet contains an RV Sales worksheet with the sales for
three account representatives, Fred, Holly, and Jack, for which you will create the
user-defined Commission function:

1. Choose the Window➪Unhide menu command to open the Unhide dialog box.
2. While PERSONAL.XLS is selected in the Unhide Workbook list box, select the OK
button.

3. Press Alt+F11 to open the Visual Basic Editor window with a new module sheet
from the PERSONAL.XLS workbook.
4. In the Projects window in the Visual Basic Editor, click the expand button in front
of the Modules folder icon at the bottom of the hierarchy.

5. Select Insert➪Module on the Visual Basic Editor’s menu bar to insert a Module2
in the hierarchy.
6. Select Module2 following (Name) in the Properties window and then replace it
with Commission_UDF and press Enter.
Excel renames the Module2 sheet to Commission_UDF and this module name
now appears above Module1 in the hierarchy in the Projects window.
7. Click the insertion point in the Code window and then type the following code on
Line1:
Function Commission(TotalSales, ItemsSold)
8. Press Enter to start a new line and Tab to indent; enter the following code on line
2 starting at column 5:

If ItemsSold <= 5 Then
9. Press Enter to start a new line and then Tab again to indent further, and then
enter the following indented code on line 3 starting at column 9:
Commission = TotalSales * 0.045
10. Press Enter to start a new line and then Shift+Tab to outdent; enter the following
indented code on line 4 starting at column 5:
Else
11. Press Enter to start a new line and then Tab to indent; enter the following
indented code on line 5 starting at column 9:
Commission = TotalSales * 0.05
12. Press Enter to start a new line and then Shift+Tab to outdent; enter the following
indented code on line 6 starting at column 5:
End If
13. Check the lines of code in your custom Commission function against those
shown in Figure 23-5. When they check out, proceed to step 14.
14. Choose File➪Save PERSONAL.XLS on the Visual Basic Editor menu bar to save
your custom function.
15. Click the View Microsoft Excel button on the Standard toolbar in the Visual Basic
Editor and then hide the Personal Macro Workbook PERSONAL.XLS by choosing
Window➪Hide on the Excel menu bar.