Registering a User Defined Function with Excel

Pages in this article

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

All comments about this page:

Comment by: Chenchengxian (12-1-2009 02:32:11) deeplink to this comment

Thank you very much, but how can I make my own dll?

Comment by: Jan Karel Pieterse (12-1-2009 02:47:54) deeplink to this comment

Hi Chenchengxian,

You would have to use any software development application that can create dll's, such as VB6 or VB.NET.

Comment by: FARAZ AHMED QURESHI (8-3-2009 14:13:32) deeplink to this comment

An xclent website recommended by Niek Otten at one of the Microsoft Excel Community Forum. However, how to copy the sheet to an addin file with UDFs?

Comment by: Ole (10-3-2009 07:26:10) deeplink to this comment

Thank you for this article. I have gotten it to work with the FunCustomize.dll from xcell05.free.fr, but I was wondering how I could make my own, working dll. I have tried to make one (in C#), and I can register it with Excel, but when I try to run your register code I get the following error: "Failed to Register Testdll.dll Func1 UDFtest"

I would greatly appreciate it if someone could help me.

Comment by: Jan Karel Pieterse (10-3-2009 10:54:38) deeplink to this comment

Hi Ole,

I'm sorry, I have no C# experience whatsoever!

Comment by: Ole (11-3-2009 01:34:30) deeplink to this comment

Hello Jan. Would you know how to make it in VB? I have also tried to make a dll file there, though I am rather inexperienced with VB. Thanks.

Comment by: Jan Karel Pieterse (12-3-2009 02:54:45) deeplink to this comment

Hi Ole,

I've discussed this with KeepITCool, my colleague who worked out a significant part of the technology. We agree that using the built-in dll's is safe enough.

The only thing to consider is when you have multiple Excel Addins using the same functions in the same dll's: you need to ensure that does not happen.

Comment by: T.Bulent TURKER (8-4-2009 07:34:14) deeplink to this comment

First of all, I would like to thank you for the method you created to register a UDF in excel. It perfectly worked for the UDF, which I created and registered by your guidance.

My problem is; assigning the help file for my UDF. I wrote the name of a help file in HlpPath column of FunctionList sheet, then I tried with the full path, different directories, etc. but there was no success. Before writing my own help file I tried with small help files (hlp extension or chm extension), but unfortunately when I click on "Help on this function" on Insert Function windor or after selecting the function, on Function Arguments window, it didn't work.

If you can show an example for this, it will be highly appreciated.

Best Regards,

Comment by: Jan Karel Pieterse (8-4-2009 22:05:00) deeplink to this comment

I had a look at the help file for the functions used in this project to achieve the registration of the UDF, but can't find any relevant information other than that we have to include the full path there. And it does not work.
If I ever find a way I'll publish it here.

Comment by: Ole (20-5-2009 02:03:52) deeplink to this comment

To show the helpfile you need to include the topicID.
For example: C:\samplehelp.chm!101
101 here is the topicID.

Comment by: Jan Karel Pieterse (20-5-2009 04:27:00) deeplink to this comment

Hi Ole,

Thanks!!!!

Comment by: kevotheclone (3-6-2009 12:56:13) deeplink to this comment

I was searching for this info, when I found your article today; but I also found what appears to be a simpler technique:

Call Application.MacroOptions from within the Workbook_Open event.

Private Sub Workbook_Open()
Application.MacroOptions Macro:="MacroName", Description:="MacroDescription", Category:="CustomCategoryName"
End Sub ' Workbook_Open()

There's other parameter values available. The above example is a minimal example.

Comment by: Jan Karel Pieterse (3-6-2009 23:38:28) deeplink to this comment

Hi kevotheclone,

Yes that is a known method. That method has one limitation: You can only have one description and noo description for each parameter separately. Also, the description can not be longer than 255 characters in total and in Excel 2003 you cannot display more than 2 lines of text on the function wizard.
But for showing a short description, this is a valid and simple method indeed.

Comment by: Josh (12-10-2009 17:06:04) deeplink to this comment

I've been using this solution for a couple of years now and it works great. Thanks again to the guys who figured it all out.

Up to now I've only had 14 UDF's to document. Now I need to go to 16 (with probably a couple more in the near future). The sample worksheet only has room for defining 15 UDF's.

Can I just continue onto row 17 or is that going to cause some kind of havoc?

TIA,

josh

Comment by: Jan Karel Pieterse (13-10-2009 00:39:59) deeplink to this comment

Hi Josh,

You can expand the table as much as you like, as long as you use unique function names.

You can use software like this to find out what names are available:

http://www.heaventools.com/PE_Explorer_Exports_Viewer.htm

Comment by: Camiel (28-10-2009 12:16:20) deeplink to this comment

I tried to include the VBA code into an VB6 automation add-in project. The reason for this is to speed up the functions and to protect the code. So far I did not succeed in registering a function (copied the example code and pick up the name, arguments etc. from a OWC spreadsheet in the project (works).
I read as well that registration can be done using user32.dll or any other dll, what or possibilities are there? Could this be a reason for the code not working or any other idea. I can share the project if someone is interested.

Regards Camiel

Comment by: Jan Karel Pieterse (30-10-2009 11:54:36) deeplink to this comment

Hi Camiel,

I strongly doubt whether putting the functions in a VB6 COM addin will speed up your code, I think at best the performance will match that of plain VBA. I can understand you want to protect your code though.

One way could be to put wrapper functions in a normal excel addin, which makes use of the solution offered in this article. The wrapper functions call your VB6 COM addin where the real logic is housed.

I wouldn't worry about using a different dll than user32, I have not had any problem yet.

Comment by: Joe (23-6-2010 17:22:34) deeplink to this comment

I have a fair amount of UDFs that I'd to use your technique on. I am not a programmer. As I add UDF's to the list, how do I decide which user32 calls to use and which not?

Comment by: Jan Karel Pieterse (24-6-2010 02:27:23) deeplink to this comment

Hi Joe,

Are you saying you need more than the list shown in the demo? I think you just have to try on a couple of systems, chances are good it will work properly.

Comment by: Ole (25-6-2010 04:52:26) deeplink to this comment

Hello. You can always make your own dll file, though a standard class library dll file won't work.

I used the following page to make a dll file that worked.
http://www.metatrader.info/node/150
This page explains how you can make a MetaTrader extension (dll), in which one can make as many dummy functions as one wants.

Comment by: Jan Karel Pieterse (25-6-2010 05:28:33) deeplink to this comment

Hi Ole,

Thanks for the pointer!!
Would it be possible to share that dll with us?

Comment by: Phillip W Richcreek (19-9-2010 20:33:29) deeplink to this comment

What are the specifications for a help file (Help Path/Help Topic column in the FunctionList spreadsheet) to be connected to a registered UDF?

Comment by: Jan Karel Pieterse (19-9-2010 23:38:22) deeplink to this comment

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 (20-9-2010 06:01:20) deeplink to this comment

<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 (20-9-2010 06:43:05) deeplink to this comment

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 (20-9-2010 23:27:11) deeplink to this comment

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 (22-9-2010 12:52:00) deeplink to this comment

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 (22-9-2010 14:41:22) deeplink to this comment

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 (23-9-2010 04:47:13) deeplink to this comment

Hi Philip,

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

Comment by: Eberhard Kuske (4-10-2010 04:56:40) deeplink to this comment

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 (6-8-2013 10:45:47) deeplink to this comment

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 (6-8-2013 11:13:22) deeplink to this comment

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 21:02:41) deeplink to this comment

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.

Comment by: Jorge Santos (11-1-2016 16:49:29) deeplink to this comment

Hi.

Thanks a LOT for this code and all it´s guides! It´s simply fantastic!

I have only one question.
I´ve registerd trough you "method" several functions that in a DLL that i created in vb6. The problem is that in excel, besides the "new" category and the new formula (i (assigned all of this trough a xlam) the VB6 formula category STILL apears in the formula wizard...

Do you know how to remove the vb6 category out of the excel wizard?

Thanks a LOT!
Cheers
Jorge

Comment by: Jan Karel Pieterse (11-1-2016 17:29:09) deeplink to this comment

Hi Jorge,

I'm sorry, I have no idea how to do that!

Comment by: Mark Egloff (17-2-2016 15:52:26) deeplink to this comment

I have a problem when passing the result of an Excel Array formula to an UDF. It looks like that Excel sets elements of an array to #Value! Error in case the element in the array contains more than 255 characters, would the proposal to register the UDF help for that too?
see for more information on http://stackoverflow.com/questions/35436777/excel-vba-array-formula-returns-value-error-2015-in-case-a-cell-contains-m

Comment by: Jan Karel Pieterse (17-2-2016 16:17:30) deeplink to this comment

Hi Mark,

See my comment in the post on S.E.

Comment by: Brett (19-5-2017 20:11:05) deeplink to this comment

Hi Jan,

I'm getting an error using the 2010 add argument descriptions method with an add-in.

I added the register function to the Workbook_Open() event, but when the Excel is opened the following error is thrown...

"Cannot edit a macro on a hidden workbook. Unhide the workbook using the Unhide command." and the debug stops on the Application.MacroOptions line.

This works fine as an .xlsm, but an .xlam causes the error.

Any insight would helpful, thanks!

Comment by: Jan Karel Pieterse (22-5-2017 09:58:59) deeplink to this comment

Hi Brett,

Are you using the Application.MacroOptions method? I just tried and it works for both "normal" workbooks and add-ins.

Comment by: Brett (22-5-2017 16:31:49) deeplink to this comment

Hi Jan,

Yes, I am using the Application.MacroOptions method.

I found the problem: I was calling the register subroutine from the wrong event. It should have been called from Workbook_AddInInstall() and not Workbook_Open() as follows:

Private Sub Workbook_Open()

End Sub

Private Sub Workbook_AddinInstall()
RegisterUDFmacro
End Sub

So, if you want to create an .xlam (add-in) and have the UDF's registered automatically, put the code in the Workbook_AddinInstall() event of the ThisWorkbook module.

Thanks for all the help you've provided over the years Jan!

Hope this helps future users!

Comment by: Jan Karel Pieterse (22-5-2017 16:59:11) deeplink to this comment

Hi Brett,

Glad you got it sorted. However, if people want to use the add-in without installing it, that won't help. Another way to solve the issue is by calling the registration from Workbook_Open using Application.Ontime:

Private Sub Workbook_Open()
Application.OnTime Now, "'" & ThisWorkbook.FullName & "'!RegisterUDFmacro"
End Sub

Comment by: Harry (12-8-2017 11:54:17) deeplink to this comment

Hi folks,

I do not get the Application.MacroOptions Function get working. Under ANY circumstances. I tried and read a lot the last hours, but nothing works. Seems I could get maybe help here, so I try:

I put the code in ThisWorkbook and also in a new Module. I tried it in my Personal.xlam (that's my real objective) and also created a new Test.xlsm. I simplified Application.MacroOptions to the basics, I even restarted the Excel after every change I made and all get the same result: just frustrating nothing.

My UDF are working fine (when created in a Module, not in ThisWorkbook) but Application.MacroOptions does not have any impact. No description, no custom Categeory, no Error or something. As it wouldn't exist in the code...

So has anyone here an idea what could be responsible for that? Maybe a hidden Option for special VBA Script Operations? Or must this be a bug?

I highly appreciate any help, and gladly provide any further content (screenshots, logfiles, ...) which could help to find the error.

Thx in advance and best regards from Germany
Harry

PS System is Win 10x64 v1703 and
MS Excel 2016 MSO (16.0.4549.10000) 64-bit
(so both latest/actual patch Level...)

Comment by: Jan Karel Pieterse (13-8-2017 17:00:33) deeplink to this comment

Hi Harry,

Does the download of this article work for you?

https://jkp-ads.com/downloadscript.asp?filename=registerudf.zip

Comment by: Harry (14-8-2017 08:10:39) deeplink to this comment

Far better ... I finally got it running this night and it all WAS my own fault! The problem was, the RegisterFunction() Function was never executed(!) because I (why ever) thought this must happen automatically (so I even didn't understand why in all instructions they wrote "the register function have to be run just once" - what do they mean, "just once"??). ^^

So after running the Register function manually by pressing F5 and selecting it, everything worked as described. No further comment... :D

But thx anyway for your help and quick response. :)

BTW: (Maybe this is new for you and therefore a nice revanche for your kindness:) ) As described in the article, UDF's now have description in the Dialog Box, but what I originally was searching for was to have IntelliSense tool-tips and argument description like the native functions have, when you type them in in a cell.

Luckily I found a solution for this too, as described in the following Link. Now I have the perfect solution, as my VBA UDF's in my Personal.xlam are 100% like the native functions. Awesome =)

https://fastexcel.wordpress.com/2016/10/07/writing-efficient-vba-udfs-part-15-adding-intellisense-to-your-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.