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.
I will be using one of my addins in the discussion that follows. This addin, called ‘SP Builder’3, 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
Workbooks.Open ThisWorkbook.Path & Application.PathSeparator & WB_RIBBON
Figure 5 – Addin Initialisation
Similarly, the workbook close changes are shown in Figure 6.
If Val(Application.Version)< 12 Then
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.
<group id=“grpSPBuilder“ label=“SP Builder“>
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.
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.
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
Public Sub rxspRibbonOnLoad(ribbon As IRibbonUI)
Set rxspIRibbon = ribbon
Figure 9 – Ribbon Callback Code
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.