Page 753 - Excel 2013 All-in-One For Dummies

Creating Custom Excel Functions 735
the Ribbon or press Alt+F8 to open the Macro dialog box and then click the name of the macro that you just wrote before you click OK.
If Excel encounters an error when running the macro, it returns you to the Visual Basic Editor, and an Alert Microsoft Visual Basic dialog box appears, indicating (in very cryptic form) the nature of the error. Click the Debug button in this dialog box to have the Visual Basic Editor highlight the line of code that it can’t execute. You can then attempt to find the mistake and edit it in the line of code. If you do eliminate the cause of the error, the Visual Basic Editor removes the highlighting from that line of code, and you can then click the Continue button (which automatically replaces the Run button when the Editor goes into debug mode) with the blue triangle pointing to the right on the Standard toolbar to continue running the macro.
Creating Custom Excel Functions
One of the best uses of VBA in Excel is to create custom spreadsheet func- tions also known as user-defined functions (UDFs for short). 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 four little 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 window and then choosing Insert➪Module on the Visual Basic Editor menu bar.
✦ Enterthenameofthecustomfunctionandspecifythenamesoftheargu- ments that this function takes on in the first line in the Code window — 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 by using 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 enter- ing the End Function command on the last line.
To see how this procedure works in action, consider this scenario: Suppose that you want to create a custom function that calculates the sales commis- sions 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,
Book VIII Chapter 2
VBA Programming