In the initial experimenting with Excel 2007, RibbonX, and application-level add-ins, I’ve run into a few issues that I thought I’d share. One is a matter of convenience. Of the other three, one is blatantly a bug that hopefully, Microsoft will fix it soon. The other two, on the other hand, I am afraid might be classifed as “by design,” especially given the incredibly paternalistic attitude the Ribbon people have demonstrated towards Microsoft’s customers. But, I guess I should withold judgement until I hear back on the bug reports I’ve filed. I sincerely hope Microsoft addresses these issues because of the potentional to significantly impact the use of existing (and new) VBA add-ins.
And, then, there’s the possibility that these may not be problems at all and simply reflect my ignorance about the product. ;-)
Before going any further one major improvement that must be applauded. Access to the Add-Ins dialog box does not require that a workbook be open!
A matter of convenience — keep the VBE open
While writing code with Excel 2007 (and I assume this applies to other Office products as well), keep the Visual Basic Editor open. Why? Because with it open each time a file with macros (XLSM) is opened, there will be one dialog box asking if macros should be enabled with just one button to click.
On the other hand, with the VBE is closed, it will be a multi-stage dance. First, a Security Alert message bar will insert itself between the ribbon and the formula bar. It will contain an option to ‘Enable Content…’ Click that to bring up the Trust In Office dialog box with “blah, blah, blah” and two options, one of which is “Enable this content.” After selecting this option, one must click the OK button.
The obvious bug
Create an application-specific add-in (XLAM extension). Load it in Excel. Quit and rerun Excel. Open the AddIns dialog box (File | Excel Options… button | Add-Ins tab | In the Manage field select Excel Add-ins and click the Go… button). The loaded add-in will be listed with a rather strange name: {filename-with-extension}{filename-w/o-extension}. For example, the add-in TM Calc 2007.xlam has a title property of TM Excel Calculator. When the add-in is originally loaded the Add-In dialog box lists TM Excel Calculator. After restarting Excel, in the AddIns dialog box, the loaded add-in will be TM Calc 2007.Xlamtm Calc 2007, while TM Excel Calculator will be unchecked. Of course, selecting the first item yields the error “Cannot find add-in… Delete from list?”
Select Yes and the RibbonX components associated with the add-in *remains* in the Ribbon. Now, sometimes using those controls continues to work even though no add-in is listed as loaded!; at other times, those controls will do nothing!
The reason for mentioning this problem is that if your code appears to do nothing it might be because of a bug in it or because of this problem!
A workbook with macros (XLSM) is really treated as a document-specific add-in
A XLSM file is truly regarded as a document-specific add-in. The associated Ribbon components are visible only while it is the active workbook! Nothing I have tried has worked so that I can access the controls while working with another workbook (including non-maximized mode with both workbooks visible, or 2 different windows with each showing one workbook). The Ribbon customization for the XLSM file is visible only when the file with the customization is active.
This, of course, makes it difficult to test an add-in by entering data in another workbook. Just as it makes it that much more difficult to easily develop a paired data template and add-in. Why is it only difficult and not impossible? Well, there’s a relatively easy workaround. Use the VBE’s Immediate window to toggle the workbook’s IsAddIn property between TRUE and FALSE as required.
Cannot share a custom tab across two application level add-ins
Two different add-ins (again, application-level XLAM types), cannot share a custom tab! Consider two add-ins with Ribbon customization code like
<!– stuff –>
</tab>
Install both as application level add-ins and they will create two individual tabs labeled TM. I have tested this with several combinations including only one of them containing the label (a variant being to load the one with the label first), and none of them containing a label. Nothing works. They have two different tabs.
Of course, if one puts them in a Microsoft created tab, for example IdMso=”TabFormulas” then there is only the one tab for all the add-ins.
This has forced me to give up on my desired method of separating all my add-ins from those of others with my own menu item (in the Commandbars world) or own tab (in the RibbonX world).
And, no, I don’t know if the same issue extends to COM Add-Ins or to combinations of COM and VBA add-ins.
Summary
A large part of the delay in publishing this note has been because I couldn’t decide how much editorializing I should include in the summary. I guess in all fairness I should withold any criticism until — if — the bug reports are closed as “works as intended” {veg}
So, I guess I’ll leave it at this. The RibbonX, like the Ribbon, has tremendous potential. Hopefully, Microsoft will realize that intentionally crippling these products and treating its customers as though they were children who cannot be trusted with fine china benefits neither it nor its customers.
A workbook with macros (XLSM) is really treated as a document-specific add-in
If that’s truly the case, MS Office designers are out of their minds. I really hope this will be fixed in the RTM, but I’m afraid that it’s by design.
Cannot share a custom tab across two application level add-ins
That’s gotta be a bug. That can’t be by design. Or maybe it’s one of those things that they don’t have time to fix.
Also, have you noticed that the Personal Macro Workbook is an XLSB binary file? That means no UI customizations from that file.
Hopefully, someone from Microsoft will see this and respond.
I just got response to one of my bug reports. Sharing a custom tab is possible through the use of the idQ property. The person responding indicated he’d try and get me a sample of how to use that property and he did note that I was using VBA. So, it would appear that at least this ‘problem’ falls under the category of “I just didn’t know how to make it work.”
I’ll keep you updated.
Another bug I noticed when using excel 2007 was after using the UCase function (I think it was UCase at least) the vba code would no longer work on an older version of excel.
Tushar,
Right now I just want to express my appreciation of Your interesting post about RibbonX. One of the things on my ‘to do’list is to explore RibbonX with COM add-ins, by using classic VB ;)
Kind regards,
Dennis
When opening a file with macros, you can skip all dialogs by using a trusted location.
A trusted location is a folder or document location, from which Excel will trust documents and allow them to open and run code without notification to the user.
More info: http://blogs.msdn.com/excel/archive/2006/08/01/672308.aspx
John –
“A workbook with macros (XLSM) is really treated as a document-specific add-in”
Ever do any programming in Word? This is pretty much how Word has worked since it acquired a VBA object model. A document has a certain context, which is limited to that document. A regular template has a context limited to documents based on that template (and there’s a whole load of stuff here, including changes to a template affect documents previously created using it, and the template doesn’t seem to have to be open for its code to run). Finally global templates line normal.dot have a context that applies to all documents, like an add-in or any workbook in Excel. This makes it easier for Word programming: you set the context, and you don’t have to keep track of which commandbars and menus need to be visible or hidden. But it makes it hard to get an Excel programmer’s head around it.
The noise from Redmond has been that Word’s model is what should have been used for all of Office, and they’re trying to impose it on Excel now. Which means it’s a feature, not a bug.
Tushar –
“I just didn’t know how to make it work.”
You mean you didn’t RTFM? What has frustrated me in all the RibbonX emanating from Redmond has been the almost total lack of examples suited to VBA, the assumption being that any self-respecting developer would move on to managed code. Yeah, VSTO and dot-net are great and all, I’ve read the press releases. But this is Office, man! Why do we need to tack on two more layers of complexity and frustration? Half my clients aren’t even up to Office 2003, so why should I get too excited?
Ever do any programming in Word?
One project. That was enough to realize that I wouldn’t be doing any more programming in Word.
I wonder why the Excel designers didn’t go all the way, and make it impossible to execute a macro that’s in another workbook? Right now, you can still press a shortcut key assigned to a macro, and the macro will run, regardless of which workbook is active. And, of course, you can add a button to a sheet and assign any macro you want. So the only thing that’s changed is UI modifications are visible only when that particular workbook is active.
I guess it’s not a big deal. I’ll eventually get my head around it.
Awwwww… I need to have a Word application, dealing with multiple documents, up and running on wednesday, and I know close to nothing about its object model… Whine.
(And ditto, thanks Tushar for another great article!)
I dont get it, who is ever going to use VSTO in an office daily environement….
I mean most excel programmers are analysts etc… who happen to know stuff about excel and often end up power users on reporting and data management, they will never ever get visual studio installed, and that was all the beauty of VBA, being packed with the regular program they do already use.
I thought that microsoft cared about users, but obvisouly it seems that office 2007 is geared toward addin resellers ……
I can already plan a long life for office 2003 at that point
Hi everyone,
Tushar, I’m glad you got that bug reported. I acutally posted about it back in June at JMT (reply #6) http://www.puremis.net/excel/cgi-bin/yabb/YaBB.pl?num=1150860446
My issue was that I couldn’t find a way to actually report it. I still haven’t received any way of signing into a Beta account to give feedback. It was, quite simply, so difficult to report a bug that I just gave up.
Interesting stuff on the rest too.
>But this is Office, man! Why do we need to tack on two more layers of complexity and frustration?
No kidding. That is truly the big question, isn’t it?
>But this is Office, man! Why do we need to tack on two more layers of complexity and frustration?
Oh yeah, it’s MICROSOFT Office, that’s why.
As long as you’re not bitter, Jon! ;)
LOL!
One of the big issues with this (“But this is Office, man…”), is not only the extra (unnessary?) layering, but is the cost and developer scope. How many office developers will have vsto, OR vs2005? Not more that 20% i’d guess. So MS might be forcing a large number of it users “in to the hands” of Sharp dev, or somthing like that…maybe…