Adding Description to UDF

[YellowLabPro]

I received this today from an online subscritpion posting. I thought to share it, it looks like useful info to us beginners, of course..... and to ask a question. :hi:


http://etips.dummies.com/images/newsletter/dummies_logo.gif (http://portal.hungryminds.com/etip.asp?topic=dd01&promo=ETIPVISIT&link=www.dummies.com)http://etips.dummies.com/images/newsletter/spacer.gifhttp://etips.dummies.com/images/newsletter/nav_visit_site_ext.gif (http://portal.hungryminds.com/etip.asp?topic=dd01&promo=ETIPVISIT&link=www.dummies.com)http://etips.dummies.com/images/newsletter/spacer.gifhttp://etips.dummies.com/images/newsletter/nav_sign_up.gif (http://portal.hungryminds.com/etip.asp?topic=dd01&promo=ETIPSIGNUP&link=etips.dummies.com/signup.asp)http://etips.dummies.com/images/newsletter/spacer.gifhttp://etips.dummies.com/images/newsletter/spacer.gifhttp://etips.dummies.com/images/newsletter/etips_logo.gif

Add a Description to a User-Defined Excel Function

To help your user understand the purpose of your custom VBA Excel functions, you can add descriptions that appear in the Insert Function and Function Arguments dialog boxes. To add a description to your user-defined VBA function, follow these steps:

Open the Visual Basic Editor by choosing Tools, Macro, Visual Basic Editor or by pressing Alt+F11.
Choose View, Object Browser from the Visual Basic Editor menu bar. The Object browser window opens.
Click the drop-down list that currently contains the value All Libraries and select VBAProject on the drop-down list. The Object Browser displays your user-defined function as one of the objects in one of the Classes in the pane on the left.
Right-click the name of your function. This action selects the function and displays it in the Members pane on the right, while at the same time displaying the object?s shortcut menu.
Choose Properties on the shortcut menu. This action opens the Members Options dialog box for your user-defined function, where you can enter your description of the function.
Type the text that you want to appear in the Insert Function and Function Arguments dialog boxes and then click OK.
Close the Object Browser and save your changes.To find out more about what you can do with VBA and Excel, get a copy of Excel 2003 All-in-One Desk Reference For Dummies (http://www.dummies.com/WileyCDA/DummiesTitle/productCd-076453758X.html), by Greg Harvey.


My questions are:
1) Is this intended for functions only? I only have one UDF to test this on, which Mdmackillop gave me, but shows up in the Object Browser as a Module, not a function.
'Last Row
Function LR(ws As Worksheet, Col As Variant) As Long
Application.Volatile
If Not IsNumeric(Col) Then Col = Columns(Col).Column()
LR = ws.Cells(Rows.Count, Col).End(xlUp).Row
End Function
When I follow the article's instructions and add a comment, I did see down below in the bottom window pane my description.

2)But the article states that:
"Type the text that you want to appear in the Insert Function and Function Arguments dialog boxes and then click OK. "
Where am I to see this in the function? Back in Excel if I look in the insert function menu box, I do not see GenFunc, nor if I type =GenFunc is it recognized as a function.

Thanks, hope the questions make sense...

If someone has a UDF they would send me to test this tip out if I don't have what I need that would be a big help...

Doug

[Bob Phillips]

You have to first select your project from the list of object libraries (most likely it will be called VBAProject unless you have renamed it), and then select the appropriate modeule from the bottom left 'Classes' pane. After this, you select the function froim the bottom right 'Members' pane.

At this point you can right-click the funxtion, Select properties and add a description.

This is only a generic description, not argumnents.

You can see the function if you go to Insert>Function, and select User Defined.

[YellowLabPro]

Bob,
Making some progress.
I did not find the Function after going to Insert>Function, but you pointed out I needed to select User Defined.

1) I then found LR, which was the name MD gave by LR().
But I changed the name to LastRow() and then went back to Insert>Function>User Defined and could not find it. I changed it back to LR() and could not find it still. Then I found it had put the name of the workbook that the function is stored in in front of the Function name.
So now it is TGSItemRecordCreator.xls!LR.LR urgghhhh... not liking this at all.
Why did it do this and how do I get it back to just LR in the function menu?

2) The article it states:
Type the text that you want to "appear in the Insert Function and Function Arguments dialog boxes" and then click OK.
What do they mean here then?
I do not see any of the info I typed here in the Function or Function arguments?

Thanks

[Bob Phillips]

Why did it do this and how do I get it back to just LR in the function menu?

YOu must have changed workbooks, go back into that workbook and it will still be just LR.
<b />
What do they mean here then?
I do not see any of the info I typed here in the Function or Function arguments?

The function box is the first box you see from Insert>Function, the Functions Arguments dialog you see IF the function has arguments, AND you have selected that function.

As shown below, your text should appear where it says No help available.