Once you have created a DLL, you should be able to call the DLL from within Microsoft Excel.
Note
The examples in this chapter discuss calling a DLL function from a worksheet or XLM sheet. For information about calling DLL functions from Visual Basic, see Chapter 5, "Using DLLs from Visual Basic." Microsoft Excel 97 has eliminated Macro Sheet as a choice from the "Insert" menu. There are still two remaining methods for getting a macro sheet from the user interface:
1. Press Ctrl+F11
2. Right-click on a sheet tab and choose "Insert".
Microsoft Excel uses three key functions to link to DLLs. The first function, REGISTER, establishes a dynamic link to a function that resides in a DLL. The CALL function then calls that DLL function. Finally, the UNREGISTER function breaks the link, allowing the operating system to remove the DLL from memory. For an example, see the following macro.
Cell B1 registers the function CalcCircum, which is exported by a DLL called CIRCUM.DLL. If the function is found, REGISTER returns a number called a register ID. That number can then be used as the first argument to the CALL and UNREGISTER functions.
The third argument to the REGISTER function informs Microsoft Excel what data types the CalcCircum function expects and what data type it will return. The first letter is a code describing the return type, in this case, E, meaning a pointer to a floating-point number. The next letter or letters describe all the arguments. The CalcCircum function takes a pointer to a floating-point number as its single argument, so the second letter of the code is E. Microsoft Excel supports several data types, such as integers, floating-point numbers, strings, arrays, and Booleans.
There is even a special code for the Microsoft Excel internal data type, the XLOPER; for more information, see "The XLOPER Data Type" on page 151. All of the recognized data types are described under "REGISTER" on page 219, and in the online Microsoft Excel Function Reference. To find this information, look under the help topic titled "Using the CALL and REGISTER functions."
After the registration, cell B2 calls the function, with an argument of 100. The result becomes the value of B2. Finally, B3 unregisters the function, allowing the DLL to be removed from memory.
Now try running the CalcCircum function. Make sure the name fCircum is defined to refer to B1. To do this, select B1; then choose the Define... command from the Name submenu on the Insert menu, type fCircum and then choose the OK button. To run the macro, choose the Macros command from the Tools/Macro menu, select fCircum, and then choose the Run button. The DLL should load and run the function. A quick way to see all the return values in the macro sheet is to press CTRL+` (accent grave). Microsoft Excel then switches into Display Values mode, and you see something like the following illustration.
The value in B1 is the result of registering the function; in other words, it is the register ID. This value may vary from session to session. The value in B2 is the result of evaluating the CalcCircum function. The values in B3 and B4 indicate that both of the commands in these cells were evaluated successfully. You can press CTRL+` again to switch back to Display Formulas mode.
For registering functions you need to use only once, there is a shortcut. You can use an alternate form of the CALL function, which automatically registers and then calls a function. The following illustration shows this form.
This shortcut is helpful because it can be entered directly on a worksheet, as well as on a macro sheet. This is one way you can allow worksheets to use functions that are defined in a DLL.
Although the preceding methods are effective, you ideally would like to be able to provide "native" functions, which look to the user like ordinary Microsoft Excel functions. To do this, you can use the full form of the REGISTER function on a worksheet. The following illustration shows this form.
In this example, in addition to registering the function, REGISTER defines a new name so that you can refer to the function directly. To do this, you need to specify the Microsoft Excel internal name, the names of the arguments, and the category to which this function belongs. Now, when the user chooses the Function... command from the Insert menu, the new function appears in the list under Math & Trig, as shown in the following illustration.
For more information about using the different forms of REGISTER, see page 219. For more information about using the different forms of CALL, see page 207.