COM Interop Principle #2 – Fear the Period

This post is the third in my series on getting your head around COM Interop. My aim here is not to teach you all the innards of COM Interop, but instead to communicate a few key principles. These principles will (hopefully) make your life a little simpler when coding in .NET against COM components (particularly in relation to getting the COM objects to clean themselves up).

Last week’s post was about what not to do when dealing with COM Interop.

So, what should you do instead? Well, first and foremost you must Fear the Period.

The most powerful weapon in your arsenal when correctly doing COM Interop is Marshal.ReleaseComObject. You can see some examples of its use in the previous two posts in this series. Unfortunately, ReleaseComObject has a natural enemy – the period (or full stop).

So, what does Marshal.ReleaseComObject do? Well, it decrements the reference count of the RCW that you pass to it. Or, in English, it releases the underlying COM object (*).

So, what does the period have to do with anything? Well, lets look at some of the code from the original post…

Option Strict Off
Option Explicit On
Imports msE = Microsoft.Office.Interop.Excel
Imports System.Runtime.InteropServices
Public Class Form1
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim xlApp As msE.Application
        '==========================
        xlApp = New msE.Application
        xlApp.DisplayAlerts = False
        xlApp.Interactive = False
        xlApp.Workbooks.Add()		<---- Focus your attention on this line
        <snip>
        Marshal.ReleaseComObject(xlApp)
    End Sub
End Class

Looking at the code we can see that xlApp is being cleaned up correctly at the end of the code. But have a look at:

        xlApp.Workbooks.Add()		<---- Focus your attention on this line

Let’s have a think about what that code is doing. We are calling a property on the xlApp object which is returning a ‘temporary’ Workbooks object. Then we are calling the Add method (**) on that ‘temporary’ Workbooks object.

But hold on, where are we cleaning up that ‘temporary’ Workbooks object? (Hint: We aren’t!)

This is why we must Fear the Period. To get the above code to work you must introduce an explicit Workbooks variable – this then allows you to explicitly clean it up. So the code would become:

        Dim wbs As msE.Workbooks
        wbs = xlApp.Workbooks
        wbs.Add()
        Marshal.ReleaseComObject(wbs)

So, how do you Fear the Period in practice?

• Do not ever allow two periods to be consecutive (as in the above example of xlApp.Workbooks.Add).
• Do not ever allow any periods to appear in the ‘In’ section of a For Each declaration (as in For Each wrk As msE.Worksheet In wb.Worksheets).

So, lets incorporate this principle into the original code:

Option Strict Off
Option Explicit On
Imports msE = Microsoft.Office.Interop.Excel
Imports System.Runtime.InteropServices
Public Class Form1
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim wb As msE.Workbook
        Dim ws As msE.Worksheet
        Dim xlApp As msE.Application
        '==========================
        xlApp = New msE.Application
        xlApp.DisplayAlerts = False
        xlApp.Interactive = False
        Dim wbs As msE.Workbooks
        wbs = xlApp.Workbooks
        wbs.Add()
        Marshal.ReleaseComObject(wbs)
        wb = xlApp.Workbooks(1)
        Dim wss As msE.Sheets
        wss = wb.Worksheets
        'Clear all but the first worksheet
        For Each wrk As msE.Worksheet In wss
            If wrk.Index > 1 Then wrk.Delete()
        Next
        Marshal.ReleaseComObject(wss)
        ws = wb.Sheets(1)
        xlApp.Visible = True
        Threading.Thread.Sleep(5000)    'So we can see Excel
        wb.Close(False)
        xlApp.Quit()
        Marshal.ReleaseComObject(ws)
        Marshal.ReleaseComObject(wb)
        Marshal.ReleaseComObject(xlApp)
    End Sub
End Class

So, did that fix the issue?

Unfortunately not. While Principle #2 is definitely the most important principle when dealing with COM Interop, there are still a couple of gotchas in the original sample that we have yet to address…

So:

COM Interop Principle #2: Fear the Period

Coming up next – COM Interop Principle #3: Fear the (Hidden) Period.

* This is an over-simplification, and there a number of finer points that I am purposely ignoring – to keep the discussion simple.
** Let’s ignore what the Add method actually does for now – it isn’t relevant to this post.