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

Put to Use

Now that I've explained some of the internals of this method, let me finish by showing you how this is put to use in your project.

This requires the following steps:

• Copy the worksheet called "FunctionList" to your own project workbook (the one with the UDFs)
• Copy the classmodule called "CUdfHelper" into your project
• Copy the two routines "LoadFunctionDescriptions" and "UnLoadFunctionDescriptions" from the module called "MFunctions" to a module in your workbook's VBA project
• Make sure you call these subs from both Workbook_Open and Workbook_BeforeClose respectively (in the Thisworkbook module):

Private Sub Workbook_Open()
    LoadFunctionDescriptions
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    UnLoadFunctionDescriptions
End Sub

Of course you can also use the old Auto_Open and Auto_Close subs in a normal module.

Next you fill in the details about your UDFs on the worksheet called FunctionList:

• Decide what dll to use (now, user32.dll is used).
• If you decide to use a different one, find out what functions inside the dll can be used for the registration and enter their names on row 7 of the FunctionDetails worksheet (you'll have to unprotect the sheet first).
• Done!

Some additional remarks:

• The "Registration" code exposes some functions thru the function wizard (even if private). This code can be anywhere and does not need a reference.
• The "Function" code with the exposed functions does NOT need to be in either the calling worksheet’s parent workbook nor in the workbook containing the "registration" code. Nor does it need a reference.
• If the functions are in an xla addin: no need to use qualified function names during the registration process (even if not in same wkb as registration code) ELSE you must use a qualified function name. The "Caller" workbook needs the functions to be loaded, and alas will create an external file link (creating a VBA reference in the workbook that uses the UDFs is optional to force loading the "Functions" workbook).

Make sure you read the last page too, it contains an important warning!