Adding hints to my vb functions

[BJV]

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,

 

[Rick Rothstein]

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 Rothstein]

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.

 

[BJV]

Sounds great. Thanks

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.

 

[Gord Dibben]

You can also do this manually.

With your UDF workbook open got to VBE, select your workbook/project and
View>Object 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

 

[Rick Rothstein]

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?

 

[Ron Rosenfeld]

You can also do this manually.

With your UDF workbook open got to VBE, select your workbook/project and
View>Object 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

 

[Ron Rosenfeld]

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,

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

 

[Jim Cone]

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"
<[email protected]>
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

 

[Dave Peterson]

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]

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.

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

 

[Ron Rosenfeld]

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

 

[Jim Cone]

Re: "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" <[email protected]>
wrote in message

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.

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

 

[Rick Rothstein]

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.

 

[Jim Cone]

Ron,
Re: "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" <[email protected]>
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

 

[Gord Dibben]

When UDF's are in an Add-in..........................

In Excel go to Tools>Macro>Macros, 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

 

[Ron Rosenfeld]

When UDF's are in an Add-in..........................

In Excel go to Tools>Macro>Macros, 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

 

[Ron Rosenfeld]

Ron,
Re: "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

 

[Ron Rosenfeld]

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

 

[Jim Cone]

I have no idea.
--
Jim Cone
Portland, Oregon USA



"Ron Rosenfeld" <[email protected]>
wrote in message
On Sat, 07 Mar 2009 19:54:55 -0500,
Ron Rosenfeld <[email protected]>
wrote:
Or might there be some other reason for using that method in 2007?
--ron