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 > How does it work?

Registering a User Defined Function with Excel

How does it work?

Laurent Longre discovered, that if one uses the Excel 4 macro language's register function to register a function residing within any system dll, using an alias name which is identical to the name of a UDF, one can assign the UDF to one's own category in the function wizard. At the same time, one can also add a function description and argument descriptions. This is a newsgroup post describing his discovery. and this forum message gives a bit of detail about the internals.

Disadvantage of this trick method, is that one is actually re-registering a function within the dll one uses, which might be used by any program. The example file belonging to this article uses some obscure functions from the user32.dll.

Up until now, people have implemented this trickery in various ways, including using ExecuteExcel4Macro (limiting total length of Register function, descriptions and argument names to 255 characters) and using a dedicated dll. Most developers stick to simply using the  Application.MacroOptions method (most limited method), which only gives you the opportunity to set the UDF to one of the built-in function categories and a function description. Argument descriptions are not possible with the pure VBA method.