Using custom icons with an application-specific add-in in Excel 2007 Beta 2

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

<button id=”Img4″ label=”Plot w/Image…” 
    size=”large” onAction=”Plot_Manager_12″
    image=”formulaimg” />

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:

<button id=”Img2″ label=”Plot w/Image…” 
    size=”large” onAction=”Plot_Manager_12″
    getImage=”getImage” />

In VBA, the callback function must be declared not as

sub GetImage(control as IRibbonControl, ByRef image)

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

but as

sub GetImage(control, ByRef image)

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:

Public Sub getImage(aCtrl, ByRef anImage)
    On Error Resume Next
    Set anImage = LoadPicture( _
        ThisWorkbook.Path & Application.PathSeparator _
        & ”formulaimg.jpg”)
    End Sub

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.

<button id=”Img5″ label=”Plot w/Image…”
    size=”large” 
    onAction=”Plot_Manager_12″
    imageMso=”ChartGalleryAll” getImage=”getImage”
    />

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.

Posted in Uncategorized

4 thoughts on “Using custom icons with an application-specific add-in in Excel 2007 Beta 2

  1. Hey Tushar,

    Both of these articles are really excellent. It looks like other than a few strong-typing issues and slight bugs that it seems to work “as advertized”, eh?

    You made a comment here in the 1st of your two articles about making use of the IRibbonExtensibility interface:

    > “from what I can tell, the many dynamic changes in the RibbonX interface are not available to VBA (those that need the IRibbonExtensibility interface).”

    I’m not sure, however, that one would actually need to use IRibbonExtensibility in VBA because the XML is embedded within the XLSM or XLAM document itself. In the case where one’s add-in really does need to respond dynamically at startup, then I would think that setting various controls’, groups’, and/or tabs’ enabled or visible states would do the trick.

    Of course, if one had VB6 or MOD then a COM Add-in could be made, implementing IRibbonExtensibility. However, if using VBA-only, and we really wanted to push the Ribbon XML dynamism at startup, then I guess one could create a startup “loader VBA” add-in which would dynamically choose to open the correct subordinate VBA Add-in as required at run-time. The “subordinate VBA Add-in” would contain the correct XML string. This is clunkier than being able to dynamically return a string, but it should be effectively pretty close.

    Thanks for an excellent read, really nice job…

    Mike

  2. Do you know if there is any way to load an image from the CustomUIimages section of the file for the getImage callback?

    Thanks,
    Michael

    The following does not work:

    Public Sub GetIcon(control As IRibbonControl, ByRef image)

    On Error Resume Next

    Dim filename As String

    filename = ActiveDocument.FullName & “customUIimagesIcon_SmileyHappy.png”

    Debug.Print “Loading picture ” + filename + “…”

    Dim picture As IPictureDisp

    Set picture = LoadPicture(filename, 16, 16, Default)

    Set image = picture

    End Sub

  3. Mike (Rosenblum):
    About the strong-type argument to the getImage callback: I should withdraw that statement. At the time I submitted the bug report and wrote my writeup, the problem happened consistently. But, Microsoft couldn’t reproduce it and when I went back to check it out, it no longer happened. The big difference between the two instances was that I had rebooted the machine. So, I imagine the problem may resurface or affect someone else, but it is not reproducible at will.

    I have thought of writing an “add-in manager” to manage all the add-ins but I hope it doesn’t come to that. It would be very clunky and kind of defeats the purpose of having RibbonX act as the “add-in manager.”

    Michael (Scherotter): In one of the many moments of sheer frustration at not getting the images to work (this was before I figured out that one should exclude the file extension) I tried something akin to what you suggest. I didn’t expect it to work. And, it didn’t. The only way for it to actually work would be to have the OS (or however LoadPicture is implemented) be knowledgable about the 2007 file format and be willing to extract the indicated file from within the zip file. Of course, it is not rocket science and should be eminently doable since the newer versions of Windows already work with zip files at a basic level.

  4. With respect to the getImage callback function declaration, this works for me using VB6:

    Public Function RibbonGetImage(Ctrl As IRibbonControl) ‘ As IPictureDisp

    Select Case Ctrl.id

    Case “adHelpGallery”
    Set RibbonGetImage = LoadResPicture(img_adHelpGallery, vbResBitmap)

    Case “adRowsMenu”
    Set RibbonGetImage = LoadResPicture(img_adRowsGallery, vbResBitmap)

    Case “adColumnsMenu”
    Set RibbonGetImage = LoadResPicture(img_adColumnsGallery, vbResBitmap)

    Case “adCellsMenu”
    Set RibbonGetImage = LoadResPicture(img_adCellsGallery, vbResBitmap)

    End Select

    End Function

Leave a Reply

Your email address will not be published. Required fields are marked *