Respond to Excel Exceptions in .NET

In .NET, you handle exceptions using the Visual Basic .NET Try...Catch...End Try construct. When .NET receives an exception from a COM component, such as Excel, it checks the COM exception code (COM identifies exceptions as HRESULTs that are 32-bit numbers) and tries to map that code 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 exception 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.

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

Get Programming Excel with VBA and .NET now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.