VBA and VSTO

In my capacity as a custom application developer, I've used Visual Basic for Applications ever since 1997. That was the year that Microsoft released the developer edition which introduced VBA into all the Office applications except Outlook which retained VBScript. Prior to that, I had done my coding in Microsoft Access in a macro language called Access Basic, a subset of Visual Basic 2.0's core syntax.

By 2004, I was doing more systems integration and IT management work and found myself doing far less coding in VBA than previously. But now, four years later, I find myself occasionally thrust back into the world of VBA, especially its incarnation in the Office 2003 product line.

Despite the interim in which I did little VBA development, I find the world of VBA comfortable and as productive an environment now as I did then. Once you understand the "basics" of VBA (not too tough, I have to admit), it's really only the object models in the various Office applications that you need to master to become productive in that environment. In other words, moving from Access 2003 to Outlook 2003, PowerPoint 2003, Visio 2003, Word 2003, and Excel 2003 isn't really that big a deal.

But things have changed in technology and in the resources available to Office developers since 2003, even though many corporations are still quite comfortable sticking with Office 2003 for general productivity applications.

Some of the gurus I used to read have moved on, writing less about VBA and more about Visual Studio, .NET, and Visual Studio for Office Tools (VSTO). They've moved on for a variety of reasons, not the least of which is that their audience is now developing Office applications not just for departments and small vertical markets but for enterprises and global markets.

When an Office developer moves from a departmental application to an enterprise application, one of the first things they usually face is a request to integrate their Office applications with back-end systems such as SQL Server, SAP, Exchange Server, SharePoint Server, and other assorted server products. When another Office developer moves from small, localized vertical markets to global markets, sometimes the driving force for change is that the market now relies upon web services; in other words, the front-end Office applications are now required to interface with back-end data repositories which deliver massive quantities of data by means of web services, either through the Internet directly or simply through corporate intranets. In both cases - whether its the move from departmental to enterprise applications development, or the move from local verticals to global verticals - the Office application developer is faced with a toolset that no longer measures up to more sophisticated demands.

Enter Visual Studio and Visual Studio Tools for Office (and possibly Visual Studio Tools for Applications).

But herein lies the rub. Yes, sophisticated users are making demands which require new skills sets and integration with more complex back-end services. But almost as many times, requests for Office applications do not involve anything more complex than automating the good old productivity applications that work as well today as they did in 2003. So where does the forward thinking Office application developer spend time? Learning VSTO and .NET programming languages? Or leveraging existing skills and 3rd-party tools which still may meet up to 80% of the market demand?

I know, the quandary isn't new. Whenever new technologies surface, application developers have to decide if and possibly when to migrate.

But now that I function primarily as an implementation consultant, time available for learning new technology is even more limited than ever, meaning that I can't afford to make mistakes about which technology learning paths to follow. So it is with some interest that I came across this interview with Bill Gates at the Office Developers Conference on 12-Feb-2008.

Gates indicated Microsoft's commitment to Office, to Access (including moving features in the next release to SharePoint Server) as well as Visual Studio. He claims to want to do some of his own coding in the area of health applications, but obviously most of his input with Microsoft development teams these days is in the realm of directing architecture initiatives. In other words, there isn't much in the interview which helps a poor implementation consultant like me figure out where to invest his time.

In another video in the spring of 2007 at Software 2007 in Santa Clara, Steve Ballmer talked about integrating Office with back-end services, jokingly suggesting that, as a salesman, PowerPoint was the only mission-critical tool. What's compelling about this video, though, is that it demonstrates convincingly how back-end servers like SharePoint Server 2007 and Communications Server 2007 can be packaged in Office 2007 Office Business Applications (OBA) in a way that obviously improves overall team collaboration and productivity. In that context, it's clear that OBA developers need to use the new tools.

But, again, those in such situations may still be in a substantial minority. So I'm not convinced just yet.

Going even further back in time, Microsoft has stated that VBA will be around for quite some time. It remains in Office 2007 products and will continue to be available in all future 32-bit Office releases. That last part is critical. VBA will not be supported by Microsoft in the 64-bit world except as 32-bit executables. That might not be a big deal for Office applications insofar as allowing VBA-enabled macros and code to survive. What this means for people like me is that I can't base my decision about technology on the forthcoming demise of VBA - that ain't gonna happen for some time yet.

If there is to be some compelling reason for abandoning VBA for Office applications in the short term, it will have to be because of customer demand for features which I can't provide in VBA and/or because of compelling development environments. I still need to be convinced.

Finally, even with the significant strides in VSTO support for Office applications in Office 2007, there is still one huge gap - Access. Maybe it's because VSTO tends to work mainly with back-end data sources or maybe it's because Access developers can still create very robust departmental applications with VBA - whatever the reason, we'll have to wait for yet another release before VSTO will include support for Access.

I guess what this means for me personally, and for many other Office developers, is that we can take our time polishing VBA skills for maybe as long as a couple more years or more, while starting to learn some .NET, Visual Studio and VSTO/VSTA technology as time permits and as markets dictate.