XML Is Such A Pain

Published Tue, Aug 18 2009 12:14 | Bob Phillips

Normal 0 false false false

Creating a custom ribbon tab in Excel 2007 is a well known technique by now, and relatively easy to achieve.  I will not go into the details here, there are many examples scattered across the web, I would suggest Ron de Bruin’s pages at http://www.rondebruin.nl/ribbon.htm.

The biggest pain for me with custom ribbon code is the XML. As an example, to create a custom ribbon tab such as this

requires the following XML code

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="rxDemoRibbonOnLoad">

    <ribbon startFromScratch="false">

        <tabs>
            <tab id="tabDemo" getLabel="Ribbon Demo" insertAfterMso="TabHome">

                <group id="grpDemoGroup1" getLabel="Formatting Group">

                    <button id="btnDemoButton1" 
                        label="Currency" 
                        image="DollarSign" 
                        imageSize="Large" 
                        screentip="Button 1" 
                        superTip="Format activecell as currency"
                        keytip="" 
                        onAction="procButton1" />

                    <button id="btnDemoButton2" 
                        label="Percentage" 
                        image="PercentSign" 
                        imageSize="Large" 
                        screentip="Button 2"
                        supertip="Format activecell as percent" 
                        keytip="" 
                        onAction="procButton2" />
                </group>

                <group id="Text Group" getLabel="rxDemoGetLabel">

                    <button id="btnDemoButton3" 
                        label="Text Wrap" 
                        getImage="WrapText" 
                        getSize="Large" 
                        getScreentip="Button 3" 
                        getSupertip="Format activecell with text wrap"
                        getKeytip="" 
                        onAction=" procButton " />
                </group>
            </tab>
        </tabs>
    </ribbon>
</customUI>

I have used all of the attributes in this example for the buttons, as I prefer to be explicit, not leave to defaults. 

So far, this piece of XML is not too bad. But I find the maintenance of this XML extremely tedious, adding more controls and/or changing any of the attributes of these controls can be long winded and extremely error prone. The tools for creating and maintaining the XML are very limited, the best is the CustomUI Editor, but this suffers from having no recent used file list, and has an irritating habit of moving the changed line to the bottom of the window (whoever thought that was a good idea?). Maintaining the XML involves switching between the CustomUI Editor and Excel 2007, and you have to close down the Excel file in between. 

Because of this, I have adopted the practice of not putting any of the values within the XML, but rather I use callback code to set the values. As an example, instead a line such as 

label="Text Wrap"

I would setup the XML as follows 

getLabel="rxDemoGetLabel"

and within my Excel file I would have a procedure that loads that label, such as

Public Function rxDemoGetLabel(control As IRibbonControl, ByRef returnedVal)

    Select Case control.Id
        Case "tabDemo":                    returnedVal = "Ribbon Demo"
    End Select
End Function

I do this for all labels, and the other attributes Image, ImageSize and so on (onAction has to be a callback of course). Using this technique, the XML code now looks like

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="rxDemoRibbonOnLoad">

    <ribbon startFromScratch="false">

        <tabs>
            <tab id="tabDemo" getLabel="rxDemoGetLabel" insertAfterMso="TabHome">

                <group id="grpDemoGroup1" getLabel=" rxDemoGetLabel ">

                    <button id="btnDemoButton1" 
                        getLabel="rxDemoGetLabel" 
                        getImage="rxDemoGetImage" 
                        getSize="rxDemoGetImageSize" 
                        getScreentip="rxDemoGetScreenTip" 
                        getKeytip="rxDemoGetKeyTip" 
                        onAction="rxDemoGetAction" />

                    <button id="btnDemoButton2" 
                        getLabel="rxDemoGetLabel" 
                        getImage="rxDemoGetImage" 
                        getSize="rxDemoGetImageSize" 
                        getScreentip="rxDemoGetScreenTip" 
                        getKeytip="rxDemoGetKeyTip" 
                        onAction="rxDemoGetAction" />
                </group>

                <group id="grpDemoGroup2" getLabel="rxDemoGetLabel">

                    <button id="btnDemoButton3" 
                        getLabel="rxDemoGetLabel" 
                        getImage="rxDemoGetImage" 
                        getSize="rxDemoGetImageSize" 
                        getScreentip="rxDemoGetScreenTip" 
                        getKeytip="rxDemoGetKeyTip" 
                        onAction="rxDemoGetAction" />
                </group>
            </tab>
        </tabs>
    </ribbon>
</customUI>

This means that I need to change the XML code far less frequently, if I wish to change a label it is all done in code, and so on. Adding a new button or group requires adding to the XML, but the additional code would be very similar to the existing code, just the control type and its id need specific values, so the maintenance is very simple.

This does mean that we need code to set the attribute values. In the rxDemoGetLabel example above, the tab’s label is assigned. As we have more controls with more labels assigned, we need more code to manage them, but this is simple extra case statements. For example

Public Function rxDemoGetLabel(control As IRibbonControl, ByRef returnedVal)
    Select Case control.Id
        Case "tabDemo":                    returnedVal = "Ribbon Demo"
        Case "grpDemoGroup1":      returnedVal = "Formatting Group"
        Case "btnDemoButton1":      returnedVal = "Currency"
        Case "btnDemoButton2":      returnedVal = "Percentage"
        Case "grpDemoGroup2":      returnedVal = "Text Group"
        Case "btnDemoButton3":      returnedVal = "Text Wrap"
    End Select
End Function

As we can see, the code is very simple, very maintainable. The other callbacks would be very similar.

One other thing that I do is to create a set of global constants for all of the values, again to make maintenance simpler. For the labels I would have the following set of constants

 

Global Const LABEL_DEMO As String = "Ribbon Demo"
Global Const LABEL_GROUP1_GROUP1 As String = "Formatting Group"
Global Const LABEL_GROUP1_BUTTON1 As String = "Currency"
Global Const LABEL_GROUP1_BUTTON2 As String = "Percentage"
Global Const LABEL_GROUP2_GROUP2 As String = "Text Group"
Global Const LABEL_GROUP2_BUTTON3 As String = "Text Wrap"

And the callback code would be

Public Function rxDemoGetLabel(control As IRibbonControl, ByRef returnedVal)
    Select Case control.Id
        Case "tabDemo":                    returnedVal = LABEL_DEMO
        Case "grpDemoGroup1":      returnedVal = LABEL_GROUP1_GROUP1
        Case "btnDemoButton1":      returnedVal = LABEL_GROUP1_BUTTON1
        Case "btnDemoButton2":      returnedVal = LABEL_GROUP1_BUTTON2
        Case "grpDemoGroup2":      returnedVal = LABEL_GROUP2_GROUP2
        Case "btnDemoButton3":      returnedVal = LABEL_GROUP2_BUTTON3
    End Select
End Function

I find this a lot easier to setup, and much simpler to maintain. It does mean that I have to invalidate the ribbon so as to refresh the attributes, but this is done on opening the file, and should not be noticeable. For info, this is the code I add to invalidate the ribbon

Public mgrxDemoIRibbonUI As IRibbonUI

Public Function rxDemoRibbonOnLoad(ribbon As IRibbonUI)
    Set mgrxDemoIRibbonUI = ribbon
    Call RibbonSetup
End Function

Public Function RibbonSetup()
    mgrxDemoIRibbonUI.Invalidate
End Function

The full code for the constants is shown below

Option Explicit

Global Const PROC_GROUP1_BUTTON1 As String = "procButton1"
Global Const PROC_GROUP1_BUTTON2 As String = "procButton2"
Global Const PROC_GROUP2_BUTTON3 As String = "procButton3"

Global Const LABEL_DEMO As String = "Ribbon Demo"
Global Const LABEL_GROUP1_GROUP1 As String = "Formatting Group"
Global Const LABEL_GROUP1_BUTTON1 As String = "Currency"
Global Const LABEL_GROUP1_BUTTON2 As String = "Percentage"
Global Const LABEL_GROUP2_GROUP2 As String = "Text Group"
Global Const LABEL_GROUP2_BUTTON3 As String = "Text Wrap"

Global Const IMAGE_GROUP1_GROUP1 As String = ""
Global Const IMAGE_GROUP1_BUTTON1 As String = "DollarSign"
Global Const IMAGE_GROUP1_BUTTON2 As String = "PercentSign"
Global Const IMAGE_GROUP2_GROUP2 As String = ""
Global Const IMAGE_GROUP2_BUTTON3 As String = "WrapText"

Global Const IMAGESIZE_GROUP1_GROUP1 As String = "0"
Global Const IMAGESIZE_GROUP1_BUTTON1 As String = "1"
Global Const IMAGESIZE_GROUP1_BUTTON2 As String = "1"
Global Const IMAGESIZE_GROUP2_GROUP2 As String = "0"
Global Const IMAGESIZE_GROUP2_BUTTON3 As String = "1"

Global Const SCREENTIP_GROUP1_GROUP1 As String = ""
Global Const SCREENTIP_GROUP1_BUTTON1 As String = "Button1 "
Global Const SCREENTIP_GROUP1_BUTTON2 As String = "Button 2"
Global Const SCREENTIP_GROUP2_GROUP2 As String = ""
Global Const SCREENTIP_GROUP2_BUTTON3 As String = "Button 3"

Global Const SUPERTIP_GROUP1_GROUP1 As String = ""
Global Const SUPERTIP_GROUP1_BUTTON1 As String = ""
Global Const SUPERTIP_GROUP1_BUTTON2 As String = ""
Global Const SUPERTIP_GROUP2_GROUP2 As String = ""
Global Const SUPERTIP_GROUP2_BUTTON3 As String = ""

Global Const KEYTIP_GROUP1_GROUP1 As String = ""
Global Const KEYTIP_GROUP1_BUTTON1 As String = ""
Global Const KEYTIP_GROUP1_BUTTON2 As String = ""
Global Const KEYTIP_GROUP2_GROUP2 As String = ""
Global Const KEYTIP_GROUP2_BUTTON3 As String = ""

and for the callback code

Option Explicit
Option Private Module

'-----------------------------------------------------------------
' Publicly exposed callbacks
'-----------------------------------------------------------------
' No values are set in the XML apart from ids, all labels, images,
' tooltips and actions are assigned in the callback code
'-----------------------------------------------------------------

Public mgrxDemoIRibbonUI As IRibbonUI

Public Function rxDemoRibbonOnLoad(ribbon As IRibbonUI)
    Set mgrxDemoIRibbonUI = ribbon
    Call RibbonSetup
End Function

Public Function rxDemoGetAction(control As IRibbonControl)
    Select Case control.Id
        Case "btnDemoButton1":      Application.Run PROC_GROUP1_BUTTON1
        Case "btnDemoButton2":      Application.Run PROC_GROUP1_BUTTON2
        Case "btnDemoButton3":      Application.Run PROC_GROUP2_BUTTON3
    End Select
End Function

Public Function rxDemoGetLabel(control As IRibbonControl, ByRef returnedVal)
    Select Case control.Id
        Case "tabDemo":                    returnedVal = LABEL_DEMO
        Case "grpDemoGroup1":      returnedVal = LABEL_GROUP1_GROUP1
        Case "btnDemoButton1":      returnedVal = LABEL_GROUP1_BUTTON1
        Case "btnDemoButton2":      returnedVal = LABEL_GROUP1_BUTTON2
        Case "grpDemoGroup2":      returnedVal = LABEL_GROUP2_GROUP2
        Case "btnDemoButton3":      returnedVal = LABEL_GROUP2_BUTTON3
    End Select
End Function

Public Function rxDemoGetImage(control As IRibbonControl, ByRef returnedVal)
    Select Case control.Id
        Case "btnDemoButton1":      returnedVal = IMAGE_GROUP1_BUTTON1
        Case "btnDemoButton2":      returnedVal = IMAGE_GROUP1_BUTTON2
        Case "btnDemoButton3":      returnedVal = IMAGE_GROUP2_BUTTON3
    End Select
End Function

Public Function rxDemoGetImageSize(control As IRibbonControl, ByRef returnedVal)
    Select Case control.Id
        Case "btnDemoButton1":      returnedVal = IMAGESIZE_GROUP1_BUTTON1
        Case "btnDemoButton2":      returnedVal = IMAGESIZE_GROUP1_BUTTON2
        Case "btnDemoButton3":      returnedVal = IMAGESIZE_GROUP2_BUTTON3
    End Select
End Function

Public Function rxDemoGetScreentip(control As IRibbonControl, ByRef returnedVal)
    Select Case control.Id
        Case "grpDemoGroup1":      returnedVal = SCREENTIP_GROUP1_GROUP1
        Case "btnDemoButton1":      returnedVal = SCREENTIP_GROUP1_BUTTON1
        Case "btnDemoButton2":      returnedVal = SCREENTIP_GROUP1_BUTTON2
        Case "grpDemoGroup2":      returnedVal = SCREENTIP_GROUP2_GROUP2
        Case "btnDemoButton3":      returnedVal = SCREENTIP_GROUP2_BUTTON3
    End Select
End Function

Public Function rxDemoGetSupertip(control As IRibbonControl, ByRef returnedVal)
    Select Case control.Id
        Case "grpDemoGroup1":      returnedVal = SUPERTIP_GROUP1_GROUP1
        Case "btnDemoButton1":      returnedVal = SUPERTIP_GROUP1_BUTTON1
        Case "btnDemoButton2":      returnedVal = SUPERTIP_GROUP1_BUTTON2
        Case "grpDemoGroup2":      returnedVal = SUPERTIP_GROUP2_GROUP2
        Case "btnDemoButton3":      returnedVal = SUPERTIP_GROUP2_BUTTON3
    End Select
End Function

Public Function rxDemoGetKeytip(control As IRibbonControl, ByRef returnedVal)
    Select Case control.Id
        Case "grpDemoGroup1":      returnedVal = KEYTIP_GROUP1_GROUP1
        Case "btnDemoButton1":      returnedVal = KEYTIP_GROUP1_BUTTON1
        Case "btnDemoButton2":      returnedVal = KEYTIP_GROUP1_BUTTON2
        Case "grpDemoGroup2":      returnedVal = KEYTIP_GROUP2_GROUP2
        Case "btnDemoButton3":      returnedVal = KEYTIP_GROUP2_BUTTON3
    End Select
End Function

Public Function RibbonSetup()
    mgrxDemoIRibbonUI.Invalidate
End Function

I think you can see this is a simpler method than holding the attribute values in the XML, all of the working code is in one place, and it makes for easier setup and maintenance.

This overall approach requires the setting of all of the values for the global constants and the code. In a future post I will describe how I have automated this.

 

 

 

Filed under: , , , ,

Comments

# ross said on August 18, 2009 11:56 AM:

An absolutely ridiculous amount of work to get a tool bar set up, MS should hang there heads in shame!!!

# Bob Phillips said on August 19, 2009 3:06 AM:

Hi Ross,

I have presented a lot of code but it really is pretty straightforward in principle. I could have reduced it by not having all of those global constants, but I prefer that way as my coding style, and after all most of the VBA code is a result of my design decision.

You might also notice that a lot of the code is setting null values. Anyone writing the code from scratch would likely ignore those items, for instance all of the supertip and screentip code might seem superfluous. As you will see in my later post, they arise from a consequence of the automation that I use to generate the code - keep watching, I hope you like the next bit.

I do agree though that MS have not made it easy to customize the ribbon. They have said many times that they feel users should not be able to customize the user experience and then confuse other users by sending those changes in emailed workbooks and so on, so they have made it more of a 'developer' type of function. Of course, they will be beaten, we will find ways to do it regardless of MS. And I note that they have added ribbon customizations in 2010 - LOL!

In summary, it is not as versatile and not as simple as the old commandbars IMO, but with automation the burden can be reduced. No matter how much I automate these things, it doesn't make me love the ribbon anymore. I think it is a flawed concept, it takes far too much space, it is less efficient to use, it is NOT intuitive, and even 3 years on, I still find it hard to remember where everything is.

But MS hold all the aces, other products are appearing with ribbons - Ick!

# Harlan Grove said on August 20, 2009 7:37 PM:

This isn't the first place domain-specific mini languages would be useful in producing much longer, more tedious output needed.

There are examples of using awk to transform highly abbreviated specs into proper XML in comp.lang.awk. It should be possible to do similar processing with VBA, just not as tersely.

The fact that MSFT added ribbon customization in Excel/Office 2010 is an indication that either MSFT admits they got this wrong in Excel/Office 2007 or enough of their customers (read: buyers of very large numbers of licenses) have complained that MSFT believes upgrade sales require adding this feature. 'Bout time!

# Dennis Wallentin said on August 21, 2009 2:18 AM:

Bob,

First of all, nice blog with some interesting posts. I agree that MS treats the Excel development platform as the second class citizen. When creating VSTO solutions we have a Visual Designer Tool available that makes it rather easy to create a Ribbon UI solution. However, Andy Pope recently released a very promising tool at the following URL: www.andypope.info/.../ribboneditor.htm

All the very best,

Dennis

# Bob Phillips said on August 21, 2009 3:26 AM:

Harlan.

I agree, and it is nice to see MS listening, let's see how much they have listened when it comes to VSTO with Excel.

# Bob Phillips said on August 21, 2009 3:31 AM:

Hi Dennis,

Nice to see you visiting.

I agree, VSTO is a richer environment, and I think MS are at a point where they seem to have listened to the community and acknowledged that they have to make the VSTO/Excel usage and deployment far simpler. Because of this, I think that now the time is right to start using VSTO, so I have built a development box for to do this. I will be calling on your site in the coming weeks for hints :)

I know about Andy's tool, I am helping test it, but I didn't think he had made it public yet. I think it looks a great tool. It addresses the 'standard' way of building a ribbon, so I think it is an alternative to my proposal, mine is much more a table driven builder/rebuilder. As will be seen in a later post, I also generate the VBA code (and could as easily generate the VSTO code if I knew it well enough). Good to have options though.

# Dennis Wallentin said on August 21, 2009 5:07 AM:

Bób,

It was not so easy to locate Your blog but I have now a link to it from my blog.

The judgement day for VSTO will be when VSTO 4.0 (as part of VS 2010) is shipped.

Kind regards,

Dennis

# Excel Do, Dynamic Does said on August 24, 2009 7:53 AM:

Normal 0 false false false MicrosoftInternetExplorer4 In a previous post, XML Is Such A Pain , I showed

# Pulpdog75 said on September 2, 2009 2:15 PM:

Bravo, I think this is a great idea

# Bob Phillips said on September 11, 2009 5:26 AM:

@Puldog75 - watch this space, I have already done a follow-up blog, and there will be more on Autogen, I use it more and more each day.

# Excel Do, Dynamic Does said on October 2, 2009 12:43 PM:

Normal 0 false false false MicrosoftInternetExplorer4 This is going to be the first in a series of items

# Excel Do, Dynamic Does said on November 29, 2009 6:20 PM:

I have written before about hand-crafting XML so as to customise an Office 2007 ribbon at XML Is Such

# MrSpreadsheet said on February 8, 2010 7:24 AM:

Did you ever think to use an ArrayList or a Dictionary data structure to store Ribbon labels, instead of having to have so many Const's, etc.?

IMO your style is not very maintainable at all :(

# Bob Phillips said on February 11, 2010 7:10 AM:

Would you care to enlighten me as to how an array or a dictionary is more maintainable than a set of constant values. They both have to be loaded with data values, so I see no difference.

As I allueded, I also havce an automated way to generate the code, so there is zero code maintenance in practice.

# resume services said on August 30, 2011 3:04 AM:

Very unique informations you've posted.. good job done.

# Research Papers said on October 13, 2011 3:15 AM:

It’s great post for important useful

# resume writing services said on October 26, 2011 2:00 AM:

It a useful and informative post, keep it up.

# Term Papers said on October 27, 2011 12:31 AM:

Very good and informative. Nice to read the blog. Great work. keep it up.

# Resume Editors said on November 18, 2011 3:30 AM:

I put into practice of making use of the internet and I take pleasure in it much and that is the cause why I know about blogging.

# math homework solutions said on March 20, 2012 4:56 AM:

Fantastic forum I'll save it

Leave a Comment

Name:  
Website:
If you can't read this number refresh your screen
Enter the numbers above: