Mine Field of Excel Development Tools – Part 1

If you’ve been reading my other blog entries, you know I’ve been working a lot with Excel lately. I worked intensely with this environment several years ago. I’ve found all of the Microsoft Office applications to be fun to work with at the object model level – OK, call me a geek. Unfortunately MS Office apps have always been far behind the technology curve when it comes to developer tools. These are my notes about how to do the development that I need given the state of the tools available.

If you’d like more links and details on this topic, be sure the check out Part 2. (EDIT: Up to 6 parts so far, please be sure to follow to the end.)

The tools for linking into the apps and adding third-party "vertical" value have been difficult to use, buggy, and incomplete. I know Microsoft tries to help the developer channel, adding .NET capabilities so that we can use the new tools with the older products as well as with the newer products, etc. But there is such a huge matrix of products and features that many of us just need to wait in line for them to get to whatever we happen to be focused on at the time. Unfortunately, as they move forward they make decisions to leave some features behind, and it’s difficult to know which one of the millions of features will get the axe. Working with Excel now it seems some functionality has been left in a perpetual "we’re looking at it" stage, so many of us need to rely on old methods to do things, or try to find ways to integrate old methods with new and useful tools.

One of my latest projects involves creating User Defined Functions that use web services to retrieve remote data. The idea is that an Excel formula has "=GetData(server,database,table,key,field)" and the data is retrieved from wherever on the planet it happens to reside. Once that data is returned, other cells on the sheet might need to be updated.

I’ve been looking at UDF via XLA, XLL, COM Add-ins, Automation Add-ins, and now VSTO (Visual Studio Tools for Office). Most of the forum threads on this topic are months to years old, so I thought I’d check to see if there is anything new in VSTO 2005 SE for Excel 2003 and VS 2005 Pro. I’ve done a lot of reading, and have sprinkled this article with many links for anyone else interested in this topic.

This forum posting reflects pretty much what I want to do.