Comments for Functions created in Visual Basic

[Mark G]

How can I create an Excel function that shows help like built-in functions.

When ABS is highlighted, a description of the function displays.



Once the function is selected, you can see what is expected in the variable list.



Can this be done when creating your own function in Visual Basic?



Thanks,
Mark

 

[Luke M]

I think this is what you are looking for Mark
http://www.ozgrid.com/VBA/DesciptionToUDF.htm

It's not a perfect replica, but you can modify the description in the function wizard, and change the category.

 

[Mark G]

Thanks Luke for your help. You gave me enough information to get a big step closer.

With the information at the link you provided I was able to find this link.

http://spreadsheetpage.com/index.ph...function_argument_descriptions_in_excel_2010/

This is copied from the URL:

Here's a simple (but very useful) user-defined function:

Function EXTRACTELEMENT(Txt, n, Separator) As String
EXTRACTELEMENT = Split(Application.Trim(Txt), Separator)(n - 1)
End Function

Here's a VBA macro that provides a description for the EXTRACTELEMENT function, assigns it to a function category, and provides a description for each of its three arguments:

Sub DescribeFunction()
Dim FuncName As String
Dim FuncDesc As String
Dim Category As String
Dim ArgDesc(1 To 3) As String

FuncName = "EXTRACTELEMENT"
FuncDesc = "Returns the nth element of a string that uses a separator character"
Category = 7 'Text category
ArgDesc(1) = "String that contains the elements"
ArgDesc(2) = "Element number to return"
ArgDesc(3) = "Single-character element separator"

Application.MacroOptions _
Macro:=FuncName, _
Description:=FuncDesc, _
Category:=Category, _
ArgumentDescriptions:=ArgDesc
End Sub

You need to run this macro only one time. After doing so, the descriptive information is stored in the workbook (or add-in) that defines the function.

Here's how the function appears in the Function Arguments dialog box:

Looks like this works with Excel 2010 and above.

Thanks for getting me in the right direction.

The next step is to see if the function description can be displayed while typing it out.

Mark