Custom Excel 2007 Ribbon and VBA

This page is not yet another treaty on the “how to” of the Office ribbon —many books have been written on this subject— but more a collection of my experience with and pitfalls of the Fluent Ribbon in combination with VBA.

I have (finally, can we say that?) started programming the Excel 2007 ribbon. Since now, I have been busy building Add-Ins with VBA using old-style CommandBars and was fairly happy with it, mainly because most customers for one reason or the other were still using Office 2003. Nevertheless, Microsoft had made to move to the so much-advertised Fluent Ribbon at the end of 2006, so I thought the time was finally ripe to start building my own ribbon, also because I didn’t like the visual of my CommandBar on the Add-Ins tab of Excel 2007.

The first aim of this exercise was to understand the Fluent Ribbon and how I could customise it. I bought books on the subject and scanned the Internet. Finally I decided to keep the Add-In as it was so it could continue to work on every PC running whatever release of Excel, and elected to create a Ribbon interface that would copycat my own CommandBar.

It took me finally full 2 weeks to get to a decent result and during that time I have learned a lot of things about the Fluent Ribbon, about its customisation possibilities and the interaction with VBA. Honestly, I am not at all impressed by the Ribbon and I think Microsoft has still a lot of work to do. The part I like less is the interface for customisation using only VBA: it is simply not possible.

Just to give you an idea about what you should do to start customising the Ribbon (copied from http://msdn.microsoft.com/en-us/library/aa338202.aspx and adapted for Excel):

1. Create a folder on your desktop named customUI.
2. Create the customization file in any text editor by writing XML markup that adds new components to the Fluent UI, modifies existing components, or hides components. You can use the XML markup from the previous example, to test the behavior. Save the file as customUI.xml (or any other name) in the folder you just created.
3. Validate the XML markup against your custom Fluent UI schema (optional).
4. Create a document in Excel, and then save it as an Office Open XML Formats file with the .xlsm extensions. File name extensions for files that contain macros have an “m” suffix. These files can contain procedures that can be called by Ribbon extensibility commands and controls.
5. Exit Excel.
6. In Microsoft Windows Explorer, add the file name extension .zip to the document file name, and then double-click the file to open it as a compressed folder.
7. Add the customization file to the container by dragging the customUI folder from the desktop to the compressed folder.
8. Drag the _rels folder to the desktop. A folder named _rels containing the .rels file appears on the desktop.
9. Open the new folder, and then open the .rels file in a text editor.
10. Between the final <Relationship> element and the closing <Relationships> element, add a line that creates a relationship between the document file and the customization file. Ensure that you specify the folder and file names correctly (the Id attribute supplies a unique relationship ID for the customUI—its value is arbitrary).
11. Save the .rels file.
12. Drag the .rels file from the desktop to the _rels folder in the compressed file, replacing the existing .rels file.
13. Remove the .zip extension from the container file.

Have you noted that, apart from step 4, all other steps are actually performed outside of Excel?

And I have not yet spoken about the VBA CallBacks needed in your code. The CallBacks are used by Excel to obtain customisation information about each control. Since Excel provides different types of ribbon controls it also calls these CallBacks with varying parameters, some ByVal, others ByRef. You need to find which parameters are passed and what value they expect back.

Issue 1: You cannot create ribbon elements dynamically in VBA

It is not possible to create ribbon elements dynamically via code as with Office 2003, where you could manage your own CommandBars and CommandBarButtons.

In Excel 2007 each ribbon element (Tab, Group, Buttons, etc.) needs to be defined statically and embedded in the Excel document using a specially crafted XML file and with quite a few manual steps, including renaming and modifying contents of the Excel document —factually a ZIP with the XLSM or XLAM extension.

Once you have embedded your ribbon definition in the Excel file then you can use VBA to control size (small or large), labels, visibility, enabled and toggled status but other things, like controlling the position on the ribbon, is not possible.

Issue 2:  Assigning custom icons dynamically is cumbersome

Excel offers the possibility to change the image of a ribbon element at run-time via a “getImage” VBA CallBack. The VBA CallBack has a ByRef parameter that allows you to specify the image you want to use. This parameter accepts 2 types of value: string or object.

When you specify a string value (eg. “FileSave”) then Excel looks up the value in the list of internal images (the so-called Mso images) and draw the corresponding icon on the ribbon element. As long as you want to use the  Mso images then there is little problem. If, however, you do not like the internal images and want something of your own mill then you are in trouble.

You can embed images in the Excel workbook but Excel can only “see” these images when you are using them statically on the ribbon element. If you want to use the VBA CallBack to get another custom image on the ribbon then you must provide, for the ByRef parameter, an object in IPictureDisp format. VBA’s LoadPicture() returns such object but it can’t read all picture formats (the recommended PNG for instance are excluded) and it also needs a solid file to read from. LoadPicture() cannot extract the image file from the Excel document, which means that you must find a way to make your custom icon available in solid form at run-time (either during installation or when booting Excel).

And if your icon file is in a non-readable format for LoadPicture, then you must use the GDI+ to convert the picture for you (look at LoadPictureGDI on Stephen Bullen’s Excel Page).

Issue 3:  If you use custom icons you may receive errors when trying to Customise the QAT

When your ribbon tab uses custom images (static or dynamic makes no difference) then as soon as you try to customise the Quick Access Toolbar (QAT) you will get prompted with an empty error for each non-Mso image on your ribbon:

Error message when customising QAT

This error occurs when your custom image is not 16×16 pixels. To obtain this error I have used, because I needed “large” buttons, only images with 32×32 pixels. On a few ribbon items I used 16×16 images and these were not triggering errors.

Issue 4: Tag property of Ribbon elements pointing to item with idMso not propagated

When you need a standard Excel ribbon element on your custom tab, forget about the Tag property. I have tried using it in my VBA CallBack but unfortunately the value was not propagated. I now check the Id property instead, which contains the internal Id (eg. “FileSave”).

Issue 5: You cannot hide item Separators dynamically at run-time

If you use separators in ribbon groups and want to hide them at run-time because the element at its right is being hidden, forget about it. There is a bug in Excel 2007 that does not permit setting the visibility of a separator with a VBA CallBack. If have reverted to a using a Ribbon Label made up of 6 non-breakable spaces (ASCII 255) that I show or hide upon need.

Issue 6: If you use a SplitButton don’t use the ‘getVisible’ CallBack of the button’s items

A SplitButton is made of a Button and a Menu. If you click the button its ‘onAction’ executes, and when you click the menu, a pop-up menu is displayed offering other options. The Paste button on Excel’s Home tab is such button.

Technically, the SplitButton contains a Button ribbon element and a Menu ribbon element. You are allowed to control the visibility of the SplitButton via VBA CallBack but it is not allowed to assign a VBA CallBack to control the Button.

Issue 7: The “List” items use a different Option Base

On the old-style CommandBar, controls that contain lists such as the Combo Box start with their first entry at 1 (one). In the new-style Ribbon, the first element has index 0 (zero).

Recommended Tools and Tips

Microsoft does not offer an integrated solution for editing the ribbon but luckily the community has made available tools that bring you really much ahead. I recommend the following free-as-beer products:

• Custom UI Editor for Microsoft Office (http://openxmldeveloper.org/default.aspx)
• RibbonX Visual Designer by Andy Pope (www.andypope.info)

The Custom UI Editor is “endorsed” by Microsoft. This tool is fast, proofs the customisation of your XML against the schema. Lacks a visual designer and the entire definition is hand-written, which does not offer a guarantee that it will work in Excel, as a matter of fact mine crashed regularly until I discovered that one of the custom image ID’s started with a number (“3DIcon” — after I changed it to “_3DIcon” everything worked again). It also keeps track of the custom image resources that you are using. However when you remove one of such resources the tool does not properly remove all the links and you may need to manually correct the “customUI.xml.rels” file located under the “customUI/_rels” folder in the Excel document.

The RibbonX Visual Designer made by Andy is an excellent Office Add-In (sits on the Developer tab) enabling you to “design” the ribbon customisation using a tree-like structure. It also offers “preview”. I have found some small problems with this tool, such as no validation of XML schema, deleting entries crashes the Add-In, regularly I get Zip/UnZip errors and once in a while Excel disappears but I have learned to Save regularly. Overall this tool has greatly supported my transition to the Ribbon interface and I will always grateful to Andy for making this tool available to us.

Both tool generate the code for the VBA CallBacks in a separate window. You need to copy this code and paste it in the file’s code module. However, if you add more ribbon elements in the above tools you can’t copy the entire VBA CallBacks to the code module anymore, but need to add only the missing CallBacks.

It would be nice if —and as an Excel Add-In Andy’s tool could do this— the VBA CallBacks could be generated automatically in the file’s code modules, closing the loop from this point of view.

Caution: do not use these tools at the same time on the same files. Sounds strange, but I had the same file open in the UI Editor and in Excel with the RibbonX Designer. At some point I saved the file in Excel and —without reopening it in UI Editor— I started making some changes which inevitably destroyed the modifications I had saved just minutes earlier in Excel.

NotePad++ is an excellent replacement for NotePad and has the necessary XML syntax highlighting. I also recommend IcoFX (www.icofx.ro) if you want to make fancy looking icons.

Tip 1: Names of VBA CallBacks

I recommend creating a single VBA CallBack procedure for each property you want to control at run-time for similar types of ribbon items. For instance, the visibility CallBack for each button ribbon item I use is named “AllButtons_getVisible” and in this procedure I use a Select Case based on the control’s Id.

This also helps keeping your VBA code module manageable and small.

Tip 2: Create a dummy Code Module with all VBA CallBacks to import in your document

I have available a VBA code module (.bas) with all possible callbacks already in place, using the indications in Tip1.  Each CallBack returns a default value that allows the ribbon to become visible and the ribbon elements to be evaluated in a proper manner. For instance, all Boolean CallBacks receive True, all textual ones receive a proper text such as “Label”, “ToolTip”, “SuperTip” and images receive “HappyFace”

When I want to create a new Ribbon, I import this code module in my macro-enabled Office document before I start working on the customisation. In this way, I can immediately preview the ribbon without error messages about missing procedures being generated.

Before I finalise the customisation, I use a Debug.Print "name_of_procedure" statement to verify whether a particular routine is being used or not and remove the unused ones.

You may also create a template file with all the necessary stuff inside and use that as a starting point.

Tip 3: Wrap a Ribbon Object made in Excel 2007 around your Excel 2003 style CommandBar

If you have an Add-In designed for pre-2007 Excel also, that creates its own CommandBar, do not embed the ribbon customisation in this file but create a separate Excel macro-enabled workbook containing the ribbon customisation. In this workbook’s Open event, hide the original CommandBar, and use the ribbon buttons’ onAction property to ‘click’ the corresponding button on the old-style CommandBar.

Tip 4: Use Windows Explorer’s Zip functionality

I recommend using Windows Explorer’s compression and decompression functionality to pack or unpack your Office documents. This functionality is normally available on the right-click contextual menu.

Explorer uses the same compression routines —and thus compression method— used by Excel. This will minimise the chances that your document gets “corrupted” by an “unknown” algorithm.

This I learned the hard way after I used IZArc (I’m a addicted freeware junkie) to re-compress a folder structure to an Excel 2007 file and, as you might expect, Excel didn’t like it too much. Probably if I’d used different parameters in IZArc the problem would never popped up, but at least now I know from the beginning that this is a potential for problems.

Tip 5: Keep it simple

In my first attempt I set VBA CallBacks with individual names for all items on my custom Ribbon. This generated a massive 2300+ line code module and I totally lost overview. Without doubt quite an achievement, and the code still didn’t do anything!

Now my entire module with comments and shared VBA CallBacks scores a little above 800 lines of code.

Tip 6: Re-think your CommandBar

The Ribbon offers a new way to present your CommandBar buttons. I think this is probably the most but only appreciated feature of this object.

Tip 7: After invalidating the *entire* Ribbon, issue a ‘DoEvents’

In Tip #3, I recommend wrapping the ribbon around the CommandBar. During the development, working with an XLSM workbook, everyhing worked without problem. The various tabs, panels, groups, labels, etc., are read at run-time from the equivalent values of the hidden, Excel pre-2007, CommandBar.

I created the final (password-protected) XLAM of my Ribbon wrapper and started a new round of tests and at that point Excel locked up almost completely. Although Excel was flashing on the TaskBar, I could not get control back, the only possibility was killing it the hard way via Windows Task Manager.

Then I opened and unlocked the Add-In in the VBIDE before running the test and there it was: the same blank VBA error message shown in Issue #3. After some googling with “excel 2007 blank vba error message” I was able to focus the problem. Excel refreshes the Ribbon in an asynchronous thread and probably during this thread it cannot access the [hidden] CommandBar it is reading from. This results in the blank VBA error message. Unfortunately Excel appears to popup this error while running macro code and when the VBIDE is *not* open, the message is shown in an unaccessible and invisible layer.

The solution that worked for me was adding a ‘DoEvents’ statement right after invalidating the entire Ribbon:

' In a Code Module of the Ribbon Wrapper
'
Public mRibbonUI As Office.IRibbonUI

Public Sub InitialiseAddInsRibbon(ribbon as IRibbonUI) ' Initialise CallBack
    Set mRibbonUI = ribbon
End Sub

Public Sub RefreshAddInsRibbon()
    '
    ' Code for refreshing the Ribbon.
    '
On Error Resume Next

    ' May not exist yet.
    '
    If mRibbonUI Is Nothing Then Exit Sub

    ' Invalidate the entire ribbon.
    '
    mRibbonUI.Invalidate

    ' --------------------------------------------------------------------- '
    ' Without this next statement, Excel can run out-of-sync, popup empty '
    ' vbCritical errors, lockup and/or destroy the custom ribbon. '
    ' --------------------------------------------------------------------- '
    '
    DoEvents ' DO NOT COMMENT OR REMOVE THIS LINE.
    '
    ' --------------------------------------------------------------------- '
End Sub

Nevertheless, re-thinking the layout of your CommandBar, of the buttons (and icons) can generate a very positive return on the acceptance of the application by your customers. Your customers don’t see and likely do not understand the triple salto’s your code makes but they will absolutely be impressed by how it presents. A well-designed look of an application often let people presume that the same cure was dedicated to the underlying code (this is no guarantee, the Ribbon itself demonstrates that this can also be the other way around).

Honestly, would you use or buy an ugly-looking software even though it promises miracles? I don’t think so, why would software vendors otherwise put so much emphasis on screenshot?

Dutch