Matthew Wills

June 4, 2008

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

Filed under: COM Interop, Technical — mjwills @ 11:39 am

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

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

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?

Is it a function?

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.

May 26, 2008

COM Interop Principle #2 – Fear the Period

Filed under: COM Interop, Technical — mjwills @ 12:37 pm

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.

May 19, 2008

COM Interop Principle #1 – Don’t Cheat

Filed under: COM Interop, Technical — mjwills @ 12:22 pm

This post is the second 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 #1: Don’t Cheat (by relying on the Garbage Collector)

In my previous post, I listed a short code sample that was not behaving as I might have liked. Specifically, it was not closing down Excel when I wanted it to. In that post I called for attempts to get the code ‘working’ without calls to GC.Collect().

But, what if you ignored my requirement to not use GC.Collect()? Well, you would likely have come up with a solution something like this:

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
        xlApp.Workbooks.Add()
        wb = xlApp.Workbooks(1)
        'Clear all but the first worksheet
        For Each wrk As msE.Worksheet In wb.Worksheets
            If wrk.Index > 1 Then wrk.Delete()
        Next
        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)
        GC.Collect()
        GC.Collect()' A couple more for good measure
        GC.Collect()' A couple more for good measure
        GC.WaitForPendingFinalizers()

    End Sub
End Class

So, what is the problem?

There are two:

  1. It doesn’t work (well, not on my machines running Visual Studio 2008 on Vista 32-bit, nor my machines running Visual Studio 2003 on XP 32-bit).
  2. Even if it did work on your PC (or in your version of the .Net Framework) there is no guarantee that it would work in the future or on other machines.

So:

COM Interop Principle #1: Don’t Cheat (by relying on the Garbage Collector)

As with all principles, there are exceptions! Such as:

  • If the lifetime of your RCWs (COM components) is the same as your application (eg you will be loading Excel when your application starts, and closing it when your application closes) then there is little point in cleaning up correctly. You can be confident (not 100% sure, but confident) that all RCWs will be cleaned up when your process shuts down. So, cheating is OK. :)

Coming up next – COM Interop Principle #2: Fear the Period.

May 15, 2008

COM Interop – The Appetiser

Filed under: COM Interop, Technical — mjwills @ 12:39 pm

So, you want to get your head around COM Interop? (You don’t? Well, just imagine you did.) You could read Adam Nathan’s 1500 page classic (which I thoroughly recommend) or you could start with something a little simpler. :)

I am hoping over the next couple of weeks to put together a set of blog posts discussing some tips on how to do COM Interop correctly – particularly in terms of cleaning up after yourself.

As part of this I will be starting with a block of code that isn’t doing what I want (it is based on some real production code). You can copy and paste the code into a new VB.NET project. The question I will be looking at over the coming blog posts is:

What code changes are required to (reliably) make sure Excel is not still running when the method finishes (without calls to GC.Collect)?

Make sure you add a reference to Excel in your project…

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
        xlApp.Workbooks.Add()
        wb = xlApp.Workbooks(1)
        'Clear all but the first worksheet  
        For Each wrk As msE.Worksheet In wb.Worksheets
            If wrk.Index > 1 Then wrk.Delete()
        Next
        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)
        'Your objective is for the EXCEL.EXE process to not be  
        'running by the time you get here...  
        'No use of GC.Collect allowed  
    End Sub
End Class

 

Feel free to post comments (or email me) with solutions, questions, comments etc…

May 5, 2008

Operation Cheapskate: Episode Two

Filed under: Cheapskate — mjwills @ 11:40 am

Given the positive feedback I continue to receive concerning my first Operation Cheapskate entry, now is as good a time as any for Episode Two.

 

Saving money on your mobile phone bill

  • If you are using Optus Prepaid you may be interested in this trick technique to make calls for around 10c / minute.

Discounts when buying Dell PCs

  • If you are planning to buy a Dell PC, then be sure to read this post about obtaining discounts. I have seen a number of people recently follow some of the suggestions in this post and save more than $300 each (not bad for 10 minutes effort).
  • You would also be wise to sign up for Dell’s email list a month or so before you plan to buy. They often include coupon codes for obtaining discounts – and are sometimes quite generous.

Entertainment Book

  • You really owe it to yourself to check out the Entertainment Book. It includes over 400 coupons for everything from restaurants, to fast food, to cinema tickets and Zoo entry. Even if you only get out to a nice restaurant twice a year, the savings from those two outings alone will cover the cost of the book. And that is ignoring the area where my family saves the most – and that is fast food discounts (McDonalds, New Zealand Natural, Pizza Hut, Krisky Kreme – that kind of thing).
  • Of course, if you are a true Cheapskate there is an even better option – to get a friend to buy the book so you can ‘borrow’ some of them. :)

Coles

  • So you shop at Coles, huh? Well even more reason to pick up an Entertainment Book. The Entertainment Book entitles you to 5% off Coles Gift Cards. So if you spend $100 a week at Coles, you could save around $150 a year after factoring in the cost of the Entertainment Book.

Choosing superannuation

  • Note this information is of a general nature and does not take into account your financial needs blah blah blah.
  • When moving over to Readify I had to choose a new superannuation fund. One of my co-workers wisely suggested I sign up with AGEST. They are very competitive in terms of fees and returns.
  • You may also find these links to be helpful in choosing your own superannuation provider:

Buying nappies

  • OK, we can kiss my street-cred goodbye (if I had any!), but regardless… If you buy disposable nappies you will know they aren’t exactly cheap. Here is my recommendation to you – give Aldi’s nappies a try. Of the many brands I have tried they are definitely the highest quality (on par with Huggies) and yet they are priced very competitively (cheaper than Snugglers).

Save money on magazines

  • I am always surprised by how many people are unaware of one of your greatest allies in saving money – your local library. Most local libraries (particularly larger ones) will carry a range of magazines. Their range of computer related magazines is never going to match your local newsagent – but you can’t argue with the price!

April 28, 2008

Code Camp Oz 2008

Filed under: Technical — mjwills @ 11:05 am

2008 was the year I finally decided to get organised and head down to Code Camp Oz. And it was more than worth my time.

For those of you who haven’t attended Code Camp before, it felt to me like a low-key TechEd or maybe a 2-day RDN. The quality of presentations was generally very high – Fernando Guerrero’s presentation (‘Lessons learned while tuning database systems’) was the highlight for me, but I also particularly enjoyed the presentations by Mahesh Kirshnan (‘How well do you know your IDE?’) and Paul Stovell (‘SyncLINQ‘). Phillip Beadle’s presentation on Test Driven Development reminded me of my urgent need to get further up to speed on TDD.

Some other random thoughts from my first experience at Code Camp:

  • I stayed at Mercury Motor Inn. I won’t be racing to stay there next year. :)
  • The organisation and catering for the event (including the Wine and Cheese night, BBQ, pizza etc) was very well co-ordinated. A big thumbs-up to all involved.
  • I had a real issue finding the venue on the first day (it took me more than 30 minutes, and even then it was only because I followed some other people who had been previously). I think next year I will volunteer to put some signs out on the Friday.
  • The WiFi at the venue was surprisingly good (initial connection was annoying, but after that…). I really wasn’t expecting to be able to make VoIP calls while I was there – but it worked well.
  • Having my Readify loaner laptop with me reminded me of one key thing I need to consider when picking a new laptop in 2 months – battery life. While the majority of Readify staff seem to get Dell laptops (particularly the XPS M1530) they don’t seem to have the variety of battery options as say the Thinkpad T61P. In fact when I try and spec a M1530 online, Dell doesn’t even provide the option of a second battery (let alone a third!) as part of the transaction.

All in all a great experience. I thoroughly recommend you attend in 2009 – if you live in Australia!

April 7, 2008

Operation Cheapskate: Episode One

Filed under: Cheapskate — mjwills @ 10:41 am

Given my reputation as a cheapskate bargain hunter, I will be starting an irregular series that may help you in your money saving ways. These will be particularly appropriate to those in Australia, but may have broader applicability in some instances.

Now the cynical among you might think this is because I can’t think of anything else to blog about (actually, that is a strong possibility). But by incorporating even 1 or 2 of these ideas you might be able to save anywhere from $10 – $500 a year. And many are quite simple to do (almost all of the ideas and services I will be suggesting I have actually used and still use today).

So here goes with Operation Cheapskate: Episode One (in no particular order)…

  

Websites to find out about bargains

Saving money on your phone bill (landline or mobile)

  • If you have a home phone line and make alot of STD calls then consider Better Telecom who do flat rate STD calls for 17.5c a call. Cheaper still is Voice over IP – you can read about this more here.

Banking

  • If you are planning to get a new loan, make sure you shop around.  There are some websites that will help you get started, but note that you may be able to negotiate a better deal with the lender or a mortgage broker (for example I negotiated not having to pay Lender’s Mortgage Insurance when I got my first home loan).  I can certainly recommend my mortgage broker (disclaimer: he is my brother-in-law) – he has saved me thousands of dollars.
  • For day-to-day banking, look at the fees you are paying and see if you can get a better deal elsewhere. Credit Unions, in particular, are an excellent option. Gateway Credit Union is open for membership to the general public and is worthwhile investigating. One of the best credit unions in Australia (from my research) is the QANTAS Staff Credit Union (of which I am a member). If you (or a member of your family) meet the eligibility criteria then you will find your transaction fees massively reduced (there are no monthly fees, free cheque facilities, 12 free monthly withdrawals at certain ATMs, free online banking, free phone banking etc etc).

Choosing a Health Insurance Provider

  • ISelect is a great tool for helping you wade through the myriad of options.

March 31, 2008

Coolest bug you have ever found?

Filed under: Technical — mjwills @ 7:34 am

So, what is the coolest bug you have ever found?

Let me state up front that I completely agree with Jeff AtwoodIt’s Always Your Fault. For me its simply a numbers game – given that history has shown me that 99% of the time it is my fault, it seems ludicrous when confronting a new problem to assume the fault lies elsewhere.

Nonetheless, sometimes the 1% case does occur. You do find a bug in the OS. Or a bug in the compiler. Or a bug in a vendor’s control. The programmers working on the OS, or the compiler, or the control, are likely more skilled than I. But they are human too – they make mistakes.

So today I would be interested to hear what is the coolest bug you have ever found (‘found’ meaning you discovered it, as opposed to having someone else show it to you)? Note that you can interpret coolest any way you like. Weirdest, most frustrating, funniest, whatever.

Below is a code snippet illustrating what is the coolest bug I have ever found. I discovered the bug while writing some code to test my VB6 application. The application I was testing converted numbers to text (for the purposes of automatic cheque generation) – so $16.52 would become Sixteen dollars and fifty two cents.

The test code I wrote is below (you can open up Microsoft Word, press Alt-F11 and paste this code in to run it for yourself – the bug still exists). Usenet has some discussion on the issue (its a very old discussion).

Public Sub Bob()

Dim c As Currency

'This code works
For c = 1@ To 10@ Step 1@
	'Call GenerateChequeName(c)
Next
MsgBox c
'11 - makes sense

'This code works
For c = 0@ To 113400000@ Step 567@
	'Call GenerateChequeName(c)
Next
MsgBox c
'113400567 - makes sense

'This code doesn't work
For c = 0@ To 1134000@ Step 5.67@
	'Call GenerateChequeName(c)
Next
MsgBox c
'858993.66 - huh?

End Sub

So, what is the coolest bug you have ever found?

March 26, 2008

You + Ian Griffiths = Free Zune (*)

Filed under: Technical — mjwills @ 10:05 am

Ian Griffiths                        Zune 30

I am assuming most of you are familiar with Ian Griffiths (of Pluralsight). He is the author of a number of books – the most recent I have read was Programming WPF (co-authored with Chris Sells). Ian’s blog is a must read.

Well, last year Ian visited Sydney, Australia to run a 4 day Applied WPF course. He was even kind enough to put his demos online.

Well, this year we have the pleasure of an encore performance - running from 29th April to 2nd May. For someone of Ian’s calibre this is a rare treat for we Sydney-siders.

Now to commemorate his return to the land down under, Readify will be giving away a free Microsoft Zune 30. And how do you get your hands on this Zune, you ask? Its simple. If you register for Ian’s course, and put ‘I want me a Zune’ under the Additional Information section of the booking form, then you are in the running.

So what else could you ask for? One of the world’s great technology experts, teaching one of the world’s most interesting rich client technologies, and the chance at winning the world’s finest MP3 player (**).

(*) Maybe.
(**) Well, ignoring the Zune 80 and the iPod Touch. :)

March 18, 2008

MIX 08 Sessions – Zune feed

Filed under: Technical — mjwills @ 12:37 pm

One of the ‘problems’ of owning a Zune is that you get used to being able to easily subscribe and download podcast content. In fact, you get to the point where if you can’t just subscribe to the RSS feed then you don’t bother downloading and viewing the content.

Generally this isn’t a major issue, but it does annoy me with regard to Flash based websites and their RSS feeds.

But just recently, an even more annoying case raised its head. And that was with regard to the MIX08 Sessions.

MIX08 was recently held in Las Vegas, and from it were made available 88 presentations that can be viewed online or downloaded. At first glance it looks promising for me and my Zune obsession – there is even a ‘Download for Zune’ option.

downloadforzunesmall.jpg

So what is the problem? Well, I am in no mood to download 88 WMV files one by one, then tell the Zune software about them and sync them across. There must be a RSS feed to do this automatically, right?

Wrong. (Well, there is this – but there are only 15 entries in it so at the very least it is incomplete).

So, seeing as I didn’t want to waste 20 minutes downloading the files manually, telling the Zune software about them and syncing them across, I instead spent 60 minutes putting together a very simple RSS feed to allow myself (and others) to download the MIX08 sessions just like any other podcast.

Keep in mind that I have had no experience putting together a RSS feed before (let alone a podcast RSS feed), so don’t expect any fancy schmancy stuff like session descriptions or file sizes. But it has been tested with the Zune client software, and it does work. I think.

Older Posts »

The Silver is the New Black Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.