Help for custom functions in xl2010 windows8 server

[Pitchblend]

I have written a custom function with the signature:
Function Contains(TestRange As Range, CriteriaRange As Range) As Long

The function works with no problems.

When I click the "fx" button to right of the range name box, a pop-up appears to allow me to select a function category (User-Defined). I then select the 'contains' function and a pop-up appears to allow me to enter the function arguments. Underneath the function arguments a message says "No Help Available." There is also a hyperlink that says, "Help on this function."

Questions:
Is it possible to provide help on the function using the Function Arguments pop-up?
Can I make the "Help on this function" hyperlink work?

I would appreciate a code snippet or your advise about where I can find additional information.

Thank you!

 

[Jerry Sullivan]

Hi Pitchblend,

John Walkenbach's site explains how to provide some argument descriptions for UDF's using xl2010.
Spreadsheet Page Excel Tips: User-Defined Function Argument Descriptions In Excel 2010

To see these descriptions, the user needs to choose a function using the Insert Function dialog box.
As far as I know, Excel 2010 and earlier doesn't have a means to have the argument descriptions appear next to the Formula Box like built-in functions.


This link provides information on various ways to provide user help.
Displaying Help from VBA Code

I haven't tried creating .chm files yet. I believe they are typically created using 3rd party software, but there may be some freeware tools out there.
Add help to "No help available" Excel, VBA , User defined functions - Stack Overflow

 

[Pitchblend]

Awesome! Exactly the information I needed. Thanks Again.

Go Sundevils!

 

[Jerry Sullivan]

Go Sundevils!

What? You're in Portland - you should be a Ducks Fan!

 

[Pitchblend]

Sorry to bother you again.

I tried running the Describe Function subroutine (see below). I get a runtime error 1004 - Method 'MacroOptions' of Object '_Application' failed.

I tried the obvious stuff like moving the arguments to one line, running the subroutine with only one argument - FuncName, etc.

Running Excel 2010 Version 14 On Windows Server 2008 R2 Enterprise

Suggestions?


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

</PRE>'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

</PRE>

 

[Jerry Sullivan]

That code works correctly for me running xl2010 on Windows 7.

When in the VBE, does the Application.MacroOptions Method come up in the Intellisense list?

If so, there might be a problem with one of the arguments.

Try running DescribeFunction with fewer arguments to see if you can isolate one that is causing the error...

    Application.MacroOptions _
        Macro:=FuncName, _
        Description:=FuncDesc

Does the function EXTRACTELEMENT work correctly as a UDF in your workbook?

Does the function show up on the Insert Formula dialog (without the desired descriptions)?