Unable to Catch COMException ( VB.net Excel workbook.styles)

1

I'm using VB.net in MS Visual Studio 2012 to create an add-in for Excel (2010, 2007). The add-in uses Excel's Styles to format reports. Rather than iterate through the Styles collection, I thought it would be easier to try getting a reference to my style by name and catch the exception if it doesn't exist.

Imports xi = Microsoft.Office.Interop.Excel

<ComVisible(True)> _
<ClassInterface(ClassInterfaceType.None)> _
Public Class ReportOptions
    Implements IReportOptions

    Private Const _TitleStyleName As String = "TitleStyle"
    Private Const _SubtitleStyleName As String = "SubtitleStyle"

    Public Sub SetDefaults() Implements IReportOptions.SetDefaults
        Dim MyApp As xi.Application
        Dim wb As xi.Workbook
        Dim styles As xi.Styles
        Dim SubtitleStyle As xi.Style
        Dim TitleStyle As xi.Style

        MyApp = GetObject(, "Excel.Application")
        wb = MyApp.ActiveWorkbook
        styles = wb.Styles
        Try
            SubtitleStyle = styles.Item(_SubtitleStyleName) 'Exception here
        Catch ex As COMException
            SubtitleStyle = styles.Add(_SubtitleStyleName)
        End Try

        TitleStyle.Font.Name = "Calibri"
        'More code setting style values

        'Code to clean up the COM Objects...
    End Sub
End Class

I keep getting Visual Studio's exception dialog for "COMException crossed a native/managed boundary" with the following details:

Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))
at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
at Microsoft.Office.Interop.Excel.Styles.get_Item(Object Index)
at ReportClassLibrary.ReportClassLibrary.ReportOptions.SetDefaults() in <Path>\ReportClassLibrary.vb:line 68

I have tried everything I could think of to catch this "simple" exception:

  1. the infamous catch-all "Catch ex As Exception", or "Catch"
  2. Googled various combinations of COMException, DISP_E_BADINDEX, workbook.styles, and several forms of "can't catch exception"
  3. verified the Thrown checkbox is NOT checked for COMException under Common Language Runtime Exceptions -> System.Runtime.InteropServices
  4. treating it as a Corrupt State Exception (CSE) by adding the HandleProcessCorruptedStateExceptions Attribute
  5. Checked Enable native code debugging in the Debug section of the Project Properties

I realize it may be better (and possibly even faster) to loop through entire Styles collection looking for a match to _TitleStyleName, but I would much prefer to understand why I've been unable to simply Catch this Exception.

Thank you for taking the time to read all this. :)

vb.net
styles
try-catch
excel-interop
comexception
asked on Stack Overflow Mar 7, 2013 by Shadovv • edited Mar 7, 2013 by Shadovv

1 Answer

1

I KNEW it had to be something simple!

I was going to double-check the Exception settings as suggested by Hans Passant, but at first glance I didn't see the Exceptions option on the Debug menu. Instead, I saw Options and Settings near the bottom. This brought up the Options dialog. In the Debugging -> General section, I found Break when exceptions cross AppDomain or managed/native boundaries.

I unchecked that box and now my try/catch block handles the COMException as expected!

SUMMARY
If you have trouble catching an exception, see if it crosses an AppDomain or managed/native boundary. If it does:
1) go to the Debug Menu
2) click Options and Settings
3) go to the Debugging -> General section
4) uncheck Break when exceptions cross AppDomain or managed/native boundaries

answered on Stack Overflow Mar 11, 2013 by Shadovv

User contributions licensed under CC BY-SA 3.0