Page 317 - Excel Workbook for Dummies

33_798452 ch23.qxp 3/13/06 7:51 PM Page 300
300 Part VII: Macros and Visual Basic for Applications
Creating User-Defined Functions

One of the best uses of Visual Basic for Applications in Excel is to create custom
spreadsheet functions, also known as user-defined functions. User-defined functions
are great because you don’t have to access the Macro dialog box to run them. In
fact, you enter them into your spreadsheets just like you do any of the other built-in
spreadsheet functions, either with the Insert Function button on the Formula bar or
by typing them directly into a cell.
To create a user-defined function, you must do the four following things:

Create a new module sheet where the custom function is to be defined in the
Visual Basic Editor by selecting its project in the Project Explorer and then
choosing Insert➪Module on the Visual Basic Editor Menu bar.
Enter the name of the custom function and specify the names of the arguments
that this function takes on the first line in the Code window. Note that you can’t
duplicate any built-in function names, such as SUM or AVERAGE, and so on, and
you must list argument names in the order in which they are processed and
enclosed in parentheses.
Enter the formula, or set of formulas, that tells Excel how to calculate the custom
function’s result using the argument names listed in the Function command with
whatever arithmetic operators or built-in functions are required to get the calcu-
lation made on the line or lines below.
Indicate that you’ve finished defining the user-defined function by entering the
End Function command on the last line.

To see how this procedure works in action, in the following exercise, you create a
user-defined function that calculates the sales commissions for salespeople based
on the number of sales they make in a month as well as the total amount of their
monthly sales (they sell big-ticket items, such as RVs). Your custom Commission
function has two arguments — TotalSales and ItemsSold — so that the first line of
code on the module sheet in the Code window is
Function Commission(TotalSales,ItemsSold)

In determining how the commissions are actually calculated, you base the commis-
sion percentage on the number of sales made during the month. For five sales or
fewer in a month, you pay a commission rate of 4.5 percent of the salesperson’s total
monthly sales; for sales of six or more, you pay a commission rate of 5 percent.

To define the formula section of the Commission custom function, you set up an IF
construction. This IF construction is similar to the IF function you enter into a work-
sheet cell except that you use different lines in the macro code for the construction in
the custom function. An ELSE statement separates the command that is performed if
the expression is True from the command that is performed if the expression is False.
The macro code is terminated by an END IF statement. To set the custom function so
that your salespeople get 4.5 percent of total sales for five or fewer items sold and 5
percent of total sales for more than five items sold, you enter the following lines of
code underneath the line with the Function command:

If ItemsSold <= 5 Then
Commission = TotalSales * 0.045
Else
Commission = TotalSales * 0.05
End If