Creating VBA Add-ins to Extend and Automate Microsoft Office Documents

  • Article

Summary:  Learn how to create add-ins for Microsoft Word, Microsoft PowerPoint, and Microsoft Excel by using Microsoft Visual Basic for Applications (VBA). Examine when VBA might be the best choice for your add-in and explore several sample tasks that can be included in VBA add-ins for Microsoft Office 2010.

Applies to: Excel 2010 | Office 2007 | Office 2010 | Open XML | PowerPoint 2010 | SharePoint Server 2010 | VBA | Word 2010

In this article
Why Use VBA?
When to Use VBA
Creating VBA Add-in Files
Troubleshooting Add-ins
What Can You Do with VBA Add-ins?
Managing Your VBA Code
Next Steps
Additional Resources
About the Author

Published:  February 2011

Provided by:  Stephanie Krieger

Contents

  • Why Use VBA?

  • When to Use VBA

  • Creating VBA Add-in Files

  • Troubleshooting Add-ins

  • What Can You Do with VBA Add-ins?

  • Managing Your VBA Code

  • Next Steps

  • Additional Resources

  • About the Author

Click to get code Download the sample code

Why Use VBA?

As a developer, you might use VBA at times to interact with your managed code when you automate documents and templates. But, are you taking advantage of all the benefits of VBA? If the first thing you do when you begin a Microsoft Office project is open Microsoft Visual Studio, you might be doing more work than necessary.

If the project requirements benefit from the power of managed code, such as for server integration, more flexible UI design, or leveraging Microsoft Silverlight technology, stop reading and open Visual Studio. But if you can meet the requirements of a particular project with a solution that is simpler, faster (and potentially more cost-effective) to build, it might be time to give VBA a try.

When to Use VBA

Recently, a large company ran into some complications with a basic add-in that it was creating in Visual Studio. The purpose of the add-in was to provide a ribbon tab in Word, PowerPoint, and Excel that linked to a handful of customized Help content.

When the company representatives contacted me for help, I asked them why they weren't using VBA for something so simple. There was no specific reason, they said. It never occurred to them to use anything other than Visual Studio. So, I opened the Visual Basic editors in the Office 2010 applications. About an hour later, they had easy, complete add-ins for Word, Excel and PowerPoint with a UI that looked exactly the way they had originally intended.

As the saying goes, don't use a cannon to kill a mosquito. When VBA can do what you need to do, you might find that you spend less time, write less code, and create a package with fewer dependencies that is simpler to deploy.

Sure, the preceding example is a very simple one. But you might be surprised at how much you can do with a VBA add-in, whether you create solutions for one user or for thousands. As a professional document consultant, I create VBA-based solutions in Word, PowerPoint, and Excel for companies totalling half a million users in approximately 100 countries/regions.

In the sections that follow, learn how to create add-in files in Word, PowerPoint, and Excel. Then, explore examples of the types of tasks that you can include in your add-ins, from simplifying enterprise document creation to interacting with built-in Office 2010 functionality.

Creating VBA Add-in Files

Read this section to learn how to create simple add-ins for Word, PowerPoint, and Excel by using the preceding example. Each add-in provides a custom ribbon tab that gives the user quick access to a custom selection of how-to content.

Creating a Word Add-in

Word VBA add-ins are global templates. That is, templates that are installed so that they are available regardless of the active document. To create a Word add-in, then, you begin by creating a new .dotm (macro-enabled Word template) file.

To create the VBA project

  1. With your new .dotm template open in Word, on the Developer tab, in the Code group, click Visual Basic. Or, press Alt+F11.

  2. On the Insert menu, click Module. Then, in the Properties window, name the module.

  3. On the Tools menu, click Project Properties to name the project, add a project description, or add password protection.

The following is the code for the custom Help add-in. Copy the code into the module that you just created.

Important

For the files referenced in these macros, you can download the Office 2010 product guides and Interactive Menu to Ribbon guides from the Microsoft Download Center. Find a placeholder file that you can use as the company branding guidelines document in the sample download for this article. Note that you must save the referenced files using the path and file names that are specified in the macros to test your add-in. If you use different path and file names, edit the macros accordingly.

Also note that completed versions of all add-in examples in this article are included in the sample download. However, to test the custom Help add-ins for Word, PowerPoint, and Excel, you must download and save or install (respectively) the applicable product guides and Interactive Menu to Ribbon guides.

Option Explicit
Public oPath As String

Sub CommandRef(ByVal Control As IRibbonControl)
On Error GoTo Errorhandler
'The FollowHyperlink method used in this procedure requires an open document.
With Documents
    If .Count = 0 Then
    .Add
    End If
End With
'If you use the environmental variable for program files as shown here,
'(which is usually advisable) rather than hard-coding the drive letter,
'note that 64-bit machines that include both a Program Files x86 folder
'and a Program Files folder may not point to the desired location.
oPath = Environ("ProgramFiles")
ActiveDocument.FollowHyperlink Address:=oPath & _
    "\CompanyTools\CustomHelp\Word 2010\Word 2010 Guide.html", NewWindow:=True
Exit Sub
Errorhandler:
'Error 4198 occurs if the file name or path is not found.
If Err.Number = 4198 Then
    MsgBox "The Word 2010 Interactive Guide is unavailable. " & _
        "Consult your local IT department for assistance.", vbInformation
End If
End Sub

Sub ProductGuide(ByVal Control As IRibbonControl)
On Error GoTo Errorhandler
With Documents
    If .Count = 0 Then
        .Add
    End If
End With
oPath = Environ("ProgramFiles")
ActiveDocument.FollowHyperlink Address:=oPath & _
    "\CompanyTools\CustomHelp\WordProductGuide.pdf", NewWindow:=True
Exit Sub
Errorhandler:
If Err.Number = 4198 Then
    MsgBox "The file 'WordProductGuide.pdf' is unavailable. " & _
        "Consult your local IT department for assistance.", vbInformation
End If
End Sub

Sub BrandOverview(ByVal Control As IRibbonControl)
On Error GoTo Errorhandler
With Documents
    If .Count = 0 Then
    .Add
    End If
End With
oPath = Environ("ProgramFiles")
ActiveDocument.FollowHyperlink Address:=oPath & _
    "\CompanyTools\CustomHelp\Brand Guidelines.pdf", NewWindow:=True
Exit Sub
Errorhandler:
If Err.Number = 4198 Then
    MsgBox "The file 'Brand Guidelines.pdf' is unavailable. " & _
        "Consult your local IT department for assistance.", vbInformation
End If
End Sub

Note

Notice that the procedures in this code sample are declared as ribbon controls. With this declaration in place, you can only run the procedure from an associated ribbon control. So, in a typical add-in where the macros would have far more code, it is often more convenient to have a separate module with a separate set of procedures for the ribbon controls and call the macros from the controls.

Once your module is complete, you can save and close the template.

To install the global template, exit Word and save the file to the Word startup folder. When Word starts, it automatically loads the global templates located in that folder. Before you can load this add-in, however, you must first add the Office Open XML markup for the custom ribbon tab, as described in the next section.

In Microsoft Windows 7 and Windows Vista, the default Word startup location is C:\Users\[user name]\AppData\Roaming\Microsoft\Word\Startup. To manage global template add-ins or to check if your add-in is loaded, in Word, on the Developer tab, click Add-Ins.

If you need to edit the code after you load the add-in, open the .dotm file in Word. Word automatically replaces the previously loaded instance with the open template. Edit the code in the Visual Basic editor, save your changes, and then close the .dotm file. The loaded instance of the add-in automatically updates with your latest changes and remains loaded; you do not need to restart Word.

Note

The Custom UI content in this article applies to Office 2010 but it does not apply to Office for Mac 2011. In Word 2011, you can add custom toolbars and menus programmatically for your specific template. But you can also do so from within Word, without using code. To do this, on the View menu, point to Toolbars, and then click Customize Toolbars and Menus.

Also note that, as of this writing, an extra step may be needed to automatically load a global template in Word 2011 when the application starts. If your template doesn’t reload after you following the preceding instructions, add a one-line macro in a module within the Normal.dotm global template that instructs the macro to add. The code for this is as follows (note that you must change the location and filename in the code to apply to your global template):

Sub AutoExec()AddIns.Add fileName:="[Complete Path and File Name]", Install:=TrueEnd Sub

Creating the Custom UI for Your Add-in

You might already use a utility to help you customize the ribbon in a document or template, but adding the markup manually is just as quick and easy. As with writing any Office Open XML markup, you can use Windows Notepad or any text editor to write the markup. However, you might find Visual Studio useful here because it can reference the ribbon customization schema, which means that you can take advantage of IntelliSense.

Note

In the steps that follow, the assumption is that you are opening the document package and manually adding the customUI to your file. If you have never done that, it is a good way to learn about the Office Open XML structure behind the document. If you have some experience with editing an Office Open XML document package and you use Visual Studio 2010, you might want to try the Open XML Package Editor Power Tool to open the document or template directly in Visual Studio. Doing so saves a few steps because the tool enables you to add folders directly to the package, makes it easier to add relationships, and automatically manages content types when you add a part or a file. Learn more and download the Open XML Package Editor Power Tool for Visual Studio 2010

You could also use a tool to help you build the customUI structure; for example, the Custom UI Editor, which is free, adds the necessary parts for you, and provides samples of customUI.xml markup.

The markup for this example includes just a few buttons, but it demonstrates a number of customizations that you can easily include. For example, notice in Figure 1 that the third button is a custom image. The markup also includes custom ScreenTip text and custom KeyTip accelerators.

To add customUI markup to an Office 2010 document or template

  1. Create a folder named customUI. You can create this folder in any location that is easy for you to access, such as on your Windows desktop.

  2. Add a file named customUI.xml to the new folder and then add the markup for your ribbon customization to that file. The following code is the markup to include for the preceding Word add-in example.

    <?xml version="1.0" encoding="utf-8"?>
<customUI xmlns="https://schemas.microsoft.com/office/2006/01/customui">
  <ribbon>
    <tabs>
      <tab id="customTab" label="Company Help" >
        <group id="CustomHelp" label="Get Help" >
          <button id="WebGuide" visible="true" size="large" 
                  label="Interactive Word 2010 Guide" keytip="W" 
                  screentip="Get help for finding commands on the ribbon." 
                  onAction="CommandRef" imageMso="FindDialog"/>
          <button id="ProdGuide" visible="true" size="large" 
                  label="Word 2010 Product Guide" keytip="P" 
                  screentip="Learn what's new in Word 2010 and how to find it." 
                  onAction="ProductGuide" imageMso="AdpPrimaryKey"/>
          <button id="BrandGuide" visible="true" size="large" 
                  label="Company Brand Guidelines" keytip="B" 
                  screentip="Get help for using company branding." 
                  onAction="BrandOverview" image="brand"/>
        </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>

  3. Add the customUI folder to the document or template package.

    To do this, you can change the file extension to .zip and then open the ZIP package to drag the new folder into it. Or use a utility that enables you to open the package without changing the file extension, such as 7-Zip.

    Warning

    If you change the file extension to open the package, don’t forget to change it back when you are done.

  4. Add a relationship definition for the customUI file to the top-level relationships (.rels) file in the ZIP package.

    1. In the _rels folder, open the file .rels.

    2. Add the following relationship definition. If another relationship in the file already uses the ID rID4, select a different ID for this relationship.

      <Relationship Id="rID4" Type="https://schemas.microsoft.com/office/2006/relationships/ui/extensibility" Target="customUI/customUI.xml"/>

      Note

      This particular case requires Steps 5 through 7 because it uses a custom image for the Company Brand Guidelines button, as shown in Figure 1. If your markup uses only built-in images, there are no more steps required. For a list of all available built-in Office 2010 images that you can use in your customUI markup, see the Additional Resources section at the end of this article.

  5. Create two subfolders in the customUI folder. One named _rels and, if using the markup from this sample, the other named images.

  6. Place the picture file for your custom control image (used in the third button in this example) in the images folder and create a file named customUI.xml.rels to place in the _rels folder. Use the following markup for the .rels file.

    <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="https://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="brand" Type="https://schemas.openxmlformats.org/officeDocument/2006/relationships/image" Target="images/brandicon.png"/></Relationships>

  7. Open the [Content_Types].xml file on the top level of the ZIP package. Add a definition for the .png file extension (the file format of the custom image), if one doesn’t already exist. Use the following markup.

    <Default Extension="png" ContentType="image/png"/>

Figure 1. Results of the customUI markup

Results of the customUI markup

Later in this article, see examples of additional customUI tasks, including the following:

  • Using a Split Button control.

  • Adding ribbon customization to a built-in tab.

  • Specifying the position for controls on a tab or for a tab on the ribbon.

  • Using VBA with the customUI markup to manage visibility of controls.

Creating a PowerPoint Add-in

To create a PowerPoint add-in, begin by creating a macro-enabled PowerPoint presentation (.pptm) file. PowerPoint uses a unique file format for add-ins (.ppam), but that format is read-only. So, you write and edit your code in the .pptm file and then save a copy as a .ppam add-in.

To create a PowerPoint add-in

  1. With the new .pptm file open, open the Visual Basic editor.

    Note

    If you are working in the Visual Basic editor for more than one application at a time, use Alt+F11 to toggle between the Visual Basic Editor and the appropriate application.

  2. Take the same steps as those provided in the Word add-in section of this article for creating a new VBA project. You can then copy the code in that section for the custom Help module, and edit it for PowerPoint as follows:

    1. Replace the Documents collection object and ActiveDocument object with Presentations and ActivePresentation, respectively.

    2. Replace references to Word 2010 content and file locations with references to comparable PowerPoint 2010 content.

      The PowerPoint product guide and Interactive Menu to Ribbon guide are also available for download from the links provided earlier. As with the Word add-in, remember to save and name the PowerPoint sample files to match the path and file names that are specified in the macro. Because some of these files are different from those for the Word add-in, this will require editing file names in the macros to match your sample file names as well.

  3. Save and close the .pptm file.

  4. Add the necessary customUI folder and content to the .pptm file.

    You can use the steps and markup provided in the preceding section, editing the markup (such as labels and ScreenTip text) as needed for the selected PowerPoint content. However, be sure to follow the steps to edit the .rels and [Content_Types].xml files that are resident in the PowerPoint file, rather than replacing those.

  5. Open the .pptm file in PowerPoint. After confirming that the custom Ribbon tab looks and functions correctly, save a copy of the file as a PowerPoint Add-In (.ppam) file. PowerPoint automatically saves the add-in to the Microsoft Add-Ins folder.

    In Windows 7, the Microsoft Add-Ins default folder path is C:\Users\user name\AppData\Roaming\Microsoft\AddIns.

Note

In PowerPoint for Mac 2011, you can programmatically add a toolbar or menu to your addin. See the accompanying downloads for this article for a sample.

To load the PowerPoint add-in

  1. In PowerPoint, on the Developer tab, click Add-Ins.

  2. Click Add New.

  3. Select the file that you just saved and then click Open.

  4. If prompted with a macro security warning, click Enable Macros.

By default, loaded PowerPoint add-in files don't appear in the Project Explorer of the Visual Basic editor. To view and open loaded .ppam projects, add a new value to the Windows Registry. In the key HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\PowerPoint\Options, add a DWORD value named DebugAddins, with a value of 1. Then, restart PowerPoint. Note that you can view, export, and copy content from the loaded .ppam project. You can also make edits to test them in the loaded add-in, which makes the add-in function similar to a handy scratch pad when you’re troubleshooting or want to try something new. However, you cannot save changes directly in the loaded add-in file.

Creating an Excel Add-in

Excel add-ins might be the most efficient to create among the three programs discussed in this article because you can save a file as an Excel add-in (.xlam), load the add-in file, and then write and edit the code directly in the add-in file while it is loaded.

To create and load an Excel add-in

  1. Create a new Excel file and save it as an Excel Add-In (.xlam). Excel automatically saves the file to the Microsoft Add-Ins folder.

  2. On the Developer tab, click Add-Ins. Your newly saved add-in should appear in the list. If so, select it (which loads the add-in) and then close the dialog box. Otherwise, click the Browse button to find and load your add-in file.

  3. Open the Visual Basic editor. Then, create and populate the project as discussed in the preceding Word and PowerPoint sections.

    If you copy the VBA code provided in the Word add-in section, change references to the Documents collection object and ActiveDocument object to Workbooks and ActiveWorkbook, respectively. Also change references to Word files and locations as appropriate for Excel content. You can download the Excel product guide and Interactive Menu to Ribbon guide for this add-in from the links provided earlier.

  4. Exit Excel and then browse to the Microsoft Add-Ins folder to locate your new add-in file.

  5. Add the customUI to your add-in file.

    If you use the markup and code provided earlier for the Word add-in, edit the attributes (such as labels and ScreenTips) as needed for the Excel content. Be sure to follow the preceding steps for customizing the UI to edit the .rels and [Content_Types].xml files that are resident in the Excel file.

When you next open Excel, your add-in should still be loaded and your customUI should now be visible.

Troubleshooting Add-ins

When your add-in does not load or run correctly, check for the following common issues:

  • Ribbon customization does not appear.  If your ribbon customization does not appear when the file that contains the customUI is opened (or when the add-in is loaded), a syntax error is most likely to blame. Check the customUI.xml file that you created and the .rels file where you added the relationship.

    • Keep in mind that a very small error such as a missing quotation mark or bracket, or incorrect capitalization can cause an XML document part to fail.

    • Also check the relationship definition that you added to the .rels file to confirm that the path matches the name of the folder and file in which you stored the customUI markup.

  • A procedure does not run.  If a procedure does not run when you click an add-in control on the ribbon, check for the following possibilities:

    • In the customUI markup, does the procedure name that you used in the OnAction attribute for that control match the name of the procedure in your VBA code?

    • In the procedure that you used as the value of the OnAction attribute, is the customUI markup also declared as a ribbon control in VBA?

  • None of the add-in code runs.  If the add-in UI appears in the ribbon but none of your procedures run, the issue is most likely security.

    On the Developer tab, click Macro Security. Select the option Disable all macros with notification. Allowing all macros to run is, of course, not advisable. But if you disable macros without notification, add-ins may be unable to load.

What Can You Do with VBA Add-ins?

As mentioned earlier, the custom Help example here is among the simplest uses for an add-in. You can use VBA to create add-ins that provide complete document solutions for many types of requirements.

The tasks in the sections that follow are just a beginning. The intent is to help you get started with concepts that you can apply to many different types of needs, including gathering and storing user information, populating documents, evaluating document content, and interacting with built-in tasks such as closing a file or starting the application.

As you explore these concepts, remember that most of the underlying VBA shown can apply to Word, PowerPoint, or Excel regardless of which application the specific example uses.

Automating Document Creation Tasks

When automating content for users, you might need to collect and store user information and preferences, and then use that information to generate or customize content programatically.

In this example, a Word add-in collects contact information and paper size preference. The add-in also gives the user the ability to generate a company-branded letter document. When creating a letter, the add-in automatically uses the saved information.

This sample add-in contains one UserForm and three modules:

  • The UserForm is for collecting and storing the user information and preferences.

  • One module is for the variables required by multiple procedures, such as retrieving the stored user information.

  • Another module is for the ribbon controls.

  • The last module is for the code to generate custom documents.

For this task, create a dialog box (UserForm) where the user can enter the information. Then, save that information to the Windows Registry.

Note

Before you begin the following procedure, you must create the VBA project. Also note that, for Office for Mac 2011, you can use the same code indicated as saving or retrieving information in the Windows Registry. On Mac OS, these methods work the same and save information to a .plist file in the user preferences folder of the library.

To collect and save user information

  1. In the Visual Basic Editor, create and populate the UserForm with the controls that you will need. To do so, on the Insert menu, click UserForm. If the Toolbox (which contains the available UserForm controls) doesn’t automatically appear, on the View menu, click Toolbox.

    1. Click the control that you need (such as a label or a text box) and then drag on the UserForm to create the control with the required size.

      (Alignment and spacing tools are available on the Format menu.)

    2. Use the Properties window to name the control and set various properties, such as to add a caption or an accelerator key, set height and width, or set position from left and top. You can specify many aspects of control appearance and behavior from this window.

    Figure 2 shows the completed UserForm object for this example. Note the usability details that make user interaction with the UserForm easy. For example, accelerator keys are provided for keyboard users, controls are consistently sized and aligned, and frames are used to visually organize controls.

    In addition to the features that you can see, the tab order is set for ease of use by keyboard users. To set tab order for controls in a UserForm, right-click the form and then click Tab Order. Then, right-click in each frame and click Tab Order to reorder the controls that appear in the selected frame.

    Figure 2. UserForm after coding is complete

    UserForm after coding is complete

  2. Create a module for the variables that you will need for the UserForm, including variables to retrieve the settings that the UserForm code will save and to populate controls in the UserForm.

    Option Explicit
Public oName As String, oTitle As String, oAdd1 As String, oAdd2 As String, _
oPh1 As String, oPh2 As String, oPDat1 As String, oPDat2 As String, i As Integer, _
oPaper As String, oPhone(2) As String, oDoc As Word.Document

Sub DocVarbs()
'GetSetting and SaveSetting use VB and VBA Program Settings in the Registry: _
HKEY_CURRENT_USER\Software\VB and VBA Program Settings
oName = GetSetting("CustomDocs", "Info", "Name")
oTitle = GetSetting("CustomDocs", "Info", "Title")
oAdd1 = GetSetting("CustomDocs", "Info", "Add1")
oAdd2 = GetSetting("CustomDocs", "Info", "Add2")
oPh1 = GetSetting("CustomDocs", "Info", "PLabel1")
oPh2 = GetSetting("CustomDocs", "Info", "PLabel2")
oPDat1 = GetSetting("CustomDocs", "Info", "PData1")
oPDat2 = GetSetting("CustomDocs", "Info", "PData2")
oPaper = GetSetting("CustomDocs", "Info", "Paper")

'populates the combo boxes in the dialog box
oPhone(0) = "Phone"
oPhone(1) = "Fax"
oPhone(2) = "Email"

'sets default values for the first use of the info dialog box
'or in the case that a user generates a document with the tools
'before saving information.
If oName = "" Then
    oName = Application.UserName
    oChk = True
End If
If oPh1 = "" Then oPh1 = "Phone"
If oPh2 = "" Then oPh2 = "Email"
If oPaper = "" Then oPaper = "L"
End Sub

  3. Add the code for the UserForm.

    The Initialize procedure populates the form with information that has already been saved or with the defaults indicated in the variables procedure shown in the previous step. The cmdSave procedure saves the information provided by the user to the Registry.

    Option Explicit

Private Sub cmdCancel_Click()
Unload Me
End Sub

Private Sub cmdSave_Click()
Dim myP As String
With Me
    SaveSetting "CustomDocs", "Info", "Name", .txtName.Value
    SaveSetting "CustomDocs", "Info", "Title", .txtTitle.Value
    SaveSetting "CustomDocs", "Info", "Add1", .txtAdd1.Value
    SaveSetting "CustomDocs", "Info", "Add2", .txtAdd2.Value
    SaveSetting "CustomDocs", "Info", "PLabel1", .cmbPh1.Value
    SaveSetting "CustomDocs", "Info", "PLabel2", .cmbPh2.Value
    SaveSetting "CustomDocs", "Info", "PData1", .txtPDat1.Value
    SaveSetting "CustomDocs", "Info", "PData2", .txtPDat2.Value
    If .optA.Value = True Then
        myP = "A"
    Else
        myP = "L"
    End If
    SaveSetting "CustomDocs", "Info", "Paper", myP
End With
Unload Me
End Sub

Private Sub UserForm_Initialize()
Call modVarbs.DocVarbs
With Me
    .txtName.Value = oName
    .txtTitle.Value = oTitle
    .txtAdd1.Value = oAdd1
    .txtAdd2.Value = oAdd2
    .txtPDat1.Value = oPDat1
    .txtPDat2.Value = oPDat2
    .cmbPh1.List() = oPhone
    .cmbPh2.List() = oPhone
    .cmbPh1.Value = oPh1
    .cmbPh2.Value = oPh2
    If oPaper = "L" Then
        .optL.Value = True
    Else
        .optA.Value = True
    End If
End With
End Sub

  4. Create a module for Ribbon controls. In that module, add a procedure to display the UserForm and declare the procedure as a Ribbon control.

    Sub UserInfo(ByVal control As IRibbonControl)
frmInfo.Show
End Sub

Notice that this example saves the user information to the Registry. That same saved information can then be accessed by other add-ins if needed, such as when creating custom content for PowerPoint or Excel. In fact, the code and UserForm from this example could have been created identically in PowerPoint or Excel.

To create a custom document

  • For this task, start with a document template such as the company-branded letter in this example. The code for this example generates a document from the template. It then customizes the document based upon user preference and populates stored user information into content controls that appear in the document body and footer.

  • Notice, in Figure 3, that this letter-size document template includes two background shapes (for the gray gradient backing and for the red bar) that will need to be adjusted programmatically if the user’s paper size preference is A4. Those shapes have been named for ease of identification in the code using the Selection pane in Word 2010. To access the Selection pane, look on the Home tab, and then in the Editing group, click Select.

  • In Figure 3, the name and title in the document body and the address, phone, and email (as well as the phone and email labels) in the footer are in content controls. You can insert content controls from the Developer tab, in the Controls group. In that same group, click Design Mode to access the placeholder text for editing. Click Properties to customize control settings, such as to add the tags that are used to identify content controls in the code that follows.

    Note

    In Word for Mac 2011, you can’t create content controls or interact with them programmatically. Instead, consider using table cells or bookmarks as reference points for populating document content as shown in this section.

    To add a bookmark so that it’s hidden from the user, begin the bookmark name with an underscore. Note that you can only do this programmatically. To do so, click in the document where you want to add the bookmark and then, in the Visual Basic Editor, use the Immediate Window to add the bookmark. The code for adding a bookmark at the selection is shown in the following code sample.

    Selection.Range.Bookmarks.Add("_name")

    Figure 3. Template that the code uses to generate a document

    Template the code uses to generate a document

  • The code to generate and customize this document follows. It is a good idea to create a separate module for this code because it is likely to be one of several document types the user could create from a real world version of an add-in such as this. Once this code is complete, add a procedure in the ribbon module created earlier that you can use to declare a ribbon control for calling this macro.

    Sub NewLetter()
On Error GoTo ErrorHandler
Dim cc As ContentControl
'If the user has not yet saved their information, prompt with that option.
If GetSetting("CustomDocs", "Info", "Name") = "" Then
i = MsgBox("Would you like to save your document details now, so that they " _
& "can be added to your documents automatically?", vbQuestion + vbYesNoCancel, _
"Custom Document Tools")
    If i = vbYes Then
    frmInfo.Show
    ElseIf i = vbCancel Then
    Exit Sub
    End If
End If

Call modVarbs.DocVarbs
'set the defined Word.Document variable to the new document when it's created.
'avoids errors related to multiple concurrent open documents.
Set oDoc = Documents.Add(oPath & "\CompanyTools\CustomDocs\CompanyLetter.dotx")

'if the saved paper size preference is A4, this changes the paper size and
'then corrects the size and position of the background graphics in the header.
If oPaper = "A" Then
oDoc.PageSetup.PaperSize = wdPaperA4
    With oDoc.Sections(1).Headers(wdHeaderFooterFirstPage).Range
            With .ShapeRange("Backing")
                .Width = 541.75
                .Height = 841.95
            End With
            With .ShapeRange("Edge")
                .Width = 53.2
                .Height = 841.95
                .Left = 542.5
            End With
    End With
End If

'populate the saved information in the controls provided in the template.
For Each cc In oDoc.Range.ContentControls
    Select Case cc.Tag
        Case "Name"
        cc.Range.Text = oName
        Case "Title"
        cc.Range.Text = oTitle
    End Select
Next cc

For Each cc In oDoc.Sections(1).Footers(wdHeaderFooterFirstPage) _
.Range.ContentControls
    Select Case cc.Tag
        Case "Address Line 1"
        cc.Range.Text = oAdd1
        Case "Address Line 2"
        cc.Range.Text = oAdd2
        Case "Label1"
        cc.Range.Text = oPh1
        Case "Label2"
        cc.Range.Text = oPh2
        Case "Phone1"
        cc.Range.Text = oPDat1
        Case "Phone2"
        cc.Range.Text = oPDat2
    End Select
Next cc
Exit Sub

ErrorHandler:
Select Case Err.Number
    Case 5174
    MsgBox "The document template is unavailable." & _
    " Please contact your local IT staff for assistance." _
    , vbInformation, "Custom Document Tools"
    Case Else
    MsgBox "The task ended in error. The template may be damaged. Note " & _
    " that your saved info may not have been added to the new document.", _
    vbInformation, "Custom Document Tools"
End Select
End Sub

  • This example used unbound content controls for the user information. Depending upon requirements, such as if the same user information needed to appear in multiple locations in the document or if information appears on a cover page that might be swapped with another cover page building block, you might prefer to use bound controls.

Creating the UI for the Add-in

The add-in UI in this example uses a split button control placed on the Home tab, following the Paragraph group, as shown in Figure 4. To create this Ribbon customization, follow steps 1 through 4 in the preceding customUI example. For step 2, use the markup that follows.

<?xml version="1.0" encoding="utf-8"?>
<customUI xmlns="https://schemas.microsoft.com/office/2006/01/customui">
  <ribbon>
    <tabs>
      <tab idMso="TabHome" >
        <group id="CustomDocs" label="Company Docs" 
               insertAfterMso="GroupParagraph" >
          <splitButton id="CompanyDocs" size="large" >
            <menu id="coDocs" >
              <button id="NewLetter" visible="true" label="New Letter" 
                      onAction="Letter" imageMso="NewPageShort"/>
              <button id="UserInfo" visible="true" label="Save Info" 
                      onAction="UserInfo" imageMso="ContactAddMyContacts"/>
            </menu>
          </splitButton>
        </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>

Figure 4. Add-in customization on the Home tab

Add-in customization on the Home tab

This example uses content controls for populating the saved information. But you can take similar action to populate other objects in a Word document or in a PowerPoint presentation or Excel workbook. For example, populate content in a table cell or at a bookmarked location in a Word document; in a text box in PowerPoint; or in a worksheet cell in Excel.

Note

Application events, discussed in the following section, are not widely supported in Office for Mac 2011. PowerPoint 2011 doesn’t support Application Events, and Word 2011 has very limited support. However, you can use the same steps for setting up the module and class module in Excel for Mac 2011 and see the range of application events available there. See the Note that follows the procedure in the next section for help getting started with application events in Word or Excel. Also note that document-level events are supported in Word 2011 and that PowerPoint 2011 supports a selection of slideshow-specific macros.

Interacting with Application Events

There might be many reasons why you want your add-in to interact with built-in actions that occur in the application or in a particular document, such as when the user prints or closes a file. VBA gives you a few ways to interact with events, including those that occur at the application-level (that is, regardless of which document is open) and those that occur at the document-level (that is, only apply to a specific document, such as those based on a particular template).

Document-level events are more commonly included in VBA for an individual document or template. And you might create code in your add-in to interact with the behavior that is built in to the document or template. But, application-level events are often more convenient for add-ins because they can be built directly into your Word, PowerPoint, or Excel add-in to interact with actions that the user takes.

This example examines how to create an application-level event in PowerPoint to check company presentations when the user closes the file. The code checks for multiple masters in the presentation and gives the user the option to programmatically clean-up the masters and leave only the original, properly-branded master in the file.

Note

The article uses this example because it helps to address a common user complaint, particularly in companies migrating from a version of Microsoft Office that uses the legacy file formats (Office 2003 or earlier) to Office 2010 (or Office 2007). When users copy slides from presentations that were created in an earlier version, they often use the Keep Source Formatting command because it appears to make the slide look like the others in the presentation. But, that command actually adds another master and set of layouts every time that it is used. The results are presentations with an unwieldy number of masters, inconsistent formatting, and bloated file size that users and tech support staff interpret as damaged or corrupted files. A tool that is similar to the example code can be used to help the user before presentations become difficult to manage and to educate the user about the ramifications of specific actions.

To create an application event, you must add some code to any existing module in your VBA project, and add a class module for the event code itself. The following example also uses a UserForm so that it can take action that is based on user preference.

To create an application-level event in your add-in

  1. On the Insert tab, click Class Module to create a class module. In the Properties window, give the class module an intuitive name, such as EventClassModule.

  2. Add code to an existing module in your project to instantiate the new class module. This requires just one declaration (note the use of the class module name in the Dim statement that appears at the top of the module) and two small procedures.

    Dim XMST As New EventClassModule
Sub Auto_Open()
Call InitializeMSTApp
End Sub

Sub InitializeMSTApp()
Set XMST.app = Application
End Sub

  3. Create the UserForm that will query the user whether they want the masters removed from the presentation. This example uses a UserForm instead of a simple message box in order to give the user an additional option.

    Use the tips in the preceding section to create a UserForm. The finished UserForm has one label, two command buttons, and one check box, as shown in Figure 5.

    Figure 5. Completed UserForm for the application event

    Completed UserForm for the application event

  4. The code behind the UserForm in this case enables the execution of the event code to continue (cmdYes), or unloads the form, ending execution (cmdNo) with the option to add a document property to the file that will prevent the user from being prompted again when working on this same file (chkDontFix). Additionally, the Terminate procedure passes a value to the event code to instruct it to discontinue if the user clicks the title bar X to close the dialog box.

    Private Sub cmdNo_Click()
If Me.chkDontFix.Value = True Then
    With ActivePresentation
        .CustomDocumentProperties.Add Name:="DontFix", _
        LinkToContent:=False, Type:=msoPropertyTypeBoolean, Value:=True
        .Saved = msoFalse
    End With
End If
Unload Me
End Sub

Private Sub cmdYes_Click()
Me.Hide
End Sub

Private Sub userform_Terminate()
oCncl = True
End Sub

  5. To add the code for the event, at the top of the class module, declare an application variable for use with events. Then, add the procedure. In this case, the PresentationClose event is the procedure used.

    Notice that the following code uses two additional variables, oCncl as Boolean and i as Integer. To publicly declare these variables for use throughout the project, declare them at the top of any regular module.

    Public WithEvents app As Application
Private Sub app_PresentationClose(ByVal Pres As Presentation)
Dim oProp As DocumentProperty
oCncl = False
i = 0
'checks if the active presentation is a branded company
'presentation. In this case, a document property is included
'in the template for company presentations for ease of
'identification.
With ActivePresentation
    For Each oProp In .CustomDocumentProperties
        If oProp.Name = "CompanyPres" Then i = i + 1
    Next oProp
    If i = 0 Then Exit Sub
'checks for the DontFix property added when the user
'selects the check box in the user form.
    For Each oProp In .CustomDocumentProperties
        If oProp.Name = "DontFix" Then Exit Sub
    Next oProp
'checks the presentation for the number of masters.
'If only one master exists, there is no need to continue.
    If .Designs.Count = 1 Then Exit Sub
End With
'initiates the UserForm.
With frmMasters
    .chkDontFix.Value = False
    .cmdYes.SetFocus
    .Show
End With

If oCncl = True Then
    Exit Sub
End If

Dim oDes As Design, oSd As slide
'attaches all slides to the first master.
With ActivePresentation
    For Each oSd In .Slides
        oSd.Design = .Designs(1)
    Next oSd
'deletes all masters other than the first.
    Do Until .Designs.Count = 1
        For Each oDes In .Designs
            If Not oDes.Index = 1 Then oDes.Delete
        Next oDes
    Loop
End With
End Sub

After you save this file as an add-in and load it in PowerPoint, you need a presentation that contains the CompanyPres variable and more than one master to test your add-in. Use the sample document that is included in the download for this article; in addition, use the template to create your own test presentations.

Note

You can use the preceding steps 1 and 2 in Word or Excel as well to setup a class module for application events. The code in step 2 is identical for any of the programs and you can use the same statement Public WithEvents app As Application at the top of the class module in the Visual Basic Editor for Word or Excel to declare a variable for working with application events. After you do, select app from Object list (the dropdown list to the left above the code window) and you will then see available events in the Procedure list (the dropdown list to the right above the code window).

Controlling the Add-in UI Programmatically

When you create a custom UI for your add-in, you might occasionally need more control over the appearance of the UI than Office Open XML markup alone can provide. For example, label text for a control might vary based on specific conditions, or visibility of controls might vary based on user preferences. Fortunately, the customUI schema includes a selection of attributes that give you this type of flexibility.

In the following scenario, a company has a set of custom tools for Excel on the ribbon. Some users want to see the tab and other users do not. The add-in queries the user on startup whether to show or hide the custom tab. If the user chooses to hide the custom tab, the add-in provides a control at the end of the Home tab where the user can change the way the custom tab is displayed.

This VBA project requires a module in which to put the ribbon control procedures, a UserForm for retrieving the user’s preference, and code in the ThisWorkbook object of the add-in where you can create the event that runs the code whenever the user starts Excel.

The procedure that follows requires that you already have the customUI.xml markup for both the custom ribbon tab and for the reset control that will appear for users who initially do not want the tab.

To control ribbon customization behavior programmatically

  1. Create the UserForm that will prompt users for their preference when they start Excel.

    As in the preceding section, this example uses a UserForm instead of a message box in order to give the user an additional option.

    Figure 6. Completed UserForm for the Workbook_Open event

    Completed UserForm for the Workbook_Open event

  2. Add the code behind the UserForm, which sets the value for the variable that indicates the user preference. Note that this variable should be declared publicly as a string in a regular module in the project.

    Private Sub cmdNever_Click()
oVis = "Never"
Me.Hide
End Sub

Private Sub cmdNo_Click()
oVis = "No"
Me.Hide
End Sub

Private Sub cmdYes_Click()
oVis = "Show"
Me.Hide
End Sub

  3. In the ThisWorkbook object for the project, add a Workbook_Open event. The code shown here displays the dialog box if the user has not already indicated that they never want to be prompted. It then shows the dialog box and saves the user’s newly-selected preference (Yes, No, or Never) to the registry.

    Private Sub Workbook_Open()
Dim iResp As Integer
oVis = GetSetting("CustomDocs", "Info", "ExcelVis")
If Not oVis = "Never" Then
    frmTools.Show
    SaveSetting "CustomDocs", "Info", "ExcelVis", oVis
End If
End Sub

    Notice here how the ThisWorkbook object in Excel is uniquely useful for add-ins because it enables you to use document-level events on an application level. Since ThisWorkbook refers to the add-in file itself, the Workbook_Open event that is used in the following code sample runs when the application starts and automatically loads (opens) the add-in file.

  4. In a new or existing module in the project, add the procedures that will interact with the customUI.

    In this example, one procedure is used to establish the visibility preferences that are passed to the customUI file. The other procedure is used for the control that appears if the user opts not to be prompted with the visibility options, so that they can reset the option later.

    Notice that the ShowControls procedure refers to control IDs from the customUI.xml file. The first in this case is the ID provided to the custom tab; the second is the ID provided to the optional reset button that is added to the Home tab.

    Public Sub ShowControls(ByVal control As IRibbonControl, ByRef visible As Variant)
Select Case control.ID
    Case "TabCompany"
        If Not oVis = "Show" Then
            visible = False
        Else
            visible = True
        End If
    Case "CustomTools"
        If oVis = "Never" Then
            visible = True
        Else
            visible = False
        End If
End Select
End Sub

Sub Reset(ByVal control As IRibbonControl)
SaveSetting "CustomDocs", "Info", "ExcelVis", ""
MsgBox "You will be prompted to show " & _
"company tools the next time you start Excel.", _
vbInformation, "Company Custom Tools"
End Sub

  5. In the customUI.xml file, add the GetVisible attribute to the tag for your custom tab and to the tag for the group in which the reset button will appear. Use the procedure name ShowControls as the value for both.

    Following first is the markup just for the two edited lines. The second set of markup shows these lines in context.

    <tab id="TabCompany" label="Company Tools" insertBeforeMso="TabHome" getVisible="ShowControls">
<group id="CustomTools" label="Company Options" getVisible="ShowControls">

    Note that the custom tab in the next example uses an arbitrary selection of built-in commands since the purpose of this example is to show the visibility control for the tab itself.

    <?xml version="1.0" encoding="utf-8"?>
<customUI xmlns="https://schemas.microsoft.com/office/2006/01/customui">
 <ribbon>
 <tabs>
  <tab id="TabCompany" label="Company Tools" insertBeforeMso="TabHome"
       getVisible="ShowControls">
    <group id="coTools" label="Sample Tools" 
           supertip="Arbitrarily selected tools to demo GetVisible behavior.">
     <button idMso="FileNewDefault" size="large" />
     <button idMso="FileSave" size="large"/>
     <separator id="space2" />
     <button idMso="Paste" size="large"/>
     <gallery idMso="PasteGallery" size="large" />
    </group>
  </tab>
  <tab idMso="TabHome" >
     <group id="CustomTools" label="Company Options" 
            getVisible="ShowControls">
       <button id="coRestore" size="large" label="Reset" 
               onAction="Reset" imageMso="GroupJournalArrangement" />
     </group>
  </tab>
 </tabs>
 </ribbon>
</customUI>

Managing Your VBA Code

When your add-in is complete and ready to share, you might want to take an additional step and digitally sign the project. VBA provides two options for signing code: self-certification and authenticated certification.

Self-certification is quick to set up and easy to use, but it doesn’t verify your identity. If you are working with a company that requires code to be signed, you most likely need to purchase an authenticated certificate from a designated commercial certificate authority.

To set up self-certification, use the Digital Certificate for VBA Projects (Start, All Programs, Microsoft Office, Microsoft Office Tools.)

To find a commercial certificate authority from which you can purchase a certificate that can be authenticated, go to the same Digital Certificate for VBA Projects dialog box and click the appropriate link to view a list of applicable vendors.

Next Steps

This article showed you the basics of how to create a VBA add-in for Word, Excel, or PowerPoint. It included examples of some key concepts that you can use for a wide range of tasks in your add-ins. Of course, no single article can show you the breadth of what you can do with a programming language as powerful as VBA. Before you assume that you need managed code for a new project, consider VBA and determine which approach will provide the simplest solution for the task at hand.

Developers commonly assume that a solution has to begin in a code window. But remember that the programs that you’re automating, whether with managed code or with VBA, are powerful and flexible in their own right. A solution that requires simple automation might have a simple solution without any code (or what we traditionally think of as code). For example, bound content controls or field codes in Word can enable a great deal of dynamic behavior, as can functions in Excel. Whether your projects can benefit from using VBA or from using features in the application windows, the key is to consider your options, evaluate the requirements, and then determine the best tools for the particular task, one project at a time.

So what do you do if you begin a project in managed code or in VBA and realize in the middle of the project that the other approach would be better? See the following Additional Resources for help converting between VBA and managed code.

Additional Resources

About the Author

Stephanie Krieger is the author of two books, Advanced Microsoft Office Documents 2007 Edition Inside Out and Microsoft Office Document Designer. As a professional document consultant, she has helped many global companies develop enterprise solutions for Microsoft Office on both platforms. Her new book for Office 2010 and Office for Mac 2011 is scheduled for release in Spring 2011.