Comparing Microsoft Visual Basic for Applications 6.0 and Microsoft Visual Studio Tools for the Microsoft Office System

Allison Balter
InfoTechnology Partners, Inc.

November 2003

Applies to:
    Microsoft® Visual Studio® Tools for the Microsoft Office System
    Microsoft Visual Basic® for Applications 6.3
    Microsoft Office Word 2003
    Microsoft Office Excel 2003

Summary: Create a Microsoft Visual Studio Tools for the Microsoft Office System or Microsoft Visual Basic for Applications 6.0 project: Which is the best choice for your solution? Read this article to discover the ways these environments differ and ultimately work together. (7 printed pages)

Contents

Introduction
Differences Between Visual Studio Tools for Office with the .NET Framework and VBA
Securing Your Code
Deploying Your Application
Is VBA 6.0 Going Away?
Conclusion

Introduction

Microsoft® Visual Studio® Tools for the Microsoft Office System is a new technology that includes the Microsoft Office Microsoft Office Access 2003 Developer Extensions. Visual Studio Tools for Office allows you to use Microsoft Visual Basic® .NET and Microsoft Visual C#® to write managed code behind Microsoft Office Word 2003 and Microsoft Office Excel 2003-based applications. Not only can you use these languages to write code in Word and Excel, you can benefit from the power and productivity afforded by the .NET Framework. These benefits include:

• Access to the Visual Studio .NET IDE (Interactive Development Environment)
• Access to the rich debugging tools available in Visual Studio .NET
• The ability to take advantage of all of the .NET object models (for example ADO.NET)
• Use of the Server Explorer
• Access to the power of the Microsoft .NET Framework classes from the Microsoft Office System
• Access to higher security options for the code you create
• Fully object-oriented programming, thus making it possible to write more efficient code
• The option of including Microsoft Windows® Forms in Excel and Word applications, making forms with controls that are richer than the controls available in Microsoft Visual Basic for Applications (VBA)

Fortunately, you can take advantage of all of these features while retaining full access to the Word and Excel object models. When you program within the Visual Studio Tools for Office environment, you are writing managed code. Managed code is written in languages that target the common language runtime, such as Visual Basic .NET, Visual C#, and Managed Extensions for C++. On the other hand, when you write code within the Word document or Excel spreadsheet, you are writing unmanaged code.

Managed code offers several advantages. The common language runtime (available with managed code) validates your code so it doesn't attempt to perform illegal operations, such as accessing memory that does not belong to it. Managed code also provides you with access to the Microsoft .NET Framework and its Base Class libraries.

So why ever use unmanaged code again? You may choose to continue writing unmanaged (VBA) code for several reasons:

• Depending on the type of application, it may still be easier and take less code to automate Excel or Word from unmanaged code. The automation of some other Office programs, because they do are not supported by Visual Studio Tools for Office, requires using VBA, unless regular COM Interop techniques are employed.
• Managed code is not always the appropriate place to create a COM add-in.
• If you are making minor enhancements to existing VBA solutions, in most cases there is no reason to rewrite your VBA code because it continues to function.
• Writing VBA code does not require deploying the .NET Framework, and not all organizations are prepared for its use.
• VBA creates embedded code inside documents in the Microsoft Office System and are thus packaged up with the document itself. Visual Studio Tools for Office code is stored outside of your document or spreadsheet, requiring a more thorough knowledge of deployment techniques.

With Visual Studio Tools for Office, you get the productivity and power of Visual Studio .NET and the .NET Framework combined with the extensibility and programmability of Word and Excel.

Differences Between Visual Studio Tools for Office with the .NET Framework and VBA

For your Microsoft Office System development, when you're evaluating whether to use VBA projects, Visual Studio Tools for Office projects, or a combination of the two, it is important that you understand the specific differences between the two. Table 1 provides a comparison.

Table 1. VBA project versus Visual Studio Tools for Office project comparison

Some items in the table are particularly noteworthy. First, notice that both types of projects receive ongoing Microsoft support. Also, whereas VBA projects exist in several versions of Office, Visual Studio Tools for Office projects are only supported in the Microsoft Office System.

The language support for the two environments is quite different. VBA projects support the VBA language, which is based on Microsoft Visual Basic 6.0. Visual Studio Tools for Office projects support Microsoft Visual Basic .NET and Microsoft Visual C#. Because of the object-oriented nature of Visual Studio Tools for Office, the programming paradigm between the two environments is quite different.

Deployment varies quite a bit between the two environments. Although the final section of this article discusses deployment, the focus here is on the core differences between deployments of the two project types. Deployment of a Visual Studio Tools for Office project also requires that the target computer have the .NET Framework installed. The code that is normally part of a Word document or an Excel spreadsheet in a VBA project is contained within an assembly produced by the Visual Studio Tools for Office project. Visual Studio Tools for Office offers no-touch deployment (NTD) support so that you can update your code behind a document without recalling and redeploying the document. Visual Studio Tools for Office is not automatically installed with the Microsoft Office System. Projects created using Visual Studio Tools for Office require .NET Framework Primary Interop Assemblies (PIAs) in order to run. These PIAs are installed on-demand automatically when the project is first run on a client computer. All of these items add up to a somewhat more complex, but ultimately more flexible and powerful, initial deployment compared to the average VBA application.

Security is another area where VBA projects and Visual Studio Tools for Office projects differ. Visual Studio Tools for Office assemblies that run behind documents in Word or Excel take full advantage of the security features in the .NET Framework. This means that the solid security foundation upon which other applications are built can now be used in Office solutions. To be sure, this great advantage also requires learning more about assemblies, trust, security policies, and other aspects of the .NET Framework, but the advantages are well worth the effort.

Here are a few final items worthy of note. Web service integration is built into the Visual Studio Tools for Office IDE, making it easy for you to incorporate Web services into your Visual Studio Tools for Office projects. Visual Studio Tools for Office projects provide extensive support for XML. Of course, you do not actually have to choose one or the other exclusively. One application or solution can contain both VBA and Visual Studio Tools for Office projects. However, keep in mind that when you create solution with mixed code, debugging can present a challenge as it is difficult to determine which code is the offending code. Furthermore, there is not guarantee that events fire (VBA or managed code) in a way you anticipate.

Securing Your Code

Visual Studio Tools for Office does not add security features, but using it allows you to use security features native to .NET Framework in addition to the security features already present in projects for the Microsoft Office System. You can use all of the security features built into the .NET Framework to control whether or not your application is allowed to run. For example, an administrator can designate that all code deployed from a specific intranet server is granted FullTrust, allowing it to run in Word or Excel on the local computer. If the user receives a document or spreadsheet with managed code from that location, the code executes without problem. If the user receives a document with managed code from another location, although the user can open the document, the code does not run.

Each user's computer has a set of rules about which code is allowed to run and what that code is allowed to do. Before the code runs, the common language runtime gathers evidence. Evidence is based on where the code came from (the server name, and whether or not the assembly has been signed. Evidence is mapped to a specific policy. There are four different policies. They are Machine, User, Enterprise, and Host. Each policy is made up of zero or more code groups. Code groups provide a mapping from the evidence to the permission set. The permission sets contain one or more permissions. A permission is the right to do something. Using the evidence obtained, the runtime maps the assembly to a code group. The code group then maps it to an intersection of all four policies. It is this mapping that determines whether or not the code can execute, and if it can execute, what it is permitted to do.

Deploying Your Application

You can place the DLL that you create when you compile a Visual Studio Tools for Office project in one of three places, depending on your specific needs. If the code is associated with a document used by a single user on a single computer, you can place the code, along with the document, on the user's computer. If the code is associated with a document accessed by multiple users, you store the code on a network share so that the latest version is always used when accessed by each client computer. As an alternative, you can store the code on a corporate intranet, or on a secured Internet site. Each option has its advantages and disadvantages.

In the first option, the sole user stores both the document and the assembly on their computer. The advantage of this is that the user does not need to be connected to a network or the Internet to use the solution that you have built. The major disadvantage is deployment of updates to the application.

In the second option, the user stores the document on their computer and the assembly on a remote share. This allows each user to keep an individual private copy of the "data," in this example, the Word or Excel document, while accessing the most recent version of the assembly. The biggest disadvantage of this option is that it requires that the user be connected to a network or the Internet to use the application. The .NET Framework provides a "no-touch deployment" feature. This feature allows you to modify code and deploy it to users easily. After you complete your coding changes, you redeploy your DLL to its original location. The next time the user opens the Word or Excel file, the computer detects that the DLL was updated and automatically downloads the latest version to the user's computer and runs that latest version. Absolutely no user intervention is required.

In the third option, the user stores both the document and the assembly on a remote network share. This allows users to take advantage of document collaboration features offered by Word and Excel. The primary disadvantage of this option is that it requires that the user always be connected to the network.

Is VBA 6.0 Going Away?

VBA 6.0 is not going away in the next release of the Microsoft Office System, and Microsoft will provide a migration strategy if VBA is ever retired. There is quite a bit of legacy code that is written in VBA 6.0. In many cases, there may be no reason for existing code to be rewritten. However, the significant advantages and capabilities the .NET Framework offers may cause you to rethink whether to leave some solutions as they are. There are no language enhancements to VBA 6.0 itself in the Microsoft Office System.

Granted, Visual Studio Tools for Office environment could prove to be a little too much to learn at first for an entry-level developer. Macro recording is still a great way for new developers to learn how to create Office solutions, and this can only be done in VBA. Entry-level developers can still contribute their portion of the development effort in VBA 6.0 while a little more seasoned developer can build a portion of the solution as managed code within the Visual Studio Tools for Office environment. These two techniques don't just co-exist; they complement each other and can work together within one cohesive solution.

Conclusion

Visual Studio Tools for Office is a revolutionary addition to the development world for the Microsoft Office System. It allows you to combine the productivity and power of the .NET Framework with the extensibility and programmability of the Microsoft Office System. Visual Studio Tools for Office allows Visual Studio .NET developers to build applications based on the Microsoft Office System, while remaining in a familiar coding environment, and taking advantage of robust security, easy deployment, and support for Web services.

VBA 6.0 and Visual Studio Tools for Office will exist side by side in some form for the foreseeable future. Developers can continue to take advantage of the rapid application development that VBA and the macro recorder bring to the Office environment. As Visual Studio Tools for Office and development for the Microsoft Office System mature, the power and benefits that .NET Framework offers to the Office environment also becomes more obvious. For these reasons, there is an ongoing melding of the two technologies.

© Microsoft Corporation. All rights reserved.