Problem solve Get help with specific problems with your technologies, process and projects.

On migrating from VBA to VSTO

The release of Visual Studio 2008 provides further compulsion to migrate applications from VBA to VSTO. Microsoft addressed some best practices for these types of migrations at DevConnections Las Vegas.

The release of Visual Studio 2008 offers, among other things, new functionality for Visual Studio Tools for Office. That, in turn, may compel programmers to take a look at the VBA, or Visual Basic for Applications, implementations that they have and assess the pros and cons of migrating those apps to the new VSTO.

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.
  • More VBA and VSTO resources
    Migrating add-ins from VBA to Visual Studio Tools for Office

    What Microsoft Office 2007 means for .NET developers

    How to customize the Office 2007 Ribbon
  • 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 last published in December 2007

Dig Deeper on Visual Studio Tools for Office (VSTO)

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.