Creating a User Defined Function

The Office applications come with a large number of built-in functions. Excel, for example, has hundreds of functions—one of the largest function libraries of any spreadsheet package. However, even with this vast collection, you'll still find plenty of situations that 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 such calculations frequently, it makes sense to define your own functions that you can use anytime. The next three sections show you how it's done.

Understanding User-Defined Functions

As I mentioned earlier, 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 generally don't affect their surroundings. In a worksheet, for example, they usually don't move the active cell, format a range, or change the workspace settings.

So, what can you put in a user-defined function? Most user-defined functions consist of one or more expressions. An expression is some combination of values (such as numbers), operators (such as + and *), variables (see Chapter 3), VBA functions, or application functions that, together, produce a result. (I discuss expressions in detail in Chapter 4, "Building VBA Expressions.")

All user-defined functions have the same basic structure:

Function ProcedureName (argumentl, argument2, ...) [VBA statements] ProcedureName = returnValue End Function

Here's a summary of the various parts of a user-defined function:

■ Function—This keyword identifies the procedure as a user-defined function. The Function keyword is the reason that user-defined functions also are also known as function procedures.

■ ProcedureName—This is a unique name for the function.

■ argumentl, argument2, ...—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 material for its calculations. You always enter arguments between parentheses after the function name, and you separate multiple arguments with commas. (If the function doesn't require arguments, you still need to include the parentheses after the function name.)

■ VBA statements—This is the code that actually performs the calculations, and it's usually a series of VBA statements and expressions that lead toward an overall result for the function.

■ returnValue—This is the final result calculated by the function.

■ End Function—These keywords indicate the end of the function.

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 expressions will you use within the function?

■ What value will be returned?

Writing a User-Defined Function

When you record a macro, VBA always puts the code inside a command macro. Unfortunately, there is no way to record a user-defined function; you have to write them out by hand. Fortunately, 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:

1. Open the module you want to use for the function.

2. Place the insertion point where you want to start the function. (Make sure the insertion point isn't inside an existing macro.)

3. Choose Insert, Procedure to open the Add Procedure dialog box.

4. Use the Name text box to type the function's name. The guidelines you must follow are the same as those for a command macro: The name must be 255 characters or fewer; the first character must be a letter or an underscore (_); and you can't use spaces or periods.

5. In the Type group, click the Function option.

6. Click OK. VBA adds the following code to the module (where ProcedureName is the name you typed in step 3):

Public Function ProcedureName()

End Function

7. Between the Public Function and End Function lines, type the VBA statements you want to include in the function. Press Enter after each statement to start a new line.

8. Be sure to include a statement that defines the return value. That statement should consist of the function name, followed by an equals sign (=), followed by the return value.

Figure 2.8 shows an example user-defined function that calculates and returns a result, using a single VBA statement:

GrossMargin = (Sales - Expenses) / Sales

Here, Sales and Expenses are the arguments that get passed to the function. The function subtracts the Expenses value from the Sales value, and then divides by Sales to return the gross margin.

Figure 2.8

The example function, ready for use in other procedures.

Bookl - Mo du lei (Code)

rsiraiE&i

|(Generai} v| |GrossMargin

zi

Public Function GrossMargin(Sales, Expenses} GrossMargin = (Sales - Expenses} / Sales End Function

You can't "run" a user-defined function in the same way that you run a command macro. Instead, you use the function either as part of a command macro (or even as part of another function), or within the application itself.

To use a function in a command macro, you create a separate VBA statement that includes the function name as well as any arguments it requires. (This is known as calling the function.) Here's a simple example:

Public Sub GrossMarginTest1()

MsgBox GrossMargin (100000, 90000) End Sub

This Sub procedure calls the GrossMargin function and supplies it with the values 100000 and 90000 for the Sales and Expenses arguments, respectively. The MsgBox function displays the result in a dialog box.

To use a function in an application, you include it as part of some other calculation. This is most useful in Excel, where you can employ a user-defined function within a worksheet formula.

The easiest way to do this is to enter the function into the cell 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 GrossMargin function and assumes the Sales and Expenses values are in cells B1 and B2, respectively (see Figure 2.9):

=GrossMargin(B1, B2)

Figure 2.9

The GrossMargin function used in an Excel worksheet formula.

Figure 2.9

The GrossMargin function used in an Excel worksheet formula.

You can also use the Function wizard to insert a user-defined function. Here are the steps to follow:

1. Click the cell into which you want to insert the user-defined function.

2. Choose Formulas, Insert Function to display the Insert Function dialog box.

3. In the Or Select a Category list, click User Defined. Excel displays a list of your user-defined functions, as shown in Figure 2.10.

Figure 2.10

In the Insert Function dialog box, choose the User Defined category to see a list of your user-defined functions.

Figure 2.10

In the Insert Function dialog box, choose the User Defined category to see a list of your user-defined functions.

4. Click the function you want to insert and then click OK. The Function Arguments dialog box appears.

5. Specify values or cell addresses for the function arguments and then click OK. Excel inserts the function.

0 0

Post a comment