Weird occasional InvalidCastException in Excel interop

Question

I am developing a fairly comprehensive Excel add-in, applying a lot of ops. Take a workbook, save under new name, apply actions (add names, change formulae, etc).

Generally it works fine, but from time to time .Net throws an InvalidCastException on virtually any of my CTypes. When I acknowledge the error and run the code again (without quitting Excel) on the exact same Excel workbook, no error occurs. The InvalidCastException occurs maybe in 40% of all tries, otherwise the add-in succeeds.

Recently, I had to convert from C# to VB.Net. I use CType all the time, like Dim interior = CType(cl.Interior, Excel.Interior) (one of the failing statements). The error does not occur in the same place, but all over my program, also when converting ranges, for an For Each r As Excel.Range In largerRange as well as when I use Object in the For Each and CType to Excel.Range. In the latter case, I acquire an Object in the Range but the CType to Excel.Range fails. It there any way to inspect the object at this point?

I really have no clue why the add-in fails one time and the other completes without errors, even without reloading Excel (or any other participating DLL).

Any help would be greatly appreciated!

PS: VS2010, .Net 3.5SP1, Excel 2007, Imports Excel = Microsoft.Office.Interop.Excel

Answer 1

Martin, I'm no expert, but are you sure you need to use CType so often? For example, here's a simple VSTO addin that manipulates some of the objects you mentioned without casting. It is targeting Excel 2010 and .Net 4.

    Public Class ThisAddIn
        Private Sub ThisAddIn_Startup() Handles Me.Startup
            test()
        End Sub
    End Class

Module Module1
    Sub test()
        Dim appExcel As Excel.Application
        Dim wb As Excel.Workbook
        Dim ws As Excel.Worksheet
        Dim intr As Excel.Interior

        appExcel = Globals.ThisAddIn.Application
        With appExcel
            .Visible = True
            appExcel.ActiveWorkbook.Close(SaveChanges:=False)
            wb = appExcel.Workbooks.Add
            ws = wb.Worksheets(1)
            For Each cell As Excel.Range In ws.Range("A1:B2")
                intr = cell.Interior
                intr.ColorIndex = cell.Row + cell.Column
            Next
        End With
    End Sub
End Module