Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding hints to my vb functions
I've written several user-defined functions in VB. How do I add the
information or tips that appear at the bottom of the "insert function" dialog box so my users will have an idea of what the function does? Thanks, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding hints to my vb functions
Use the MacroOptions property of the Application object to do that...
Application.MacroOptions Macro:="FunctionName", Description:="Text to show" where you would replace "FunctionName" with your function's name (with quote marks around it as in my example) and the "Text to show" description with the description you want to show for your function. Note that you can't put this statement inside your function... it needs to be run outside of it, perhaps in the Workbook_Open event procedure. -- Rick (MVP - Excel) "BJV" wrote in message ... I've written several user-defined functions in VB. How do I add the information or tips that appear at the bottom of the "insert function" dialog box so my users will have an idea of what the function does? Thanks, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding hints to my vb functions
By the way, while I haven't tried to play much with them, there are other
options available in the MacroOptions property. The most noteworthy, in my opinion, is the Category options where you can set which category of functions your own function will be listed with. See the help files for the MacroOptions property (and the example in the Remarks sections) to see how to use this option. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Use the MacroOptions property of the Application object to do that... Application.MacroOptions Macro:="FunctionName", Description:="Text to show" where you would replace "FunctionName" with your function's name (with quote marks around it as in my example) and the "Text to show" description with the description you want to show for your function. Note that you can't put this statement inside your function... it needs to be run outside of it, perhaps in the Workbook_Open event procedure. -- Rick (MVP - Excel) "BJV" wrote in message ... I've written several user-defined functions in VB. How do I add the information or tips that appear at the bottom of the "insert function" dialog box so my users will have an idea of what the function does? Thanks, |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding hints to my vb functions
Sounds great. Thanks
"Rick Rothstein" wrote: By the way, while I haven't tried to play much with them, there are other options available in the MacroOptions property. The most noteworthy, in my opinion, is the Category options where you can set which category of functions your own function will be listed with. See the help files for the MacroOptions property (and the example in the Remarks sections) to see how to use this option. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Use the MacroOptions property of the Application object to do that... Application.MacroOptions Macro:="FunctionName", Description:="Text to show" where you would replace "FunctionName" with your function's name (with quote marks around it as in my example) and the "Text to show" description with the description you want to show for your function. Note that you can't put this statement inside your function... it needs to be run outside of it, perhaps in the Workbook_Open event procedure. -- Rick (MVP - Excel) "BJV" wrote in message ... I've written several user-defined functions in VB. How do I add the information or tips that appear at the bottom of the "insert function" dialog box so my users will have an idea of what the function does? Thanks, |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding hints to my vb functions
You can also do this manually.
With your UDF workbook open got to VBE, select your workbook/project and ViewObject Browser Right-click on the UDF name and under Properties you can type a description. Will show up after saving the workbook/project. Gord Dibben MS Excel MVP On Sat, 7 Mar 2009 07:34:06 -0800, BJV wrote: I've written several user-defined functions in VB. How do I add the information or tips that appear at the bottom of the "insert function" dialog box so my users will have an idea of what the function does? Thanks, |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding hints to my vb functions
Doh! It never occurred to me to right-click the entry. Okay, I see you can
do the definition there, and that is probably a better way to do it than my suggestion, but I don't see a way to set the Category from this dialog box... can the Category be set manually somewhere like this also, or does it have to be done in code using the method I posted? -- Rick (MVP - Excel) "Gord Dibben" <gorddibbATshawDOTca wrote in message ... You can also do this manually. With your UDF workbook open got to VBE, select your workbook/project and ViewObject Browser Right-click on the UDF name and under Properties you can type a description. Will show up after saving the workbook/project. Gord Dibben MS Excel MVP On Sat, 7 Mar 2009 07:34:06 -0800, BJV wrote: I've written several user-defined functions in VB. How do I add the information or tips that appear at the bottom of the "insert function" dialog box so my users will have an idea of what the function does? Thanks, |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding hints to my vb functions
On Sat, 07 Mar 2009 10:02:22 -0800, Gord Dibben <gorddibbATshawDOTca wrote:
You can also do this manually. With your UDF workbook open got to VBE, select your workbook/project and ViewObject Browser Right-click on the UDF name and under Properties you can type a description. Will show up after saving the workbook/project. Gord, Although that method seems to work OK for a VBA project, I tried it with an **add-in** and the description "doesn't take". By that I mean if I right-click on the UDF name; enter something in the Property dialog box; hit <OK. When I immediately re-open the property dialog box, the entry is blank. The MacroOptions mentioned by Rick seems to not only work, but, after running, the description will be in the properties dialog box for my add-in. --ron |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding hints to my vb functions
On Sat, 7 Mar 2009 11:51:53 -0500, "Rick Rothstein"
wrote: Use the MacroOptions property of the Application object to do that... Application.MacroOptions Macro:="FunctionName", Description:="Text to show" where you would replace "FunctionName" with your function's name (with quote marks around it as in my example) and the "Text to show" description with the description you want to show for your function. Note that you can't put this statement inside your function... it needs to be run outside of it, perhaps in the Workbook_Open event procedure. -- Rick (MVP - Excel) Rick, My UDF's and custom macros are all in an add-in. I can run the macro "normally" after Excel has loaded. e.g: ====================== Option Explicit Sub Assign() With Application .MacroOptions Macro:="RECount", _ Description:="Count of substrings matching Regex", _ Category:="Regular Expressions" .MacroOptions Macro:="Enable", Description:="Re-Enable Events" End With End Sub ============================== If I try to run this as in the Workbook_Open event of my add-in, it returns an error message -- ---------------------- Run-error '1004': Cannot edit a macro on a hidden workbook. Unhide the workbook using the Unhide command. ---------------------- This is true whether the macro is placed directly in the Workbook module; or if the Workbook_Open event "calls" the above Sub which is in a regular module. So what is the best way around this issue? Thanks. --ron |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding hints to my vb functions
Ron,
You can enter the description in Tools | Macro | Macros using the Option button. The add-in must be unlocked and you have to manually type in the Function name. The option button is enabled after the function name is entered. I then save the add-in. -- Jim Cone Portland, Oregon USA "Ron Rosenfeld" wrote in message Gord, Although that method seems to work OK for a VBA project, I tried it with an **add-in** and the description "doesn't take". By that I mean if I right-click on the UDF name; enter something in the Property dialog box; hit <OK. When I immediately re-open the property dialog box, the entry is blank. The MacroOptions mentioned by Rick seems to not only work, but, after running, the description will be in the properties dialog box for my add-in. --ron |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding hints to my vb functions
Have you tried adding:
On error resume next 'your code on error goto 0 Yeah, that should just suppress the error message. But I seem to recall it worked for me a longgggggg time ago. Ron Rosenfeld wrote: On Sat, 7 Mar 2009 11:51:53 -0500, "Rick Rothstein" wrote: Use the MacroOptions property of the Application object to do that... Application.MacroOptions Macro:="FunctionName", Description:="Text to show" where you would replace "FunctionName" with your function's name (with quote marks around it as in my example) and the "Text to show" description with the description you want to show for your function. Note that you can't put this statement inside your function... it needs to be run outside of it, perhaps in the Workbook_Open event procedure. -- Rick (MVP - Excel) Rick, My UDF's and custom macros are all in an add-in. I can run the macro "normally" after Excel has loaded. e.g: ====================== Option Explicit Sub Assign() With Application .MacroOptions Macro:="RECount", _ Description:="Count of substrings matching Regex", _ Category:="Regular Expressions" .MacroOptions Macro:="Enable", Description:="Re-Enable Events" End With End Sub ============================== If I try to run this as in the Workbook_Open event of my add-in, it returns an error message -- ---------------------- Run-error '1004': Cannot edit a macro on a hidden workbook. Unhide the workbook using the Unhide command. ---------------------- This is true whether the macro is placed directly in the Workbook module; or if the Workbook_Open event "calls" the above Sub which is in a regular module. So what is the best way around this issue? Thanks. --ron -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding hints to my vb functions
On Sat, 7 Mar 2009 11:19:52 -0800, "Jim Cone"
wrote: Ron, You can enter the description in Tools | Macro | Macros using the Option button. The add-in must be unlocked and you have to manually type in the Function name. The option button is enabled after the function name is entered. I then save the add-in. -- Jim Cone Portland, Oregon USA Thanks for that information. I think I may just put all the descriptions in code, though, using the MacroOptions property, especially since I can then specify the category also. By the way, any easy way of getting the macros in an add-in to show up in the macro dialog box? --ron |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding hints to my vb functions
On Sat, 07 Mar 2009 13:23:33 -0600, Dave Peterson
wrote: Have you tried adding: On error resume next 'your code on error goto 0 Yeah, that should just suppress the error message. But I seem to recall it worked for me a longgggggg time ago. I set up code like this: In my add-in workbook: Private Sub Workbook_Open() On Error Resume Next Assign On Error GoTo 0 End Sub and in a regular module: ------------------- Option Explicit Sub Assign() With Application .MacroOptions Macro:="RECount", _ Description:="Count of substrings matching Regex", _ Category:="Regular Expressions" .MacroOptions Macro:="Enable", Description:="Re-Enable Events" End With End Sub ===================== There was no error message. The "Enable" macro picked up its description. The RECount UDF neither picked up its description nor category. So although that technique seemed to work for the Macro, it did not work for the UDF. --ron |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding hints to my vb functions
"By the way, any easy way of getting the macros in an add-in to show up in the
macro dialog box?" Not that I know of. I've always been more concerned about Not having subs/functions show up there. '-- Jim Cone "Ron Rosenfeld" wrote in message On Sat, 7 Mar 2009 11:19:52 -0800, " Jim Cone" wrote: Ron, You can enter the description in Tools | Macro | Macros using the Option button. The add-in must be unlocked and you have to manually type in the Function name. The option button is enabled after the function name is entered. I then save the add-in. -- Jim Cone Portland, Oregon USA Thanks for that information. I think I may just put all the descriptions in code, though, using the MacroOptions property, especially since I can then specify the category also. By the way, any easy way of getting the macros in an add-in to show up in the macro dialog box? --ron |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding hints to my vb functions
So what is the best way around this issue?
To answer your question... I have no idea.<g I came up with the solution I offered by reading the help files and experimenting... I never did it before and it wouldn't have occurred to me to try to do it except for the fact the OP asked the question. -- Rick (MVP - Excel) "Ron Rosenfeld" wrote in message ... On Sat, 7 Mar 2009 11:51:53 -0500, "Rick Rothstein" wrote: Use the MacroOptions property of the Application object to do that... Application.MacroOptions Macro:="FunctionName", Description:="Text to show" where you would replace "FunctionName" with your function's name (with quote marks around it as in my example) and the "Text to show" description with the description you want to show for your function. Note that you can't put this statement inside your function... it needs to be run outside of it, perhaps in the Workbook_Open event procedure. -- Rick (MVP - Excel) Rick, My UDF's and custom macros are all in an add-in. I can run the macro "normally" after Excel has loaded. e.g: ====================== Option Explicit Sub Assign() With Application .MacroOptions Macro:="RECount", _ Description:="Count of substrings matching Regex", _ Category:="Regular Expressions" .MacroOptions Macro:="Enable", Description:="Re-Enable Events" End With End Sub ============================== If I try to run this as in the Workbook_Open event of my add-in, it returns an error message -- ---------------------- Run-error '1004': Cannot edit a macro on a hidden workbook. Unhide the workbook using the Unhide command. ---------------------- This is true whether the macro is placed directly in the Workbook module; or if the Workbook_Open event "calls" the above Sub which is in a regular module. So what is the best way around this issue? Thanks. --ron |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding hints to my vb functions
Ron,
"Category:="Regular Expressions"" Since you are trying to create a new category for your function, I believe the following notes I saved with one my own early add-ins apply... '-------------- 0 = All 11 = Customizing 1 = Financial 12 = Macro Control 2 = Date & Time 13 = DDE/External 3 = Math & Trig 14 = User Defined 4 = Statistical 15 = Engineering 5 = Lookup & Reference 6 = Database 7 = Text 8 = Logical 9 = Information 10 = Commands '-------------- XL2000: How to Add a New Category to Function Category List Article ID : 213813 To manually create a custom category for a user-defined function that you write in Microsoft Visual Basic for Applications, you must insert a Microsoft Excel 4.0 macro sheet before you create the category. To create a custom category, follow these steps: 1. In Microsoft Excel 2000, right-click a sheet tab in the workbook and then click Insert. 2. Click MS Excel 4.0 Macro, and then click OK to add a macro sheet to the workbook. 3. On the Insert menu, point to Name, and then click Define. Under Macro, click Function. 4. In the Name box, type TEST1. 5. In the Category box, replace User Defined with the new category name, and then click OK. 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. APPLIES TO Microsoft Excel 2000 Standard Edition '-- I also have a post from Jerry Lewis in 2006 that I kept... "Categories 0-10 are standard and stable, but subsequent categories are added by various applications/add-ins and will vary (both the names and the order in which they occur) from PC to PC." -- Jim Cone Portland, Oregon USA "Ron Rosenfeld" wrote in message -snip- If I try to run this as in the Workbook_Open event of my add-in, it returns an error message -- Run-error '1004': Cannot edit a macro on a hidden workbook. Unhide the workbook using the Unhide command.-- This is true whether the macro is placed directly in the Workbook module; or if the Workbook_Open event "calls" the above Sub which is in a regular module. So what is the best way around this issue? Thanks. --ron |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding hints to my vb functions
When UDF's are in an Add-in..........................
In Excel go to ToolsMacroMacros, type in the UDF name and "Options" Type the description and OK Do for other UDF's Hit Cancel key to leave Macros dialog when done with adding the descriptions. Save the add-in. Or Excel will ask next you close Excel. Gord On Sat, 07 Mar 2009 13:59:01 -0500, Ron Rosenfeld wrote: Although that method seems to work OK for a VBA project, I tried it with an **add-in** and the description "doesn't take". By that I mean if I right-click on the UDF name; enter something in the Property dialog box; hit <OK. When I immediately re-open the property dialog box, the entry is blank. |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding hints to my vb functions
On Sat, 07 Mar 2009 16:05:56 -0800, Gord Dibben <gorddibbATshawDOTca wrote:
When UDF's are in an Add-in.......................... In Excel go to ToolsMacroMacros, type in the UDF name and "Options" Type the description and OK Do for other UDF's Hit Cancel key to leave Macros dialog when done with adding the descriptions. Save the add-in. Or Excel will ask next you close Excel. Gord Yes, it works typing in the description in the macro dialog box within Excel. But not in the Properties box when going through the VBA object browser. But I think I'll just use the macro method. --ron |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding hints to my vb functions
On Sat, 7 Mar 2009 13:50:38 -0800, "Jim Cone"
wrote: Ron, "Category:="Regular Expressions"" Since you are trying to create a new category for your function, I believe the following notes I saved with one my own early add-ins apply... '-------------- Thanks for that. But in Excel 2007, the application.macrooptions method seems to work OK for adding the category. --ron |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding hints to my vb functions
On Sat, 07 Mar 2009 19:54:55 -0500, Ron Rosenfeld
wrote: On Sat, 7 Mar 2009 13:50:38 -0800, "Jim Cone" wrote: Ron, "Category:="Regular Expressions"" Since you are trying to create a new category for your function, I believe the following notes I saved with one my own early add-ins apply... '-------------- Thanks for that. But in Excel 2007, the application.macrooptions method seems to work OK for adding the category. --ron Or might there be some other reason for using that method in 2007? --ron |
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding hints to my vb functions
I have no idea. -- Jim Cone Portland, Oregon USA "Ron Rosenfeld" wrote in message On Sat, 07 Mar 2009 19:54:55 -0500, Ron Rosenfeld wrote: Or might there be some other reason for using that method in 2007? --ron |
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding hints to my vb functions
Don't know why it won't take when adding descriptions through the Object
Browser with an Add-in but works OK with a workbook. One of those Excel mysteries. Gord On Sat, 07 Mar 2009 19:49:18 -0500, Ron Rosenfeld wrote: On Sat, 07 Mar 2009 16:05:56 -0800, Gord Dibben <gorddibbATshawDOTca wrote: When UDF's are in an Add-in.......................... In Excel go to ToolsMacroMacros, type in the UDF name and "Options" Type the description and OK Do for other UDF's Hit Cancel key to leave Macros dialog when done with adding the descriptions. Save the add-in. Or Excel will ask next you close Excel. Gord Yes, it works typing in the description in the macro dialog box within Excel. But not in the Properties box when going through the VBA object browser. But I think I'll just use the macro method. --ron |
#22
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding hints to my vb functions
BJV - this post at my blog covers what you want, I think: http://newtonexcelbach.wordpress.com...ction-categori es-and-descriptions/ *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Keeping track of useful hints in Excel Community | Excel Discussion (Misc queries) | |||
Sudoku hints | Excel Worksheet Functions | |||
Hints And Tips For New Posters In The Excel Newsgroups | Excel Worksheet Functions | |||
Why no hints after typing . | Excel Programming | |||
Hints for working with budget and register excell worksheets | Excel Programming |