Pumped by the success of porting the Plot Manager add-in to 2007, I spent way too much time adding icons to the buttons in Excel 2007. This note describes that experience.

I don’t know if Microsoft will resolve the issues addressed below or not. I have filed (or will file) bug reports on them but who knows if the bugs will be fixed — or even considered as such! {grin}

This note addresses the following issues:

Introduction

How does one actually specify an image attribute?

How does one actually make the getImage callback function work?

What happens when one specifies multiple attributes (e.g., imageMso and getImage)?

Summary and conclusion

Introduction

To add an image to a control, the XML schema associated with the control requires specification of the image attribute for a custom icon, the imageMso for a MS created icon, or the getImage attribute for a callback function.

If that is too cryptic…and I imagine it might…In all likelihood, this note will make a lot more sense to those who have had at least a preliminary look at the Office 2007 RibbonX interface. The intent here is not to explain the basics but rather address what one must actually do to make the code work. A good introduction is http://msdn2.microsoft.com/en-us/library/ms406046.aspx

How does one actually specify an image attribute?

The first thing is that the icon image goes in the images folder in the customUI folder in the XLSM file. I imagine it is documented somewhere but I could not find it. So, I put the image file in the root folder. Opening the XLSM file in Excel resulted in a “file is corrupt; do you want to repair it?” message. Next, I put it in the customUI folder. Same result. Finally — and at some level this is really scary {grin} — I decided to think about where where I would put images if I were designing the file format…and lo, and behold it worked.

Subsequently, I confirmed it by downloading and using the UI editor available at http://openxmldeveloper.org/articles/CustomUIeditor.aspx

But, I still could not get the image to show up in the Ribbon.

It turns out that the image specification in the XML schema should not include the file extension, even if the little existing literature states/implies it should. So, the correct syntax is

Having already spent several hours trying to make all this work, I did not test what filetypes are actually acceptable or what would happen if multiple files with the same name but of different types were present in the customUI/images folder. The above worked for a file of type PNG.

How does one use the getImage callback function?

The documentation and the implementation are inconsistent and I hope this is classified as a bug on the implementation side and fixed.

The XML specification works as documented:

In VBA, the callback function must be declared not as

(a la http://msdn2.microsoft.com/en-us/library/ms406047.aspx#office12customizingribbonuifordevelopers2_table12)

but as

The former generates a type mismatch error. Also, I cannot figure out what control contains beyond that it is an object. Trying to check any of the button/control properties fails with an “unsupported” error.

The next thing is that the returned type for other languages is documented as IPictureDisp. The only function I could think of was LoadPicture even though its documentation didn’t specifically indicate the returned data type. Surprisingly, it worked! Of course, the image file of interest has to exist on the system. The code, in my case, looks like:

What happens when one specifies multiple attributes (e.g., imageMso and getImage)?

I tested just one combination where imageMso was specified first with a subsequent specification of the getImage attribute. The callback function did get called but the image used was the imageMso one.

Summary and conclusion

Once Microsoft gets both the documentation and the code working and in sync, adding an icon will be pretty straightforward — well, as straightforward as editing XML code and putting image files in the correct folder in the XLSM file can be.

How about comparison with the pre-2007 environment? Well, one would essentially have to write the code to create a commandbar, add a button, and update the appropriate property (FaceID?). I imagine most experienced developers have this code “black boxed” so that it works off a table (1). But, now, instead of relying on user code that must be included in each add-in, one can rely on Microsoft doing the work for them. So, in a way, it shifts this ability more towards the less-experienced developers.

(1) My menuing system, for example, requires that one paste an image into the Menus worksheet, give it an unique name, and in the appropriate column of the row associated with the menu item include that unique name.