none
Question about Excel VSTO Project C#, how to correctly use GC.Collect(); RRS feed

  • Question

  • Hello,

    I'm developing big VSTO Excel project. There is a lot of functions and cycles, in most cases it's filling Excel cells with a lot information. 

    Found out that after each method i should set objects to null and make Garbage Collect, for e.g I'm calling similar method from 1-100 times. In rare scenarios getting random errors.

    private void go_for_it() {

    try

    { Excel.Application _with = Globals.ThisAddIn.Application; Excel.Worksheet sh = (Excel.Worksheet)_with.ActiveSheet; ... for (int i=1; i < 50000; i++) { sh.Range["AF" + i].Value = ... }
    }

    catch(exception ex)

    { MessageBox... } }

    Then I added at the end: 

    _with = null;
    sh= null;
    GC.Collect();
    GC.WaitForPendingFinalizers();
    GC.Collect();
    GC.WaitForPendingFinalizers();
    Random errors stop popping but performance went down. But the question is not about performance. It's about how/then use GC.Collect() correctly on VSTO project?

    Thursday, December 14, 2017 9:09 AM

Answers

  • Hi Vytautas,

    >> I'm calling similar method from 1-100 times. In rare scenarios getting random errors.

    Did you call go_for_it from 1-100 times? Did you call it from separate places or in a 100 loop?

    In general, VSTO Runtime does not help us to dispose COM objects. They will be disposed after we close Office application. But, if you keep Office application open for a long time and much operations, you will need to write code to clean up manually.

    You could use code below anywhere there are some objects which will not need. It will force GC to collect unused COM objects.

    For another way, you could try  System.Runtime.InteropServices.Marshal.ReleaseComObject to release object when you have finished using it.

    I suggest you check ReleaseComObject will resolve the random errors and do a better performance.

    Best Regards,

    Tao Zhou


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Vytautas A Friday, December 15, 2017 8:55 AM
    Friday, December 15, 2017 7:11 AM
  • Hi Vytautas,

    >> But also i found there is "Marshal.FinalReleaseComObject Method (Object)". Which should i use?

    The FinalReleaseComObject method releases the managed reference to a COM object. Calling this method is equivalent to calling the ReleaseComObject method in a loop until it returns 0 (zero).

    To avoid release reference when it is used by others, I think you would be better use ReleseComObject during loop, you could use FinalReleaseComObject when you are sure it will not be used anywhere else.

    >> For example if I call Marshal.FinalReleaseComObject(_with) i still need GC.Collect()? In some forums noticed for VSTO projects we need to call twice this 2 lines:

    After calling Marshal.FinalReleaseComObject, it will wait for GC, and then GC will collect randomly. Whether to call GC.Collect depends on whether you want to force it to be collected or wait for GC. If you called GC, you need to call it twice.

    Best Regards,

    Tao Zhou


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Vytautas A Friday, December 15, 2017 8:55 AM
    Friday, December 15, 2017 8:52 AM

All replies

  • Hi Vytautas,

    >> I'm calling similar method from 1-100 times. In rare scenarios getting random errors.

    Did you call go_for_it from 1-100 times? Did you call it from separate places or in a 100 loop?

    In general, VSTO Runtime does not help us to dispose COM objects. They will be disposed after we close Office application. But, if you keep Office application open for a long time and much operations, you will need to write code to clean up manually.

    You could use code below anywhere there are some objects which will not need. It will force GC to collect unused COM objects.

    For another way, you could try  System.Runtime.InteropServices.Marshal.ReleaseComObject to release object when you have finished using it.

    I suggest you check ReleaseComObject will resolve the random errors and do a better performance.

    Best Regards,

    Tao Zhou


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Vytautas A Friday, December 15, 2017 8:55 AM
    Friday, December 15, 2017 7:11 AM
  • There is a lot of loops, this was just example, but i noticed that the longer user works with project the slower it became and RAM used increasing. So I started to set COM objects = null and Gc.Collect() but was not sure or I'm right by doing this. The loops working much slower but RAM usage and stability improved heavy.

    "In general, VSTO Runtime does not help us to dispose COM objects. " - this sentence helped to understand why this happening, thank you :). 

    Also you told "Marshal.ReleaseComObject to release object when you have finished using it." But also i found there is "Marshal.FinalReleaseComObject Method (Object)". Which should i use? 

    For example if I call Marshal.FinalReleaseComObject(_with) i still need GC.Collect()? In some forums noticed for VSTO projects we need to call twice this 2 lines:

    GC.Collect();
    GC.WaitForPendingFinalizers();

    If you could answer me these few questions, i would mark this question answered, as in most part you already did :)


    • Edited by Vytautas A Friday, December 15, 2017 8:11 AM
    Friday, December 15, 2017 8:04 AM
  • Hi Vytautas,

    >> But also i found there is "Marshal.FinalReleaseComObject Method (Object)". Which should i use?

    The FinalReleaseComObject method releases the managed reference to a COM object. Calling this method is equivalent to calling the ReleaseComObject method in a loop until it returns 0 (zero).

    To avoid release reference when it is used by others, I think you would be better use ReleseComObject during loop, you could use FinalReleaseComObject when you are sure it will not be used anywhere else.

    >> For example if I call Marshal.FinalReleaseComObject(_with) i still need GC.Collect()? In some forums noticed for VSTO projects we need to call twice this 2 lines:

    After calling Marshal.FinalReleaseComObject, it will wait for GC, and then GC will collect randomly. Whether to call GC.Collect depends on whether you want to force it to be collected or wait for GC. If you called GC, you need to call it twice.

    Best Regards,

    Tao Zhou


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Vytautas A Friday, December 15, 2017 8:55 AM
    Friday, December 15, 2017 8:52 AM