none
VSTO - Memory Leak... "Starter question" RRS feed

  • Question

  • Hi there, I am new in the area of VSTO/Excel programming and discovered quite fast some memory issues which cause non-reproducible runtime errors.
    I read things about GC issues and guess I´ve got problem around that stuff. After reading this following, I hope I get some solutions for my code example at the bottom:

    http://social.msdn.microsoft.com/Forums/en/vsto/thread/45f689de-aa63-4c88-9932-9bad9b6bb299

    Unfortunately I am not an english native speaker and for that it´s quite unclear from my point of view. Her my code example which case high memory load:

      Private Sub MPOCtr_SetWeekCalendar_Click(ByVal Ctrl As Office.CommandBarButton, ByRef CancelDefault As Boolean) Handles MPOCtr_SetWeekCalendar.Click
            Dim zelle As Excel.Range
            Dim zeile As Integer = 0
            Dim strTest As String = ""
            Dim cPr As New clsProof
            Dim cCal As New clsCalendar

            Try

                For Each zelle In TargetCell
                    If cPr.isCalCell(zelle) = True Then
                        zeile = zelle.Row - 1
                        strTest = Globals.ThisWorkbook.ActiveSheet.Cells(zeile, 1).Value
                        While strTest <> "head_space3"
                            zeile -= 1
                            strTest = Globals.ThisWorkbook.ActiveSheet.Cells(zeile, 1).Value
                        End While
                        If Globals.ThisWorkbook.ActiveSheet.Cells(zeile, zelle.Column).Value = 7 Then
                            zelle.Value = "X"
                        Else
                            If Globals.ThisWorkbook.ActiveSheet.Cells(zeile, zelle.Column - 1).Value = 7 Then
                                Dim wo As String = "xxxxxxx"
                                zelle.Value = wo.Substring(0, Globals.ThisWorkbook.ActiveSheet.Cells(zeile, zelle.Column).value)
                            Else
                                Dim wo As String = "xxxxxxx"
                                zelle.Value = wo.Substring(0, Globals.ThisWorkbook.ActiveSheet.Cells(zeile, zelle.Column).Value)
                            End If
                        End If
                    End If
                Next

            Catch ex As Exception
                Dim cFehler As New clsFehler
                cFehler.Fehlerbericht(ex, "Kalender Wochenbuchung", Globals.ThisWorkbook.ActiveSheet.Name)
            End Try

        End Sub

    Perhaps Geoff Darst can help? It sound very competently in the thread mentioned above.. ;)

    Thanks all!
    Cheers Stefan

    smoeHH
    Thursday, June 23, 2011 10:13 AM

All replies

  • Hi There,

    no one can help? :(

    I am the only one, who have got experiences which "performance" issues in terms of "Cells-Operations"? It seems, that heavy usage of this kind
    of operations lead on to non-reproducible runtime errors. After such kind of error the same operation runs without an error.


    Some Examples:

    0x800A01A8;System.Collections.ListDictionaryInternal;Void HandleReturnMessage(System.Runtime.Remoting.Messaging.IMessage, System.Runtime.Remoting.Messaging.IMessage)
    0x800A03EC;System.Collections.ListDictionaryInternal
    0x8007000E (E_OUTOFMEMORY));System.Collections.ListDictionaryInternal;


    I hope anybody has some tips or/and hints regarding usage of cells/ranges and similar...

     

    Cheers
    Stefan

     


    smoeHH
    Tuesday, June 28, 2011 12:37 PM
  •  

    Hello Stefan,

    There have been other reports about both performance and apparent memory leak problems by other users of this forum – “…Visual Studio Tools for Office”.  You found one that discussed using garbage collection, and another that discussed ExcelLocale1033 proxy purposes.  There is other content at the links below that may be useful to you for the issue in your thread.

    VSTO Add-ins for Access? - Andrew Whitechapel - Site Home - MSDN ...
    http://blogs.msdn.com/b/andreww/archive/2008/02/19/vsto-add-ins-for-access.aspx?PageIndex=3

    In the blog at this site Microsoft developer Andrew Whitechapel talks in general about the purpose of the proxy for Excel.Locale,  
    “VSTO supports add-ins for most Office applications that support IDTExtensibility2-based COM add-ins, that is: Excel, InfoPath, Outlook, PowerPoint, Project, Visio and Word, but not for Access, FrontPage, Publisher or SharePoint Designer. The VSTO AddIn base class is host-agnostic (that is, agnostic to the type of the host application), and the VSTO add-in project templates/wizards are almost (but not quite) host-agnostic. Each project template generates code that is host-specific. For some hosts (Excel and Outlook) there is host-specific host-specific code, while for others there is only generic host-specific code.

    “That last sentence doesn't make a lot of sense, so let me explain. Here's what I mean by "host-specific host-specific" code – that is, code that only applies to a specific host, and uses types that are specific to that host:

    · In Excel projects, the template generates a line in the assemblyinfo.cs to set the assembly-level ExcelLocale1033 attribute. This causes the Excel object model to act the same in all locales, which matches the behavior of VBA. This attribute is specific to Excel, and is only used in Excel projects.

    · In Outlook projects, the template generates additional code in the hidden part of the partial ThisAddIn class (in ThisAddIn.Designer.cs) specific to custom form regions, based on the FormRegionReadOnlyCollection class. This is specific to Outlook, and only used in Outlook projects”


    Some other links that point to discussions of similar or related issues are found at the sites below:

    VSTO instability?
    http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/fc8b8c8c-b9b2-45a8-8f07-341666e691cd

    VSTO 2008 Excel 2007 Transparent Proxy Failure
    http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/013c294d-95b9-4a34-bf57-b014eba833c2/

    Your reply of June 28, 2011 12:37 PM says “. . . leads on to non-reproducible runtime errors. After such kind of error the same operation runs without an error.”  This implies blocking due to overuse of system resources, or synchronization conflicts between threads.  But calls to Excel members and from Excel methods are synchronized so your code shouldn’t encounter such conflicts.

    If you try to improve by steps based on the information in the content above the irregularity of your issue can be exposed by taking an iDNA TimeTravel trace.  This can be started when you start the application and run until you get one of the errors – any one if they’re not reproducible in some specific way.

    Then open a Microsoft Support incident with the purpose of getting someone to read the trace and look into the reason for the exception, and possibly provide a remedy, follow the information in the following Microsoft Knowledge Base site  -- http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone

      Regards,
    Chris Jensen
    Senior Technical Support Lead

     

    Wednesday, June 29, 2011 7:49 PM
    Moderator
  • Hi there, I am new in the area of VSTO/Excel programming and discovered quite fast some memory issues which cause non-reproducible runtime errors.
    I read things about GC issues and guess I´ve got problem around that stuff. After reading this following, I hope I get some solutions for my code example at the bottom:

    http://social.msdn.microsoft.com/Forums/en/vsto/thread/45f689de-aa63-4c88-9932-9bad9b6bb299

    Unfortunately I am not an english native speaker and for that it´s quite unclear from my point of view. Her my code example which case high memory load:

      Private Sub MPOCtr_SetWeekCalendar_Click(ByVal Ctrl As Office.CommandBarButton, ByRef CancelDefault As Boolean) Handles MPOCtr_SetWeekCalendar.Click
            Dim zelle As Excel.Range
            Dim zeile As Integer = 0
            Dim strTest As String = ""
            Dim cPr As New clsProof
            Dim cCal As New clsCalendar

            Try

                For Each zelle In TargetCell
                    If cPr.isCalCell(zelle) = True Then
                        zeile = zelle.Row - 1
                        strTest = Globals.ThisWorkbook.ActiveSheet.Cells(zeile, 1).Value
                        While strTest <> "head_space3"
                            zeile -= 1
                            strTest = Globals.ThisWorkbook.ActiveSheet.Cells(zeile, 1).Value
                        End While
                        If Globals.ThisWorkbook.ActiveSheet.Cells(zeile, zelle.Column).Value = 7 Then
                            zelle.Value = "X"
                        Else
                            If Globals.ThisWorkbook.ActiveSheet.Cells(zeile, zelle.Column - 1).Value = 7 Then
                                Dim wo As String = "xxxxxxx"
                                zelle.Value = wo.Substring(0, Globals.ThisWorkbook.ActiveSheet.Cells(zeile, zelle.Column).value)
                            Else
                                Dim wo As String = "xxxxxxx"
                                zelle.Value = wo.Substring(0, Globals.ThisWorkbook.ActiveSheet.Cells(zeile, zelle.Column).Value)
                            End If
                        End If
                    End If
                Next

            Catch ex As Exception
                Dim cFehler As New clsFehler
                cFehler.Fehlerbericht(ex, "Kalender Wochenbuchung", Globals.ThisWorkbook.ActiveSheet.Name)
            End Try

        End Sub

    Perhaps Geoff Darst can help? It sound very competently in the thread mentioned above.. ;)

    Thanks all!
    Cheers Stefan

    smoeHH


    Hello Stefan,

    try to define a variable

    Dim rng as Excel.Range

    rng = Globals.ThisWorkbook.ActiveSheet.Cells

    then use rng(x,x) at all other calls.

    Maybe that would help and get a better performance.

    Don't acces Properties over other Properties.

    The Memoryleak is there when a COM-Object is not cleaned up after usage.

    So generally when using COM Objects by .Net, you need to clean up the Objects manually.

    Set Objects to Nothing, Remove EventHandlers

    At the End do a

    GC.Collect()

    GC.WaitForPendingFinaliozers()

     

    Also there's a german VSTO-Forum where you can ask your question in youir native.

    http://social.msdn.microsoft.com/Forums/en-US/vstode/threads

     

    Hope that helps, greets - Helmut

     

     

     

     

     

     


    Helmut Obertanner [http://www.obertanner.de] [http://www.outlooksharp.de]
    Thursday, June 30, 2011 6:55 AM
    Answerer
  • Hi Chris,

    thanks for that information. I´m just wondering if the CID proxy feature in VSTO 3.0, captured in Connect bug 374434.is solved?
    Because, we are using not the en-US locale.

    However I will try it! Thanks so far..

    Regards
    Stefan

     


    smoeHH
    Thursday, June 30, 2011 4:44 PM
  • Hi Helmut!

    Klingt, als könntest Du dem Deutschen mächtig sein ? ;)

    Unfortunately, it seems that the German forum has got a much lower cases.

    Gruß
    Stefan

    smoeHH
    Thursday, June 30, 2011 4:49 PM