Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Home > English site > Articles > Register UDFs > Wrap Up

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 33 in total (Show All Comments):

 


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?

 


Comment by: ken (8/6/2013 10:45:47 AM)

Hi,

I tried to create a DLL and use it but it gives me error saying it failed to register the procedure.

Is there any default folder I should place my DLL? What should I do after creating a DLL?

Thanks.

Ken

 


Comment by: ken (8/6/2013 11:13:22 AM)

Hi,

Thanks for sharing this solution.

I tried to create a DLL and your solution.

But I get the error message that it fails to register my DLL.

May I know what are the procedure involve after we create the DLL? Is there any folder/directory that I should be using?

Thank you.

Ken

 


Comment by: Jan Karel Pieterse (8/8/2013 9:02:41 PM)

Hi Ken,

There really is no need to create your own dll to do this, it works just fine with the ones in the example.

 


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.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].