Some may remember my earlier posts on RibbonX when I first explored the subject with Office 2007 Beta 2:
Hello! and a report of an initial UI-specific experience porting an Excel add-in to 2007
Using custom icons with an application-specific add-in in Excel 2007 Beta 2
Some more observations on application level add-ins and RibbonX with Excel 2007
Sharing RibbonX controls across application-level (XLAM) add-ins
As an aside, those posts turned out to be life savers. Visiting the RibbonX issue for the first time since the official release of Office 2007, I had forgotten most of what I had learned about RibbonX. Re-reading my posts was a vital refresher. In fact, I wish I had finished the fifth post about a Quick Calculate capability using only RibbonX since I deleted all my old beta files and now will have to relearn how I used the Gallery as an audit trail. :(
But, today’s post is on a different subject.
The problem
The proof of concept
The first solution
The generic solution
Extending to another legacy add-in
Limitations
Summary
It became apparent with the preliminary exploration of the RibbonX interface that there were two somewhat related issues that had to be addressed before I could make extensive use of RibbonX. First, how does one develop an add-in to support both 2007’s Ribbon UI and the older Commandbar UI? Second, how does one convert an existing add-in to use RibbonX (obviously, all the existing add-ins support only the Commandbar UI).
The ‘obvious’ solution was to maintain two versions of each add-in, one with XLS/XLA files for pre-2007 and one with XLSM/XLAM files for 2007+. With a lot of discipline one could ensure that the underlying functional code in the two remained the same. We all know how long that would last, don’t we? Clearly, that is not a meaningful option.
A few days ago while coding a Application.Run statement the right combination of neurons kicked in. Why not have a XLAM add-in use the Run method to call the XLA add-in’s entry point? In fact, at its core, all that the XLAM add-in would have in it would be the XML for the RibbonX interface and the Run statement!
Not surprisingly, KISS works. Or maybe it doesn’t…and someone will point out why this entire approach makes little sense. {grin}
The add-ins of immediate interest were those that have what is essentially a static UI. Addins in this category add some number of items to the menu system when they start up and remove those items when they end. They don’t change the UI on a dynamic basis. Also, each of the menu items uses the OnAction property to link to an entry point in the add-in.
Returning to my favorite test add-in for testing purposes, Plot Manager, I wrote a Plot 12.xlam
The first iteration, a proof-of-concept, included
It was never meant for production because the hardcoded filename was simply unacceptable. But, it revealed other issues. The first was that once the Run method completed Excel kept the Plot.xla file open. While a resolution might not be critical it would be nice to close files that were opened behind the customer’s back, so to say, rather than simply leave them open until Excel itself shut down. Another, though arguably less important, problem was that the Plot.xla file created its own commandbar menu items, which Excel 2007 automatically nested within the Add-Ins tab.
The first two problems had to be solved. The last one was more of an inconvenience; not to mention that it had no solution short of changing the Plot.xla file. By leveraging the Add-in’s name, a simple solution to the two “must solve” problems was:
‘Callback for customButton onAction
Sub TMPlot(control As IRibbonControl)
Static AddInFullName As String
On Error Resume Next
With Application.AddIns(“TM Plot Manager”)
If Not .Installed Then .Installed = True
AddInFullName = .FullName
End With
Application.Run “‘” & AddInFullName & “‘!Plot_Manager”
On Error GoTo 0
End Sub
and the ThisWorkbook module contained
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.AddIns(“TM Plot Manager”).Installed = False
End Sub
By installing the add-in before using it the first time around, I could now uninstall it whenever the XLAM add-in was uninstalled. The observant reader might have picked up on a subtle bug, which is that the XLAM add-in uninstalls the legacy add-in even if it did not install it. As it happens the more generic version of the code automatically fixes the problem.
While reviewing the above code, the next “obvious” step was to remove the Add-in name and the entry point from the code. If they were provided by the RibbonX control, the same callback procedure would work for all add-ins. Then, a generic Addins 12.xlam add-in could handle every legacy add-in that had a static UI! Perfect for the job of storing legacy add-in name and the code entry point was the tag attribute of the RibbonX Button control. The convention adopted separated the add-in name from the entry point with a colon. Now, the code became:
Public InstalledAddIns As New Collection
‘Callback for customButton onAction
Sub TMAddInEntry(control As IRibbonControl)
Dim AddInFullName As String
On Error Resume Next
Dim Arr
Arr = Split(control.Tag, “:”)
With Application.AddIns(Arr(LBound(Arr)))
If Not .Installed Then
.Installed = True
InstalledAddIns.Add Arr(LBound(Arr))
End If
AddInFullName = .FullName
End With
Application.Run “‘” & AddInFullName & “‘!” & Arr(LBound(Arr) + 1)
On Error GoTo 0
End Sub
The ThisWorkbook module contained:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Dim aStr As Variant
For Each aStr In InstalledAddIns
Application.AddIns(aStr).Installed = False
Next aStr
End Sub
The associated XML code for the button:
imageMso=“ChartTypeLineInsertGallery”
size=“large”
onAction=“TMAddinEntry”
tag=“TM Plot Manager:Plot_Manager” />
The resulting UI:
Extending to another legacy add-in
If the above process truly worked, porting another add-in to 2007 should require nothing more than an appropriate button entry in the XML. The TM Calculator add-in also has a ‘static’ UI. So, the following should hook it to the 2007 Ribbon UI:
image=“SSPX0156”
size=“large”
onAction=“TMAddinEntry”
tag=“TM Calculator:startCalculator” />
And, yes, that is indeed all that it took.
Limitations
One of the limitations to the above approach has already been noted. It works only with those add-ins that don’t change the UI while running.
Another issue is that the add-in still adds a commandbar button that is available through the Add-Ins tab. It’s not a big thing since using that menu item works just fine but it would be nice to have a single UI item. Removing the old commandbar entry will require retrofitting the add-in so that if it detects an application version greater than or equal to 12 (2007 or later) it doesn’t add or delete any commandbar entries.
Add-ins that dynamically adjust the menu will still continue to work through the Add-Ins tab but would need some amount of work to use RibbonX features natively.
One menuing feature that I don’t know how to duplicate is the way that a Office 2007 application shows certain tabs when a specific type of object is selected. For example, in Excel 2007 selecting a chart shows a ‘Super Tab’ called Chart Tools and three new tabs under it named Design, Layout, and Format. In 2003 an add-in could use the Chart Menu commandbar to add menu items meant for display only when a chart was selected.
I don’t know if Microsoft has documented this ‘Super Tab’ 2007 capability or made it available through the RibbonX. If not it is critically needed.
Summary
I don’t know if this approach or something similar has already been documented elsewhere. If it has hopefully someone will post a link to it.
The method documented above shows how one can easily add a RibbonX interface to pre-2007 add-ins that use what I call a static UI. A final “touch up” would be to modify the legacy add-in to not add its own commandbar interface items when the running with an application version 12 (2007) or later.
For all I know there are problems that haven’t surfaced in the day or so since I started using this approach. And, I am sure that readers who know of problems won’t hesitate to post their comments.
Tushar, I discovered that you can delete items in the Add-Ins tab via code. The old CommandBars are still recognized. For example, if your XLA add-in adds a menu named “My Menu”, you can remove it from the Excel 2007 interface with:
Application.CommandBars(“Worksheet Menu Bar”).Controls(“My Menu”).Delete
This would solve the duplicate UI item you mentioned.
Your approach will be helpful for those who don’t want to create a separate Excel 2007 version of their add-in. Personally, I think it’s worth the effort to create a new version for Excel 2007. I always find ways to improve it.
This is exactly the approach that I took to have one single code base for our dictator application. And I didn’t have to worry about the old commandbars because I was using ‘startFromScratch’, which basically hides everything for me.
I did modify the XLA in order to modify the UI by calling one single sub in the XLAM file, which looks something like
‘Invalidate the Ribbon and/or controls here depending on the newRibbonState variable
End Sub
I haven’t tried using any Contextual Tabs, but I assume they are usable right now, because you can have a getVisible callback on Tabs as well, which allows you to control when they appear. I don’t think, however, that you can give them special colors like the builtin.
And so far, I haven’t found any particular problems with this approach, but we’ve only had it “out” in production for about 2 weeks and we only have one or two customers with Office 2007.
Hi Tushar,
Nice method.
I do it like this:
– VBA decides what version we have and either runs the commandbar code (pre-2007) or does nothing
– When I develop, I develop in pre-2007 version
– Then when done I save-as to xlam.
– open older xlam with custom UI tool and copy XML
– open new xlam, paste UI XML. If needed import custom icons.
You do need to declare all RibbonX event sub arguments generic, e.g. as Object.
Pro: No extra loader xlam needed, no VBA code maintenance problem.
Con: Extra step in copying the RibbonX XML and the icons.
Jan Karel,
I’m curious… do you save your original to xla or xlam? Are you using the compatibility packs to get the 2007 versions to work with prior versions of Office, and how far back will they actually go? (I was under the impression it was 2003 only.)
I work on the xls in 2003 or XP, then save it and open the xls in 2007 and save-as xlam. Then I add the XML to the xlam.
Hi, Tushar,
Your approach is exactly what I need, however I wonder whether it will work for the following case:
I have an application saved as a pre-Excel 2007 XLT file which is a dictatorship application hiding Excel menus and creating commandbars. This application is started via automation by VB6 front loader (exe file). My question is how to save this XLT file so it will work as a template in both pre Excel 2007 versions and in Excel 2007, i.e. it will create its own copy either in old or in a new version of Excel, e.g. similar to creation of Template1.xls from Template.xlt in old Excel. Actually I do not need this file in a form of AddIn, therefore I do not need to install or uninstall it.
Will this file work in earlier versions if it is saved in Excel 2007?
Thanks in advance for your kind assistance.
Vladimir Nazarov
Vladimir –
I don’t believe there is a practical one-workbook solution that is fully compatible with both 2003 and 2007. If your Excel 2003 environment has the file conversion elements, perhaps you could get away with it, and Excel 2003 will ignore the 2007 interface part of the file. However, you have to assume that 2003 users will not have the compatibility pack installed (I don’t even have it: I open the 2007 file in 2007 and save as 2003).
The Excel 2003 workbook can contain the code you need and the 2003 UI customizations. If it detects that it is running in 2007, it skips the 2003 customization and opens the sister file with the 2007 customizations. A very clever programmer could have code in the 2003 template to build the XML interface code, save it in a new 2007 workbook, and open the workbook. But this approach has many places where you could introduce inefficiencies and errors.
Vlad –
(Pressed ‘Send’ prematurely!)
The best approach may be to have two one-workbook solutions, one for each version of Excel.
Excellent article. I have just implemented it and it works well. Thanks.