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.
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
See also:
| Index to Excel VBA Code |
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.
Gallery
