Call Custom Microsoft VBA Code From a Managed COM Add-in in the Microsoft Office System

  • Article

 

Ty Anderson
Credera

November 2003

Applies to:
    Microsoft® Office PowerPoint® 2003
    Microsoft Office Excel 2003
    Microsoft Office Word 2003
    Microsoft Office System

Summary: Discover how to extend Microsoft Office using managed COM add-ins by calling existing Microsoft Visual Basic Application (VBA) code. In this article, learn to call existing functions, create your own custom Add-in, and use it directly from Office Applications. (9 printed pages)

Contents

Introduction
The Business Scenario
Calling Office Document VBA Functions
Creating the COM Add-in
The Connect Class
The PowerPoint File
Trying It Out
Conclusion

Introduction

COM add-ins are commonly used to extend the functionality of Microsoft Office applications. COM add-ins allow developers to give users custom Office features and behaviors that meet their specific needs. As a result, many developers build entire applications whose code is contained within a single COM add-in. However, there is a risk that in some cases developers may be inclined to duplicate code in an add-in that already exists in the VBA code tied to existing document, spreadsheet, or presentations. In other words, you can significantly enhance a COM add-in and speed its creation if it can use existing Microsoft Visual Basic® Application (VBA) code. Keep in mind that VBA macros use the same object model as Office COM add-ins (you can do everything you need to do in the add-in without invoking VBA code), but focus here is on leveraging existing VBA code that you do not want to replace, at least for now. This article shows how to call VBA code in Microsoft PowerPoint® presentations from a managed COM add-in.

The Business Scenario

One of the great strengths users derive from Microsoft Office is that they can have custom templates for commonly created documents. You can significantly fortify these templates by embedded VBA code. For example, you could add a wizard that helps users add data to the final document or that simplifies complex tasks to a document template. You could use custom wizards for each template, but call each wizard in a consistent, standardized way.

Using this approach, you can create a single COM add-in that provides a consistent entry point, perhaps by using a custom menu or other mechanism, for the special procedures and wizards that you build into the document templates. In essence, each document fully encapsulates its business rules by using VBA and Office forms. The COM add-in provides the consistent shell users can employ to use the code encapsulated in the document.

This architectural design means that you split the overall add-in application in two. The first component being the actual COM add-in, the second, the Office document and its embedded VBA code. The goal of this article is not to elaborate on either the custom VBA code or how you should present the COM add-in to the user. Rather, its focus is on how the two architectural components can work together. Hence, a simple sample of VBA code and basic COM add-in serves the purpose of demonstrating the connection between the two.

Calling Office Document VBA Functions

The bridge between the two application components is the Run method of the Application object. This method simply executes a VBA macro contained within an Office document. All that is required to call the Run method is a string containing the macro name you wish to run. For the purposes of this application, the name of the macro to be executed is already known and is thus placed directly in the code. However, you could just as easily use the Run method to call VBA macros dynamically by getting the macro names from some source and building a string of the names. You can then pass this string to the Run method rather than hard-coding it.

The syntax of the Run method for PowerPoint is as follows:

Application.Run(MacroName, safeArrayOfParams)

In the context of this article the Application object is a reference to PowerPoint. Using the Application object, you can call the Run method by passing a string that contains the name of the macro you want to run. In addition, the Run method has the SafeArrayOfParams argument which allows you to pass other types of information to the called macro using a simple parameter array. This allows you to pass arguments through from your COM add-in to the VBA procedure.

It is important to know that Microsoft Office Word 2003 and Microsoft Office Excel 2003 expose the Run method in a slightly different fashion. You can still pass arguments to this method for these applications, but rather than an array of parameters, the method includes a long list of optional parameters as follows:

Note   The following example includes line breaks to allow this code example to display correctly. To use, remove all line breaks.

Application.Run(MacroName As String, [varg1], [varg2], [varg3], [varg4], [varg5], [varg6], [varg7],
 [varg8], [varg9], [varg10], [varg11], [varg12], [varg13], [varg14], [varg15], [varg16],
 [varg17], [varg18], [varg19], [varg20], [varg21], [varg22], [varg23], [varg24], [varg25],
 [varg26], [varg27], [varg28], [varg29], [varg30])

Creating the COM Add-in

To demonstrate this technique, you need a managed COM add-in that loads when PowerPoint starts. You can easily apply the same technique to Excel or Word with small modifications. In this article, the add-in acts as a simple wrapper allowing it to interact with the desired application events. The simple add-in is composed of a single class, the Connect class, that contains both the implementation of the IDTExtenisbility2 interface and the custom code.

To begin writing the add-in, complete the following steps:

  1. Open Visual Studio.Net and on the File menu, point to New and then click Project.

    –OR-

    Press CTL + N on the keyboard.

  2. In the Project Types window, expand the Other Projects folder and click Extensibility projects.

  3. In the Templates window, click Shared Add-in.

  4. In the Name box, type a name the new project such as RunVBA.

  5. In the Location box, type or browse to a location for the project files, and then click OK.

    Visual Studio launches the Extensibility Wizard. This wizard, like most wizards, provides a user-friendly interface to the developer and helps to simplify the process of creating the add-in.

  6. Click Next. On the Select a Programming Language page, click Create an Add-in using Visual Basic, and then click Next.

    On the Select An Application Host page, to load this add-in only in PowerPoint, clear all applications except Microsoft PowerPoint, and then click Next.

  7. On the Enter a Name and Description page, type a name for the add-in (RunVBA).

  8. In the What is the description for your Add-in? box, type Sample add-in calling VBA functions,**and then click Next.

  9. On the Choose Add-in Option page, select the I would like my Add-in to load when the host application loads check box. Verify the other check boxes are cleared.

    Figure 1. The Choose add-in Option page

    **Note   **Selecting "My add-in should be available to all user of the computer it was installed on. . ." hides the add-in to be hidden from the list of Available COM Add-ins of the host application. This is great for the corporate environment where you may not want to give your users the ability to remove the add-in. However, if you plan to package and sell the add-in as a product, your users may not appreciate the fact that the only mechanism to unload the add-in is to remove it or edit the registry. For more information see Microsoft Knowledge Base article 316723 PRB: Visual Studio .NET Shared Add-in Is Not Displayed in Office COM Add-ins Dialog Box.

  10. Click Next. The Summary page shows all the selected options. Click Finish.

The new project is created. Notice that the wizard created two projects. The first is the add-in project that serves as the basic shell that provides an interface for the targeted Office applications. The second project is the setup project for the add-in. This project creates the necessary Windows Installer (MSI) files for deploying the add-in.

One last step is required before getting into the code. You need to set a reference to Microsoft PowerPoint. To do so, on the Project menu, click Add Reference. Click the COM tab. In the Component list, click Microsoft PowerPoint 11.0 Object Library. Click Select, and then click OK.

In Solution Explorer, under the References node, you should now see PowerPoint listed. In addition you should also see a reference to the Microsoft.Office.Core library. These two libraries provide all the objects needed for this add-in. Your solution in the Solution Explorer should look similar to the one shown in Figure 2.

**Note   **If you see references to both Microsoft.Office.Core and Office in the Solution Explorer's Reference section, right-click on the Office reference and select Remove. Having both of these libraries referenced is redundant and causes conflicts.

Figure 2. The entire add-in solution as shown in the Solution Explorer

The Connect Class

During the creation of the project, Visual Studio adds a Connect class to the project. This class implements the IDTExtensibility2 interface required for all add-ins. Although there are five methods available for our use, we are only interested in the OnConnection method. The OnConnection method executes once when the host application loads the add-in and a connection is made between them. Modify the OnConnection method as follows:

Public Sub OnConnection(ByVal application As Object, _
  ByVal connectMode As Extensibility.ext_ConnectMode, _
  ByVal add-inInst As Object, ByRef custom As System.Array) _
  Implements Extensibility.IDTExtensibility2.OnConnection
  applicationObject = application
  add-inInstance = add-inInst
  Setup(application)
 End Sub

Using the passed Application parameter (this is a reference to the host application), the routine acts as a glorified traffic cop. The method uses a Select statement to determine which Office application is serving as the host and then calls the Setup method (which is explained in a moment), passing the Application object as an argument.

The Connect class imports the following namespace:

Imports Microsoft.Office.Interop.PowerPoint

The Connect class contains only one class-level variable which is used to store a reference to the PowerPoint host application:

Private Shared WithEvents m_appPPT As Application

For this add-in, the only PowerPoint event we capture is the AfterPresentationOpen event. This event fires immediately after a presentation is opened with PowerPoint. Insert the following code into the Connect class:

 Private Shared Sub m_appPPT_AfterPresentationOpen(ByVal Pres As _ 
  Presentation) Handles m_appPPT.AfterPresentationOpen  
  Dim strMacroName As New String("!VBACode") 
  Dim myParamArray() = {"This is text from the COM add-in"}
  strMacroName = strMacroName.Concat(Pres.Name, strMacroName.ToString)
  m_appPPT.Run(strMacroName.ToString, myParamArray)
 End Sub

Using the name of the just opened presentation file, a string is built to call the VBA function contained within the file. In addition a parameter array (myParamArray) is created and given a single value. This value is used by the PowerPoint file to display a message. Although this example passes only a single value in the array, you could just as easily pass multiple values specifying all kinds of additional info. Keep in mind, however, that the array cannot contain any object references or named arguments. Other than these the range of possible values is fairly broad since the array type is Variant.

With the string built containing the macro name, the Run method of the PowerPoint application is used to call the VBA function. This is an effective way to hand off control of the processing logic from the add-in engine to the presentation file containing further, more specific business logic relevant to the file.

To complete the Connect class, two custom functions and one property are required as follows:

 Public Shared Function Setup(ByVal oApp As Application) As Boolean
  m_appPPT = oApp
 End Function
 Public Shared Function ShutDown()
  m_appPPT.Quit()
 End Function
 Public Shared Property App() As Application
  Get
   App = m_appPPT
  End Get
  Set(ByVal Value As Application)
   m_appPPT = Value
  End Set
 End Property

The Setup function stores a reference of the host application which is passed as an argument. This is the method called in the Connect.OnConnection event. The ShutDown function closes the referenced PowerPoint application, releasing it from memory. The App property sets and retrieves a reference to the PowerPoint application.

The PowerPoint File

Once the add-in code is complete, the add-in is ready to load. To do so, you must acquire a pointer to the host application, PowerPoint, and call a VBA procedure embedded within a presentation. Therefore, we need presentation with some embedded VBA code for it to call. For the purposes of this article add a simple procedure that, when called by the add-in, displays the text passed from the add-in in a message box. Recall that this article explains how to call VBA code from an add-in, and a real business solution presumably includes VBA code much more worthy of attention than this one here.

Create the PowerPoint presentation with embedded VBA code by completing the following steps:

  1. Open PowerPoint and create a presentation.

  2. If the VBA Editor is not started, open it by pressing ALT + F11.

  3. From the Insert menu, click Module to insert a new module into the files.

  4. In the Properties window, name the new module basMain. In the Code window, insert the following procedure:

    Public Sub VBACode(vParams)
  Dim strName as String
  strName = vParams
  MsgBox "Text to display: " & strName & "!"
End Sub

  5. Save the file and make note of its location because you need it to test the solution.

The procedure added to the presentation takes the passed Array, assigns its value to a String and then displays a friendly message. This procedure assumes that only a single value is contained in the array. If the passed Array contains more values then this, procedure simply loops through the array values and operates on each as required.

Trying It Out

All the pieces are now in place, and all that is left is to compile the add-in and see if the VBA code is called when you open the PowerPoint presentation. Remember that the add-in calls the VBA code when the AfterPresentationOpen event fires. You can either build a release version of the COM add-in, or, if you want to step through the code to debug, you can configure Visual Studio to allow this. For debugging, be sure to set the project to start PowerPoint when you run the project. This is easily done by editing the "Start external program" value in the project's property pages (Figure 7). Press the . . .Browse (. . .) button to browse to the location where you installed PowerPoint.

Figure 3. The property page for the add-in

With this configuration complete, set any desired breakpoints in your code, and press F5 to run the add-in. Once PowerPoint opens, open the file you created in the previous section. As the presentation opens, the COM add-in calls the VBA procedure in the presentation and passes some text to it. The VBA procedure, in its turn, displays the text in a message box.

Conclusion

Calling VBA code from a COM add-in has some important benefits. First of all, it means that the wealth of VBA code stored with countless documents is fully accessible from the COM add-in. Of course, this assumes that you have knowledge of the VBA code and that you can predicate the availability of this code when the COM add-in executes. If this is indeed the case, you can make the VBA code an important part of your COM add-in design. Here, the design of the add-in is quite basic, and for simplicity all of the code is placed in the Connect class that Visual Studio adds when creating the project for you. As you create a real-world solution, you should strongly consider creating a separate class in your add-in to contain the custom code rather than placing it in the Connect class. Then, you could load the custom class using the methods of the Connect class.

About the Author

Ty Anderson, CPA, is a founding partner of Credera in Richardson, TX, a strategically focused consulting company that helps clients use technology to grow their business. Ty leads all things technical at Credera, including development of tailored solutions using Microsoft technologies.

© Microsoft Corporation. All rights reserved.