JKP Application Development Services.

                    Microsoft Office Application Development

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.

    Subscribe in a reader

Use the contact page to issue questions or comments about this website.
Copyright © 2003-2007 JKP Application Development Services.