Add Excel UDF/Custom Function to a Category & Add a Description

< Back to Search results

 Category: [Excel]  Demo Available 

Add Excel UDF/Custom Function to a Category & Add a Description


Redefine a Range so That no Headings are Included

Got any Excel Questions? Excel Help.


Excel: Add Description & Category for UDF

Excel custom functions are great way to eliminate horribly long nested functions and/or to use ones own custom calculations. However, when a Custom Function (UDF/User Defined Function) is written it is, by default, added to the User Defined category of the Insert Function dialog. With the use of the MacroOptions Method we can add any Custom Function to any category, or add it to a custom category that we create. We can also use MacroOptions Method to write a brief description about our Custom Function.

MacroOptions Method

Syntax: ALL arguments are optional.
MacroOptions(Macro, Description, HasMenu, MenuText, HasShortcutKey, ShortcutKey, Category, StatusBar, HelpContextID, HelpFile)

The following table lists which numbers correspond to the built-in categories that can be used in the Category parameter.

2=Date & Time
3=Math & Trig
5=Lookup & Reference
12=Macro Control
14=User Defined
15=First custom category

The MacroOptions Method is a method of the Application Object and corresponds to options in the Macro Options dialog box. We can also use this method to display a User Defined Function (UDF) in a pre-existing built-in category or new category within the Insert Function dialog box. The main thing you must be aware of is that the custom category, if we specify one, is not retained be Excel sessions. This means we should use the Workbook Open Event to call our Procedure for adding a UDF to a category and adding a description.

The code below shows the use of the MacroOptions Method. In this case it will add a Function called ColorFunction to its own category called "Color Functions" and add a description. If this category does not yet exist, it will create it for you.

Sub AddCategoryDescription()

    Application.MacroOptions Macro:="ColorFunction", _

        Description:="Sums or counts cells based on a specified fill color", _

        Category:="Color Functions"

End Sub

Add More Than One Function, Description & Category

It's often the case that more than one custom function needs to be added. In cases like these we can use the Choose function and a simple For Loop .

Sub AddManyCategoryDescription()

Dim strFunction As String

Dim strDescript As String

Dim vCat

Dim lLoop As Long

    With Application


        For lLoop = 1 To 3


            'Pass function name

            strFunction = Choose(lLoop, "ColorFunction", _

                          "StatsFunction", "DatabaseFunction")


            'Pass function description

            strDescript = Choose(lLoop, _

                         "Sums or counts cells based on a specified fill color", _

                         "A statistical function", "A database function")


            'Pass function category

            vCat = Choose(lLoop, "Color Functions", 4, 6)


            .MacroOptions Macro:=strFunction, Description:=strDescript, Category:=vCat


        Next lLoop


    End With

End Sub


See also:

Index to Excel VBA Code
Show/Hide a Custom Toolbar & Remove/Restore Excel's Toolbars
Track/Report User Changes on an Excel Worksheet/Workbook
Transfer Multi-Select ListBox To Range Of Cells
Transpose Rows Into Columns
Excel Lookup nth Occurrence/Instance


See also Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions.


Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.



stars (0 Reviews)