Thursday, November 10, 2005

VSTO: A Difficult upgrade to VS 2005

WARNING! This post will only be interesting to anyone upgrading Visual Studio Tools for Office projects from VSTO 2003 to VSTO 2005. All fans of distributed computing, you may want to skip this one!

Last week I wrote an entry about installing some minor difficulties installing VSTO 2005--it was not as painless and trivial as it should have been.

After getting things installed, I immediately tried to upgrade a VSTO project that I had written for Excel. I needed to get it done quickly, because we needed to demonstrate it at the VS 2005 Launch. The upgrade of the VSTO 2003 project did not go as easily as I had hoped.

It seems Microsoft has made some pretty significant changes to both the mechanics and the object model of the VSTO projects. Most importantly, they've changed things from a workbook-centric model to a worksheet-centric model. This means that, in practical terms, you may need to do some re-architecture of your model before it will work.

I opened up my old project in VS 2005, and it offered to upgrade (making a backup, of course). After a few changes I was able to get my project to compile; however, when I tried to run it I got the dreaded "Office document customization is not available" error. Of course, I had no idea why I was getting it; my first assumption was that my project is dependent on two different libraries, and that one of them didn't have permission to load (this was a difficulty in VSTO 2003).

So I went off to my Administrative Tools->Microsoft .NET 2.0 Configuration tool. When using VSTO 2003, I generally used the .NET 1.1 Wizard to give full trust to any libraries loaded by my assembly. However, .NET 2.0 doesn't have a wizard. Instead, I had to use the Configuration tool. The VSTO projects in the .NET 2.0 Configuration Tool are set up differently than they were in .NET 1.1. Formerly, I'd see either a Wizard entry under the Machine->Code Groups->All Code entries, or I'd see an entry under User->Code Groups->All_Code->Office_Projects that was based on the directory where I built my project. In the .NET 2.0 Configuration Tool, projects are listed under User->Code Groups->All_Code->VSTOProjects, and each is listed by its GUID rather than its folder.

Because I had had such difficulties with security when using VSTO 2003, I assumed that my problem here was the same. I was wrong. When I tried tweaking the settings in the .NET 2.0 Configuration Tool, I started getting a new error that made it clear that now I didn't have permission to load that assembly.

Eventually, I gave up and tried recreating the project. Once again, I upgraded my VSTO 2003 project. At Rob's suggestion, I followed the directions in the "How to Upgrade Solutions from Visual Studio Tools for Office" document (I was wondering why the upgrade hadn't told me to read that first, and also why that document is so hard to find when searching in MSDN). I copied and pasted code as directed; some of my code used to get called when the workbook opened, so I put it in the ThisWorkbook_Startup method. Again, it wouldn't open.

At this point I was pretty frustrated, having spent the better part of a day trying to get this working.

Finally, I "started from scratch." Rather than upgrading my old project, I created a new project. When the wizard asked if I wanted a new workbook or a copy of an existing workbook, I pointed to my old workbook. It created a brand new project, with files for each of my sheets (that's another change between 2003 and 2005--there are .cs files for each sheet of your workbook).

I then added references to the DLLs that I needed my project to load; I built and ran--it loaded! Now I just needed to add my code. I copied and pasted most of the code from my old project into Sheet1.cs (this time, I didn't put any code in ThisWorkbook.cs).

Almost everything was ready. The only problem I had was that I had no way to get values from multiple sheets--in the VSTO 2003 paradigm, your workbook had access to all of the sheets; the new object model seems to make that harder. I used to use code snippets like
mwksResultsWorksheet = (Excel.Worksheet)ThisWorkbook.Worksheets["Results"];, then I'd use that worksheet to get values. Now I didn't have access to the other sheets, because I was working within a sheet rather than within a workbook.

Eventually I came upon the Globals class. This class gave me access to all of the sheets, using construction like this: Excel.Range cellBetas = Globals.Sheet3.get_Range("cellBeta1", "cellBeta30");

The most inconvenient thing about it is that I have to refer to the sheets by sheet number rather than by their name.

Anyway, at long last, I had my VSTO sample working. It's awesome. Using distributed code behind my Excel spreadsheet, I can have 10 machines sharing in the 700,000,000 calculations necessary for my retirement simulator. All in all, I get 3 minutes of work done in about 20 seconds.

Other than the difficult upgrade process, I actually like some of the new aspects of VSTO 2005. But I'll have more on that later...