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