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 database 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
A 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.