Registering a User Defined Function with Excel

Wrap-up

For Excel 2010, adding argument descriptions for your UDFs has become quite simple now we have a new argument we can use.

For the older versions however -since this whole method revolves around some trickery involving re-registering a function that resides within a system dll- I want to make sure I caution the casual user. This is not a thing to mess around with. I would not advise using this method to register a large number of UDFs, using just any random set of function names from the dll. Make sure you test this thoroughly before distributing it to other users.

The ones we used in the demo work flawlessly however and have not caused any problems with our systems in the last few years.

If you are in the situation that you might have multiple Excel add-ins using this technique, make sure the add-ins do not use the same functions.

Comments

Showing last 8 comments of 30 in total (Show All Comments):

Comment by: Jan Karel Pieterse (9/19/2010 11:38:22 PM)

Hi Phillip,

Just include the full path to the help file, then type the exclamation mark, followed by the topic Id, like so:

c:\SomePath\macrofun.hlp!100

Comment by: Phillip W Richcreek (9/20/2010 6:01:20 AM)

<Just include the full path to the help file,>

I apologize for not making my question clearer. I realize that I want a full path name to the help file, BUT I don't know the FORMAT required for a .hlp file! I imagine that info is avaiable elsewhere.

Thanks for your help,

Phil

Comment by: Jan Karel Pieterse (9/20/2010 6:43:05 AM)

Hi Phil,

No problem ! A help file is a compiled html file. There is dedicated software available to generate help files.

One is:

http://www.west-wind.com/wwHelp/

BTW: Including the topic Id is mandatory, otherwise the help link will not work.

Comment by: Rick Gilbert (9/20/2010 11:27:11 PM)

With regard to using MacroOptions to register a UDF in Excel, one can add custom categories by assigning a string value ot the Category parameter of the MacroOptions method.

There are 18 categories available.

The following code assigns TestMacro to the "JKP Custom" category. If this is the first custom category, it will have a category index of 15.

Sub AddUDFToCustomCategory()
Application.MacroOptions Macro:="TestMacro", Category:="JKP Custom"
End Sub

This information is from
http://msdn.microsoft.com/en-us/library/ff838997.aspx
about Excel 2010, but it works in Excel 2007 as well.

Comment by: Phillip W Richcreek (9/22/2010 12:52:00 PM)

I've set up help info using the Microsoft Help Workshop. After struggling to get a topic ID defined (seems like that should be pretty straightforward but it wasn't)I'm still unable to get the help topic to work. I have the correct path and filename and topic ID. Here's what it looks like in shFuncList:

C:\Documents and Settings\Bill Gates\UDF Help Documentation\WORKDAY UDF ISWORKDAY() HELP PROJECT FILE.HLP!200

Another issue: The "Function Help" doesn't show up in the <shift>F3 popup box. Here's and example of Function help in shFuncList:

Author:Steve Jobs. Returns TRUE if given date is a workday, otherwise FALSE.

Any ideas how I might troubleshoot these issues?

TIA,

Phil

Comment by: Phillip W Richcreek (9/22/2010 2:41:22 PM)

Both issues seem to be resolved after closing EXCEL and restarting. Seems RegisterUDF was not picking up changes to shFuncList until EXCEL was restarted.

I wanted to confirm whether restarting EXCEL was necessary, or if just closing and re-opening RegisterUDF would have the same effect. So I made additional changes, closed and re-opened RegisterUDF. That DID NOT seem to pick up the additional changes.

Comment by: Jan Karel Pieterse (9/23/2010 4:47:13 AM)

Hi Philip,

Indeed, after making changes to the table, you need to close and re-open Excel and the tool.

Comment by: Eberhard Kuske (10/4/2010 4:56:40 AM)

wouldn't it be helpful if "somebody" could provide to this forum a dummy DLL featuring several 100 empty functions to be utilized for registration of UDFs?

Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.