Excel 2019 Bible

Part VI: Automating Excel

argument descriptions display in the Function Arguments dialog box, which appears after

you select the function in the Insert Function dialog box. Excel 2010 added the ability to

provide descriptions of function arguments.

Figure 43.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 and Trig). We’ve 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 you execute it, the description, category, and argument descriptions are

stored in the file.

FIGURE 43.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 cus-

tom functions. It should be enough to get you started, however, if you’re indeed interested

in this topic.

See Chapter 47, “Seeing Some VBA Examples,” for more examples of useful VBA functions. You may be

able to use the examples directly or adapt them for your needs.