Page 320 - Excel Workbook for Dummies

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

5. While the insertion point is in the ItemsSold argument text box, click cell C5 in
the worksheet that contains the count of Fred’s sales and then select the OK
button.
The custom commission function computes Fred’s commission as $2,655 (using
the 4.5% commission rate because the number of his sales is below five).
6. Use your custom Commission function to compute the sales commissions for
Holly in cell E10.
Select cell C9 as the TotalSales argument and cell C10 as the ItemsSold argument
for this Commission function.
7. Save your changes to the RV Sales worksheet in a new workbook file named
Solved23-4.xls in your Chapter 23 folder in the My Practice Spreadsheets folder
and then leave the workbook open for Exercise 23-5.

Saving custom functions in add-in files

The only limitation to the user-defined functions that you save as part of a regular
workbook files or the Personal Macro Workbook file is that when entering them
directly into a cell (without the use of the Insert Function dialog box), you must pref-
ace their function names with their filenames. So, for example, if you want to type in
the custom Commission function that’s saved in the Personal Macro Workbook and
you enter the following formula:

=Commission(C9,C10)
(assuming that cell C9 contains the total sales and cell C10 contains the number of
items sold), Excel returns the #NAME? error value to the cell. If you then edit the func-
tion to include the Personal Macro Workbook’s filename as follows:

=Personal.xls!Commission(C9,C10)
Excel then calculates the sales commission based on the TotalSales in C9 and the
ItemsSold in C10, returning this calculated value to the cell containing this user-
defined function.

To be able to omit the filename from the custom functions you create when you enter
them directly into a cell, you need to save the workbook file that contains them as an
add-in file (with an .xla filename extension). Then, after you’ve saved the workbook
with your user-defined functions as an add-in file, you can start entering them into
any worksheet without their filename qualifier after activating the add-in in the Add-
Ins dialog box (Tools➪Add-Ins).

Try It

Exercise 23-5: Saving a Custom Function as an Excel Add-In
Use the Solved23-4.xls workbook with the RV Sales commissions that you created in
Exercise23-4 to practice saving your custom PERSONAL.XLS!Commission function in
an Excel add-in file:

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.