Adding a Description and Category to your User Defined Functions in Excel

Got any Excel/VBA Questions? Free Excel Help.

There are at least 2 ways you can use to add a description to you UDF's, one is relatively well known, while the other is a simple but little known method. Lets use the better known method first.

Open Up The VBE (Alt+F11) And Select Anywhere Within Your Function Code.
Now Push F2 To Open The "Object Browser".
At The Top Of The Object Browser There Are 2 Drop Down Boxes. Click The Top One And Select "VBAProject".
You Should Now Have All Modules And Global Objects Showing In The "Classes" Box Situated At The Bottom Of The Object Browser.
Click On The Name Of The Module That Houses Your UDF.
In The "Members Of..." Box To The Right You Should See The Names Of All Functions And Procedures Within The Selected Module.
Simply Right Click On The Name Of Your UDF And Select "Properties".
Type A Description For Your UDF, Then Click Ok And Then Save.

Now here is the little known method. This is best done before you write your UDF.

Go To Tools>Macro>Record New Macro.
In The Description Box, Type A Description For Your UDF. Type The Name Of Your UDF And Click Ok.
Now Stop Recording.
Open The VBE (Alt+F11) And Replace The Word "Sub" With "Function"
Add Your Function Arguments As Normal.

How to add your UDF to a category

Another common question is, how to add your UDF to a certain category in the "Paste Function" dialog. By default all UDF's are added to the "User Defined" category. Here is how you can change this.

Simply run some code like this:

Application.MacroOptions Macro:="MyCustomFunction", _

 Category:=3'Add to Math & Trig

The category numbers are as shown below:

0 No category appears only in All
1 Financial
2 Date & Time
3 Math & Trig
4 Statistical
5 Lookup & Reference
6 Database
7 Text
8 Logical
9 Information
10 Commands normally hidden
11 Customizing normally hidden
12 Macro Control normally hidden
13 DDE/External normally hidden
14 User Defined default
15 Engineering only available if the Analysis Toolpak add-in is installed

How to add a new category

By default when you write a Custom function (UDF) it is added to the User Defined by default. Although not well known, we can easily create our own category(s) to keep all our Custom functions in, here's how

Right-Click Any Sheet Name Tab And Select Insert. Now Select MS Excel 4.0 Macro And Click OK To Add A Macro Sheet To The Workbook.
Go To Insert>Name>Define, Or Push Ctrl+F3. Within The Macro Box (Near Bottom Right), Click Function.
In The Names In Workbook: Box, Type Any One Word Name, I'll Use MyFunction .
From The Category Box Select User Defined. Now Replace "User Defined" With Your New Category Name, And Then Click OK.

Microsoft Excel will add the new category to the list for you. Once you have added at least one custom function to the new category (it will be probably be # 16), you can delete the "MyFunction" (or whatever name you used) name we typed in step 3. The new category will remain. If you delete the Microsoft Excel 4.0 macro sheet, functions already assigned to the custom category remain in that category; however, you cannot assign a new function to that category after you delete the macro sheet.

Gallery

stars (0 Reviews)

Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
OzGrid is in no way associated with Microsoft.

Website designed by Astrava Solutions Ltd.