Custom Function Description

[Steve]

Does anyone know how to put your own custom formula DESCRIPTION into the
Edit Formula "Palette". For example, for the Excel standard =Index
function, if the gray "=" button is selected, a gray dialogue box pops
up giving you a description: "Returns a value or a reference to
value..." It also contains the parameter (function argument) names as
well as a description for each of them when you click on the boxes (How
would you do that too?).

I realize that I can record a macro, insert the description during the
recording wizard, then change the "Sub" to a "Function", etc. But I
already have around 30 Public Functions that I created (without
recording).

Any help would be appreciated.

Steve L.

Sent via Deja.com http://www.deja.com/
Before you buy.

[Bernie Deitrick]

Steve,

Choose Tools | Macro | Macros... then type the function name into the box
(it won't be a choice because it's not a macro: you may need to include the
workbook name like "Personal.xls!IsPrime" or even the module name like
"Personal.xls!PrimeModule.IsPrime", depending on where the function is) and
then click on the Option button and type your description in the box.

HTH,
Bernie

[Tom Ogilvy]

As to the second question, there is no way to provide argument descriptions
for a VBA function.

Regards,
Tom Ogilvy
MVP Excel

Steve <ste...@zacks.com> wrote in message
news:86ieu7$1jt$1...@nnrp1.deja.com...

[Niek Otten]

Except for this trick from Laurent Longre. It is not a general solution
because it requires a macro to be included in Auto_Open, but if that is
acceptable it works fine.
I forgot on which page it wa so I include the text.

Groeten,

Niek Otten
----------------------------------------------------------------------------
-------------------

POWER PROGRAMMING TECHNIQUES

Created by Laurent Longre

This example shows how to register functions into user-defined catagories
and
provide descriptions for their arguments. The Auto_Open procedure registerst
he two functions, Multiply and Divide in two categories Multiplication andD
ivision and provides descriptions of the input parameters.

Const Lib = """c:\windows\system\user32.dll"""
Option Base 1

Private Function Multiply(N1 As Double, N2 As Double) As Double
Multiply = N1 * N2
End Function

'==========================================

Private Function Divide(N1 As Double, N2 As Double) As Double
Divide = N1 / N2
End Function

'==========================================

Sub Auto_open()

Register "DIVIDE", 3, "Numerator,Divisor", 1, "Division", _
"Divides two numbers", """Numerator"",""Divisor """, "CharPrevA"
Register "MULTIPLY", 3, "Number1,Number2", 1, "Multiplication", _
"Multiplies two numbers", """First number"",""Second number """, _
"CharNextA"

End Sub

'==========================================

Sub Register(FunctionName As String, NbArgs As Integer, _
Args As String, MacroType As Integer, Category As String, _
Descr As String, DescrArgs As String, FLib As String)

Application.ExecuteExcel4Macro _
"REGISTER(" & Lib & ",""" & FLib & """,""" & String(NbArgs, "P") _
& """,""" & FunctionName & """,""" & Args & """," & MacroType _
& ",""" & Category & """,,,""" & Descr & """," & DescrArgs & ")"

End Sub

'==========================================

Sub Auto_close()

Dim FName, FLib
Dim I As Integer
FName = Array("DIVIDE", "MULTIPLY")
FLib = Array("CharPrevA", "CharNextA")
For I = 1 To 2
With Application
.ExecuteExcel4Macro "UNREGISTER(" & FName(I) & ")"
.ExecuteExcel4Macro "REGISTER(" & Lib & _
",""CharPrevA"",""P"",""" & FName(I) & """,,0)"
.ExecuteExcel4Macro "UNREGISTER(" & FName(I) & ")"
End With
Next

End Sub

Tom Ogilvy <twog...@email.msn.com> schreef in berichtnieuws
Op$51TsZ$GA.204@cppssbbsa04...

[Steve]

Thanks Bernie, Tom, & Niek. I've tried your suggestion Bernie. It
works but does not get saved upon application exit. But I can use a
simple, but lengthy macro upon Workbook_open to initialize a bunch of
function descriptions (using Application.MacroOptions). I'll also try
Niek's suggestion next.

In article <86jl8g$97g$1...@news1.xs4all.nl>,

[Tom Ogilvy]

Well, Niek, I stand corrected. Of course my source was the same Laurent
Longre who said, for example (and there have been several posts with this
message):

============>
Subject: Re: Describing arguments in userdefined functions
Date: 12/04/1998
Author: Laurent Longre <lon...@wanadoo.fr>

Sorry, but this can be done only with DLL/XLL functions, not with VBA.

This feature will be perhaps available in XL2K? <g>

Laurent

Jens Woeste Christensen wrote:
>
> Hi' there
>
> Is there any way of describing the arguments of a userdefined function,
> so that when it appears in the function wizard/paste function every
> argument is described.
>

<snip>
===================>
Apparently Laurent has made a breakthrough.

Thanks for the update.

Regards,
Tom Ogilvy
MVP Excel

Niek Otten <nico...@xs4all.nl> wrote in message
news:86jl8g$97g$1...@news1.xs4all.nl...

> Except for this trick from Laurent Longre. It is not a general solution
> because it requires a macro to be included in Auto_Open, but if that is
> acceptable it works fine.
> I forgot on which page it wa so I include the text.
>
> Groeten,
>
> Niek Otten

<snip>