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

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

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.