Creating Custom Functions

To create a user-defined function, you must do four things:

• Create a new module sheet where the custom function is to be defined in Visual Basic Editor. To do this, select its project in the Project Explorer window and then choose Insert → Module on the Visual Basic Editor menu bar.
• On the first line in the Code window, enter the name of the custom function and specify the names of the arguments that this function takes. Note that you can't duplicate any built-in function names, such as SUM or AVERAGE functions, 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. You do this by entering the argument names listed in the Function command with whatever arithmetic operators or built-in functions are required to get the calculation 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, consider the following scenario. You need a custom function for your spreadsheets that calculates the sales commissions for your 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). The custom Commission function you want to create requires two arguments: TotalSales and ItemsSold so that the first line of code on the module sheet in the Code window would have to be this:

Function Commission(TotalSales,ItemsSold)

In determining how the commissions are actually calculated, suppose that you base the commission 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 need to set up an IF construction. This IF construction is similar to the IF function that you enter into a worksheet cell except that you use different lines in the macro code for the construction in the custom function. An ELSE command 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 command.

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 ThenCommission = TotalSales * 0.045ElseCommission = TotalSales * 0.05End If

After entering the definition for your user-defined function, save it by choosing File → Save on the Visual Basic Editor menu bar or by pressing Ctrl+S. Then click the View Microsoft Excel button on the Editor's Standard toolbar to return to the worksheet where you can try out your new custom function.

If you want to be able to use your user-defined function in any spreadsheet you create, be sure that you select VBAProject(personal.xls) in the Project Explorer window before you open a new module and define the custom function there.

The really nice thing about custom functions is that you can use Excel's Insert Function button on the Formula bar to use them in your spreadsheets.