Registering a User Defined Function with Excel

Pages in this article

1. Introduction
2. How does it work?
3. Setting things up
4. Class Module
5. VBA code
6. Put to Use
7. Wrap Up

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.

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.