Excel user-defined functions.

[Guest]

I would like to add descriptions to user-defined function arguments similar
to "Rate is the interest rate ...." in the FV function. I would also like to
create seperate categories for my functions instead of using the default
"User Defined" category in the insert function dialog. How can I add "Help
on this function" for the user-function I have created.

 

[Guest]

thx Tom

here are entire code

Option Explicit
Dim ThisDay As Date
Dim ThisYear, ThisMth
Dim CreateCal As Boolean
Dim i As Integer

Private Sub UserForm_Initialize()
Application.EnableEvents = False
ThisDay = Date
ThisMth = Format(ThisDay, "mm")
ThisYear = Format(ThisDay, "yyyy")
'For i = 1 To 12
' CB_Mth.AddItem Format((i) & "/1/" & (ThisYear), "mmmm")
'Next
For i = 1 To 12
CB_Mth.AddItem Format(DateSerial(Year(Date), Month(Date) + i, 0),
"mmmm")
Next
'CB_Mth.ListIndex = (ThisMth) - 1
CB_Mth.ListIndex = Format(Date, "mm") - Format(Date, "mm")
For i = -20 To 50
If i = 1 Then CB_Yr.AddItem Format((ThisDay), "yyyy") Else
CB_Yr.AddItem _
Format((DateAdd("yyyy", (i - 1), ThisDay)), "yyyy")
Next
CB_Yr.ListIndex = 21
CreateCal = True
Call Build_Calendar
Application.EnableEvents = True
End Sub
Private Sub CB_Mth_Change()
Build_Calendar
End Sub
Private Sub CB_Yr_Change()
Build_Calendar
End Sub
Private Sub Build_Calendar()
If CreateCal = True Then
CalendarFrm.Caption = " " & CB_Mth.Value & " " & CB_Yr.Value
CommandButton1.SetFocus
For i = 1 To 42
If i < Weekday((CB_Mth.Value) & "/1/" & (CB_Yr.Value)) Then
Controls("D" & (i)).Caption = Format(DateAdd("d", (i -
Weekday((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), _
((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), "d")
Controls("D" & (i)).ControlTipText = Format(DateAdd("d", (i -
Weekday((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), _
((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), "m/d/yy")
ElseIf i >= Weekday((CB_Mth.Value) & "/1/" & (CB_Yr.Value)) Then
Controls("D" & (i)).Caption = Format(DateAdd("d", (i -
Weekday((CB_Mth.Value) _
& "/1/" & (CB_Yr.Value))), ((CB_Mth.Value) & "/1/" &
(CB_Yr.Value))), "d")
Controls("D" & (i)).ControlTipText = Format(DateAdd("d", (i -
Weekday((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), _
((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), "m/d/yy")
End If
If Format(DateAdd("d", (i - Weekday((CB_Mth.Value) & "/1/" &
(CB_Yr.Value))), _
((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), "mmmm") =
((CB_Mth.Value)) Then
If Controls("D" & (i)).BackColor <> &H80000016 Then Controls("D"
& (i)).BackColor = &H80000018 '&H80000010
Controls("D" & (i)).Font.Bold = True
If Format(DateAdd("d", (i - Weekday((CB_Mth.Value) & "/1/" &
(CB_Yr.Value))), _
((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), "m/d/yy") =
Format(ThisDay, "m/d/yy") Then Controls("D" & (i)).SetFocus
Else
If Controls("D" & (i)).BackColor <> &H80000016 Then Controls("D"
& (i)).BackColor = &H8000000F
Controls("D" & (i)).Font.Bold = False
End If
Next
End If
End Sub

regards, Utham

 

[Andibevan]

Hi There,

I have also searched, looking for a solution to this. I started by looking
at Microsofts Statistical add-in but this talks to an XLL file which I can't
open yet. By looking at the APVBAEN.xla file there is nothing I can see
which effects the help text.

1. One obvious solution is to try to make the argument names explanatory.
In other words, instead of

Function foo(a,b,c,d,optional e)

Do something like:

Function foo(StartDate, EndDate, WeekendDay1, WeekendDay2, optional
Holidays)

2. You could look at Longre's FUNCUSTOMIZE.DLL (at http://xcell05.free.fr/)
which puports to add more involved descriptions. I have only taken a brief
look at this but It is a good starting point. It took me quite a while to
find as I couldn't get any joy out of google.

Bonne Chance,

Andi

 

[Andibevan]

Forgot about this as well:-

You can add a brief description(not arguments) if you open the VBEditor and
View>Object Browser.

Find your UDF and right-click and you can add a comment there.

This comment will be shown in the Function Wizard when you click on your
UDF.

i.e. Click on ShowFormula in UDF category and see this.......

ShowFormula(Cell)

Returns the formula of a cell as Text.
Usage is: =ShowFormula(cellref)

 

[Jerry W. Lewis]

There is no standard way to to this, however Laurent Longre has written
an .xll that will help. Go to
http://xcell05.free.fr/
(JavaScript required to navigate his new page) and download FUNCUSTOMIZE.DLL

Jerry

 

[Bob Phillips]

For two UDFs, you can use this method described by Laurent Longre in the EEE
newsletter at
http://j-walk.com/ss/excel/eee­/eee009.txt

I just tested it and it still works, you just need to make sure that you
have the correct path to user32.dll.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Andibevan]

I think Bob meant:-

http://www.j-walk.com/ss/excel/eee/eee009.txt

Regards

Andi

 

[Tony]

If you put:

Application.MacroOptions Macro:="Fn Name", _
Description:="Short description here"

in the Open event of the workbook containing the
functions, then the description will show up in the Paste
fn window along with the fn name and args. Hardly a full-
blown help function, but it might be useful. Works best
with an AddIn, I think.

Tony

 

[Bob Phillips]

You're right, I use this often and no-one has corrected me before. Thanks

Bob