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).
COM Interop Principle #2: Fear the Period
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.
