Handling "No cells were found." Error in Excel

Question

I am working on Excel VSTO application and finding error cells in the worksheets using the below code

Excel.Range rngTemp;
Excel.Range rngErrorRange;

Excel._Worksheet Sheet1 = (Excel._Worksheet)xlCTA.Sheets["Sheet1"];
rngTemp = wsCTAWK11.UsedRange;
rngErrorRange = rngTemp.SpecialCells(Excel.XlCellType.xlCellTypeFormulas, Excel.XlSpecialCellsValue.xlErrors);

when there are really error cells found then i do not have any issues but when i dont have any error cells in these sheet i get the below exception

**threw an exception of type 'System.Runtime.InteropServices.COMException'
    base {System.Runtime.InteropServices.ExternalException}: {"No cells were found."}**

How to handle this... Pls help

Answer 1

Instead of using the built in SpecialCells method write your own extension method that wraps the call to myRange.SpecialCells with error handling.

I did the following:

 public static Range SpecialCellsCatchError(this Range myRange, XlCellType cellType)
    {
        try
        {
            return myRange.SpecialCells(cellType);
        }
        catch (System.Runtime.InteropServices.COMException ex)
        {
            return null;
        }
    }

Then you will have to account for null but it won't throw an error.

Answer 2

Catch the exception and handle it however you wish?

Answer 3

try
{
    Excel.Range rngTemp;
    Excel.Range rngErrorRange;

    Excel._Worksheet Sheet1 = (Excel._Worksheet)xlCTA.Sheets["Sheet1"];
    rngTemp = wsCTAWK11.UsedRange;
    rngErrorRange = rngTemp.SpecialCells(Excel.XlCellType.xlCellTypeFormulas,
Excel.XlSpecialCellsValue.xlErrors);
}
catch (System.Runtime.InteropServices.COMException ex)
{
    //Handle here
}