Nine Compelling Reasons to Move From VBA to VSTO 2005

When it was first released in 2002 as part of the larger .NET initiative, Visual Basic .NET made an evolutionary leap from Visual Basic 6.0. At the same time, Active Server Pages underwent a similar seismic shift to ASP.NET, and its VBScript was replaced by VB.NET. So VB6 to VB.NET...VBScript to VB.NET... do you see a pattern?

Microsoft Office 2003 and the release of Visual Studio Tools for Office (VSTO) completed the consolidation. It gave Office application developers a way to partake of the bounty of .NET by letting them use VB.NET in the place of the familiar-but-tired Visual Basic for Applications (VBA).

With Visual Studio 2005 on the way, that means VSTO 2005 is on the way, too. Which means a sweet time for Office developers. Let's be real. As powerful as it is, VBA sometimes feels like the redheaded stepchild of VB (no offence to redheads or stepchildren - it's just an expression). But with VSTO, Office developers now have the same powerful tools and libraries at their disposal as the rest of the .NET generation.

To show you why this is a good thing, I'll cover the top nine benefits of VSTO as compared to VBA. Then, as an added bonus, I'll toss in four new features coming with VSTO 2005.

1. Managed Code Beats Unmanaged Code
While the specific architecture of a VBA app is up to the developer, typically the source is created in, and shipped with, the client document itself. This has on occasion caused some, shall we say, issues regarding security, proprietary information, and distribution of updates, to name a few.

With VSTO, business logic is compiled to a separate .NET assembly. The document in the user's hands then contains managed code extensions, which trigger the assembly's download at run time. Decoupling code from UI in this way gives both the developer and the user huge benefits that just aren't possible with VBA.

For a complete description of the architecture of VSTO applications, see "Architecture of Office Solutions That Use Managed Code Extensions."

2. Object-Oriented Programming Beats Procedural Programming
If you went through the conversion from VB6 to VB.NET then you might be familiar with many of the structural changes to the language. This was done partly so that VB.NET could share a common foundation with other languages in the Microsoft family. As the most profound change, the language was restructured to give it a more OOP flava, with all the organizational benefits that entails, such as abstract classes, inheritance, and interfaces.

If you're a VB scripter who's new to object-oriented programming, then learn to "Get the Most From OOP" or learn more about "Using Abstract Classes in Visual Basic.NET".

3. .NET Framework Beats... Not Having the .NET Framework
Moving to VSTO gives the Office developer access to the .NET in VB.NET, and all of the rich libraries of the .NET Framework. The benefits are too many to list here. But for a primer on what .NET has to offer over old skool VB, check out MSDN's "Introduction to VB.NET for VB Veterans", much of which applies to Office development, as well.

4. A Better Development Environment
Can we be honest? The VB Editor within Word and Excel doesn't hold a candle to Visual Studio. Using VSTO means working with a world-class IDE to create your Office products. In addition to the bells and whistles you would expect, it gives you access to all of the Base Classes of the .NET CLR, rich debugging tools, a customizable IDE interface, emulators, add-ins, and more. And as the launching point for apps of many different platforms, VS.NET also gives you the potential for...

5. Code Reusability Beats Rewriting Every Time
An object-oriented language is, by nature, highly reusable. But here's the juice in consolidating all of the variations of Visual Basic. By using a common syntax for every platform, VB.NET gives you the chance to share code with standalone apps, web apps, handhelds, cell phones, etc. Imagine making a D&D Character Sheet with logic that works on every platform!

For an example of code re-use and extensibility, check out "Extending a Single .NET App Across Two Platforms".

6. Web Services Is The Best Way To Integrate
The other big sell of .NET, Web Services fuel SOA - Service Oriented Architecture. Using VS.NET to build your Office applications gives you the chance both to create and to consume Web Services.

For example, suppose you wanted to distribute customized Excel spreadsheets to external clients and they all depended on a core set of proprietary formulas. One option might be to write those formulas in VB.NET and expose them as one or more Web Services. Then, your spreadsheets could consume those services, allowing you to keep the code in-house, where it can be transparently maintained or updated as needed without having to send revisions to your clients.

For a primer on Web Services, ask yourself "What Are Web Services?". Otherwise, skip to an interesting tutorial on MSDN on "Accessing Web Services in Excel Using Visual Studio Tools for the Microsoft Office System".

7. Security - To Protect Your Users and Your Code
It would be cruel to say that VBA has a reputation as a malware transport device. But it's also honest. For many end-users, their first exposure to VBA, so to speak, is from malicious macros.

VSTO goes a long way towards alleviating many of the security issues facing Office developers. In addition to the inherent safety of putting some distance between interface and logic, .NET introduces new security protocols and classes to help you protect your code and your customers. In addition, assemblies created with VSTO are locked down by default. The administrator must explicitly enable the assembly to run.

Get a lockdown on VSTO security on MSDN, "Security in Office Solutions That Use Managed Code Extensions".

8. Ease of Deployment - No More Emailing Patches
When deploying managed code built with VSTO, you'll usually be delivering two files: the Office document itself and the assembly that runs the code behind it. VS.NET's project and solution management give you different deployment options and leads you through them, making it easier to package your finished product just the way it needs to be received.

For more info on "How to: Deploy Office Documents and Assemblies" ... well, you just found it.

9. Robust and Programmable Error Handling
I know the problems in your applications are almost certainly due to user error, but it's nice to know that VB.NET has some exception handling. While the GoSub statement has been deprecated and use of the GoTo statement is discouraged, Try...Catch...Finally makes for a nice, elegant way to prove your code's infallibility.

For a tutorial on structure exception handling, try catching this. Finally!

Change is hard. But even if you live and breathe VBA, it's worth it to look at VSTO. Depending on your code, you may run into some migration issues. But whether migrating old projects or starting entirely new ones, Visual Studio Tools for Office will take your Office apps to an entirely new level. And with VSTO 2005, it's only going to get better.