Office Apps

Extend Your VBA Code With VSTO

Paul Stubbs and Kathleen McGrath

This article is based on a prerelease version of Visual Studio "Orcas." All information herein is subject to change.

Contents

Language Differences
Controlling the User Interface
Deploying VSTO Solutions
Extending VBA with VSTO

If you develop for Microsoft® Office using Visual Basic® for Applications (VBA) and have not yet made the jump to the Microsoft .NET Framework, Visual Studio® Tools for Office (VSTO) in the next version of Visual Studio, code-named "Orcas," makes the move easier and more compelling. VSTO brings you the full feature set of Visual Studio, including language-integrated query (LINQ), Windows® Presentation Foundation (WPF), Windows Communications Foundation (WCF) and the .NET Framework 3.5. In addition, VSTO has many new features targeting the 2007 Microsoft Office system, including visual designers, which make it easy to create custom form regions and customize the Ribbon. You can create custom document-level and application-level task panes and easily create and debug Office SharePoint® Server 2007 workflow solutions. VSTO solutions are simple to deploy thanks to full support for ClickOnce. In addition, VSTO allows you to leverage your existing investments in VBA by extending your existing solutions with managed code.

Language Differences

There are a number of differences in the language features available in VBA and Visual Basic. Some of the biggest concern data types, statements, and new language features. One example is that an Integer in VBA is equivalent to a Short in Visual Basic and a Long in VBA is equivalent to a Visual Basic Integer. There are also some data types that are no longer supported in Visual Basic, such as the Variant—instead, you can use the Object data type.

The default properties, popular in VBA, are not supported in Visual Basic or VSTO. For example, you cannot assign a String to the Range property of a paragraph as you can in VBA; instead, you must specify the Text property, as you see in the following code:

‘ VBA
Me.Application.ActiveDocument.Paragraphs(1).Range = _
    “This is my first paragraph.”

‘ Visual Basic
Me.Application.ActiveDocument.Paragraphs(1).Range.Text = _
    “This is my first paragraph.”

You still have access to many of the VBA-specific language features, such as MsgBox, although Visual Basic also supports the use of MessageBox. And although the OnError statement is still supported in Visual Basic, you should consider using the structured error handling that try/catch statements provide.

The differences between VBA and Visual Basic grow with each new version of Visual Basic that is released. Some of the new features of Visual Basic 9.0 include local type inference, object initializers, anonymous types, extension methods, XML integration, and LINQ.

For example, Visual Basic can now determine the type of a local variable by evaluating the value that is assigned to it, known as local type inference. Although the data type isn’t specified, do not confuse this with late binding, where the data type is determined at run time. Local type inference occurs at design time (early binding) and all of the benefits, such as IntelliSense® are available, as you see in Figure 1.

Figure 1** IntelliSense Is Available with Local Type Inference, Thanks to Early Binding **(Click the image for a larger view)

With LINQ, you can create queries using a familiar syntax that is integrated into the Visual Basic language, rather than having to learn a completely different query language, such as SQL or XQuery.

Controlling the User Interface

In typical VBA solutions, the entry points into the code are made available through UI elements, such as toolbars and menus, or by creating UserForms to gather input from end users. With VSTO, you can customize similar UI elements and, depending on the Office application, you can customize additional UI elements. Whether you create a solution using VBA or VSTO, you collect user input through controls that you provide in the UI. You can add controls to toolbars, menus, forms, documents, task panes, the Ribbon, and so on. The difference, then, really lies in the type and number of controls and the types of UI surfaces available that can contain these controls.

When you create an Office application using VBA, the forms are different from the forms you create with VSTO. With VBA, your only option is to display a UserForm, and the ActiveX® controls available for use on this type of form are limited. VSTO, on the other hand, supports a wide variety of Windows Forms controls. You can always find a Windows Forms control that matches the functionality of the controls available to a UserForm (although the name of the control might differ). For example, the equivalent of an OptionButton on a Windows Form is a RadioButton in VSTO. And although you will not find a ToggleButton on the Toolbox in a VSTO project, you can use a CheckBox control and set its Appearance property to Button. The properties, methods, and event names may differ between these types of controls as well, but you can typically find an equivalent. In VSTO, instead of setting the Caption property of a Label as you would in VBA, you would set the Text property of the Label.

The numerous Windows Forms controls available provide advanced functionality and allow you to build a much more professional UI for your application than you can build with UserForms. Because VSTO solutions are created in Visual Studio, you have access to many features that are not available in the VBA code editor, such as the Data Sources window, which enables you to add data-bound controls directly to a Windows Form. You can also use some of the new language features of Visual Basic and Visual C#® that enable the use of LINQ queries to fill a ListBox or ComboBox control.

You can add WPF controls to your application with VSTO by selecting a WPF user control item from the Add New Item dialog box and designing it by either dragging WPF controls from the Toolbox or adding XAML markup to the XML window. Then add a user control to your project and drag an ElementHost control from the WPF Interoperability tab of the Toolbox to the control. When you set the Child property of this control to the WPF user control, all of the WPF controls become hosted on the Windows Form. Now you can add this control to a Windows Form, the document’s surface, or a task pane.

Task Panes Since Office 2003, you’ve been able to customize the Office task pane to provide the same type of UI that a Windows Form enables, but with the added benefit of being able to dock the task pane outside the document surface. Customization of the task pane is not possible with VBA. Using VSTO, there are two types of task panes that can be customized: Document Actions task panes and Custom task panes.

The Document Actions task pane is a document-level task pane visible only to a document or workbook that is associated with the VSTO customization. You can only have one Document Actions task pane per document or workbook. You customize this document-level task pane through the ActionsPane object when you create a Microsoft Word or Excel® document or template project in VSTO.

To customize the document-level task pane, you add an instance of the user control to the Controls collection of the ActionsPane object. You cannot change the name that appears as the title of the task pane:

Dim TaskPaneControl As New UserControl1
Me.ActionsPane.Controls.Add(TaskPaneControl)

The 2007 Microsoft Office system introduces a new type of task pane, known as the custom task pane, which is customizable when you create application-level add-ins with VSTO. You can customize the actions pane in both the 2003 and 2007 versions of Word and Excel, but custom task panes are available (and thus customizable) in the 2007 version of five applications: Word, Excel, InfoPath®, Outlook®, and PowerPoint®.

A custom task pane can display the same type of Windows Forms controls as a document-level task pane; however, a custom task pane is available at the application level, and you can display multiple custom task panes within your solution. Whether you choose to customize an application-level or document-level task pane, the process is very similar.

To customize the application-level custom task pane, add an instance of the user control to the CustomTaskPanes collection and pass the title that you would like the task pane to display:

Dim CustomTaskPaneControl As New UserControl1
Me.CustomTaskPanes.Add(CustomTaskPaneControl, “Format Document”)

Whether you use VBA or VSTO to create Office 2003 solutions, you can customize the toolbars and menus. Typically, you would call a macro from the OnAction property of a button on the toolbar or in a menu. Using VSTO, you must create the new menu or toolbar items programmatically, rather than using the OnAction property (which cannot be set to call managed code). Additionally, you must create an event handler for the button. The 2007 Office system introduces the Office Fluent interface, which includes the Ribbon. The Ribbon replaces toolbars and menus in most 2007 Office system applications, although you will still find menus and toolbars in the main window of Outlook 2007.

You can use VBA to customize the Ribbon using RibbonX, but VSTO makes it even easier by providing a Ribbon designer that makes adding new tabs, groups, and controls to the Ribbon as intuitive as adding a control to a Windows Form. You can drag buttons from the Toolbox (in the Ribbon Designer tab) to the Ribbon Designer, set properties of the button such as its size, and create event handlers for the button’s Click event. Alternatively, you can customize the Ribbon with several other controls using the Ribbon XML project item. Figure 2 shows the "Ribbon (Visual Designer)" and "Ribbon (XML)" project items that are available in the Office tab of the Add New Item dialog box.

Figure 2** Ribbon Project Items in VSTO **(Click the image for a larger view)

These Ribbon customization options are available to both application-level add-ins and document-level customizations that support the Ribbon. For example, you can create a document-level Ribbon customization for a Word template solution that assembles Professional Services Proposals for an engineering company.

There are three parts to a Ribbon: a tab, a group, and the controls you add to a group. Using the Ribbon designer in VSTO, you can quickly customize an existing tab or create a separate tab that contains all the functionality for your application. The Toolbox in Figure 3 lists all of the Office Ribbon controls available in a Word solution. When you drag a control to the designer, you can set properties, such as its size, its text, and whether it contains an image, in the Properties window. Some controls can contain other controls. For example, Figure 4 shows an Employees Menu control that contains a number of SplitButton controls, which, in turn, contain a number of Buttons.

Figure 3** Employees Menu Control in the Designer **(Click the image for a larger view)

Figure 4** Menu Images Rendered **(Click the image for a larger view)

You can use a built-in image by specifying its name in the OfficeImageId property of a control, or you can easily add your own custom image. You can add custom images to a control by specifying an image that has been added to the project as a resource in the Image property. When you add an image from a resource, it is displayed in the designer, as shown in the PEPs group in Figure 3. However, when you use built-in images, a placeholder image is displayed within the designer instead. When you run the application, the images are displayed as in Figure 4.

One of the advantages of using the Ribbon Designer over Ribbon XML is that you can create the event handlers for these controls in the same way you create them for controls on a Windows Form. For example, if you double-click the Title combobox, you could add the code shown in Figure 5 to the Title_TextChanged event handler to insert the title into the document. Ribbon XML enables advanced customizations not available in the Ribbon designer. You can export the Ribbon customization to Ribbon XML to further customize your solution; but you cannot import the Ribbon XML back into the designer. You must also create a callback method for each event handler that was in the Ribbon designer code.

Figure 5 Inserting Title

Private Sub Title_TextChanged(ByVal sender As System.Object, _
    ByVal e As Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) _
        Handles Title.TextChanged

    With Globals.ThisDocument.Paragraphs(1)
        .Range.Text = Me.Title.Text & “ Proposal”
        .Range.Font.AllCaps = True
        .Range.Font.Bold = True
        .Range.ParagraphFormat.Alignment = _
            Word.WdParagraphAlignment.wdAlignParagraphCenter
    End With

End Sub

Deploying VSTO Solutions

The VSTO security and deployment model is very different from that of VBA. In VBA, the source code is stored inside of the document itself, so deploying the code is as simple as copying the document. Although this model makes deployment very easy, trusting the VBA code becomes more difficult, as evidenced by the high number of VBA macro viruses that have developed. Another problem with the VBA embedded-code model becomes apparent when you try to roll out a new version—you can’t update the VBA code in your documents. You could open each document to change the VBA code inside,but how do you guarantee that you have updated every copy? Even in a small group you may not find every instance because documents are saved in e-mail and on servers, and they can be renamed.

In VSTO the code is actually linked to the document, where it is deployed to a central location and all of the documents just link to the code. This makes updating quite easy. VSTO supports publishing to local drives, UNC paths, HTTP sites, and removable media such as CDs, DVDs, and USB drives. The developer or administrator publishes a new version of the code and every user gets the new code when the document is opened again. Additionally, VSTO adds full support for ClickOnce, the technology that allows VSTO to publish, update, and cache the code locally. Although a full discussion of ClickOnce is outside the scope of this article, you will learn about some of the features as they apply to VSTO solutions.

You install an add-in the same way you install any other application by running an installer program called setup.exe that prompts the user to allow the setup program to install, register, and set the trust level. You no longer need to remember obscure registry keys or complex caspol.exe commands to install the add-in. Instead, you can use the publish feature of VSTO to create the setup.exe program and the supporting ClickOnce files.

The deployment feature is supported across all 2007 Office system applications that use VSTO. As an example of using VSTO to publish a solution, you can start by creating a standard Word 2007 add-in using Visual Basic. Double-click ThisAddin.vb to open the code window. (You’ll also need to add a reference to the System.Deployment assembly. This is not required by VSTO in order to deploy, but for the sample code to display the ClickOnce version information, you need to use some functions in this assembly.)Add the code in Figure 6 to the Startup event of the add-in. The code uses the IsNetworkDeployed to determine if this is running from the ClickOnce cache. If so, then you want to display the published version number of the application which is returned by the CurrentVersion property of the CurrentDeployment object. The CurrentVersion number matches Publish Version fields of the Publish tab in the property pages of the project in Figure 7. This also displays the application version, which is the version of the assembly set under the Application tab. Start by verifying that the application works by pressing F5 to run it. Word should open and display a dialog with the text "Not a ClickOnce Application". This is correct because you ran the application from VSTO. Close Word to stop debugging.

Figure 6 Is This a ClickOnce Application?

If Not System.Deployment.Application.ApplicationDeployment _
        .IsNetworkDeployed Then

    ‘This is a ClickOnce Application
    Dim currentVersion As String
    currentVersion = System.Deployment.Application _
        .ApplicationDeployment.CurrentDeployment _
            .CurrentVersion.ToString()

    MsgBox(“Started “ & vbCrLf & “ App Version:” _
        & My.Application.Info.Version.ToString() & vbCrLf & _
        “ Published Version “ & currentVersion)
Else
    MsgBox(“Not a ClickOnce Application”)
End If

Figure 7** The Add-in Project **(Click the image for a larger view)

Open the Project property page and click the Publish tab to see the Publish Settings which contains two properties—publish location and install location. VSTO will build the published project at the publish location. Think of this as the bin directory for publishing—the final deployed location of the solution. The administrator can change the publish location and re-sign the manifest using the ClickOnce Manifest Generation and Editing Tool (Mage.exe). In the Install Settings section, you can set the prerequisites and the update frequency. For performance reasons, by default the update frequency checks for new versions every seven days. Change this to "Check every time the customization runs" so that you can see the changes immediately. You can set the publish version using the standard major, minor, build, and revision format. By default, it is set to automatically increment the revision number every time you successfully publish. With all of these properties set correctly, just press the Publish Now button at the bottom of the page.

The publish wizard creates a setup.exe bootstrapper file and a VSTO deployment manifest file in the publish location. It also creates a subdirectory named with the version number and places the assemblies and the application manifest file in this location. When you publish again, another subdirectory will be created and a new VSTO deployment manifest file will be built that points to the current version of the application manifest. You can see in Figure 8 that there are two published versions of the add-in: 1.0.0.0 and 1.0.0.1. There are also two back-up copies of the deployment manifest files: DeployWordAddin_1_0_0_0.vsto and DeployWordAddin_1_0_0_1.vsto. These are only there to help you roll back to a particular version. For example, if you want to roll back to version 1.0.0.0, just rename DeployWordAddin_1_0_0_0.vsto to DeployWordAddin.vsto and the next time the application checks for an update it will get version 1.0.0.0.

Figure 8** Word Add-In Versions **(Click the image for a larger view)

Document solutions work the same way, except that the document is also published to the publish folder and a copy is published to the version subdirectory. There is no need to run setup.exe—to install the document solution you can just open the document, and you will be prompted to allow the installation of the solution. This enables you to publish the solution to a central location and then distribute the document to the users.

Once you publish the solution, users can install it by running the setup.exe bootstrapper, which will start the ClickOnce installation. The security model is more robust than it is in VBA and much easier to use than previous versions of VSTO. The first check looks for the solution to be signed by a trusted certificate. If the certificate is trusted, the solution is installed. If not,the user will be prompted to allow the installation. As I mentioned, document solutions are almost identical to add-ins except that you can install the solution by simply opening the document. One caveat with document solutions is that if the document is not on the local machine or contains VBA code, then the document location must be added to the Office trusted folders list. Once installed, the solution is available offline without running the solution first.

Extending VBA with VSTO

Another great feature of VSTO is the ability to work side-by-side with VBA, allowing you to keep your existing investment in VBA and utilize VSTO where it makes sense. For example, VSTO makes it easy to extend your VBA code to customize the Ribbon, create a custom task pane, or call a WCF service.

The first way for VSTO and VBA to communicate is for VSTO to call VBA functions. You use the object model of the Office application to call the function. For example, Excel provides the Run method to call VBA subroutines and functions. To use the Run method, you pass the name of the subroutine or function as the first parameter. The remaining optional parameters are used to pass data to the functions. Imagine you have a VBA function called AddNumbers, which takes two parameters and returns an integer. The VSTO code to call that VBA function would be:

Dim answer as integer = Me.Application.Run(“AddNumbers”, 10, 2)

One thing to note is that this code will fail if the appropriate access is not granted to the VBA macros. By default, VBA macros are not enabled. But then again, this scenario assumes you have existing VBA code that has permission to run and you’re now calling those existing macros from VSTO. This solution is also fragile because the user can easily change VBA code, which will cause the Run method to fail. Additionally, you will not have IntelliSense to guide you, which makes errors more common. You need to write your code defensively to handle these cases.

The second way for VSTO and VBA to communicate is for VBA to call VSTO functions. This was previously possible only in a very limited and fragile way, but is now built into VSTO. The common scenario for this feature is that you have an existing Excel document containing VBA code and now you want to add additional functionality written in managed code using VSTO.

Another common scenario is that the IT department creates a library of functionality in VSTO to be consumed by the VBA user. You might want to simply create an Excel 2007 workbook project with macros enabled, but first you must have an existing workbook that contains VBA code. Create a new workbook, add some VBA code, and save it as a macro-enabled workbook called VBAInterop.xlsm. Open Visual Studio "Orcas" and create a new Visual Basic Excel 2007 Project.

When the new project dialog prompts you to select a document for your application, select "Copy an existing document" and browse to the VBAInterop.xlsm you just created. This allows you to add VBA macros that call functionality in the VSTO solution attached to the document. If you are using a new installation of Visual Studio, you may get a warning dialog telling you that you need to allow access to the VBA project system. Click OK to allow and the project will be created.

After the project is created, save it by clicking File | Save All; the default location is fine for this example. Double-click on ThisWorkbook.vb in Solution Explorer to open the designer for the workbook. You can expose any view to VBA that you choose. A view in VSTO is the top-level object, which is represented in Solution Explorer as a file under the document node. Excel has four views by default, one for the workbook and one for each worksheet. Word, on the other hand, has only one—the document. You will expose the workbook view to VBA by setting a property in the property grid of the ThisWorkbook.vb file.

There are two properties that control the VBA interop behavior: EnableVbaCallers and ReferenceAssemblyFromVbaProject. You only need to set EnableVbaCallers to True; ReferenceAssemblyFromVbaProject will automatically be set to True also. ReferenceAssemblyFromVbaProject supports advanced scenarios around VBA interop and is beyond the scope of this article.

When you set the EnableVbaCallers to true, you will receive a warning dialog to let you know that VBA code that you write when debugging the project will not be saved in the project. Your VSTO project contains the Office document. When you build and debug/run the project, VSTO makes a copy of the document in the output location. It is this copy that is running, so changes you make will not be persisted back into the project’s copy of the document. If you want your VBA code persisted in the document, you must open the document in the project outside of VSTO and add the VBA code.

Now that you have enabled VBA interop, you can add a function for VBA to call. Right-click on the ThisWorkbook.vb and choose View Code. Create a function which will add two numbers together and return the result:

   Public Function AddNumbers (ByVal _
     x As Integer, _
     ByVal y As Integer) As Integer
       Return x + y
   End Function

You can now run the solution by pressing F5. After the workbook opens, press Alt+F11 to open the VBA editor shown in Figure 9. You will see the VBA code that was generated by VSTO to connect VBA to the VSTO assembly. You can now call the VSTO functions from the CallVSTOAssemby object with full IntelliSense that supports the parameters and return types.

Figure 9** VBA Editor **(Click the image for a larger view)

You can see how easy it is to extend your VBA code with VSTO and managed code. This is a great technique for adding WPF dialogs to your VBA solution or calling WCF services and other .NET Framework 3.5 features. You can also use this with user-defined functions to call managed code. You can even fully debug this solution by setting breakpoints in both the VBA code and the VSTO code, allowing you to step back and forth between the two development environments.

While VBA support is not going away in the near future, now is a perfect time to make the transition to managed Office development with VSTO. There are many new features available in the latest version of Office and VSTO that make this combination the most productive way to create great Office apps. For more information about VSTO, you can check out the VSTO forum on MSDN®online.

Paul Stubbs is a Senior Program Manager with the Visual Studio Tools for Office and Visual Tools for Applications team. He coauthored VSTO for Mere Mortals with Kathleen McGrath (Addison-Wesley, 2006). He has spoken at Tech•Ed and TechReady and participates in the developer community on the Microsoft forums. Read Paul’s blog at blogs.msdn.com/pstubbs.

Kathleen McGrath is a programming writer at Microsoft. She has written documentation for Visual Studio 2005 Tools for the Microsoft Office System (VSTO), Visual Studio Tools for Applications (VSTA), and Visual Basic. She has created video demonstrations of the features of VSTO and Visual Basic on her blog at blogs.msdn.com/kathleen.