Same function, different add-ins

Ever since Microsoft introduced the ribbon and I did my initial development work with it, an open issue has been how to handle the case where two, or more, add-ins offer the same functionality.  One scenario is when the feature is something required for the larger functionality offered by an add-in.  Here’s an example.

The TM Chart Utilities add-in offers the capability whereby for a chart series labels one can specify a range other than just the X or Y values.

The TM Chart Labels Hover add-in, developed to display a label only when the user hovers over the associated data point, incorporates, as a sub-function, if you will, the capability to specify a range as the source for a series’ data labels.  The UI and the code are the same in the two add-ins (I essentially copy the form and the supporting modules from one add-in to the other).

The problem is that with both add-ins installed the UI displays two buttons, both labeled Set Data Labels, that do the same time.  It looks clumsy, to say the least. For a sample see Figure 1.

Figure 1

What I would like is that whether one or both of the add-ins are installed, there is only one Set Data Labels button as in Figure 2.

Figure 2

The ideal solution

The ideal approach would be for the add-in to check if the particular button was already in the ribbon, and if so not show its own button.  I spend several hours searching VBA help, msdn.microsoft.com, and Google to see if I could access the ribbon.  The closest I could come was through the Commandbars object.  Unfortunately, Commandbars(“Ribbon”) returns an object that I couldn’t explore further.  The Commandbars.xxxMso methods / properties – e.g., Commandbars.GetLabelMso(“Paste”) – were not much help either since they apparently work only with built-in elements and not with custom elements.  And, there are no corresponding methods for non-Mso elements (i.e., no GetLabel or GetLabelQ).

If someone knows how to access ribbon element properties directly, please share that information.

The implemented solution

The alternative plan is to define a hierarchy.  If an add-in finds any other add-in “higher up” in the hierarchy is already installed then it will not display this particular button as part of its own UI.

Since the button is shown only conditionally, the Ribbon XML for TM Chart Labels Hover uses the getVisible attribute to decide at ribbon initialization time whether or not to show the button.

      <button id="ChartHoverDataLabels" label="Set Data Labels..."

size="large" imageMso="ChartDataLabel"

getVisible="isDataLabelsVisible"

onAction="UserDataLabels12" />

The isDataLabelsVisible subroutine provides a True/False result to indicate whether or not to show the button.  The result is based on whether or not the primary add-in TM Chart Utilities is installed.  In the subroutine AddInInstalled, the quick way to check if the add-in is installed would be AddInInstalled=Application.AddIns(AddInName).Installed.  However, it appears that one cannot index the AddIns collection with the name of the add-in.  Hence, the loop through individual add-ins.

Function AddInInstalled(ByVal AddInName As String) As Boolean

    Dim X As AddIn

    For Each X In Application.AddIns

        If X.Name = AddInName Then _

            AddInInstalled = X.Installed: Exit Function

        Next X

    End Function

Sub isDataLabelsVisible(control As IRibbonControl, ByRef returnedVal)

    returnedVal = Not AddInInstalled("TM Chart Utilities (Ribbon UI).xlam")

    End Sub

Additional Considerations

There are two issues I have thought of.

1) Because of the establishment of a hierarchy, there will be additional maintenance overhead.  If another add-in were to provide the same capability, it would have to check if either of TM Chart Utilities and TM Chart Labels Hover was installed.  This problem might be alleviated with the IsOpen property of an add-in that was introduced with Office 2010.  Obviously, it is not usable if one wants to provide backwards capability to Office 2007.

2) The add-in has to adjust the UI when the user installs or uninstalls another add-in because if might be an add-in within the UI button’s hierarchy.  For example, consider the scenario where both TM Chart Utilities and TM Chart Labels Hover are installed.  The visible Set Data Labels button belongs to TM Chart Utilities.  Now, suppose the user uninstalls TM Chart Utilities.  This will cause the Set Data Labels button to disappear.  The TM Chart Labels Hover add-in should now make its own button visible.  To accomplish this, the add-in must monitor the WorkbookAddInInstall and WorkbookAddinUninstall events and invalidate the ribbon.  This will cause a ribbon rebuild and the add-in will now display its own Set Data Labels button.