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:
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. :)
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
User contributions licensed under CC BY-SA 3.0