Tuesday, 29 March 2011

The Official VSTO 2010 Sanity Guide For C# Developers

Chris Domino, Director, Enterprise Architect

I just spent the last three hours trying to get a reference to the first row of an Excel Spreadsheet using a Visual Studio Tools for Office (VSTO) 2010 managed add-in. If I were still a VB guy like I was seven years ago, this would have taken only a three minutes. But as fate would have it, arrays of cells in Excel VSTO 2010 are not zero-based; they are 1-based. So to get the first row of a workbook, you need to do this:

  1. Range firstRow = Globals.ThisAddIn.Application.Cells[1, Globals.ThisAddIn.Application.Columns.Count].EntireRow;

This is probably due to the fact that VSTO grew out of VBA which is based in Visual Basic, where arrays are 1-based and the sky is green and gravity is reversed. I want my evening back.

As I dug to figure this out, I was presented with so many COM errors and HRESULT exceptions that I almost gave in. Immediate-Window-debugging your way through Office interop is onerous. Even in the 2010 release of Visual Studio, VSTO, which is indeed easier to work with than it was in the past, is still a flimsy crust of managed code floating overtop of a thick, gooey, impenetrable mantle of magma-like COM.

One of the major bullet points of .NET 4.0 is better COM integration, and I assume that most people who will experience this at all are going to do so through the use of VSTO. So why is it all COM? Even the SharePoint object model embraces its modern cousin technologies (for example, the Silverlight Client Object Model and SPMetal). Why isn't the Office API more managed? I know I'm being a total Barbie Doll about this, but programming against Office, at least for me, just isn't fun.

The VSTO API, fortunately, does have enough managed code to keep me sane. Since I learn new APIs via IntelliSense and the Immediate Window, every other object being of type "System.__ComObject" doesn't help much. And no, all you .NET 4.0 fanboys out there, the dynamic keyword doesn't make it all better. COM errors still jump out of the screen and punch me in the face. I guess the world is still made out of COM.

So if you are a C# person and keep getting HRESULT COM errors in VSTO, be wary against bad casts and invalid indices. But to end this post on a good note, the API is EXPANSIVE and once you get the hang of it, you can make Office applications really dance.