How to Use the Insert Function Dialog Box in Excel 2016 VBA - dummies

How to Use the Insert Function Dialog Box in Excel 2016 VBA

By John Walkenbach

The Insert Function dialog box is a handy Excel tool that lets you choose a VBA worksheet function from a list and prompts you for the function’s arguments. Your custom worksheet functions also appear in the Insert Function dialog box. Custom functions appear in the User Defined category.

Function procedures defined with the Private keyword do not appear in the Insert Function dialog box. Therefore, if you write a Function procedure that’s designed to be used only by other VBA procedures (but not in formulas), you should declare the function as Private.

Displaying the function’s description

The Insert Function dialog box displays a description of each built-in function. But as you can see, a custom function displays the following text as its description: No help available.

By default, the Insert Function dialog box does not provide a description for custom functions.
By default, the Insert Function dialog box does not provide a description for custom functions.

To display a meaningful description of your custom function in the Insert Function dialog box, perform a few additional (nonintuitive) steps:

  1. Activate a worksheet in the workbook that contains the custom function.

  2. Choose Developer  →  Code  →  Macros (or press Alt+F8).

    The Macro dialog box appears.

  3. In the Macro Name field, type the function’s name.

    Note that the function does not appear in the list of macros; you must type the name.

  4. Click the Options button.

    The Macro Options dialog box appears.

  5. In the Description field, type a description of the function.

  6. Click OK.

  7. Click Cancel.

    Now the Insert Function dialog box displays the description of your function.

    The custom function now displays a description.
    The custom function now displays a description.

Custom functions, by default, are listed in the User Defined category. To add a function to a different category, you need to use VBA. This statement, when executed, adds the TopAvg function to the Math & Trig category (which is category 3):

Application.MacroOptions Macro:=“TopAvg”, Category:=3

Check the Help system for other category numbers.

You need to execute this statement only one time. After you execute it (and save the workbook), the category number is permanently assigned to the function.

Adding argument descriptions

When you access a built-in function from the Insert Function dialog box, the Function Arguments dialog box displays descriptions of the arguments.

By default, the Function Arguments dialog box displays Function argument descriptions for built-in
By default, the Function Arguments dialog box displays Function argument descriptions for built-in functions only.

In the past, it was not possible to add argument descriptions. But beginning with Excel 2010, Microsoft finally implemented this feature. You provide argument descriptions by using the MacroOptions method. Here’s an example that adds descriptions for the arguments used by the TopAvg function:

Sub AddArgumentDescriptions()
  Application.MacroOptions Macro:=“TopAvg”, _
    ArgumentDescriptions:= _
    Array(“Range that contains the values”, _
    “Number of values to average”)
End Sub

You need to execute this procedure only one time. After you execute it, the argument descriptions are stored in the workbook and are associated with the function.

Notice that the argument descriptions appear as arguments for the Array function. You must use the Array function even if you’re assigning a description for a function that has only one argument.