Microsoft Visual Studio Tools for the Microsoft Office system (3.0), part of Microsoft Visual Studio 2008, has a new feature called VSTO/VBA interoperability that enables you to discover and call Visual Studio Tools for Office–based code from within Microsoft Visual Basic for Applications (VBA) macros. This feature also supports Microsoft IntelliSense within the Visual Basic Editor so that you can discover and call your Visual Studio Tools for Office–based code by using IntelliSense.
The VSTO/VBA interoperability feature enables you to add new Visual Studio Tools for Office functionality to existing VBA applications, preserving your investment in VBA. This eliminates the need to rewrite the existing application using Visual Studio Tools for the Office system (3.0).
This Visual How-to article demonstrates interoperability between Visual Studio Tools for the Office system (3.0) and VBA in a Microsoft Office Excel 2007 workbook project written using Microsoft Visual Basic .NET.
You can activate VSTO/VBA interoperability in a Visual Basic project without writing a line of code in Visual Studio. Activating interoperability in a Microsoft Visual C# project is more complex and is outside the scope of this article.
Introducing VBA to Visual Studio Tools for the Office System (3.0)
Before activating the VSTO/VBA feature, you need to base your Visual Studio Tools for the Office system (3.0) project on an existing document that contains VBA macros. This section shows you how to create a simple example using Excel 2007. The six major steps to create the example are as follows:
Add VBA to a new workbook, or start with an existing workbook that contains VBA macros.
Create a Visual Basic Excel Workbook project using Visual Studio Tools for the Office system (3.0), and copy the workbook from step 1 as the template for the project.
Activate COM callers on one of the host object classes in the project.
Implement the desired functionality in the host object class from step 3.
Build the project.
Open the extended workbook and modify the VBA code to call your Visual Studio Tools for the Office system (3.0) functionality.
The following procedure demonstrates how to activate VSTO/VBA interoperability.
To activate VSTO/VBA interoperability
Open a new workbook in Microsoft Office Excel 2007.
Switch to the Visual Basic Editor by pressing ALT+F11.
Double-click Sheet1 in the Project Explorer window (press CTRL+R if it is not visible) and enter the HelloWorldFromVBA procedure.
MsgBox "Hello from VBA."
Test the code by placing the cursor in the HelloWorldFromVBA procedure and pressing F5.
Save the workbook as an Excel macro-enabled workbook.
Create a Visual Basic Excel 2007 Workbook project in Visual Studio.
When you select a document for the application, select Copy an existing document and select the workbook from step 5 into which you added the HelloWorldFromVBA procedure.
After you create the project, right-click Sheet1 and select View Designer.
Locate ActivateComCallers in the Properties window and set the value to True.
Right-click Sheet1.vb in Solution Explorer and select View Code.
Add a Visual Studio Tools for the Office system (3.0) implementation named HelloWorldFromVSTO.
MessageBox.Show("Hello from VSTO.")
Build and run the project. After the customization is running, open the Visual Basic Editor and view the code associated with Sheet1. Notice that Visual Studio added a VBA procedure to Sheet1.
'' DO NOT EDIT - This code is needed to call Visual Studio Tools for the Office system (3.0)
Property Get CallVSTOAssembly() as HelloWorld.Sheet1
Set CallVSTOAssembly = GetManagedClass(Me)
'' END OF DO NOT EDIT
Modify the existing HelloWorldFromVBA procedure as shown in the following listing.
' MsgBox "Hello from VBA."
Place the cursor inside the HelloWorldFromVSTO procedure and press F5. Notice that instead of seeing a VBA message box, you see the message box from your Visual Studio Tools for the Office system (3.0) project.
To activate interoperability between Visual Studio Tools for the Office system (3.0) and a Visual Basic project, you modify the EnableComCallers property in a properties window. Although you only need to modify one property, you should remember some important points as you experiment with this feature.
Open XML File Formats and the VBA Interoperability Feature
One of the major changes in the 2007 Microsoft Office system is the new file formats associated with Microsoft Office documents. The default Open XML file format for Microsoft Office documents does not support embedded macros such as VBA. To save a document that contains VBA, you must choose the macro-enabled format, or a legacy file format such as a Microsoft Office Excel 2003 workbook. Because the VSTO/VBA interoperability feature is all about integrating VBA, you must choose to use documents that support VBA to use this feature.
Documents Must Include Embedded VBA
To activate VSTO/VBA interoperability in a Visual Studio Tools for the Office system (3.0) project, you must start with a document that contains VBA code. If you try to use the feature in a document that does not contain macros, Visual Studio displays the message The host item cannot be exposed to VBA code because the VBA project in the document cannot be loaded.
Exposing Visual Studio Tools for the Office system (3.0) Procedures to VBA
When you activate the EnableComCallers property on a host item class within a Visual Studio Tools for the Office system (3.0) project, all of the public properties and methods that are implemented within the class are visible to VBA. To call your Visual Studio Tools for the Office system (3.0) procedures from within VBA, use the CallVSTOAssembly property that is associated with the matching host item object in VBA.
For example, if you exposed the Sheet1 class in Visual Studio Tools for the Office system (3.0), use the CallVSTOAssembly property associated with the Sheet1 object in Excel 2007.
Manage Projects Carefully
It is tempting to make changes to the VBA code in the document that is located in your Debug directory (the document that opens when you debug your Visual Studio Tools for the Office system (3.0) project). Remember that this document is overwritten every time you build or execute your project in debug mode. If you make changes to the VBA code and save the document, unless you save it to a different location, your changes are overwritten the next time you build your Visual Studio Tools for the Office system (3.0) project. Consequently, you need to manage your project carefully to guard against this.