Put to Use

JKP Application Development Services.

Microsoft Office Application Development

Put to Use

• •

Search

Zoeken

Go to	Ga Naar
Home Up

Donate	Donaties
Did you find something helpful on my site? Consider a donation! Heeft u iets gevonden waar u wat aan had? Overweeg dan een donatie!

Registering a User Defined Function with Excel

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 UDF's)
	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 UDF's 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 UDF's is optional to force loading the “Functions” workbook).

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