Deploy Me Simple


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, NameManager1 and Snagit2



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 Builder3, 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.Run4.


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

5 thoughts on “Deploy Me Simple”

  1. Bob,

    Nice approach, not done anything with ribbon really as yet… it only been 3 years!

    Would it be possible to have all the code in one file, check the version then choose which UI to implement.
    I guess the issue is with the xml?

    Could the XML be saved in a worksheet? or written to a file during start up/install? (once only obviously)

  2. Hi Bob,

    I like this approach.

    All of the code in Name Manager is generic (i.e. works in all versions of Excel 97 up to 2010). There are some version dependent decisions in there of course.

    When I deploy a new version of Name Manager, I have these steps to take:
    - Save as my developer xls to xla (2003 and older version)
    - save-as my xls to xlam (I do that from 2003, using the openxml file converter tool)
    - Open xlam in custom ui Editor tool and paste xml code and two icon pics.
    - Done.

    Ross:

    You could use a technique I discussed on my site to push RibbonX xml to an xlam file and load the xlam file afterwards, but I think Bob’s method is simpler.

    Article:
    http://www.jkp-ads.com/Articles/Excel2007FileFormat03.asp

  3. Ross,

    It would be nice wouldn’t it?

    As you say the problem is the XML, you cannot update that on the fly. As Jan Karel says you could update the CustomUI.xmml file in the 2007 workbook, but you can only do that to a saved copy, so I think you would still be talking about two files.

    I have deployed this a few times now and the extra files is not really an issue, especially when you have an installer. The beauty is that the 2003/2007 components are truly abstracted, maintenance is simple.

  4. Hi Jan Karel,

    I used to do something along those lines, until I came up with this.

    I see you maintain xls files too, I do that. I am currently wondering whether I should bother now that I have this technique.

    What is the openXML file converter? The only thing I can find about that is for Macs, must admit I ignore Macs.

    BTW, I write the XML to the workbook in my Ribbon Autogen tool, and Andy does with his wonderful RibbonX Visual Designer. This also made creating the xlam version simplere when I built a full 207 copy.

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>