Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA® نسخه متنی

Creating an XLL Project in Visual Studio

An XLL is just a Windows DLL with a .xll file extension and an internal structure that Excel recognizes. Prior to creating your XLL project, you need to copy the xlcall.h and xlcall32.lib from the Microsoft Excel 97 SDK into your Visual Studio include and lib directories, respectively.

We're using the SDK for Excel 97 because that was the last version of Excel for which Microsoft released an SDK. There have been no fundamental changes to the underlying Excel C API since the Excel 97 SDK was written, so this is not a problem. The Excel 97 SDK can be downloaded from [ http://download.microsoft.com/download/excel97win/Install/1.0/W9XNT4XP/EN-US/excel97sdk.exe ].

To create your XLL project, choose File > New > Project from the Visual Studio menu. In the New Project dialog, expand the Visual C++ Projects folder on the left side and select the Win32 folder. From the Templates list, select Win32 Console Project. Enter the name for your XLL in the Name text box and select the location where you want the project to be saved. This is shown in Figure 19-1.

Figure 19-1. Creating the Initial XLL Project

[View full size image]

Click the OK button and Visual Studio will display the Application Wizard dialog box shown in Figure 19-2. Select Application Settings on the left side and choose DLL as your Application type. Place a check mark in the Empty project check box under Additional options and click the Finish button. You will now have an empty Win32 DLL project.

Figure 19-2. Choosing the Correct Application Wizard Settings

[View full size image]

Next you will add two files to your project. Select File > Add New Item from the Visual Studio menu. In the Add New Item dialog, select C++ File (.cpp) from the Templates list. This will be the file that contains your code. Because we are creating a C project, you need to explicitly give your file a .c extension, as shown in Figure 19-3. The Location setting should already be pointing to your project folder. Leave this as is.

Figure 19-3. Adding a Code File to Your XLL Project

[View full size image]

The next file you need to add to your project is a module definition file. This will enable you to export function names from your XLL that Excel can recognize. Select File > Add New Item from the Visual Studio menu again, but this time choose Module-Definition File (.def) from the list of templates. Give your module definition file the same name as the .c code file you added above, but with a .def file extension, as shown in Figure 19-4.

Figure 19-4. Adding a Module Definition File to Your XLL Project

[View full size image]

You need to make several project-level settings before you're ready to start writing code. Choose Project > Sample Properties from the Visual Studio menu (where Sample is the actual name you selected for your project). In the Sample Property Pages dialog shown in Figure 19-5, select the Debugging folder from the tree view. Then select the Command option from the list of settings on the right side and browse to the location of Excel.exe on your computer. This tells Visual Studio what program to use when debugging your XLL.

Figure 19-5. Specifying Excel.exe as the Debugging Program for Your XLL Project

[View full size image]

Then select the C/C++ folder, followed by the General subfolder from the tree view. The first setting you need to change is the Debug Information Format setting. The default value for this setting is Program Database for Edit and Continue. This value will add debug symbols to the XLL that will cause Excel not to recognize it. Change this setting to Program Database instead. This setting is demonstrated in Figure 19-6.

Figure 19-6. Specifying the Correct Debug Information Format Setting

[View full size image]

Then select the Linker folder, followed by the General subfolder from the tree view. In the Output File setting, manually change the file extension of the output filename from .dll to .xll, as shown in Figure 19-7.

Figure 19-7. Changing the Output Filename Extension

[View full size image]

Select the Release entry from the Configurations drop-down and repeat the file extension change for the release build. Finally, choose the Input folder from the Linker section of the tree view. Choose the All Configurations entry from the Configurations drop-down. Type the filename xlcall32.lib into the Additional Dependencies setting as shown in Figure 19-8.

Figure 19-8. Adding xlcall32.lib to the Additional Dependencies for Your Project