Andrei Smolin

Why doesn’t Excel quit?

The case

In today’s portion of questions at Excel for Developers forum, there’s this one:

I am currently working on a project that requires Excel to be controlled from within the C# environment. The program runs correctly and quits excel however it doesn’t remove the .EXE from the task manager. This is a problem as I need to open and close the Excel many times and it is filling up my RAM. Here is a code snippet:

Ex._Application OpenExcel;
OpenExcel = new Microsoft.Office.Interop.Excel.Application();
OpenWorkbook.Save();
OpenExcel.Quit();

From my experience, Excel doesn’t quit because there’s a COM object left unreleased either in your code or in the code of a COM add-in loaded in Excel. That is, to debug such an issue, you need to start with turning all COM add-ins off.

To turn a COM add-in off:
Set LoadBehavior = 0 in {HKLM and HKCU\Software\Microsoft\Office\Excel\Addins\{an add-in}.

The theory

Now, let’s discuss this code line:

oWb = oApp.Workbooks.Open()

Its execution results in creating two COM objects: the first is a Workbook object, the other one is a Workbooks collection object. Let’s look at how the latter is created.

Because of the way .NET manages COM objects, oApp.Workbooks creates three objects:

  • #1 – the Workbooks collection, a COM object. You don’t access it in your code; instead, you manipulate it using #2 and #3 below.
  • #2 – an object called RCW which is an intermediate layer between a COM object and .NET; it deals with how the COM object is created/released. Although this is a .NET object, it isn’t accessible in your code.
  • #3 – a regular .NET object which holds a reference to the RCW (#2 above); this object represents the Workbooks collection in .NET.

That is: a .NET object (#3) holds a reference to the RCW (#2), which in its turn references the COM object (#1). When you call Marshal.ReleaseComObject(#3), #2 releases #1. Starting from this moment, invoking any property/method on #3 produces the exception “COM object that has been separated from its underlying RCW cannot be used”.

If you use Marshal.ReleseComObject to release a COM object, set the corresponding .NET variable to null (Nothing in VB.NET) as an indication that the corresponding COM object is released.

Setting null (or Nothing in VB) to a .NET variable that references a COM object doesn’t release the COM object which will stay alive until the next run of the GC. This results in holding resources and poor memory management.

When you call oApp.Workbooks.Open(), the three objects above are created for oApp.Workbooks… and another three for …Open(). The difference is: object #3 for …Open() is kept in oWb, while object #3 for oApp.Workbooks… is not referenced. It will be collected if only GC has a chance to run.

When Excel is closing…

Now, let’s see what occurs when you quit Excel.

If Excel was started by the user and the construct above is created in a VSTO add-in, then the VSTO add-in unloads the corresponding AppDomain; this kills #3, then #2 and, consequently, #1. In case of a COM add-in, Excel 2007-2010 kills unreleased objects after a time-out and this allows the add-in to unload and Excel to quit.

If Excel was run via Automation e.g. new Excel.Application(), you are responsible for releasing everything.

Conclusions:

  • When developing an application that deals with an Office application such as Excel, you need to release COM objects.
  • Debug your application or an add-in with all extensions in the Office application turned off.
  • If you develop an add-in for an Office application, you must release COM objects too; otherwise running the Office application by any standalone application will leave the Office application in the Processes list.

How and when to release COM objects?

There are three strategies:

  1. Not to release COM objects at all.
  2. Call GC.Collect at a proper moment.
  3. Use Marshal.ReleaseComObject() to release every COM object created in your code.

Strategy #1 is most wide-spread. It results in problems which are numerous and various. Excel’s non-quitting is just an example.

Strategy #2 requires that you define:
a) a proper moment(s);
b) what COM objects will be released at this moment;
c) what COM objects will live until the next such moment (or to the end) ;
d) how all this stuff will behave in different scenarios in different Excel versions.

Besides, running GC is a time-consuming operation.

Strategy #3 requires that you explicitly declare variables to hold EVERY COM object you create (such as oApp.Workbooks above) and apply Marshal.ReleaseComObject to every variable as soon as you don’t need the corresponding COM object. This is a scrupulous approach. But this is exactly what COM Rule #1 says: if you create a COM object, then you must release it. That is, following this approach is correct and expected. It does no harm, it does good only.

In When to release COM objects in Office add-ins developed in .NET, I voted for using Marshal.ReleaseComObject(). Note that Microsoft votes for it too, see the article I mentioned in my reply to the forum question.

4 Comments

  • https://secure.gravatar.com/avatar/7fcee3a2f28ba6771f4a94f8b1738bfb?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Martin says:

    Please find the code in VB.NET. It successfully releases the Excel

    Add these procedures to your source code:

    Private Sub ReleaseObject(ByVal obj As Object)
    Try
    If Not obj Is Nothing Then
    System.Runtime.InteropServices.Marshal.FinalReleaseComObject(obj)
    obj = Nothing
    End If
    Catch Ex As Exception
    obj = Nothing
    End Try
    End Sub

    Private Sub CollectReleasedObjectsUsingGC()
    Try
    GC.Collect()
    GC.WaitForPendingFinalizers()

    ‘ GC needs to be called twice in order to get the Finalizers called
    ‘ – the first time in, it simply makes a list of what is to be
    ‘ finalized, the second time in, it actually is finalizing. Only
    ‘ then will the object do its automatic ReleaseComObject.
    GC.Collect()
    GC.WaitForPendingFinalizers()
    Catch Ex As Exception
    Throw Ex
    End Try
    End Sub

    Do the following in your Method that has this Excel Application.

    Private sub ReadExcelData()
    .
    .
    If Not objExcelWorksheet Is Nothing Then ReleaseObject(objExcelWorksheet)
    If Not objExcelWorkbook Is Nothing Then ReleaseObject(objExcelWorkbook)
    If Not objExcelApplication Is Nothing Then ReleaseObject(objExcelApplication)
    .
    .
    End Sub

    Do as the following where you call the method “ReadExcelData()”
    Private sub MainMethodOrEvent()
    .
    .
    ReadExcelData()
    CollectReleasedObjectsUsingGC()
    .
    .
    End Sub

    This will surely release the Excel Process

  • https://secure.gravatar.com/avatar/29957f26ad2d8ba527fd9cc8cfa7b2e0?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Andrei Smolin (Add-in Express Team) says:

    Thank you, Martin,

    You use Excel in this way: you get required data and you don’t care about details such as when to release this or that object – you release all of them at once. In such a scenario, I probably wouldn’t release any COM object at all – calling GC.Collect() + GC.WaitForPendingFinalizers() twice should suffice.

    However, consider writing data to an Excel book – when the code reaches a certain degree of complexity, you might need to use COM objects more effectively. Also, sometimes the speed matters while GC.Collect() is a slow thing.

    A couple of suggestions regarding your ReleaseObject().

    1. You can declare the argument as ByRef rather than ByVal. In this way you guarantee that in your main code the .NET object (referencing a COM object) is released and *nullified*. Alas, passing an Object by reference doesn’t allow using Strict On.
    2. You can use Marshal.IsComObject(obj) instead of Try/Catch; the latter is resource-consuming.

  • https://secure.gravatar.com/avatar/d0507ea08085d3a602146d0a2ff4e5d9?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Salvi says:

    Very well explained! It did the trick for me on an issue I’ve been trying to solve for many days. Through this I realized that not only we have to make sure we release all the created COM objects but also the order in which it’s done matters. I was working on an application that was built by another party and we detected that defect. We spent a good deal of time trying to figure that out. But after reading your post, I realized they placed the release method of one of the worksheets last after the release method of the Excel Application. I even first thought that it was missing, added it, and looked again and realized it was there at the last line in the finally block. So I just fixed the methods call order.
    Thanks a lot for your great post!

  • https://secure.gravatar.com/avatar/29957f26ad2d8ba527fd9cc8cfa7b2e0?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Andrei Smolin (Add-in Express Team) says:

    @Salvi. Thank you. Although I suspected that the order of releasing can be essential, I’ve never met such cases. Can you please provide some code? Also, what Excel version do you use?

Post a comment

Have any questions? Ask us right now!