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

Question

In an Excel formula you can use =ISERR(A1) or =ISERROR(A1)

In a VBA macro you can use IsError(sheet.Cells(1, 1))

But using a VSTO Excel Addin project I did not found similar function under the Microsoft.Office.Interop.Excel API. I only want to know if there is an error in the cell, I'm not really interested in the type of error.

My current workaround is to do this for all the existing error messages.:

if (((Range)sheet.Cells[1, 1]).Text == "#N/A" || ...)

Is there a better way to do this. Is there a simple function in the API for that?

Answer 1

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#.