Section 5.10.Respond to Excel Errors in .NET | Excel 2003 Programming: A Developers Notebook (Developers Notebook)

5.10. Respond to Excel Errors in .NET

In .NET, errors are reported as exception objects. You handle errors using the VB.NET Try...Catch...End Try construct. When .NET receives an error from a COM component, such as Excel, it checks the COM error code (COM identifies errors as HRESULTs which are 32-bit numbers) and tries to map that error to one of the .NET exception classes, such as DivideByZeroException.

If .NET can't map an HRESULT to a .NET exception class, it reports that error as a COMException. A COMException includes Source and Message properties that are filled in if they are available, plus it includes the HRESULT as an ErrorCode property.

5.10.1. How it works

When working with Excel from .NET, you will find that most errors are reported as COMExceptions and that the Source and Message properties are sometimes, but not always, helpful. For example, referring to a worksheet that doesn't exist causes an COMException with Source equal to "Microsoft.Office.Interop.Excel" and a Message property "Invalid index." But setting a cell to an invalid value is reported as a COMException with an empty Source property and a Message property set to "Exception from HRESULT: 0x800A03EC."

The following code illustrates causing, catching, and reporting different types of Excel errors in .NET:

    Private Sub cmdCauseErrors_Click(ByVal sender As System.Object, _      ByVal e As System.EventArgs) Handles cmdCauseError.Click        Try         ' This worksheet (9) doesn't exist.         m_xl.ActiveWorkbook.Sheets(9).Range("B2").value = 42        Catch ex As System.Runtime.InteropServices.COMException         Debug.WriteLine(ex.Source & " " & ex.Message & " " & _           Hex(ex.errorcode))        End Try        Try         ' This is an invalid value for a cell.         m_ws.Range("A3").Value = "=This won't work."        Catch ex As System.Runtime.InteropServices.COMException         Debug.WriteLine(ex.Source & " " & ex.Message & " " & _            Hex(ex.errorcode))        End Try        Try         ' Set breakpoint here and edit a cell in Excel to see error.         m_xl.ActiveWorkbook.Sheets(1).Range("B3").select(  )         ' Can't change a cell while Excel is editing a range.         m_xl.ActiveWorkbook.Sheets(1).Range("B2").value = 42        Catch ex As System.Runtime.InteropServices.COMException         Debug.WriteLine(ex.Source & " " & ex.Message & " " & _            Hex(ex.errorcode))        End Try    End Sub

The preceding code catches the COMException that occurs for each deliberately caused error. If you run the code, the following report will display in the Visual Studio .NET Output window:

    Microsoft.Office.Interop.Excel Invalid index. 8002000B     Exception from HRESULT: 0x800A03EC. 800A03EC    mscorlib Call was rejected by callee. 80010001

As you can see, the Source and Message properties are not always helpful (or even present). In many cases, it is better to use the ErrorCode , which contains the original COM HRESULT.

HRESULTS consist of several parts, but the last 16 bits are the most useful when programming with Excel from .NET; those 16 bits contain the Excel error code for the error. The following helper function parses an HRESULT and returns the Excel error code:

    ' Returns the last 16 bits of HRESULT (which is Err code).    Function GetErrCode(ByVal hresult As Integer) As Integer        Return hresult And &HFFFF    End Function

That said, Excel assigns the error code 1004 (application error) to most of the errors it returns. All of this means that it is pretty hard to find out what specific error occurred within Excelusually you just know that the operation failed.

5.10.2. What this means

The best strategy for handling Excel errors in .NET is to:

Unitize Excel operationsthat is, try to group operations that use Excel into a single procedure that performs some atomic operation such as creating, populating, and saving a workbook.
Call these unitized operations from within a TRy...Catch structure.
Notify users of a general problem if operation fails.
Avoid user-interactive modes. Operations such as changing spreadsheet cell values can fail if the user is editing a cell when the programmatic operation occurs. Use the Excel Application objects Interactive property to turn user-interactive mode on and off.

The following code illustrates the preceding approaches in the context of making some changes to cells on a worksheet. The Excel operations are unitized in a single procedure and those operations all run within a try...Catch block. User interaction is turned off at the start of the procedure then re-enabled at the end.

    Private Sub cmdChangeCells_Click(ByVal sender As System.Object, _      ByVal e As System.EventArgs) Handles cmdChangeCells.Click        If Not SquareCells(  ) Then _            MsgBox("Excel operation failed.")    End Sub        Private Function SquareCells(  ) As Boolean        Try            ' Try to turn off interactive mode.            m_xl.Interactive = False            ' For each cell in the active sheet's used range...            For Each cel As Excel.Range In m_xl.ActiveSheet.UsedRange                ' Square the value.                cel.Value = cel.Value ^ 2            Next        Catch ex As System.Runtime.InteropServices.COMException            ' Something happened in Excel.            Debug.Fail(ex.Source & " " & Hex(ex.errorcode), ex.Message)            Return False        Catch ex As Exception            ' Something happened in .NET (display error while debugging)            Debug.Fail(ex.Source, ex.Message)            Return False        Finally            Try                ' Try to turn interactive mode back on.                m_xl.Interactive = True            Catch ex As Exception                ' No need to do anything here.            End Try        End Try        ' Success.        Return True    End Function

There are a couple of important details to point out here: first you must turn interactivity back on inside its own try...Catch block. This protects against an unhandled exception if m_xl is not a valid object (perhaps because the user has closed Excel). Second, if the worksheet contains cells with text, an error will occur, but it will be handled. This may or may not be what you want to occurthat decision is up to you .

Tip: Be careful when using For...Each with the Excel UsedCells collection. VB.NET doesn't always recognize UsedCells as a proper collection. In those cases you will encounter a Member Not Found COM error. UsedCells seems to work only when called directly from the Application.ActiveSheet or Application.Worksheets( index ) objects, rather than from variables referencing those objects.

5.10.3. What about...

To learn about	Look here
Structure of COM HRESULTs	Search msdn.microsoft.com for "Structure of COM Error Codes"
Problems using Excel collections from .NET	support.microsoft.com/?kbid=328347