How to know if a cell has an error in the formula in C#

Dealing with CVErr values in .NET is a very tricky subject. The problem is that .NET (rightfully) regards CVErr as obsolete with respect to error handling. CVErr values, however, are still used in Excel cells, so this is a rather large omission for Excel automation.

Fortunately, there is a workaround. The way to check for CVErr values is to examine the data type held by the cell. If the value held is typed as an Integer (Int32) then the value held is a CVErr. (Note that numerical values held in a cell are normally typed as Double, only CVerr values can come through as Integer.)

That is, at the simplest level, to test for a CVErr value, all you need to do is use the following function:

bool IsXLCVErr(object obj)
{
    return obj is Int32;
}

If you need to check for a specific CVErr value (e.g., #N/A), then you would first check to make sure that the data type is an Integer (Int32) and then check the specific value held by the cell, according to this table:

• -2146826281 = #DIV/0!
• -2146826246 = #N/A
• -2146826245 = #GETTING_DATA
• -2146826259 = #NAME?
• -2146826288 = #NULL!
• -2146826252 = #NUM!
• -2146826265 = #REF!
• -2146826273 = #VALUE!

For example, your code could look like this:

enum CVErrEnum : Int32
{
    ErrDiv0 = -2146826281,
    ErrGettingData = -2146826245,
    ErrNA = -2146826246,
    ErrName = -2146826259,
    ErrNull = -2146826288,
    ErrNum = -2146826252,
    ErrRef = -2146826265,
    ErrValue = -2146826273
}

bool IsXLCVErr(object obj)
{
    return (obj) is Int32;
}

bool IsXLCVErr(object obj, CVErrEnum whichError)
{
    return (obj is Int32) && ((Int32)obj == (Int32)whichError);
}

I wrote a detailed two-part article on this a few years ago:

• Dealing with CVErr Values in .NET – Part I: The Problem
• Dealing with CVErr Values in .NET – Part II: Solutions

The articles are written for VB.NET, but the principles are exactly the same as for C#.

You can use the WorksheetFunction method:

Globals.ThisAddIn.Application.WorksheetFunction.IsErr(...)

or

[Your Excel Object].WorksheetFunction.IsErr(...)

The IsErr is semantically identical to the Excel worksheet function, only instead of the cell reference pass in the actual value - AFAIK.