Excel 2019 Bible

Chapter 43: Creating Custom Worksheet Functions

43

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.

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


2. Activate Excel.


3. Choose Developer ➪ Code ➪ Macros. The Macro dialog box appears.


4. Type the name of the function in the Macro Name field. Notice that functions
   don’t appear in this dialog box, so you must enter the function name yourself.


5. Click the Options button. The Macro Options dialog box appears, as shown in
   Figure 43.3.


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

FIGURE 43.3

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

dialog box.

The description that you enter 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