Excel VBA Video Training/ EXCEL DASHBOARD REPORTS

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 .

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 Become an ExcelUser Affiliate & Earn Money

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 [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Excel VBA Video Training/ EXCEL DASHBOARD REPORTS

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