Creating User -Defined Functions with VBA The Office applications come with a large number of built-in functions. Excel, for example, has hundreds of functionsone of the largest function libraries of any spreadsheet package. However, even with this vast collection, you'll still find that plenty of applications are not covered. For example, you might need to calculate the area of a circle of a given radius or the gravitational force between two objects. You could, of course, easily calculate these things on a worksheet, but if you need them frequently, it makes sense to define your own functions that you can use any time. The next three sections show you how it's done. Understanding User-Defined Functions As I mentioned Chapter 1, the defining characteristic of user-defined functions is that they return a result. They can perform any number of calculations on numbers , text, logical values, or whatever, but they're not allowed to affect their surroundings. In a worksheet, for example, they can't move the active cell , format a range, or change the workspace settings. In fact, anything you can access using the application menus is off-limits in a user-defined function. So, what can you put in a user-defined function? All the application's built-in functions are fair game, and you can use any VBA function that isn't the equivalent of a menu command or desktop action. All user-defined functions have the same basic structure, as shown in Listing 2.1. Listing 2.1. An Example of a User-defined Function Function HypotenuseLength(x, y) HypotenuseLength = Sqr(x ^ 2 + y ^ 2) End Function This is a function named HypotenuseLength that calculates the length of a right triangle's hypotenuse given the other two sides ( x and y ). Using this example, here's a summary of the various parts of a user-defined function: The Function statement This keyword identifies the procedure as a user-defined function. The Function keyword is the reason that user-defined functions are also known as Function procedures. The function name This is a unique name for the function. Names must begin with an alphabetic character, they can't include a space or a period, and they can't be any longer than 255 characters . The function arguments Just as many application functions accept arguments, so do user-defined functions. Arguments (or parameters, as they're sometimes called) are typically one or more values that the function uses as the raw materials for its calculations. You always enter arguments between parentheses after the function name, and you separate multiple arguments with commas. The VBA statements This is the code that actually performs the calculations. Each statement is a combination of values, operators, variables , and VBA or application functions that, together, produce a result. The return value User-defined functions usually return a value. To do this, include a statement where you set the name of the function equal to an expression. For example, in the HypotenuseLength function, the following statement defines the return value: HypotenuseLength = Sqr(x ^ 2 + y ^ 2) The End Function keywords These keywords indicate the end of the Function procedure. All your user-defined functions will have this basic structure, so you need to keep three things in mind when designing these kinds of macros: -
What arguments will the function take? -
What formulas will you use within the function? -
What value or values will be returned? Writing User-Defined Functions User-defined functions can't contain menu commands or mouse and keyboard actions. This means, of course, that there is no way to record user-defined functions. You have to write them out by hand, and the process is very similar to creating a command macro from scratch. Here are the general steps to follow to write a user-defined function: -
Open the module you want to use for the function. -
Place the insertion point where you want to start the function. -
If you like, enter one or more comments that describe what the function does. Be sure to type an apostrophe ( ' ) at the beginning of each comment line. -
Start the procedure by typing Function followed by a space and then the name of the macro. If your function uses arguments, enclose them in parentheses after the function name (be sure to separate each argument with a comma). When you press Enter, VBA inserts the End Function statement. -
Between the Function and End Function lines, enter the VBA statements that you want to include in the function. As with Sub procedures, you should indent each line for clarity by pressing the Tab key at the beginning of the line. -
Be sure to include a line that defines the return value. Employing User-Defined Functions You'll probably find that user-defined functions are most useful in Excel. In this case, you can employ these functions only within worksheet formulas or in other VBA statements. You have two choices: -
In the cell, enter the function the same way you would any of Excel's built-in functions. In other words, enter the name of the function and then the necessary arguments enclosed in parentheses. Here's a sample formula that uses the HypotenuseLength function: =HypotenuseLength(3,4) -
Select Insert, Function to display the Insert Function dialog box. Highlight All in the Or Select a Category list and then highlight the macro in the Select a Function list. Click OK and enter the arguments. When you're done, click OK. |
