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

File crashes Excel!!

Save my workbook!
The best tool for salvaging problematic workbooks.

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

 


Comment by: Jorge Santos (1/11/2016 4:49:29 PM)

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)

Hi Jorge,

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

 


Comment by: Mark Egloff (2/17/2016 3:52:26 PM)

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)

Hi Mark,

See my comment in the post on S.E.

 


Comment by: Brett (5/19/2017 8:11:05 PM)

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 (5/22/2017 9:58:59 AM)

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 (5/22/2017 4:31:49 PM)

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 (5/22/2017 4:59:11 PM)

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

 


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