COM Interop Principle #3 – Fear the (Hidden) Period

This post is the fourth 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 covered the key principle of COM Interop – Fear the Period (aka Full Stop).

As a result, we had the following 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

In the above code, we have diligently followed the two key ways that we learnt to Fear the Period:

• 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).

Or have we?

The Hidden Period

While not evident at first glance, it actually turns out that in the above code there are hidden periods.

Huh? What are you talking about?

Well, lets take a look at one of the lines of code…

        ws = wb.Sheets(1)

The code is simple enough. We want to get the first worksheet of the workbook – so we are calling the Sheets function on the wb (Workbook) object, passing 1.

Or are we?

Whoops, it seems that Sheets isn’t a function – it is actually a Default Property. This means that:

        ws = wb.Sheets(1)

is just shorthand for:

        ws = wb.Sheets.Item(1)

See – there it is – our hidden period.

So, lets’s add a third practical way to Fear the Period (hidden or otherwise):

• 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).
• Do not ever call a Default Property.

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()
        wb = wbs.Item(1)
        Marshal.ReleaseComObject(wbs)
        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
        ws = wss.Item(1)
        Marshal.ReleaseComObject(wss)
        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. I know the tension must be killing you, but there is still one more gotcha to address…

So:

COM Interop Principle #3: Fear the (Hidden) Period

Coming up next – COM Interop Principle #4: Watch the Loops.