The following code will add a custom category to the excel function wizard, and then assign all of my custom built functions to that category. The routine even solves a problem which microsoft states as having no solution.
However, it uses a test1 and test2 function in order to create the category and evaluate it's Category ID.
The problem is that I cannot seem to hide or delete the Test1 and Test2 functions once the custom category has been created. I do not want the Test1 and Test2 functions to be visible in the functions wizard.
I know that it is something to do with the fact that they were created in the Excel 4 macro sheet, but how do I stop them from showing.
And now the code.....
Dim TheCategoryID As Integer
Dim TheNewCategory As String
Dim TheCurrentCategory As String
TheNewCategory = "My New Category"
'add a test function to create the new category
ActiveWorkbook.Names.Add Name:="Test1", RefersTo:="sheet1!$a$1", _
'add a second function to the User Defined category
ActiveWorkbook.Names.Add Name:="Test2", RefersTo:="sheet1!$a$1", _
Category:="User Defined", MacroType:=xlFunction
For i = 1 To 100
'Set the category of function 2 to i and see if the
'category name matches the new category
TheCategoryID = i
ActiveWorkbook.Application.MacroOptions Macro:="Test2", _
TheCurrentCategory = ActiveWorkbook.Application.Names("Test2").Category
If TheCurrentCategory = TheNewCategory Then
ActiveWorkbook.Application.MacroOptions Macro:="MyFunction1", Category:=TheCategoryID
ActiveWorkbook.Application.MacroOptions Macro:="MyFunction2", Category:=TheCategoryID
ActiveWorkbook.Names("Test1").Visible = False 'or .Delete
ActiveWorkbook.Names("Test2").Visible = False 'or .Delete