Help for custom functions in xl2010 windows8 server

Pitchblend

New Member
Joined
Feb 16, 2012
Messages
44
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!
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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

New Member
Joined
Feb 16, 2012
Messages
44
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>
 
Last edited:

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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...

Code:
    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)?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,437
Messages
5,596,130
Members
414,043
Latest member
thomas Stein

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top