20.4 Adding Descriptions to Functions

You may have noticed that the UDFs we’ve written do not have IntelliSense function, and they lacked descriptions in the Function Arguments dialog box. Unfortunately, we can’t provide the exact same experience as built-in functions, but we can provide some additional information to help individuals when they’re interacting with our UDFs.

You’ll remember the user function we created that allows us to pull the name of the user from the Excel options.

Function USER() As String
     USER = Application.UserName
End Function

This function works exactly how we’d like it to. However, we had to know it existed and how to use it. There is a way to get a list of all UDFs loaded on your computer. Under the Formulas menu, you can use the Insert Function option to see all UDFs.

Figure 20.3: Functions can be inserted from the Formulas menu.

The Insert Function dialog box allows you to see all functions for a variety of categories, including the User Defined category. Selecting a specific category will display functions in only that category. Note that each new function we write is automatically placed in the User Defined category. In my open worksheets, I have four UDFs.

Figure 20.4: All loaded UDFs can be seen from the Insert Function dialog box.

You can insert a function in the current cell by double-clicking on the function, which will bring up the Function Arguments dialog box for that function. In this case, we don’t have to specify any arguments, and there isn’t any help available, but it does reveal what the result of the function will be.

Figure 20.5: Function Arguments for USER function.

It would be really nice if there were some sort of description associated with that function to give users an idea of what it’s trying to accomplish. We can add a description to our own UDFs by setting options for them using the Macros option found on the Developer menu.

Figure 20.6: Macro dialog box.

Notice that our functions do not show up in the list of macros. That’s okay. Go ahead and just type the name of the function, USER, in the line under Macro name. Next, click Options. The Macro Options dialog box will allow us to provide a description of the function.

Figure 20.7: Set function descriptions using the Macro Options dialog box.

Type in the description, “Returns the user’s name found on the computer.” Click OK.

Now, when we click on the fx button (the Insert Function option) on the Formulas menu, you’ll notice that the description for USER now shows in the Insert Function dialog box. So, it’s a little bit wonky to add that description, but that’s how we do it—at least historically.

Now, under the Formulas menu, we have lots of different categories of functions. For example, we have one for Text functions, another for Financial functions, another for Date & Time, etc. What if we wanted to add this USER function to the Text category? How would you do that?

The following code will add the USER function to the Text functions category. The Text functions are category number 7.

It’s probably easiest to just execute this code in the Immediate window. Go ahead and copy and paste the above code snippet. When you hit Enter in the Immediate window, the code won’t produce any feedback. However, check to make sure the USER function is now found under category 7, Text functions.

Figure 20.8: USER function is categorized with other Text functions.

If we wanted to, we could create our own category for functions by using code similar to the following code.

Application.MacroOptions Macro:="USER", Category:="My Functions"

We can change the category name to whatever we’d like. These functions will show up under the Insert menu, under the category name we specify, but they won’t be found elsewhere on the menu. However, if we have several functions related to our domain or specialization, it might be nice to have them all organized together.

Previous Next