The proper way to dispose Excel com object using VB.NET?

Question

I have following code (obtained from online tutorial). The code is working but I suspect the way to dispose the Excel com object is somewhat not proper. Do we need really need to call GC.Collect? Or what is the best way to dispose this Excel com object?

Public Sub t1()
    Dim oExcel As New Excel.Application
    Dim oBook As Excel.Workbook = oExcel.Workbooks.Open(TextBox2.Text)

    'select WorkSheet based on name
    Dim oWS As Excel.Worksheet = CType(oBook.Sheets("Sheet1"), Excel.Worksheet)
    Try

        oExcel.Visible = False
        'now showing the cell value
        MessageBox.Show(oWS.Range(TextBox6.Text).Text)

        oBook.Close()
        oExcel.Quit()

        releaseObject(oExcel)
        releaseObject(oBook)
        releaseObject(oWS)
    Catch ex As Exception
        MsgBox("Error: " & ex.ToString, MsgBoxStyle.Critical, "Error!")
    End Try
End Sub

Private Sub releaseObject(ByVal obj As Object)
    Try
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
        obj = Nothing
    Catch ex As Exception
        obj = Nothing
    Finally
        GC.Collect()
    End Try
End Sub

Solution 1

@PanPizza C# and VB.NET are very similar, remove the ; from the end of the line, Worksheets sheets = ... becomes Dim sheets Worksheets = .... If you're interested in getting better at programming you should really learn how to transition between both as many .NET examples are only provided in one or the other and you are really limiting yourself.

As mentioned in this answer: How do I properly clean up Excel interop objects? "Never use two dots" this means always step down into a single sub-object and never do this Dim oWS AS Excel.Worksheet = oExcel.Worksheets.Open(...) always step down to workbook and then step down to the worksheet, never directly from the Excel.Application.

As a general rule what you need to do is release your items in the reverse order to that which they were created. Otherwise you're taking the feet out from underneath your other references and they won't correctly deallocate.

Notice how you create Excel Application (oExcel), then Excel Workbook (oBook) and then finally Excel Worksheet (oWS), you need to release them in the reverse order.

Thus your code becomes:

    oBook.Close()
    oExcel.Quit()

    releaseObject(oWS)
    releaseObject(oBook)
    releaseObject(oExcel)
Catch ex As Exception

and just remove this code entirely from the Sub releaseObject(ByVal obj As Object)

Finally
    GC.Collect()

It's not needed, GC occurs naturally and don't expect your applications to instantly free up memory, .NET pools unallocated memory so that it can readily instance objects in this memory rather than having to ask the OS for more memory.

Solution 2

First - you never have to call Marshal.ReleaseComObject(...) or Marshal.FinalReleaseComObject(...) when doing Excel interop. It is a confusing anti-pattern, but any information about this, including from Microsoft, that indicates you have to manually release COM references from .NET is incorrect. The fact is that the .NET runtime and garbage collector correctly keep track of and clean up COM references. For your code, this means you can remove the whole releaseObject(...) Sub and calls to it.

Second, if you want to ensure that the COM references to an out-of-process COM object is cleaned up when your process ends (so that the Excel process will close), you need to ensure that the Garbage Collector runs. You do this correctly with calls to GC.Collect() and GC.WaitForPendingFinalizers(). Calling twice is safe, end ensures that cycles are definitely cleaned up too.

Third, when running under the debugger, local references will be artificially kept alive until the end of the method (so that local variable inspection works). So a GC.Collect() calls are not effective for cleaning object like rng.Cells from the same method. You should split the code doing the COM interop from the GC cleanup into separate methods.

The general pattern would be:

Sub WrapperThatCleansUp()

    ' NOTE: Don't call Excel objects in here... 
    '       Debugger would keep alive until end, preventing GC cleanup

    ' Call a separate function that talks to Excel
    DoTheWork()

    ' Now Let the GC clean up (twice, to clean up cycles too)
    GC.Collect()    
    GC.WaitForPendingFinalizers()
    GC.Collect()    
    GC.WaitForPendingFinalizers()

End Sub

Sub DoTheWork()
    Dim app As New Microsoft.Office.Interop.Excel.Application
    Dim book As Microsoft.Office.Interop.Excel.Workbook = app.Workbooks.Add()
    Dim worksheet As Microsoft.Office.Interop.Excel.Worksheet = book.Worksheets("Sheet1")
    app.Visible = True
    For i As Integer = 1 To 10
        worksheet.Cells.Range("A" & i).Value = "Hello"
    Next
    book.Save()
    book.Close()
    app.Quit()

    ' NOTE: No calls the Marshal.ReleaseComObject() are ever needed
End Sub

There is a lot of false information and confusion about this issue, including many posts on MSDN and on StackOverflow.

What finally convinced me to have a closer look and figure out the right advice was this post https://blogs.msdn.microsoft.com/visualstudio/2010/03/01/marshal-releasecomobject-considered-dangerous/ together with finding the issue with references kept alive under the debugger on some StackOverflow answer.

Solution 3

The key for me was to let the GarbageCollector (GC) know I wanted something cleaned up. I realize this usually isn't necessary, but when working with COM objects, it is sometimes necessary. See this link for more information https://www.add-in-express.com/creating-addins-blog/2013/11/05/release-excel-com-objects/

After releasing the objects, ask the GC to clean up by calling Collect() and WaitForPendingFinalizers(). The link above states it is necessary to call these mehtods twice in order to completely remove COM objects from memory. In my case, calling these methods once worked, but it may be worth calling it twice.

oBook.Close()
oExcel.Quit()

releaseObject(oExcel)
releaseObject(oBook)
releaseObject(oWS)

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

Comments

[Petr Abdulin]

possible duplicate of How to properly clean up Excel interop objects in C#

[Pan Pizza]

@Petr Abdulin, C# is a foreign language to me. Not duplicate exactly. I even struggle to understand the accepted answer in the so called duplicate.

[Seph]

You should never need to call GC.Collect()

[Pan Pizza]

@Seph, is that required to call 'releaseObject() Sub' in order to dispose the Excel com object?

[Petr Abdulin]

@PanPizza harding.edu/fmccown/vbnet_csharp_comparison.html

[Mr.J]

@PetrAbdulin C# does not have "ME" namespace, VB.Net per se cant be deployed on Linux, sounds different to me.

[Jeremy Thompson]

@Seph I like'd your root ref explanation but with VSTO it goes a little further than that: jake.ginnivan.net/vsto-com-interop

[dustinrwh]

@Seph Thanks for the comments. I agree that GC.Collect() is not necessary in most cases because it occurs naturally, but sometimes when you are working with COM objects, it is necessary. For me, after releasing all the objects I had to call GC.Collect() followed by GC.WaitForPendingFinalizers(). There are some comments here about it.

[HackSlash]

This is a nasty hack that kills the process from the OS without de-allocating the objects you used to create those processes. This will result in null objects that will throw exceptions next time they are used.

[HackSlash]

This is interesting because when studying opinion on this topic this is the first I have ever seen an example like yours. It seems the anti-pattern is rampant in examples.