VBA Interoperability with Visual Studio Tools for the Office System (3.0)

Article
05/23/2014

Summary: Learn how to make Microsoft Visual Basic for Applications (VBA) an integrated part of your Microsoft Visual Studio 2008 Tools for the Office system (3.0) project. (12 printed pages)

Steve Hansen, VSTOtips.com

November 2007

Applies to: Microsoft Visual Studio Tools for the Microsoft Office System (3.0), Microsoft Office Excel 2007, Microsoft Office Word 2007

Contents

Overview of Visual Studio Tools for the Office System (3.0)
Adopting Visual Studio Tools for the Office System (3.0)
Introducing the VBA Interoperability Feature
Visual Basic Quick Start
Visual C# Quick Start
Example: Integrating VBA and Visual Studio Tools for the Office System (3.0)
Conclusion
Additional Resources
About the Author

Microsoft Visual Studio Tools for the Microsoft Office system (3.0), helps you to create applications that combine the power of the Microsoft .NET Framework and the rich functionality of Microsoft Office by using Microsoft Visual Studio 2008.

In the past, developers traditionally used Microsoft Visual Basic for Applications (VBA) for extending Microsoft Office. Corporations, ISVs, and individual developers invested heavily in VBA over the past decade. The interoperability feature that enables you to call procedures directly from VBA allows you to maintain your investment in VBA and benefit from Visual Studio Tools for the Office system (3.0)—without porting entire VBA applications or libraries to the Microsoft .NET Framework. You can add new functionality by using Visual Studio Tools for the Office system (3.0) and then call the new procedures where appropriate from VBA.

Overview of Visual Studio Tools for the Office System (3.0)

Visual Studio Tools for the Office system (3.0) is a developer toolset that extends the 2003 release and 2007 release of Microsoft Office applications by using Microsoft Visual Basic 2005 or Microsoft Visual C#. Rather than using the Visual Basic Editor from within a Microsoft Office application and writing VBA, Visual Studio Tools for the Office system (3.0) uses the rich and robust Visual Studio integrated development environment (IDE).

Using Visual Studio Tools for the Office system (3.0), you can create document-level customizations that associate code with a particular document, or application-level customizations (add-ins) that associate code with a particular application.

Choosing between VBA and Visual Studio Tools for the Office system (3.0) involves several considerations. Visual Studio Tools for the Office system (3.0) offers a professional-grade development environment, powerful data-binding capabilities, a much richer set of controls, the ability to tap into the power and flexibility of the Microsoft .NET Framework, and programmatic access to several new Microsoft Office Fluent user interface (UI) elements that you cannot manipulate by using VBA. For more information, see Additional Resources.

Adopting Visual Studio Tools for the Office System (3.0)

After deciding to adopt Visual Studio Tools for the Office system (3.0), you must consider several things. For example, how do you preserve your investment in existing VBA code? Because of differences between VBA and Visual Studio Tools for the Office system (3.0), there is no tool to automatically migrate VBA-based code or applications to applications based on Visual Studio Tools for the Office system (3.0).

From a skills migration perspective, you should understand several fundamental concepts including interoperability between COM and the .NET Framework, language differences, event handling, and deployment. Interoperability between VBA and Visual Studio Tools for the Office System (3.0) can help you with these concepts.

Due to the development scope of migrating VBA solutions to Visual Studio Tools for the Office system (3.0), you may prefer to take an incremental approach to adopting Visual Studio Tools for the Office system (3.0). Prior to Microsoft announcing the VBA interoperability feature, however, the practicality of this approach was limited. While you can call VBA code from Visual Studio Tools for the Office system (3.0) by using the Application.Run method, this is not always a desirable solution. In addition, Microsoft Office Excel 2007 allows you to write a user-defined function (UDF) that can provide a bridge between VBA and managed code. However, you can benefit from using a UDF in only limited types of situations. The VBA interoperability feature provides an easy way to call Visual Studio Tools for the Office system (3.0) procedures from within VBA.

Introducing the VBA Interoperability Feature

You can now call Visual Studio Tools for the Office system (3.0) procedures from within VBA by using the interoperability feature. This feature also supports IntelliSense within the Visual Basic Editor, which you can use to discover and call Visual Studio Tools for the Office system (3.0) code.

The VBA interoperability feature addresses two primary scenarios. In the first scenario, you extend a VBA application with Visual Studio Tools for the Office system (3.0). In this scenario, imagine an existing document that contains VBA code. You can build a new project in Visual Studio Tools for the Office system (3.0) using the existing document as a base and expand on the functionality. You can then modify the existing VBA code to call into the Visual Studio Tools for the Office system (3.0) project as necessary.

The second scenario involves creating a base application in Visual Studio Tools for the Office system (3.0) that you can extend later with VBA. In this scenario, imagine creating an application in Visual Studio Tools for the Office system (3.0) that contains important business logic and UI elements. After you deploy the application, end users or other developers can extend the functionality by using VBA that optionally calls into the Visual Studio Tools for the Office system (3.0) functionality.

Overview of the Development Workflow

To use the VBA interoperability feature, start by creating a Visual Studio Tools for the Office system (3.0) project based on an existing Microsoft Office Word 2007 or Office Excel macro-enabled document. Give Public visibility to methods in your Visual Studio Tools for the Office system (3.0) project that you want to make available from within VBA. To enable and use the interoperability feature, you must configure the VBA interoperability attributes. For Visual Basic projects this involves setting the ReferenceAssemblyFromVbaProject property in the Designer associated with a given host object as described later in this article.

With the property set, you can build and run the solution. With your Office document open, start the VBE and call the Visual Studio Tools for the Office system (3.0) procedure. The next sections demonstrate this process first in Visual Basic and then in Visual C#.

Visual Basic Quick Start

This example calls a Visual Studio Tools for the Office system (3.0) method that displays a simple message box from VBA by using Visual Basic.

Hello World Visual Basic Example

In Office Excel, open a workbook, and then press Alt+F11 to open the Visual Basic Editor.

Click the Sheet1 object and add the following code.

Sub SayHelloVBA()
   MsgBox "Hello from VBA."
End Sub

Save the workbook as SayHello.xlsm (Macro-Enabled Workbook),and then close Excel.
Open Visual Studio, and then create a Visual Basic project.
Select Office 2007 Excel Workbook, and then name it SayHelloVB.
When prompted to select a document for your application, select Copy an existing document, and then select the SayHello.xlsm workbook.
In Design view, select Sheet1.
In the Properties window, set EnableComCallers = True. See Figure 1.

Figure 1. Enable COM callers for Sheet1
In Solution Explorer, right-click Sheet1.vb, and then click View Code.
After the Sheet1_Shutdown event handler, add the following code to Sheet1.vb.
```
Public Sub SayHelloVSTO()
   MessageBox.Show("Hello from VSTO")
End Sub
```
Press F5 to run the application.
To view the code for Sheet1, after Excel starts, press Alt+F11 to open the Visual Basic Editor, and in the Project Explorer window, double-click Sheet1.

Notice the code that Visual Studio Tools for the Office system (3.0) entered.
Modify the SayHelloVBA macro to add a line after the MsgBox statement.
```
Me.CallVSTOAssembly.SayHelloVSTO.
```
Figure 2. Calling a Visual Studio Tools for the Office system (3.0) procedure from within the Visual Basic Editor
Execute the SayHelloVBA macro.

Enabling VBA interoperability by using Visual Basic requires that you modify only one property in the visual designer. Of course, you must base the project on a file format that supports macros such as a macro-enabled workbook or document in Office Open XML formats or a legacy file format (such as an Office 2003 document). Additionally, the Office document must contain VBA code to enable this feature. If you try to use the feature in a document that does not contain macros, Visual Studio displays the following message: "The host item cannot be exposed to VBA code because the VBA project in the document cannot be loaded."

The EnableComCallers property enables VBA interoperability. Set this property to True from the Design view of the host item (such as Sheet1 or ThisDocument) containing the code that you want to expose to VBA. This automatically changes the ReferenceAssemblyFromVbaProject property to True.

The EnableComCallers property controls whether the class associated with a host item is visible to COM clients. The ReferenceAssemblyFromVbaProject indicates whether to add a reference to the assembly’s type library to the VBA project at design time. ReferenceAssemblyFromVbaProject also has a build time implication. If ReferenceAssemblyFromVbaProject is true, Visual Studio Tools for the Office system (3.0) creates a type library and embeds it inside the assembly. If you set ReferenceAssemblyFromVbaProject to False, then you must create a type library and manually set a reference to it from the Visual Basic Editor. In most cases, you want to set ReferenceAssemblyFromVbaProject to True if EnableComCallers is True. Although the ReferenceAssemblyFromVbaProject property appears as a property on each host item’s designer, this property is actually a document-level property. That is, it is either true or false for all host items as a group.

When developing by using Visual Studio Tools for the Office system (3.0), you may want to edit VBA code while debugging the application. However, it is important to remember how Visual Studio Tools for the Office system (3.0) processes files when compiling and building the application. Only changes made to the document in the main project directory are permanent. When debugging, the active document is served out of the bin\debug directory. Any changes to this document are overwritten the next time you debug or build the project.

While you could add the VBA code you want that refers to the Visual Studio Tools for the Office system (3.0) procedures to the document in the main project directory, you cannot debug that code in the Visual Basic Editor. In fact, the VBA code will not compile because the document in the main project directory does not have the type library associated with the Visual Studio Tools for the Office system (3.0) project registered with the VBA project. Consequently, use caution when editing the VBA code during the development process so that you do not inadvertently overwrite your changes.

Visual C# Quick Start

This example calls a Visual Studio Tools for the Office system (3.0) method that displays a simple message box from VBA by using Visual C#.

Hello World Visual C# Example

Open a workbook in Office Excel 2007.
To open the VBE, press Alt+F11.

Click the Sheet1 object and add the following code.

Sub SayHelloVBA()
   MsgBox "Hello from VBA."
End Sub

Save the workbook as SayHello.xlsm (Macro-Enabled Workbook) and close Excel.
Open Visual Studio, and then create a Visual C# project.
Select Office 2007 Excel Workbook, and then name it SayHelloCS.
When prompted to select a document for your application, select Copy an existing document, and then select the SayHello.xlsm workbook.
In Solution Explorer, right-click Sheet1.cs, and then select View Code.
To avoid fully qualifying items near the top of Sheet1.cs, add a using directive statement from the InteropServices namespace.
```
using System.Runtime.InteropServices;
```

To enable VBA interop for Sheet1, override GetAutomationObject.

protected override object GetAutomationObject()
{
   return this;
}

To mark it as COM visible, add the [ComVisible(true)] attribute to the Sheet1 class declaration.
To indicate the type of COM class interface to generate, add the [ClassInterface(ClassInterfaceType.None)] attribute to the Sheet1 class declaration. Figure 3 shows an example of how the class should look.
To provide an example method to call from VBA, add the following code to Sheet1.cs.
```
public void SayHelloVSTO()
{
   MessageBox.Show("Hello from VSTO.");
}
```
Figure 3. Generating an interface for Sheet1.cs
Create a COM interface for Sheet1.cs that includes the public methods that you want to expose to VBA. As shown in Figure 3, right-click inside the Sheet1 class and from the shortcut menu, choose RefactorExtract interface. Select all public members to add to the interface (Figure 4), and click OK.

Figure 4. Specify the interface name, file name, and public members of an interface
In Solution Explorer, double-click ISheet1.cs to view the code generated by the Extract Interface feature.
To expose this interface to COM callers, add [System.Runtime.InteropServices.ComVisible(true)] to the ISheet1 interface declaration.

Add the Public modifier to the interface declaration so that it is visible to external processes. The following example shows the complete ISheet1 interface.

using System;

namespace SayHelloCS
{
    [System.Runtime.InteropServices.ComVisible(true)]
    public interface ISheet1
    {
        void SayHelloVSTO();
    }
}

In Solution Explorer, double-click Sheet1.cs to view the Designer and set the ReferenceAssemblyFromVbaProject property to True.
Press F5 to run the application.
After Excel starts, press Alt+F11 to open the Visual Basic Editor.
In the Project Explorer, double-click Sheet1 and add a property procedure to call the Visual Studio Tools for the Office system (3.0) procedures.
```
Property Get VSTOAssembly() As SayHelloCS.Sheet1
    Set VSTOAssembly = GetManagedClass(Me)
End Property
```
In the code associated with Sheet1, modify the SayHello macro to call into Visual Studio Tools for the Office system (3.0) from VBA. Add VSTOAssembly.SayHelloVSTO after the line with the MsgBox statement.
```
Sub SayHello()
    MsgBox "Hello from VBA."
    VSTOAssembly.SayHelloVSTO
End Sub
```
With the cursor inside the SayHello procedure, press F5 to run the SayHello procedure.

As discussed earlier, Visual C# does not provide the same facilities as Visual Basic for creating COM visible classes and interfaces. The .NET Framework facilities that help enable COM interoperability are located in the InteropServices namespace. To simplify statements, it is helpful to add a using directive that refers to System.Runtime.InteropServices. The InteropServices namespace defines the attributes needed to mark a class as visible for COM and to specify the type of class interface to generate for a class exposed to COM callers.

You must override GetAutomationObject so that VBA sees the interface associated with Sheet1 (ISheet1) rather than the Automation object associated with a generic Microsoft.Office.Tools.Excel.Worksheet. That is, you want it to see the Visual Studio Tools for the Office system (3.0) worksheet object rather than the native interop worksheet. To experiment with this, replace return this; in GetAutomationObject with return base.GetAutomationObject();.

After you implement all of the methods that Visual Studio Tools for the Office system (3.0) should expose to VBA, create an interface to expose to COM. Make this interface public and visible to COM. You can easily create an interface by using the Refactor feature of Visual Studio. Figures 3 and 4 both show this. To review, see Steps 15-17 of the Visual C# Quick Start.

Unlike Visual Basic, in Visual C#, the VBA interoperability feature exposes only one property in the Design view of a host object (such as Sheet1.cs). Because you must manually expose a class to COM in Visual C#, there is no EnableComCallers property. The only property available is the ReferenceAssemblyFromVbaProject property. As explained in the Visual Basic example, setting the ReferenceAssemblyFromVbaProject property to True eliminates the need to set the reference to the Visual Studio Tools for the Office system (3.0) assembly manually from within the Visual Basic Editor. Additionally, when this property is set to True, Visual Studio Tools for the Office system (3.0) embeds a type library into the assembly at build time.

Example: Integrating VBA and Visual Studio Tools for the Office System (3.0)

With the 2007 Microsoft Office system, companies may have updated their VBA applications created for Microsoft Office 2003. Although their applications still work in the 2007 Office environment, depending on the application, there could be several opportunities available to enhance the Microsoft Office solution. For example, the 2007 release deprecates command bars in favor of the new Microsoft Office Fluent UI. If your Office 2003 solution uses command bars extensively, you may prefer to create your own customized Office Fluent Ribbon.

For example, Figure 5 shows the interface of a sample application. This application, originally created for Microsoft Office Excel 2003 with VBA, uses command bar controls to provide a UI. The SayHello menu item displays a message box that says "Hello from VBA". The Say Goodbye menu item displays a message box that says "Goodbye from VBA".

Figure 5. Custom menu in Excel 2003

Custom menu in Excel 2003

Figure 6 shows how the custom UI appears in Office Excel 2007 without making any updates to the application. In Office Excel 2007 and Office Word 2007, any custom command bars are displayed on the Add-Ins tab. This situation is complicated if there are multiple add-ins loaded that use command bars. In this scenario, all of the command bar controls are combined on the Add-Ins tab in a confusing manner.

Figure 6. Custom menu in Excel 2007

Custom memu in Excel 2007

The following example shows the code for the current VBA implementation. The Workbook.Open event calls the CreateMenu procedure. This code is contained in the Sheet1 object in the VBA project.

Public Sub SayHello()
    MsgBox "Hello from VBA."
End Sub

Public Sub SayGoodbye()
    MsgBox "Goodbye from VBA."
End Sub

Sub CreateMenu()
    Dim cbWSMenuBar As CommandBar
    Dim cbc As CommandBarControl
    
    ' Set a reference to the main menu bar.
    Set cbWSMenuBar = Application.CommandBars("Worksheet Menu Bar")
    ' Add a new menu item.
    Set cbc = cbWSMenuBar.Controls.Add( _
        Type:=msoControlPopup, Temporary:=True)
    
    With cbc
        ' Tag the menu item so it is easy to find in VBA.
        .Tag = "MyMenu"
        .Caption = "&My menu"
        With .Controls.Add(Type:=msoControlButton, Temporary:=True)
            .Caption = "SayHello"
            .OnAction = "Sheet1.SayHello"
            .Tag = "Item1"
        End With
        With .Controls.Add(Type:=msoControlButton, Temporary:=True)
            .Caption = "Say Goodbye"
            .OnAction = "Sheet1.SayGoodbye"
            .Tag = "Item2"
        End With
    End With
    
    Set cbWSMenuBar = Nothing
    Set cbc = Nothing
End Sub

The following example shows how to mix Visual Studio Tools for the Office system (3.0) and VBA code, by converting this application so that it uses a customized Ribbon. For this example, assume that the SayHello procedure is an extensive VBA procedure that you want to preserve in VBA. However, I am porting the SayGoodbye procedure to managed code by using Visual Studio Tools for the Office system (3.0).

To convert the application to a Visual Studio Tools for the Office system (3.0) application that uses the Office Fluent Ribbon and preserves some VBA procedures while replacing others, perform the following steps.

To convert to a Visual Studio Tools for the Office system (3.0) application

Open Visual Studio, and using the Excel 2007 workbook project template, create a Visual Basic project. Optionally, comment out or remove any code that creates command bars before performing this step.
Use a copy of the existing workbook as a document for the application.

In addition to macro-enabled workbooks, the interoperability feature can also use Excel 2003 workbooks.
In Solution Explorer, right-click Sheet1, click View Code, and then add the following procedure after the Sheet1_Shutdown event handler.
```
    Public Sub SayGoodbye()
        MessageBox.Show("Goodbye from VSTO.")
    End Sub
```
In Solution Explorer, right-click Sheet1, and then select View Designer.
Set the EnableComCallers property to True.
On the Project menu, point to Add New Item, and then click Ribbon (Visual Designer).
Add the two buttons described in Table 1 to the Office Fluent Ribbon and save them.

Table 1. Ribbon button property values

Button Name

ControlSize

ImageMso

Label

Button1

RibbonControlSizeLarge

AcceptInvitation

Say Hello

Button2

RibbonControlSizeLarge

HighImportance

Say Goodbye

Figure 7. Two buttons added to the Office Fluent Ribbon
Double-click Button1 to enter the OnAction event handler. Add the following line of code to this procedure.
```
Globals.ThisWorkbook.Application.Run("Sheet1.SayHello")
```
Double-click Button2 to enter the OnAction event handler. Add a line of code to this procedure.
```
Globals.ThisWorkbook.Application.Run("Sheet1.SayGoodbye")
```
Press F5 to save, build, and then run the application. At this point, the new custom Ribbon is visible and each of the buttons calls the appropriate VBA code when clicked. Figure 8 shows what happens after you click the Say Goodbye button.

Figure 8. Test the VBA procedure SayGoodbye
To replace the VBA SayGoodbye procedure with the Visual Studio Tools for the Office system (3.0) SayGoodbye procedure, open the Visual Basic Editor and view the code associated with Sheet1.
Comment out the VBA MsgBox statement in the SayGoodbye procedure and as shown in Figure 9, add the following statement: CallVSTOAssembly.SayGoodbye.

Figure 9. Modify the SayGoodbye procedure to use Visual Studio Tools for the Office system (3.0)
Now when you run the project and click the Say Goodbye button, the application calls the Visual Studio Tools for the Office system (3.0) SayGoodbye procedure shown in Figure 10.

Figure 10. The SayGoodbye procedure after converting to Visual Studio Tools for the Office system (3.0)

Button Name	ControlSize	ImageMso	Label
Button1	RibbonControlSizeLarge	AcceptInvitation	Say Hello
Button2	RibbonControlSizeLarge	HighImportance	Say Goodbye

This converted example shows how to integrate managed code created with Visual Studio Tools for the Office system (3.0) with existing VBA code. Notice that the process of replacing VBA procedures with Visual Studio Tools for the Office system (3.0) procedures can occur without drastic code changes.

Conclusion

Before the availability of the VBA interoperability feature, the decision to choose between VBA and Visual Studio Tools for the Office system (3.0) was, for all practical purposes, an either/or decision. Likewise, the costs associated with an all or nothing switch to Visual Studio Tools for the Office system (3.0) outweighed the benefits for many VBA applications. With Visual Studio Tools for the Office system (3.0), the interoperability feature allows you to call Visual Studio Tools for the Office system (3.0) procedures from VBA code with the added bonus of enabling you to take advantage of IntelliSense capabilities with the Visual Basic Editor.

The interoperability feature helps you extend and preserve existing VBA code bases while at the same time taking advantage of new Visual Studio Tools for the Office system (3.0) functionality. This allows companies to port their existing applications gradually as they need.

Additional Resources

About the Author

Steve Hansen is a Microsoft Office system Architect at Dakota Technology Group, Inc.. Steve has been enthusiastically developing Office solutions for over a decade. He has written two books on Microsoft Office Excel, Mastering Excel 2000 Premium Edition, a comprehensive guide to Microsoft Excel published by Sybex in 1999 (co-authored with Mindy Martin and Beth Klinger) and Mastering Excel 2003 Programming with VBA, published by Sybex in 2004.

Steve holds a Master of Business Administration degree with a concentration in Finance from the Carlson School of Management at the University of Minnesota. He maintains a blog at VSTOtips.com.