?
Solved

Custom Function Category - Excel 4 Macro Sheet

Posted on 2000-03-17
12
Medium Priority
?
1,023 Views
Last Modified: 2008-02-20
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.....

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

    TheNewCategory = "My New Category"
    Workbooks.Add
    Worksheets.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:="MyFunction1", Category:=TheCategoryID
    ActiveWorkbook.Application.MacroOptions Macro:="MyFunction2", Category:=TheCategoryID

    ActiveWorkbook.Names("Test1").Visible = False      'or .Delete
    ActiveWorkbook.Names("Test2").Visible = False      'or .Delete
   
    ActiveWorkbook.Close (False)

End Sub
0
Comment
Question by:leedurrant
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
12 Comments
 
LVL 32

Expert Comment

by:Brendt Hess
ID: 2630240
You need to use the .Item property of the .Names collection to delete a name.  Try this:



    ActiveWorkbook.Names.Item("Test1").Delete
    ActiveWorkbook.Names.Item("Test2").Delete
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 2630418
Hi Leedurrant,

I've tested your code, and the lines you posted/used seem to work correctly for me:

ActiveWorkbook.Names("Test1").Visible = False
ActiveWorkbook.Names("Test2").Visible = False

The behaviour is strange though, the delete seems to delete the names, but they remain in the Function Wizard Categories List. The Visible = False propery hides the names in my test.

0
 

Author Comment

by:leedurrant
ID: 2631730
I have tried both of these recommendations, but the problem still remains.

For your information, the code is called from the ThisWorkbook Workbook_Open of an xla file.

Do either of you have any other ideas that will resolve this problem?

Thanks

Lee
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 17

Expert Comment

by:calacuccia
ID: 2631820
Hi Leedurrant,

I've put your code in the auto-open event of an xla file, and without any problem your original code works perfectly.

Afterwards I've tested this, works also, and maybe a solution:

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

    TheNewCategory = "My New Category"
    Workbooks.Add
    Worksheets.Add Type:=xlExcel4MacroSheet
    'add a test function to create the new category
     
    ActiveWorkbook.Names.Add Name:="MyFunction1", RefersTo:="sheet1!$a$1", _
        Category:=TheNewCategory, MacroType:=xlFunction
     
    'add a second function to the User Defined category
    ActiveWorkbook.Names.Add Name:="MyFunction2", 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:="MyFunction2", _
            Category:=TheCategoryID
        TheCurrentCategory = ActiveWorkbook.Application.Names("MyFunction2").Category
        If TheCurrentCategory = TheNewCategory Then
            Exit For
        End If
    Next i
     
    ActiveWorkbook.Close (False)
End Sub

In the Workbook open event I just placed this code:

Private Sub Workbook_Open()
AddFunctionToCategory
End Sub

Hope this helps

Calacuccia
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 2698887
Any better, Leedurrant ?
0
 

Author Comment

by:leedurrant
ID: 2702059
Sorry Calacuccia,

I did type out a comment, but it seems to have dissappeared.

Your previous suggestion is the way in which I am actually calling the macro at the moment.

The problem is still unsolved. I am not sure why it is that it doesn't delete/hide the functions for me.
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 2855282
Leedurrant:

I suggest you delete this question and ask a new one to get new attention if the problem persists..

Calacuccia (out of ideas myself)
0
 

Author Comment

by:leedurrant
ID: 2856540
Calacuccia,

I resolved this problem about a month ago. Sorry, I didn't realize that the question was still outstanding.

FYI, I changed the function names and found that the Test1 and test2 names still appeared. From this I realized that the functions were actually kind of imbedded into the add-in file from when I didn't call the delete line.

I simply copied all of the coding into a new add-in and hey-presto it worked.

Thanks for your help on this one anyway
0
 
LVL 17

Accepted Solution

by:
calacuccia earned 400 total points
ID: 2856546
You can delete this question now as you have solved it yourself, or grade it for the persistence of me telling you it should have worked ;-)

Your call.

Calacuccia
0
 

Author Comment

by:leedurrant
ID: 2859984
Comment accepted as answer
0
 

Author Comment

by:leedurrant
ID: 2859985
Go on then, I think you've helped me enough whilst I've been building this add-in.

Thanks again....
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 2859990
My Pleasure, LeeDurrant.

Hope to be able to help you again later, and allow me to learn from it, as I certainly did with your custom function categories.

Cheers !
Calacuccia
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question