Microsoft Powerpoint - PowerPoint Add-in for one-stop embedded chart formatting?

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 clickNext.

   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 loadscheck 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 theIDTExtensibility2 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 theSetup 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.

//For Full Details Click Here

//I hope This Will Very Helpful To You.......