In the previous post, Some more observations on application level add-ins and RibbonX with Excel 2007, I had indicated I could not find a way to share RibbonX controls between multiple add-ins. In response to my bug report, I learnt that the custom namespace used with the qualified ID (idQ) property is designed for just that purpose. Several hours of trial and error fleshed out the little documentation that I did find on the subject. Here’s a report of what’s what from my perspective as someone who is essentially experimenting with the capability.

Introduction to sharing controls: Using a namespace
Using multiple namespaces: Mix-and-match
Handling conflicting attributes
Summary, limitations, untested features, and bugs
Download the sample workbooks

Introduction to sharing controls: Using a namespace

The key to sharing controls across add-ins is to create a namespace that RibbonX is aware of and subsequently put controls in it. When two add-ins use the same namespace, RibbonX knows that those controls go into the same "container," as it were.  The way one declares the namespace is a xmlns clause in the customUI statement.

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

The xmlns:x="TMNamespace" creates a namespace TMNamespace that RibbonX is aware of.  The x: part creates a reference that can be used with the idQ property later in the XML specification.  For example, to create a tab within that namespace, one would use

<tab idQ="x:TabTM" label="TM">

Similarly, to create a group in the above tab that is in the same namespace, one would use

<tab idQ="x:TabTM" label="TM">
    <group idQ="x:TMChart" label="Charting">

The complete XML to create one non-shared button in a shared group in a shared tab is given below.  If you want to follow along with this example, put the code in a workbook [1] named, say, Book1.xlsm.

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"
    xmlns:x="TMNamespace" >
<ribbon> <tabs>
<tab idQ="x:TabTM" label="TM">
    <group idQ="x:TMChart" label="Charting">
        <button id="TMButton" label="Button1" onAction="TMButton_onAction" />
        </group>
    </tab>
</tabs> </ribbon> </customUI>

Open the workbook in Excel, add the following callback procedure in a standard module.

Save the file (book1.xlsm), and also save it as an add-in (book1.xlam)

Create a similar setup in Book2.xlsm, where the XML is the same as in book1 except for the button itself.  Note that the local name x could just as easily be something else.  It’s the TMNamespace that must be common across the add-ins.

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"
    xmlns:x="TMNamespace" >
<ribbon> <tabs>
<tab idQ="x:TabTM" label="TM">
    <group idQ="x:TMChart" label="Charting">
        <button id="TMButton21" label="Button21" onAction="TMButton21_onAction" />
    </group>
</tabs> </ribbon> </customUI>


The corresponding callback procedure (in a standard module in book2.xlsm) would be

Save this file in both the XLSM source with macros format and the XLAM application-specific add-in format.

Load both add-ins and the result should look like:

And, of course, clicking either button runs the correct procedure.

 

Using multiple namespaces: Mix-and-match

Once I had implemented shared controls through one namespace, an immediate question that arose was "Well, can I have multiple namespaces with each having an otherwise identically declared container (such as a tab or a group)?  The answer is yes, though I am not yet sure what to do with this capability.  Nonetheless, a qualified control can be nested in a qualified control from the same namespace or another namespace!

 

Nesting controls in containers all from the same namespace

Before jumping into the code associated with controls and namespaces, it might help to look at the structure of how the add-ins will share controls.

Once all three add-ins are loaded, the result will look like:

and the Utilities tab will contain:

The two distinct namespaces are declared in Book2.xlsm with two xmlns attritutes. The rest of the code simply creates two tabs, each in a different namespace. Each tab contains one group from the same namespace and the group contains one button with no explicit namespace qualification.

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"
    xmlns:x="TMNamespace" xmlns:z="TMNamespace2" >
<ribbon><tabs>
<tab idQ="x:TabTMChart" label="Charts">
    <group idQ="x:TMChart" label="Charting">
        <button id="TMButton21" label="Button21" onAction="TMButton21_onAction" />
        </group>
    </tab>
<tab idQ="z:TabTMUtil" label="Utilities">
    <group idQ="z:TMChart" label="Charting">
        <button id="TMButton22" label="Button22" onAction="TMButton22_onAction" />
        </group>
    </tab>
</tabs> </ribbon> </customUI>


The XML in Book3.xlsm is similarly organized and is not shared here.

 

Nesting controls from one namespace in a container from another namespace

As it turns out, there is no mandated nesting hierarchy on controls and namespaces.  What that means is that a control from one namespace can contain a control from another namespace!  Consider a new workbook, that started life as a copy of Book2.xlsm:

Note that the x:TabTMChart tab (namespace TMNamespace) contains one control, x:TMChart, from its own namespace, and one, z:TMChart, from the other namespace. The result of loading all four add-ins looks like:

Again, I don’t know what to do with this capability but it exists.

Just for the record, there’s no reason why the various buttons have to be normal sized or without an image. Pick random icons from those used by Excel and specify a large button size to use up more real estate as in:

 

Handling conflicting attributes

From limited tests with Office 2007 Beta 2, it is the last add-in to be loaded that gets the final say.  For example, if there are two add-ins with the same tab declaration but with conflicting labels, the label specified by the last add-in to be loaded is the one that is shown.

 

Summary, limitations, untested features, and bugs

As I’d noted in one of the earlier posts, the RibbonX capabilities make it easier for one to create components in the "menu" part of the user interface.  A key to making that happen is that RibbonX takes over most of the administrative overheads associated with building a menu.  For example, one no longer needs to worry about whether a tab (menu item in the old days) already exists or not.  In the commandbar world, the developer would have had to create a menu, embed a Commandbar Popup control, and reference the commandbar embedded in that control.  With the RibbonX architecture, all that responsibility is moved into RibbonX.

Limitations: Nonetheless, before someone gets the wrong idea about what I think of RibbonX, I should clarify something.  As good as it can be, in its current implementation it has a lot of weaknesses, a lot of deficiencies.  The biggest, of course, lies not in the programmability but in the core design itself.  There’s only one Ribbon, it’s not user customizable, and it’s locked at the top of the monitor.  In addition, from a programmability viewpoint, it’s not dynamically customizable.  Yes, if one were writing a COM Add-In one could specify the RibbonX XML at run-time.  But, not only would that be a PITA (try parsing XML in your code) and COM Add-Ins come with their own set of problems.  Why am I focusing on this issue?  Even though I am essentially a rookie when it comes to RibbonX, I already hit one wall.  One of the ideas that I thought of, because RibbonX made it easy, was to implement a "control panel" (shared across all my add-ins) through which the user could decide what functions of my various add-ins would be visible at any given time.  Something along the lines of leaving the Quick Calculate add-in loaded but temporarily hiding the group associated with it as in:

and

The Quick Calculate group has the RibbonX getVisible attribute specified.  The XML for the Quick Calculate togglebutton specifies the onAction and the getPressed attributes.  The associated VBA simply decides what to show when.

But, now try doing the same where the shared group (Graph in the below snapshots) may contain multiple buttons, only one of which belongs to the add-in.  Hence, the add-in should not mess with the group’s visibility setting but only that of its own button.  But, if the group contains a single button at the moment (because all other buttons are already hidden or because this is the only add-in loaded), the result would look like:

and

Essentially, the problem is that we now have a visible group with no visible buttons in it!

Another major limitation in the current set up is that one must create the XML "by hand."  That leaves a lot to be desired and hopefully Microsoft will release a product similar to how one currently creates a userform through the GUI.  I don’t know if Trang Luu works for Microsoft since, if that is the case, it would be a matter of extending the custom UI editor, which, by the way, is very useful in its current form.

I thought of building an utility myself but in addition to the various technical issues involved, it would be something that no one would use once Microsoft came up with its own capability.

Untested features: Obviously, my testing barely scratches the surface of RibbonX.  I don’t know the answer to many questions including "what happens if we have two add-ins using the same button?"

Bugs: There are also several bugs that exist in the RibbonX related code.  I’ve shared one of them with Microsoft and will share the other one later today.  These are the following and they limit the extent to which one can test RibbonX capabilities in a multiple add-in environment.

If there are two XLSM files open that have the same namespace specified in the RibbonX XML code, only the tab(s) associated with the first workbook will be visible and that too only when that workbook is the active workbook.  Even when the 2nd workbook becomes the active workbook no Ribbon customization will be visible.  I am told this has been fixed in an internal build later than the latest I have, which is Beta 2.

Another that might be related to the previous bug is this.  If there are two application level (XLAM) add-ins loaded, they both share a RibbonX namespace, and they both have a onLoad attribute specified, the callback routine in the first add-in to be loaded is the only one called.  For example, consider the add-ins TM Calc 2007 and TM Plot 2007.  Each of them contains a onLoad=RibbonLoaded specification.  However, the RibbonLoaded callback routine of the first add-in to be loaded will be called twice (I assume once for each of the add-ins) while the procedure in the add-in loaded second is never called.  Of course, this means, none of the initialization code in the 2nd add-in can run.  In particular, it does not get a reference to the Ribbon object.  Consequently, calls to onAction or getPressed or getVisible callback routines that refer to the Ribbon object fail.

 

Download the sample workbooks

All of the five workbooks, Book1.xlsm to Book5.xlsm are in this zip file.  I don’t know if I am breaking/bending the rules for authors by making this file available for download.  If I am I’m sure Dick or Jake will tell me and we’ll see what else I can do about the zip file.

 

[1] For an introduction on how to add RibbonX XML code to an Office add-in, see Frank Rice’s document and also Using custom icons with an application-specific add-in in Excel 2007 Beta 2. At some point, once you get the hang of where the XML and the images go, simplify your life with the editor from Trang Luu mentioned above.