26

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?

Pascal
  • 1,131
  • 1
  • 9
  • 14
  • `(sheet.Cells[1,1] as Range).Text` looks better. – Pratik Deoghare Mar 11 '10 at 12:24
  • It does look better but if for some reason Cells[1,1] is not of type Range then we will have a NullReferenceException rather than a InvalidCastException. I prefer to an get more precise Exception type than improving readability is this case. – Pascal Mar 15 '10 at 11:53

2 Answers2

76

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:

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

ΩmegaMan
  • 29,542
  • 12
  • 100
  • 122
Mike Rosenblum
  • 12,027
  • 6
  • 48
  • 64
  • 13
    This kind is the of answer that makes people wish SO had a 'one-a-day super-upvote' feature – AakashM Mar 11 '10 at 16:08
  • Thanks Mike this is the information I have been looking for all day. was getting a weird number of rows returned and didin't realise VBA treats cells with a CVErr differently to .net (Which just regards them as a Int value. – Anonymous Type Mar 12 '10 at 05:04
  • Glad this helped! When I first hit on this problem some years back, it really knocked me for a loop. It's a seemingly impossible problem to solve. So once I figured out how to handle it, I just had to write it up. – Mike Rosenblum Mar 13 '10 at 23:51
  • Finally, I think that the solution by code4life is exactly what I was looking for. Thanks for all the good information on the subject anyway. – Pascal Mar 29 '10 at 13:34
  • Ok, but I'm not sure that you checked the correct answer. For example, code4life's answer will not pick up #N/A values. But it can be easily corrected: the code should be using `WorksheetFunction.IsError` instead. I can't argue with that simplicity, but I've also now added some code to my explanation above, so that you can see that it is also, ultimately, very simple. And if you ever need to pick up a *particular* CVErr value (other than #N/A, which can be picked up via `WorksheetFunction.IsNa`) then the approach that I've laid out here really is the only way. – Mike Rosenblum Mar 29 '10 at 14:38
  • You are right for IsError, but I prefer a simple function over custom code for my situation. I don't need to know the kind of error. I have edited my question to make this clear. Thanks for the help on this Mike – Pascal Mar 30 '10 at 08:03
  • No problem! One can't argue with using a built-in function. Glad I could help, and hopefully it will help others. – Mike Rosenblum Mar 30 '10 at 12:56
  • Still a very useful answer, but hard to find ! – Cyril Gandon Jun 22 '11 at 14:10
14

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.

eykanal
  • 26,437
  • 19
  • 82
  • 113
code4life
  • 15,655
  • 7
  • 50
  • 82
  • 2
    Quick note: `WorksheetFunction.IsErr` will return false for #N/A values, by design. Therefore, to use this approach, one should use `WorksheetFunction.IsError` instead. If you need to pick up a *particular* CVErr value, then you can pick up #N/A values using `WorksheetFunction.IsNA`, but for any other particular CVErr types, you'd have to use the approach I've outlined below. – Mike Rosenblum Mar 29 '10 at 14:43
  • For some reason the WorksheetFunction.IsError() doesn't work for me, but the other solution (test for `is Int32`) does work. – earcam Mar 09 '12 at 16:29
  • 1
    Note that if you already have the cell value, using Application.WorksheetFunction is very slow compared to checking the type of the value, as it is a COM interop function which must be dispatched to the excel application. – Alain Oct 01 '15 at 18:43