<<Convert Excel Spreadsheets to Web Pages \| Trading Software That Operates Within Excel \| Convert Excel, Access & Other Databases \| Merge Excel Files>>

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

| | Information Helpful? Why Not Donate.

TRY OUT: Smart-VBA | Code-VBA | Analyzer-XL | Downloader-XL | Trader-XL| More Free Downloads.. Best Value: Finance Templates Bundle

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 Functionsare 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 Eventto 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 ColorFunctionto 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 simpleFor Loop .

Sub AddManyCategoryDescription()Dim strFunction As StringDim strDescript As StringDim vCatDim 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 WithEnd 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. ALLpurchases 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

Try out:Analyzer XL |Downloader XL |Smart VBA |Trader XL Pro (best value) |ConsoXL | MergeXL | O2OLAP for Excel | MORE>>

Excel Trader PackageTechnical 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