Rapid Application Development / Advanced Excel Development

Debugging Excel xlls

Whilst the xll features of Excel offer excellent functionality and superb performance, they are not for the faint hearted. Excel/VBA developers especially have trouble deciphering the documentation. Add in the need to use a different development environment and language and its easy to see why this powerful option is often rejected.

Xll Series Contents

(note this is a work in progress - more pages will be added and linked too as and when we have time to complete them to an acceptable level)

• Series intro
• Brief introduction to xlls - what, why, etc.
• The Codematic xll base project
• Installing Visual Studio C++ 2008 Express.
• Installing the Excel Xll SDK for use with Visual Studio 2008 (C++ Express)
• Very brief overview of C and C++ editors (VS2008)
• Creating a simple dll
• Debugging xlls (this page)
• Introduction to XLM.
• Using CALL to access external dll functions from XLM
• Registering dll functions for use from a worksheet cell - using XLM and VBA
• The XLOPER data type and xlcall.h
• The Excel4() function and xlcall.h
• Converting a dll to xll by adding in the registration information
• The Microsoft xll Software Development Kit and framework.
• The Codematic custom xll development service

Debugging xlls

As part of the xll creation process several Visual Studio settings need to be changed so your xll will work with Excel. In summary these are:

1. Change debug information format
2. Change extension to xll
3. Add xllcall32.lib dependency
4. Set Excel as the debug host program

These are already set in the Codematic xll demo project (available from here).

1. Debug mode.

You can use VB style breakpoints in your xlls, although the change/continue features are not so rich. Sadly Excel does not understand the standard debug build xlls, so you need to modify that setting. In VS2008:

In Project>>Project Name>>properties>>Configuration Properties>>C/C++ change the 'Debug Information Format' from the default 'Program Database for Edit & Continue (/ZI)' (which adds symbols that Excel does not understand) to 'Program Database (/Zi)'

If you forget to do this you will either get an 'Unrecognisable file format' error when you try to load your xll in Excel or the even more obvious:

2. Change extension to xll

By default the type of base project to use to create an xll is a dll. VS will therefore want to add a .dll extension to the final output file. To change that to xll:
In Project>>Project Name>>properties>>Linker select Output File and change the dll to xll. You need to do this for the debug configuration and the release one (or all configurations)

3. Add xlcall32.lib dependency

To work with the Excel C API you have to tell VS how to, that information is contained in xlcall32.lib. You need to add it to the project dependencies:

In Project>>Project Name>>properties>> Linker>>Input Additional Dependencies add xlcall32.lib (you should have previously copied that file from the SDK to the lib folder).

4. Set Excel as the debug host program

Finally to make debugging easier you can set the debugger to open Excel. Unfortunately it does not load the xll, you must do that using Excel functionality (either using Tools>>Add-ins, or copying the xll to the xlstart folder).
To set the debug exe:

In Project>>Project Name>> properties>>Configuration Properties>>Debugging select Command and choose browse from the drop down. Browse to Excel.exe and click ok.

The generator tries to set this up but Visual Studio ignores it, so it must be set manually for each project. Once it has been set manually VS remembers it for the future.

If you require any more information please get in touch.