At the recent DevConnections conference in Las Vegas, Naveen Yajaman, a senior program manager on VSTO, offered some points to ponder regarding VBA application migrations.
Yajaman began by outlining what Visual Studio Tools for Office for VS 2008 offers that VBA does not -- namely, access to the .NET Framework 3.5, to managed controls and to Web services; the ability to consume SharePoint 2007 and other SOA interfaces; the ability to build UI elements modeled after Office 2007, and the availability of ClickOnce deployment for applications and components. Also of interest is the fact that VSTO, previously available only as a downloadable add-in to VS 2005, is now part of the VS 2008 package.
Yajaman then suggested a few different options for migrating certain elements of VBA applications into Visual Studio Tools for Office. (They are ranked below in order from what he considered most ideal to least ideal.)
- Programmers can create new functionality in VSTO, which can either stand alone or be called from a VBA application. In Yajaman's demo, a VBA macro populated an Excel spreadsheet, which then consumed data in a Windows Presentation Foundation control that allowed an end user to flip through pages rather than click on tabs.
- Another option is to build a VSTO application that calls into existing VBA code, thereby preserving the existing and, presumably, fully functional VBA investment. Visual Studio Tools for Office was built so that any type defined in the VSTO assembly, or any application add-in, can be exposed to VBA code, Yajaman said. Other advantages here include the ability to use C# as well as Visual Basic and the ability to take advantage of IntelliSense.
- A third possibility is to only migrate individual subroutines.
- Finally, one can throw caution to the wind and migrate an entire VBA project. This is risky, and it costs a lot, Yajaman said: "There are tools out there that do a decent job, but, if you're in an enterprise, then decent won't cut it."
That said, Yajaman also provided some advice for analyzing VBA applications and the extent to which they should be migrated to Visual Studio Tools for Office.
For a VBA solution that is rather complex -- in terms of lines of code or application logic -- the best option is to call existing VBA code from new VSTO code, he said. The same is true of mission-critical VBA applications.
Om the other hand, if the deployment footprint of a VBA solution is wide, with lots of VBA code to be found in lots of different documents, then a full-scale migration to VSTO might make sense -- though, as Yajaman put it, "It's difficult to make that choice."
This was first published in December 2007