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.

2 thoughts on “Mine Field of Excel Development Tools – Part 1

    • The article covers a lot of things and includes a lot of useful links..Thanks for all those informations..

      Simply my question is there any way to provide function description , argument description etc to the function wizard from excel automation addin using c#.(MSDN answers NO..but all those are before 2005. ). Or is it possible to add a drop down list or check box control in function wizard(instead of text box)?

      Pls share if you have any ideas

    • Sorry for getting to this so late, I accidentally thought this comment was spam.

      I highly recommend you check with the people at Add-In Express to see if you can do what you ask to extend Excel. Please post here when you get an answer. Good luck!

      http://www.add-in-express.com/

Leave a Reply