XML Is Such A Pain

































































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.


 


 



 

23 thoughts on “XML Is Such A Pain”

  1. 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 – [+o(]

  2. 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!

  3. 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: http://www.andypope.info/vba/ribboneditor.htm

    All the very best,
    Dennis

  4. 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.

  5. 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.

  6. 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

  7. @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.

  8. 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 :(

  9. 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.

Leave a Reply

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


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>