Creating New Function Category

I have created some functions which reside in an excel add-in file. At present they appear in the "User-defined" function category.

Is it possible to create a new category and attach them to this new category? If so how do I do this.
leedurrantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

calacucciaCommented:
Hi leedurrant,

You can find lots of info in this MSKB article:

http://support.microsoft.com/support/kb/articles/Q137/5/26.asp

The important part (tested):

######################################
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.


5-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.

######################################
Now to have these custom categories, put this Excel4 sheet in the personal.xls or in another permanently present sheet.

Good Luck

Calacuccia
0
calacucciaCommented:
0
calacucciaCommented:
Hi Leedurrant,

How are you doing ?

Grading to an answer ...
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

leedurrantAuthor Commented:
Calacuccia,

Although your answer details how to create a new category (and I have learnt something new), it does not work when the excel file is an add-in. I cannot seem to get the MS Excel 4.0 Macro sheet to transfer into the add-in.

I believe that I need to create the category in code somehow. Is this possible?

Thanks

Lee
0
calacucciaCommented:
Hi Leedurrant,

I don't know if this will work, I've had a mass of Excel shutdowns trying this, and the performance is very unpredictible, but at least it works from time to time. It could be linked to corrupt files in my Excel installation, I have to re-install one of these days.

Here it comes:

Sub AddCategory()
Application.Names.Add Name:="Test10", RefersTo:="sheet1!$a$2", Category:="NewCategory"
End Sub

Sub AssignCategoryToFunction()
Application.MacroOptions Macro:="YourFunction", Category:=1
End Sub

Sub DeletePhantomName()
Application.Names("Test10").Delete
End Sub

The first macro is just a trick to insert a new function category (NewCategory), with a phantom reference in it.

The second macro is the important one, it assigns a category (must be existing) to your function. You have to use a number reference for the category, and have to run through different categories to find the correct one. (Little hint, they seem to be numbered from top to bottom, Last Used and All categories excluded).
Type MacroOptions in VBE help to find more info.

The third macro is just to delete the phantom name "Test10"

Good luck

Calacuccia
0
leedurrantAuthor Commented:
Adjusted points to 200
0
leedurrantAuthor Commented:
Calacuccia,

Thankyou....

It's now working, and I have even managed to resolve the problem mentioned in  http://support.microsoft.com/support/kb/articles/q141/8/25.asp

Take a look at the following....

Sub AddFunctionToCategory()
Dim TheCategoryID As Integer
Dim TheNewCategory As String
Dim TheCurrentCategory As String


TheNewCategory = "A New Category"

    Sheets.Add Type:=xlExcel4MacroSheet
    'add a test function to create the new category
    ActiveWorkbook.Names.Add Name:="Test1", RefersTo:="sheet1!$a$1", _
        Category:=TheNewCategory, MacroType:=xlFunction
    '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", _
            Category:=TheCategoryID
        TheCurrentCategory = ActiveWorkbook.Application.Names("Test2").Category
        If TheCurrentCategory = TheNewCategory Then
            Exit For
        End If
    Next i
   
    ActiveWorkbook.Application.MacroOptions Macro:="1ofmyAddInFunctions", Category:=TheCategoryID
    ActiveWorkbook.Application.MacroOptions Macro:="2ofmyAddInFunctions", Category:=TheCategoryID
   
    ActiveWorkbook.Names("Test1").Visible = False
    ActiveWorkbook.Names("Test2").Visible = False
End Sub

What do you think...

Couldn't get the names.delete to work though.

Thanks for your input on this, and I am so happy that I've increased the points to 200...

Come and get them.

Lee

0
calacucciaCommented:
Hi Leedurrant,

(grading to an answe....)

I'm glad you found a workaround. Your solution is indeed very inventive and shows once again that where Microsoft throws the towel, there's still plenty of possibilities to explore.

I don't know about the names thing. If your hiding method satisfies you, that's a good method. I'll try to find something for that (not immediately though, I don't have the time right now).

I learned a lot from this too, and hope to use this new knowledge very soon.

Cheers

Calacuccia
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
leedurrantAuthor Commented:
Thanks again
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.