Ribbon on the Fly, or the Zip

Bob Phillips — Tue, 22 Feb 2011 16:10:00 GMT

I recently needed to build an Excel addin that supported a variable number of options, with buttons to invoke the options. That in itself is not unusual, but I also wanted the numbers to be driven by the user; which always complicates matters.

The definition of the buttons is simple, even allowing for the fact that it will be maintained by users. I could use a separate configuration workbook, an INI file, or even XML. As I am looking to make this a user driven facility, within an overall Excel framework, an Excel workbook seems the best option.

For the addin, in Excel 2003, we have a simple solution, namely read the workbook and build a menu of options dynamically on startup. As seems to be the norm, it is not so simple for Excel 2007 and 2010. I could just build commandbars as for Excel 2003 and let them show in the Addins tab, but this is a very poor solution in my view. I showed in a previous post last year how we can manage addins in both Excel 2003 and later versions, and support menus or the ribbon, and it was my intention to use this technique here. This does not cater for the variable options in the ribbon however.

There couple of obvious ways to manage the variable options in the ribbon. One would be to use a dynamic menu control, which can be dynamically populated when the menu is invoked. This is a nice option for lists, such as file lists, but I don’t like it as the only option in my ribbons, it does not seem to fit the ribbon paradigm. Another way would be to define a fixed number of buttons, make the visibility of the buttons a dynamic setting, and use the values in the configuration workbook to determine which are made not visible. Again, this is not an ideal solution in my view, it is almost guaranteed that not enough will be defined.

As I mentioned above, I already have a technique for supporting menus and the ribbon in my addins, and this consists of an Excel 2003 addin which is the primary component that checks the version, builds commandbar menus if the Excel version is less than 12 (Excel 2003 or earlier), otherwise it opens a ribbon wrapper addin. All of the grunt work is done in the Excel 2003 addin, and these procedures are called from the ribbon wrapper addin. The key here for Excel 2007 and on is that the ribbon addin is not initially opened. As Excel 2007 is a zip file with various components, including a CustomUI XML component, it is possible to open this zip file and overwrite the CustomUI XML. So, before I need to open the ribbon addin, I can dynamically update the XML, and I have a fully dynamic ribbon.

Of course, there is still the issue of calling the correct procedure regardless of whether the addin is loaded in Excel 2003 or a ribbon enabled Excel. The issue arise in the ribbon wrapper, the button actions need to call procedures in the Excel 2003 addin, and in my technique I do this using Application.Run calls across projects. Because all of the code in my required addin will perform the same action, just on differing files which are defined in the configuration file, this works fine, but the configuration file could always be extended to provide the name of the project and procedure to be called.

So much or the theory, just need to get on and create it now.

Deploy Me Simple

Bob Phillips — Sat, 27 Mar 2010 12:50:00 GMT

Excel 2003 Addins In Excel 2007

Excel 2007 and 2010 is so different in concept to Excel 2003 and before that there are many new challenges in providing solutions that can be deployed in either version.

One challenge that I have been faced with is creating addins that may be used in Excel 2007 or earlier versions. My addins would usually have a menu and/or a toolbar to provide access to the functionality within the addin. In previous versions of Excel, this has been an elegant solution as most people are familiar with the menus and are easily able to find the new functions in the Excel 2003 UI.

As with many things, Excel 2007 changed all of that. Excel 2007 supports the old commandbar model, but in a particularly inelegant way. The menus and toolbars that an addin might create are dropped onto the Add-Ins tab. Figure 1 shows my Excel 2007, with two addins installed, NameManager¹ and Snagit²

Figure 1 - Addins Tab

Figure 2 - NameManager Menu

As I said, this is not an elegant solution, far better to adopt the Ribbon paradigm and either add a ribbon tab, or add a group to an existing tab.

The problem here is that whilst Excel 2007 can handle commandbars, Excel 2003 doesn’t understand the ribbon. The obvious solution is to have two versions of your addin, one that is Excel 203 compliant, one for Excel 2007. This is far from ideal, having to maintain two versions where 90+% of the code will be the same is a maintenance nightmare. Another alternative is to put all of the common code into a DLL, which would still be multiple files and might entail a large amount of work re-writing the code to manage an Excel instance.

The solution that I am proposing here is to have two addins, but two very different addins.

Overall Design

I will be using one of my addins in the discussion that follows. This addin, called ‘SP Builder’³, is a small addin with a simple menu that will usefully demonstrate this technique without confusing the matter with too much detail. The 2003 menu for ‘SP Builder’ is shown in Figure 3

Figure 3 – SP Builder Menu

Figure 4 shows the ribbon version of ‘SP Builder’. As you can see, I have added a three button group to the Developer tab in the ribbon.

Figure 4 - SP Builder Ribbon

The functionality of each addin is as follows:-

Excel 2003 – functionally unchanged, apart from modifying the workbook open code to test the Excel version, if it is 2003 or lower the commandbars are loaded, if not the Excel 2007 workbook is opened. The workbook close code deletes the commandbars or closes the Excel 2007 addin
Excel 2007 – a basic addin that has ribbon XML. The callback code invokes the actual procedures in the Excel 2003 addin. When this addin loads, the ribbon is automatically built, and, because the Excel 2003 addin checks the version, the coomandbars are not loaded so we have nothing in the Add-Ins tab.

Excel 2003 Addin

As mentioned above, the only changes to the Excel 2003 addin are the open and close routines.

The workbook open checks the current Excel version and either creates the commandbars, or loads the Excel 2007 workbook, depending upon the version, as shown in Figure 5.

     If Val(Application.Version) < 12 Then

	                If Not mhCommandBars.CreateMenu(False) Then Err.Raise appErrorGeneric

	            Else

	                Workbooks.Open ThisWorkbook.Path & Application.PathSeparator & WB_RIBBON

	            End If

Figure 5 – Addin Initialisation

Similarly, the workbook close changes are shown in Figure 6.

     If Val(Application.Version)< 12 Then

	   	            Call mhCommandBars.DeleteMenu

	   	        Else

 	   	            Workbooks(WB_RIBBON).Close SaveChanges:=False 

	  	        End If

Figure 6 – Addin Removal

That is all that is required in the Excel 2003 addin, as you can see this adds no maintenance overhead.

Excel 2007 Addin

The Excel 2007 addin is completely new, but again there is nothing difficult or overly complex in this code. It is basically a wrapper to present the ribbon to the user, it has no real functional purpose in itself.

First we will look at the XML required to define the ribbon changes. Figure 7 shows the XML.

The XML here is very straight-forward, a single group with three buttons. It is likely that your addin will have a richer interface which will require more XML but that is not the objective of this article. As you can see, the group is added to the Developer tab. This is because I see the SP Builder addin as a development tool, but your addin could be in any tab, or even add another tab to the ribbon. One other thing to note is that all of the buttons call the same callback routine, this is explained later.

<customUI xmlns=http://schemas.microsoft.com/office/2006/01/customui


		          onLoad="rxspRibbonOnLoad">
  <ribbon startFromScratch="false">
    <tabs>
      <tab idMso="TabDeveloper">
        <group id="grpSPBuilder" label="SP Builder">
 
          <button id="btnSPBuilder" 
                  label="Build SPs" 
                  imageMso="ControlsGallery" 
                  size="large"
                  onAction="rxspOnAction" />
 
          <button id="btnSPList" 
                  label="List SPs"
                  imageMso="ConditionalFormattingHighlightDuplicateValues"
                  size="large" 
                  onAction="rxspOnAction" />
 
          <button id="btnSPAbout" 
                  label="About..."
                  imageMso="Info"
                  size="large"
                  onAction="rxspOnAction" />
        </group>
      </tab>
   </tabs>
  </ribbon>
</customUI>

Figure 7 – Ribbon XML

In my Excel 2007 ribbon handling code, I always create a set of constants for the ids of each of the controls, as shown in Figure 8, which are then used in the callback code.

Option Explicit

			Global Const CONTROL_ID_BUILD As String = "btnSPBuilder"

			Global Const CONTROL_ID_LIST As String = "btnSPList"

			Global Const CONTROL_ID_ABOUT As String = "btnSPAbout"

Figure 8 – Ribbon Constants

The final piece of this jigsaw is the ribbon callback code, Figure 9. As said earlier, I use a single callback and test the control id to determine the action to take. The key aspect of this code is that the action for each of the buttons is to run the ‘real’ procedure in the Excel 2003 addin, which is achieved with Application.Run⁴.

Option Explicit

Global rxspIRibbon As IRibbonUI

			Public Sub rxspOnAction(control As IRibbonControl)

			    Select Case control.ID

			        Case CONTROL_ID_BUILD:  Application.Run "'SP Builder.xla'!SPBuild"

			        Case CONTROL_ID_LIST:   Application.Run "'SP Builder.xla'!SPList"

			        Case CONTROL_ID_ABOUT:  Application.Run "'SP Builder.xla'!AboutSPBuilder"5

			    End Select

			End Sub

			Public Sub rxspRibbonOnLoad(ribbon As IRibbonUI)

			    Set rxspIRibbon = ribbon

			End Sub

Figure 9 - Ribbon Callback Code

Summary

This provides a simple, and elegant, way to manage addins that are required to be deployed in Excel 2007 or earlier versions.

By using a ribbon wrapper to deliver the ribbon changes, we are keeping the code to a minimum, and it is not introducing a maintenance overhead.

1There is a 2007 version of NameManager which I believe has a ribbon, but I have not installed it

2 Interestingly, Snagit has not added to the ribbon although the product has adopted the Ribbon UI!

3 Although t is not material to this discussion, ‘SP Builder’ is a small addin that simplifies building stored procedures and loading them into a database

4 Although you cannot pass parameters directly in a callback, this technique also allows passing a paremeter to the 2003 procedure. This could be useful to let the 'worker' addin know that you are running from Excel 2007 if it wanted to do something differently in that instance

5 It would be a far better practice to create global constants with the called addin and procedure details rather that repeating this in hardcoded form, similar to how I manage the control Ids