Most Valuable Professional


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

Subscribe to our mailing list

* indicates required

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
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 37 in total (Show All Comments):

 


Comment by: Eberhard Kuske (10/4/2010 4:56:40 AM) Direct link 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 (8/6/2013 10:45:47 AM) Direct link 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 (8/6/2013 11:13:22 AM) Direct link 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 9:02:41 PM) Direct link 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 (1/11/2016 4:49:29 PM) Direct link to this comment

Hi.

Thanks a LOT for this code and all its guides! Its simply fantastic!

I have only one question.
Ive 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 (1/11/2016 5:29:09 PM) Direct link to this comment

Hi Jorge,

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

 


Comment by: Mark Egloff (2/17/2016 3:52:26 PM) Direct link 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 (2/17/2016 4:17:30 PM) Direct link to this comment

Hi Mark,

See my comment in the post on S.E.

 


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].