The Interface to Microsoft Excel

To write an XLL, you should provide three functions (described in this section) that are called by Microsoft Excel and three functions that are called by the Add-In Manager. For information about the functions called by the Add-In Manager, see "Supporting the Add-In Manager" on page 188.

The functions in the following table are called by Microsoft Excel.

The only function that is required for Microsoft Excel to open a DLL is xlAutoOpen. For more information on these three functions, see "xlAutoOpen," "xlAutoClose," and "xlAutoRegister" on pages 242, 240, and 243, respectively.

Opening an XLL

You can open an XLL in the same ways in which you open any Microsoft Excel file. You can use the Open command on the File menu, the Microsoft Excel startup directory, the command line, or the OPEN= entries in the registry. You can open an XLL in the macro language by calling the REGISTER function with only one argument (the name of the XLL). Here is what happens:

1. Microsoft Excel tries to register a function in your XLL called xlAutoOpen, which should be declared as type "A" (returns a Boolean, no parameters). For more information, see "xlAutoOpen" on page 242.

2. If that succeeds, Microsoft Excel runs the xlAutoOpen function. This function should:

• Call xlfRegister to register all the functions that the XLL makes available.
• Add any menus or menu items that the XLL makes available.
• Do any other necessary initialization.
• Return 1.

There is no guarantee that xlAutoOpen will be called before a function in your XLL is run. For example, the user can simply register and call one of the functions without opening the XLL, bypassing xlAutoOpen.

3. Microsoft Excel unregisters the xlAutoOpen function, since it is no longer needed.

Important

Calling xlAutoOpen is the only way the Open command loads an XLL. This function is required in every DLL with the extension XLL.

Unregistering the Entire XLL

A user can call the function UNREGISTER and specify the name of your XLL. For example, if UNREGISTER("GENERIC.XLL") is called from a Microsoft Excel macro or another DLL, this instructs Microsoft Excel to remove that DLL from memory. Here is what happens:

1. Microsoft Excel tries to register a function called xlAutoClose, which should be declared as type A.

2. If xlAutoClose is found, Microsoft Excel runs the xlAutoClose function. This function should:

• Do any necessary global cleanup.
• Remove any menus or menu items that were added in xlAutoOpen.
• Delete any names that were created in xlAutoOpen. Remember that calling xlfRegister with a fourth argument causes a name to be created. You can delete names by calling xlfSetName with the second argument omitted. This is important; otherwise, the names will still appear in the Paste Function dialog box.

3. When xlAutoClose returns, Microsoft Excel unregisters all functions registered in that DLL, no matter who registered them or how often they were registered in that instance of Microsoft Excel.

Note

While you are developing XLLs, you may find it convenient to call the UNREGISTER function, with the name of your XLL as its argument, from a macro in Microsoft Excel. This ensures that the XLL is completely unloaded. Thus you can compile a new version of the XLL in another process without conflicts.

Quitting Microsoft Excel

When the user quits Microsoft Excel, all loaded XLLs are unregistered as described in the previous section. The xlAutoClose function is called for every XLL that has one.

Be aware that it is still possible that your XLL will be removed from memory without xlAutoClose being called. For example, the user could unregister every function individually, and xlAutoClose will never be called.

Note

There is no menu item that allows a user to close an XLL.

Registering Functions Without a Type String

You may want to allow end users to register functions in your XLL without specifying the type_text argument (for more information on the type_text argument, see "REGISTER" on page 219). This makes it easier for users to load individual XLL functions. To do this, you need to provide the xlAutoRegister function. Here is what happens:

1. A macro sheet calls REGISTER, specifying the name of the XLL and the name of the function but omitting the type_text argument.

2. Microsoft Excel tries to register a function in the XLL called xlAutoRegister, which should be of type RR (which takes a value LPXLOPER and returns a value LPXLOPER). If this fails, the REGISTER function returns #VALUE!

3. If this succeeds, Microsoft Excel calls xlAutoRegister, passing the name of the function (as xltypeStr) as the argument. xlAutoRegister should:

• Determine whether the function name is recognized. If it is not, your xlAutoRegister function should return #VALUE! as an xltypeErr.
• If the function is recognized, xlAutoRegister should call xlfRegister as usual, specifying at least the first three arguments (including type_text).
• Return the same value as returned by xlfRegister (an xltypeNum if a success or an xltypeErr if a failure).

Note

If xlAutoRegister calls xlfRegister without providing the type_text argument, an infinite loop results.