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

NEW! More Books..

Add Excel Answers & Search To Your Google Toolbar Details

Redefine a Range so That no Headings are Included

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only ~~$145.00~~. $59.95 Instant Buy/Download, 30 Day Money Back Guarantee & Free Excel Help for LIFE!

Got any Excel Questions? Free Excel Help .

Lot's More: Excel VBA . See Also: Manually Adding UDF to Category and Add Description

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.

1=Financial
2=Date & Time
3=Math & Trig
4=Statistical
5=Lookup & Reference
6=Database
7=Text
8=Logical
9=Information
10=Commands
11=Customizing
12=Macro Control
13=DDE/External
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

Lot's More: Excel VBA . See Also: Manually Adding UDF to Category and Add Description

Excel Dashboard Reports & Excel Dashboard Charts 50% Off

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to special@ozgrid.com 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

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