User Defined Categories

By default any user defined functions will be added to the User Defined category in the (Insert > Functions) dialog box.
It is possible to create a new category to store your functions but it involves using an Excel 4.0 Macro.
There is no direct way to add a function to a function category when it is created.
By default they will all be added to the User Defined category.
The table below lists the category names with their corresponding numbers.

0All (no specific category)10Commands (normally hidden but visible if you add a function to this category)
1Financial11Customizing (normally hidden but visible if you add a function to this category)
2Date & Time12Macro Control (normally hidden but visible if you add a function to this category)
3Maths & Trigonometry13DDE / External (normally hidden but visible if you add a function to this category)
4Statistical14User Defined (default)
5Lookup & Reference15Engineering
6Database16Cube
7Text17First custom category
8Logical18Second custom category
9Information  

You can define the category for your functions when the workbook or add-in is opened.

Private Workbook_Open() 
   Application.MacroOptions macro:="Macro Name", Category:=2
End Sub

Insert an Excel 4.0 Macro worksheet

Right click a worksheet and select the Insert button.
Select "MS Excel 4.0 Macro" and click OK.
This will insert a new Excel 4.0 Macro worksheet into your workbook (or add-in).

microsoft excel docs

Add the new Function Category

Select (Insert > Name > Define) and in the bottom right click "Function".
Type the name of your category in the "Names in workbook" box.

microsoft excel docs

The new category will be added to the list in the (Insert > Functions) dialog box.
Once you have added at least one function to this new category you can actually delete the Macro 4.0 worksheet.
You will not be able to add any more functions to the category once the Macro 4.0 worksheet has been deleted.



© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext