It is very important that Excel add-in functions do not crash Excel. Many users of Excel expect to keep an instance of Excel running for days at a time. If there are a lot of other add-ins registered, it can often take several minutes for Excel to restart.

If your add-in is calling somebody else's code, you can never be sure that it will behave as it should, particularly if the 3rd-party code is frequently updated. What can you do if a fatal bug is introduced?

One useful way to make an add-in bomb-proof is to catch hardware exceptions. This class of exception includes the following old favourites:

• Integer divide by zero
• Access violation

Dangerous code

Let's look at a couple of examples of the sort of code that can bring Excel crashing down. All of the code in this section is from the demo project SafeCode.

Integer divide by zero

Our first example divides by an integer, without first checking that it is non-zero.

long IntAverage(int nCount, long* alItems)
{
    int i;
    long lTotal, lResult;
    for (i = 0, lTotal = 0; i < nCount; i++)
        lTotal += alItems[i];
    // The next line causes a divide/0 error if nCount = 0
    lResult = lTotal / nCount;
    return lResult;
}

If your add-in function calls this code with nCount = 0, Excel will crash immediately. Under the Visual Studio debugger, the following message will appear:

If the bug occurs without the debugger, the following message will appear:

In either case, Excel is toast and will need to be restarted.

Access violation

Another very common error is to write to memory that is not owned by the calling process, particularly at address 0000:0000.

void MakeSeries(double dBase, double dInc, int nOut, double* adOut) 
{
    int i;
    // The next line causes an access violation if adOut = 0
    adOut[0] = dBase;
    for (i = 1; i < nOut; i++)
        adOut[i] = adOut[i - 1] + dInc;
}

If your add-in function calls this code with nOut = 0 and adOut = 0, which is a degenerate but perfectly legal case, Excel will crash as in the case of Integer divide by zero, above. The message will be different - "0xC0000005: Access Violation" - but the consequences will be the same.

The way to catch these errors is by using the C Structured Exception Handling (SEH) mechanism provided by Microsoft.

Trapping C Exceptions

By default, the wrapper functions generated by the XLL+ Function Wizard trap C runtime exceptions, and instead throw an exception of type CXlStructuredException. CXlStructuredException is derived from CXlRuntimeException, so the standard exception handling code in the wrapper functions deals with the C++ exception by returning a description to Excel.

extern "C" __declspec(dllexport)
LPXLOPER12 INTAVG_12(LPXLOPER12 Input)
{
    XLL_FIX_STATE;
    CXlOper xloResult, Input__port(Input);
    try {
        CXlStructuredExceptionHandler _seh_;
        xloResult.HandleResult(INTAVG_Impl(xloResult, &Input__port));
    }
    catch(const CXlRuntimeException& ex) {
        CXllApp::Instance()->DisplayException(xloResult, ex);
    }
    return xloResult.Ret12();
}

In the example above, if a C runtime exception occurs during the call to INTAVG_Impl (where your code lives), then it will be trapped and a C++ exception wil be thrown. The CXlStructuredExceptionHandler instance does the work. In its constructor it plugs itself into the C runtime exception handler, and in its destructor it removes itself.

The error information will be returned to Excel in an error message.

Not trapping C exceptions

You may have code which already deals with these exceptions, in which case you will wish to switch off the default handling of C Runtime Exceptions. You can do this by setting the advanced function property DoNotCatchStructuredExceptions to true.

Next: Introduction to macro functions >>