XL: How to Add a New Category to Function Category List

ID: Q137526


The information in this article applies to:
  • Microsoft Excel for Windows, versions 5.0, 5.0c
  • Microsoft Excel for the Macintosh, version 5.0
  • Microsoft Excel 98 Macintosh Edition
  • Microsoft Excel for Windows NT, version 5.0
  • Microsoft Excel for Windows 95, version 7.0
  • Microsoft Excel 97 for Windows


SUMMARY

To manually create a custom category for a user-defined function that you write in Microsoft Visual Basic for Applications, you must insert a Microsoft Excel 4.0 macro sheet before you create the category.


MORE INFORMATION

To create a custom category, follow these steps:

  1. In Microsoft Excel 97, right-click a sheet tab in the workbook and click Insert. In Microsoft Excel 98 Macintosh Edition, hold the Control key and click a sheet tab in the workbook and click Insert. Click MS Excel 4.0 Macro and click OK to add a macro sheet to the workbook.

    In Microsoft Excel 5.0 and 7.0, point to Macro on the Insert menu, and click MS Excel 4.0 Macro to add a macro sheet to the workbook.


  2. On the Insert menu, point to Name, and then click Define. Under Macro, click Function.


  3. In the Name box, type TEST1.


  4. In the Category box, replace "User Defined" with the new category, and then click OK.


Microsoft Excel adds the new category to the list. After you add one or more custom functions to the new category, you can delete the "TEST1" function name. When you do this the new category remains. If you delete the Microsoft Excel 4.0 macro sheet, functions already assigned to the custom category remain in that category; however, you cannot assign a new function to that category after you delete the macro sheet.

Excel 97 for Windows and Excel 98 Macintosh Edition

You cannot add a user-defined function to a specific category by selecting the function in a dialog box. You must use a macro to specify a category for the function. For additional information, please see the following article in the Microsoft Knowledge Base:
Q157108 Options not Available in Macro Options Dialog Box

Microsoft Excel 5.0 and 7.0

To specify a category for a user-defined function, follow these steps:
  1. With a module sheet active, press F2 to start the Object Browser


  2. Select the workbook in the Libraries/Workbooks list.


  3. Select the custom function in the Methods/Properties list, and then click Options.


  4. Select the category in the Function Category list, and then click OK.


  5. Click Close to close the Object Browser.



REFERENCES

"User's Guide 2," version 4.0, Chapter 5, "To Name a custom function," page 210

For additional information, please see the following article(s) in the Microsoft Knowledge Base:

Q141825 XL: Custom Function in the Wrong Category in Function Wizard

Additional query words: 5.00c 7.00a 8.00 howto udf vbe

Keywords : kbprg kbdta PgmHowto KbVBA
Version : MACINTOSH:5.0,98; WINDOWS:5.0,5.0c,7.0; winnt:5.0
Platform : MACINTOSH WINDOWS winnt
Issue type : kbhowto


Last Reviewed: November 9, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.