[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Custom Function Category - Excel 4 Macro Sheet

Posted on 2000-03-17
12
Medium Priority
?
1,026 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
With its various features, Office 365 can not only help you with your day-to-day business tasks, it can also do wonders for your marketing campaign.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

650 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