Adding Ribbons to VBA Add Ins

It’s fairly easy to update an old Office Add In to use the new Ribbon user interface.

There are two major steps to the process: creating an XML file to hold the ribbon information, and updating the macros to use IRibbonControls.  These directions will focus on Excel 2010, but the process will work for any Office 2007 or 2010 application with only minor changes.

Creating the Ribbon XML

The easiest way to get started is to use the Visual Studio tools for Office projects.  This will allow you to create the basic toolbar using drag and drop style tools.  In Visual Studio, create a new project and select Visual C# -> Office -> 2007 -> Excel 2007.  Right click in the project and select Add -> Office -> Ribbon(Visual Designer).  Right click on the created file and open the Designer.  Drag and drop and rename to your heart’s content.  When you’re done, right click on Ribbon and click “Export Ribbon to XML.”  Now you have the XML file that you will be using in future steps.

In order for the buttons on the ribbon to actually do anything, they need to have callbacks associated with them.  They will need to be added manually to your XML file.  All items have the option to set whether they are visible or enabled (using getVisible and getEnabled attributes).  Buttons have an onAction attribute, which is called when the button is pressed.  All of these are just the names of the macro that you want to call.

Example XML

<?xml version="1.0" encoding="UTF-8"?>
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    <ribbon>
         <tabs>
               <tab id="Tab1" label=”TabName”>
                   <group id=”Group1” label=”GroupName”>
                       <button id=”button” label”ExampleButton” imageMso=”PercentStyle” 
                         onAction=”ButtonAction”/>
                       <menu id=”Menu1” label=”MenuName” size=”large”>
                           <checkBox id=”Checkbox1” label=”CheckboxName” 
                            onAction=”ToggleCheckbox” getPressed=”CheckCheckbox”/>
                       </menu>
                   </group>
                </tab>
          </tabs>
     </ribbon>
</customUI>
 

Creating the Add In File

Create a new workbook in Excel 2010.  Save it as an Excel Add In (.xlam).

Navigate to the new file in Explorer.  Rename the file to be a .zip file.  Modern office files are generally just zip files filled with xml files.  Extract the zip file so we can make some changes to it.

  • First, open _rels/.rels and add

<Relationship Id=”customUIRelID” Type=”http://schemas.microsoft.com/office/2006/relationships/ui/extensibility” Target=”customUI/customUI.xml”/>

beneath the other Relationships in the file.

  •   Next, create a folder in the top level of the zip file called customUI.  Copy your XML file into that folder and rename it customUI.xml.  Turn the zip file back into an .xlam (or whatever the original suffix was).  If you open the file, you should have a new ribbon on your tool bar.  It may throw errors if some of the callback macros don’t exist yet, but that is fine.

 

Hooking everything up

We’ll need to create or adjust the macros used in the XML file.  If you’re updating an existing add in, many of those macros probably already exist.  They won’t work out of the box, though.  All onAction macros require an IRibbonControl to be passed to it:

Sub ExampleMacro(control As IRibbonControl)

Other macros (such as getVisible or getEnabled) also need a return value:

Sub ExampleGetVisible(control As IRibbonControl, ByRef visible)

     visible = false

End Sub

These can all go in a module in the add in file.

 

At this point you should have a working add in with a ribbon interface.  The Microsoft website also has some good information on this process.

Related posts: