Chapter 40: Creating Custom Worksheet Functions

Wouldn’t it be easier if Excel had a function named TopAvg? For example, you could use the following (nonexistent) function to compute the average:

=TopAvg (Data,5)

This situation is an example of when a custom function can make things much easier for you. The following is a custom VBA function, named TopAvg, which returns the average of the top n values in a range:

Function TopAvg(Data, Num)

‘Returns the average of the highest Num values in Data Sum = 0

For i = 1 To Num

Sum = Sum + WorksheetFunction.Large(Data, i)

Next i

TopAvg = Sum / Num

End Function

This function takes two arguments: Data (which represents a range in a worksheet) and Num (the number of values to average). The code starts by initializing the Sum variable to 0. It then uses a For-Next loop to calculate the sum of the nth largest values in the range. (Note that Excel’s LARGE function is used within the loop.) You can use an Excel worksheet function in VBA if you precede the function with WorksheetFunction and a dot. Finally, TopAvg is assigned the value of Sum divided by Num.

You can use all Excel worksheet functions in your VBA procedures except those that have equivalents in VBA. For example, VBA has a Rnd function that returns a random number. Therefore, you can’t use Excel’s RAND function in a VBA procedure.

Debugging Custom Functions

Debugging a Function procedure can be a bit more challenging than debugging a Sub procedure. If you develop a function to use in worksheet formulas, an error in the Function procedure simply results in an error display in the formula cell (usually #VALUE!). In other words, you don’t receive the normal run-time error message that helps you to locate the offending statement.

When you’re debugging a worksheet formula, using only one instance of the function in your worksheet is the best technique. The following are three methods that you may want to use in your debugging:

•Place MsgBox functions at strategic locations to monitor the value of specific variables. Fortunately, message boxes in function procedures pop up when the procedure is executed. But make sure that you have only one formula in the worksheet that uses your function; otherwise, the message boxes appear for each formula that’s evaluated.

833

Part VI: Programming Excel with VBA

•Test the procedure by calling it from a Sub procedure. Run-time errors display normally, and you can either fix the problem (if you know what it is) or jump right into the debugger.

•Set a breakpoint in the function and then use the Excel debugger to step through the function. Press F9, and the statement at the cursor becomes a breakpoint. The code will stop executing, and you can step through the code line by line (by pressing F8). Consult the Help system for more information about using VBA debugging tools.

Inserting Custom Functions

The Excel Insert Function dialog box is a handy tool from which you can choose a worksheet function; you even can choose one of your custom worksheet functions. After you select a function, the Function Arguments dialog box prompts you for the function’s arguments.

Note

Function procedures that are defined with the Private keyword do not appear in the Insert Function dialog box. So, if you create a function that will be used only by other VBA procedures, you should declare the function by using the Private keyword. n

You also can display a description of your custom function in the Insert Function dialog box. To do so, follow these steps:

1.Create the function in a module by using the VB Editor.

2.Activate Excel.

3.Choose Developer Code Macros. Excel displays its Macro dialog box.

4.In the Macro dialog box, type the name of the function (in the Macro Name field).

Notice that functions don’t typically appear in this dialog box, so you must enter the function name yourself.

5.Click the Options button. Excel displays its Macro Options dialog box. (See Figure 40.3.)

6.Enter a description of the function and then click OK. The Shortcut key field is irrelevant for functions.

The description that you enter appears in the Insert Function dialog box.

834

Chapter 40: Creating Custom Worksheet Functions

FIGURE 40.3

Entering a description for a custom function. This description appears in the Insert Function dialog box.

Another way to provide a description for a custom function is to execute a VBA statement that uses the MacroOptions method. The MacroOptions method also lets you assign your function to a specific category, and even provide a description of the arguments. The argument descriptions display in the Function Arguments dialog box, which appears after you select the function in the Insert Function dialog box.

New Feature

The ability to provide a description of function arguments is new to Excel 2010. n

Figure 40.4 shows the Function Arguments dialog box, which prompts the user to enter arguments for a custom function (TopAvg). This function appears in function category 3 (Math & Trig). I added the description, category, and argument descriptions by executing this Sub procedure:

Sub CreateArgDescriptions()

Application.MacroOptions Macro:=”TopAvg”, _

Description:=”Calculates the average of the top n values in a range”, _

Category:=3, _

ArgumentDescriptions:=Array(“The range that contains the data”, _

“The value of n”)

End Sub

The category numbers are listed in the VBA Help system. You execute this procedure only one time. After executing it, the description, category, and argument descriptions are stored in the file.

835

Part VI: Programming Excel with VBA

FIGURE 40.4

Using the Function Arguments dialog box to insert a custom function.

Learning More

The information in this chapter only scratches the surface when it comes to creating custom functions. It should be enough to get you started, however, if you’re interested in this topic. See Chapter 44 for more examples of useful VBA functions. You may be able to use the examples directly or adapt them for your needs.

836