Excel 2010 Bible

Chapter 40: Creating Custom Worksheet Functions

835

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 dis-

play 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.