How Do I add a UDF to a Tool Menu

[trevor101]

I have created several Functions and would like to add them to a custom Menu. I know how to create custom menus from the Customise and Commands options and how to assign a macro, but can't for the life of me see how to add functions.
Any one know how?

Thanks in advance

 

[mikerickson]

If these are UDF's to be called from a spreadsheet formula, they are already in the Insert Function dialog box, under "User Defined Functions".

Would that work for you?

 

[trevor101]

Thank you for the response, but, I was specifically after attaching them to a menu, rather than go through the Insert Function & User Defined route. E.g. I have one to reverse the text, and would like a sub-menu item called reverse text.

 

[mikerickson]

When the user click the toolbar's "Reverse Text" button, what do you want to happen?

If ReverseText is a sub, a menu item makes sence, but if it is a function, functions return values, how would the user pass an argument to the function and where would the value be returned?

What version of Excel are you using?
In 2004 (and 2003 I think) this can be done by using the CommandBars collection of the Application object. Manipulating Application.CommandBars("Worksheet Menu Bar") and its controls would be involved.

 

[trevor101]

Verison would be 2003
I was envisaging the user being 'in' a cell and instead of using paste insert to enter a formula they would select the appropriate menu item, this would then enter the formula and the user would have to select the various cells.
I suppose the nearest I could visualise it would be almost like replicating the fx button; thereby having my custom functions come up straight away for the user to initially select a group such as text functions or arithmetic functions and then choosing the desired function from within that option.

 

[tusharm]

Yes, you would essentially be trying to duplicate the functionality of the Fx button. Rather like trying to reinvent the wheel, don't you think? I would think it would be a lot better -- easier, more transparent, simpler to maintain, and more intuitive -- to let Excel integrate your functions into its existing set of functions.

It is possible to create your own category of functions. Search Google but from what I remember, the result is not 100% robust and reliable if someone else also tries to use custom function categories.

Verison would be 2003
I was envisaging the user being 'in' a cell and instead of using paste insert to enter a formula they would select the appropriate menu item, this would then enter the formula and the user would have to select the various cells.
I suppose the nearest I could visualise it would be almost like replicating the fx button; thereby having my custom functions come up straight away for the user to initially select a group such as text functions or arithmetic functions and then choosing the desired function from within that option.