none
Random InvalidCastException on Excel Addin RRS feed

  • Question

  • I'm getting random annoying InvalidCastException. Sometimes the error message is "Unable to cast transparent proxy to type Microsoft.Office.Interop.Excel.Range" and some other  times "Return argument has an invalid type"

    It's basically the same issue than http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/785d743f-e9ff-4c1e-a96d-6b0f12c17eb0

    but I opened a new thread to give my example of code and attach a sample VS project to reproduce it:

    Dim sheet As Excel.Worksheet = DirectCast(Globals.ThisAddIn.Application.ActiveSheet,Excel.Worksheet)
    Dim tmpRange As Excel.Range
    For i As Integer = 1 To 60
     For j As Integer = 1 To 60
       tmpRange = DirectCast(sheet.Cells(i, j), Excel.Range)
       For Each cell As Excel.Range In tmpRange.Cells
          With cell.Interior
    
          End With
       Next
      Next
    Next
    

    After a few times this code is executed, an InvalidCastException is thrown regarding the instruction "sheet.Cells(i, j)".

    I attach a project in which this code is executed inside an infinite loop inside a try catch block. At least in my environment, it usually fails before the 7th time it enters the loop.

    The example project is TestInvalidCastException.zip: https://skydrive.live.com/?cid=9db0e3ac656426f8&sc=documents&uc=1&id=9DB0E3AC656426F8%21136#

    Could this be related with Project References? The project by default includes Microsoft.Office.Interop.Excel version 12.0.0.0

    Thursday, July 7, 2011 9:36 PM

Answers

  • Hello Tom,
    I've tested the issue on my PC.
    The code (based on the code supplied by FechoArg is as follows:
      Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) Handles Button1.Click
        Dim counter As Long = 0
        Try
          Dim sheet As Excel.Worksheet = CType(Globals.ThisAddIn.Application.ActiveSheet, Excel.Worksheet)
          Dim cells As Excel.Range = sheet.Cells
          Dim tmpRange As Excel.Range
          Do
            For i As Integer = 1 To 60
              For j As Integer = 1 To 60
                Dim tempObj As Object = cells.Item(i, j)
                tmpRange = CType(tempObj, Excel.Range)
    
                Dim theColumns As Excel.Range = tmpRange.Columns
                Dim theRows As Excel.Range = tmpRange.Rows
                For k1 As Integer = 1 To theColumns.Count
                  For k2 As Integer = 1 To theRows.Count
                    Dim cells2 As Excel.Range = tmpRange.Cells
                    Dim tempObj2 As Object = tmpRange.Item(k2, k1)
                    Dim cell As Excel.Range = CType(tempObj2, Excel.Range)
                    Dim inter As Excel.Interior = tmpRange.Interior
                    With inter
    
                    End With
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(inter) : inter = Nothing
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(tempObj2) : tempObj2 = Nothing
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(cells2) : cells2 = Nothing
                  Next k2
                Next k1
                System.Runtime.InteropServices.Marshal.ReleaseComObject(theRows) : theRows = Nothing
                System.Runtime.InteropServices.Marshal.ReleaseComObject(theColumns) : theColumns = Nothing
                System.Runtime.InteropServices.Marshal.ReleaseComObject(tempObj) : tempObj = Nothing
              Next
            Next
            counter = counter + 1
          Loop Until counter > 100
          System.Runtime.InteropServices.Marshal.ReleaseComObject(cells) : cells = Nothing
    
        Catch ex As Exception
          MsgBox("Failed after " & counter.ToString & " loops.")
          MsgBox(ex.Message & System.Environment.NewLine & ex.StackTrace)
        End Try
        MsgBox("OK. " & counter.ToString)
      End Sub
    
    The issue is reproducible in a new Excel 2007 add-in project created in VS 2008; the add-in is loaded in Excel 2010 32 bit. I confirm that the issue isn't reproducible if I set ExcelLocale1033(False). The issue isn't reproducible in an Excel 2010 add-in project created in VS 2010; the add-in is loaded in Excel 2010 32-bit, too. All service packs and updates are installed.

    Regards from Belarus (GMT + 2),

    Andrei Smolin
    Add-in Express Team Leader
    Wednesday, July 13, 2011 3:59 PM

All replies

  • Hello,

    I think your code produce problems on some worksheets only. Is it correct? If so, I suggest that you debug it. A good example of the need to debug is the very first line of your code: it will fail on a chart sheet. Also, I suggest that you pay attention to the page describing DirectCast on MSDN.


    Regards from Belarus (GMT + 2),

    Andrei Smolin
    Add-in Express Team Leader
    Friday, July 8, 2011 9:29 AM
  • No, it actually produce problems in any random worksheet, and randomly in the time. I attached a project in which the code is executed inside an infinite loop and there the "randomness" of the problem can bee seen.

    I'm testing the code above (and the project attached) with clean new workbooks. If it would be the case that I'm dealing with a Chart, the exception would be thrown the first time that line of code is executed, but instead of that the code runs well until some random point in which the error arises.

    I do know about DirectCast restrictions. I'm really passing Worksheets to the code. Besides, the code above is not the real code of my program, it's just a sample code that does nothing just to isolate the problem.

    I think the problem is not related to the actual code but maybe with the references of the project (something with Microsoft.Excel.Interop, PIA, etc), the LCID problem (ExcelLocale1033). But I've tried changing references and the issue persists.

     

    Friday, July 8, 2011 5:22 PM
  • There is nothing attached to this discussion - it's not possible to post attachments to MSDN forum messages. You'll need to post a link to a Website or Windows Live or something where those who are interested can d/l it...
    Cindy Meister, VSTO/Word MVP
    Saturday, July 9, 2011 2:38 PM
    Moderator
  • I uploaded TestInvalidCastException.zip to skydrive here (my mistake for not hyperlinking the text in the first post):

    TestInvalidCastException.zip on Skydrive

    We've just noticed that this issue disappears when we set <Assembly: ExcelLocale1033(False)> in AssemblyInfo.vb

    BUT when we do that, this issue arises:

    RemoveHandler throws NullReferenceException when there's no handler registered and ExcelLocale1033 is set to false

    so we are between a rock and a hard place.


    Saturday, July 9, 2011 5:41 PM
  • We could obviously add a try catch to avoid this, but if ExcelLocale1033 breaks something so unrelated like RemoveHandler, I think we can expect it will break some other code that is now working correctly.
    Saturday, July 9, 2011 5:59 PM
  • Hi FerchoArg,

    Thanks for posting in the MSDN Forum.

    I download the project from your skydrive. I found there exists endless circle in you code. So I can not reproduce your issue on my side. Would you please check you project again?

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, July 12, 2011 9:16 AM
    Moderator
  • Yes there is an intentional infinite loop as I mentioned before in that sample code because -anyway- it fails before the 10th time it enters that loop.

    That's because Assembly: ExcelLocale1033 is set to True.

    If you change ExcelLocale1033 and you set it to  False, then it would be a real endless loop because it does not throw any random exception.

    Anyway, I've just upload a new file with the same sample code (it is clear that this code does nothing, it's just to isolate the issue) without the endless loop, using a for 1 to 200 instead.

    https://skydrive.live.com/?cid=9db0e3ac656426f8&sc=documents&uc=1&id=9DB0E3AC656426F8%21136#

    Be patient, it takes about to 25 seconds until the exception is thrown after you press button1.

     

    Thanks for your time, I really appreciate it.

     

    Fer

     

    Tuesday, July 12, 2011 9:36 AM
  • FerchoArg,

    I tried to run your project on my side (Win7, Visual Studio 2008(not sp1), Office 2007), it caused a great deal of errors on my side, would you please tell me your environment to develop your project?

    And “ExcelLocale 1033” is an attribute to check whether your project is used English (US) format for the resource for the culture-sensitive call. Do you develop your project in English environment?

    Did you add VSTO SmartTags and Custom Task Pan in your project ago? Form the error that Visual Studio provided it seems exist VSTO SmartTags and Custom Task Pan in you project, but I can’t find them. I assume you have created them before, and delete them from your project at last. I would recommend you create a clear project to reproduce your issue.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, July 13, 2011 8:52 AM
    Moderator
  • Hello Tom,
    I've tested the issue on my PC.
    The code (based on the code supplied by FechoArg is as follows:
      Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) Handles Button1.Click
        Dim counter As Long = 0
        Try
          Dim sheet As Excel.Worksheet = CType(Globals.ThisAddIn.Application.ActiveSheet, Excel.Worksheet)
          Dim cells As Excel.Range = sheet.Cells
          Dim tmpRange As Excel.Range
          Do
            For i As Integer = 1 To 60
              For j As Integer = 1 To 60
                Dim tempObj As Object = cells.Item(i, j)
                tmpRange = CType(tempObj, Excel.Range)
    
                Dim theColumns As Excel.Range = tmpRange.Columns
                Dim theRows As Excel.Range = tmpRange.Rows
                For k1 As Integer = 1 To theColumns.Count
                  For k2 As Integer = 1 To theRows.Count
                    Dim cells2 As Excel.Range = tmpRange.Cells
                    Dim tempObj2 As Object = tmpRange.Item(k2, k1)
                    Dim cell As Excel.Range = CType(tempObj2, Excel.Range)
                    Dim inter As Excel.Interior = tmpRange.Interior
                    With inter
    
                    End With
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(inter) : inter = Nothing
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(tempObj2) : tempObj2 = Nothing
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(cells2) : cells2 = Nothing
                  Next k2
                Next k1
                System.Runtime.InteropServices.Marshal.ReleaseComObject(theRows) : theRows = Nothing
                System.Runtime.InteropServices.Marshal.ReleaseComObject(theColumns) : theColumns = Nothing
                System.Runtime.InteropServices.Marshal.ReleaseComObject(tempObj) : tempObj = Nothing
              Next
            Next
            counter = counter + 1
          Loop Until counter > 100
          System.Runtime.InteropServices.Marshal.ReleaseComObject(cells) : cells = Nothing
    
        Catch ex As Exception
          MsgBox("Failed after " & counter.ToString & " loops.")
          MsgBox(ex.Message & System.Environment.NewLine & ex.StackTrace)
        End Try
        MsgBox("OK. " & counter.ToString)
      End Sub
    
    The issue is reproducible in a new Excel 2007 add-in project created in VS 2008; the add-in is loaded in Excel 2010 32 bit. I confirm that the issue isn't reproducible if I set ExcelLocale1033(False). The issue isn't reproducible in an Excel 2010 add-in project created in VS 2010; the add-in is loaded in Excel 2010 32-bit, too. All service packs and updates are installed.

    Regards from Belarus (GMT + 2),

    Andrei Smolin
    Add-in Express Team Leader
    Wednesday, July 13, 2011 3:59 PM
  • Hi Andrei,

    Thanks for you great work.

    As you side this issue seems has been fixed in Visual Studio 2010. I think it can be an answer for FerchoArg’s issue.

    @FerchoArg,

    What do you think about this? If you feel Andrei’s answer can solve your issue please mark it as an answer. If you have any questions please feel free to let me know.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, July 15, 2011 6:24 AM
    Moderator
  • I'm experiencing the exact same issue as FerchoArg.

    When the InvalidCastException occurs in my code, I can unwind in visual studio, then press F5 to run the exact same line again, and it will succeed. 

    I can "work around" the issue by littering my code with statements like the following:

    Dim gCell As Excel.Range = Sheet.Cells(gRow, gCol)

    Try gCell.Interior.Color = System.Drawing.ColorTranslator.ToOle(My.Settings.NoTestsColor) 'white Catch ex As InvalidCastException gCell.Interior.Color = System.Drawing.ColorTranslator.ToOle(My.Settings.NoTestsColor) 'white End Try


    Is the approved Microsoft solution really "Upgrade to a newer version of visual studio"? Isn't it reasonable to expect the version of Visual Studio/.NET/VSTO that I have to at least work consistently?



    Tuesday, June 11, 2013 11:02 PM