Hello! and a report of an initial UI-specific experience porting an Excel add-in to 2007

First of all I’d like to thank Dick for setting me up as an author. I’d told him my posts would be to introduce material on my website, but that’s not the category in which this falls. So, I lied. Sue me. {grin}

The last few days have been brutally hectic and I needed a break doing something “different.” Of course, being a geek and all I figured a productive change of pace would be to port one of my add-ins to Excel 2007. Here’s the report.

Background: The add-in, Plot Manager, is relatively small and very stable with a minimalistic UI. The last substantative change was…well, not since I wrote the add-in back in 1997-1998. The last bug fix was in 2002. The last Excel UI related enhancement was in 2004, when I incorporated my then state-of-the-art menuing system into the add-in. For those who are interested in the add-in it’s at http://www.tushar-mehta.com/excel/software/plot_manager/index.html

So, this, Plot Manager, seemed like the ideal candidate for a port with one caveat. It’s claim to fame is charting the result of a formula by varying one variable. And, of course, the charting module in 2007 has undergone a major overhaul and is still far from perfect. So, there was some concern on my part that I would have to muck around with the charting stuff rather than be able to focus on the UI.

Another reason for this test was this: All of the literature I can find on the subject of 2007 refers to VBA as a “hobbyist” tool and as good for only document-specific add-ins. This is patently untrue since one can create an application-specific add-in (e.g., a XLA file in Excel or a PPA file in PowerPoint). So, I was curious if Microsoft had de-implemented application add-ins as an incentive for developers to migrate to .Net and VSTO. Obviously, I hoped to find that that was not the case.

First changes: As a first pass, I installed the add-in in 2007 and used it as-is. Accessed through the automatically added AddIns tab it worked fine with one minor problem. The chart’s title was wrong. But, it was reassuring that the rest of it worked fine. Of course, I wasn’t (and am not) happy that the charting code will not port over unchanged but, in this specific case, the solution was a minor fix that was retrofittable to 2003.

The old menuing system relied on a table in an Excel worksheet. Since I wanted to do this in a way I had a better understanding of what was going on, I decided to skip the various tools available and do everything “by hand.” I’ve had Frank Rice’s MSDN articles including http://msdn2.microsoft.com/en-us/library/ms406046.aspx with me for several weeks and this was the opportune time to read ’em. :)

Something that worked to my benefit was that I’ve always segregated my menu items from Excel’s menus using a TM menu to the right of the Worksheet (or, if appropriate, the Chart) commandbar. So, that gave me the natural choice for a RibbonX Tab. I created a group called ‘Graph’ and the original caption ‘Plot Manager…’ became the caption for the button. The resulting XML was

<customUI xmlns=”http://schemas.microsoft.com/office/2006/01/customui” > 
   <ribbon startFromScratch=”false” > 
         <tab id=”TMTab” label=”TM”> 
            <group id=”Graph” label=”Graph” > 
               <button id=”Plot” label=”Plot Manager…” 
                  size=”large” onAction=”Plot_Manager” />
            </group > 

Porting over the pre-2007 file: That was easy, kinda. I copied the source file over to the machine that has the Office 2007 Preview (or whatever Beta 2 is officially called) and opened the XLS file in Excel 2007. I had already set the Office setting to disable macros with notification but in this case, Excel popped up a warning about the file being signed but with an untrusted certificate. The only choice was to disable macros. So, I did that, switched to the VBE, replaced one blank line with 2, switched back to Excel and saved the file as a XLSM file. Excel warned me that a digitally signed file had been changed but it couldn’t find the certificate. My choices were to discard the certificate and save or retain the certificate and discard the changes. Obviously, I chose the former.

Adding the XML part: In Windows, I changed the XLSM file to a ZIP file by appending a .ZIP to the name. Opened it, and added the file with the XML shown above together with the other necessary changes (see the MSDN article). Saved the file, removed the .ZIP extension and opened it in Excel.

The code tweaks: Since I had left my old menuing code active, the AddIns tab showed up. Of course, the RibbonX stuff also showed up. But, clicking on the Plot Manager… button resulted in an error. The subroutine associated with the Commandbars OnAction attribute does not require an argument whereas the one associated with the RibbonX OnAction attribute does. It has to be of type IRibbonControl. Both being minor glitches, the fixes were straightforward.

The old menuing code was made conditional with

    If Val(Application.Version) >= 12 Then Exit Sub

though it would be safer in the long run to reverse the test and put the pre-12 code within the ‘Then’ part of the If.

I also added a new module that contained the 2007 subroutine

Option Explicit
Public Sub Plot_Manager_12(x As IRibbonControl)
    End Sub

Putting this in a separate module meant it will work with earlier versions of Excel.

Now, with the new entry point, Plot_Manager_12, I had to go back and change the XML to refer to it rather than Plot_Manager.

               <button id=”Plot” label=”Plot Manager…” 
                  size=”large” onAction=”Plot_Manager_12″ />

Well, that was about it. I saved the file as a XLSM as well as a XLAM, loaded the latter as an add-in and did some testing. It worked in both 2003 and 2007.

Summary: Most reassuring was that the application level add-in still exists. Of course, from what I can tell, the many dynamic changes in the RibbonX interface are not available to VBA (those that need the IRibbonExtensibility interface).

The port of the UI was no more difficult and no easier than what I had expected given that I intentionally picked an add-in with a simple and static UI together with relatively simple and highly stable function code. Essentially, all I wanted to do was translate the row in my menu table into a XML entry and still have an add-in in which the code remained backward compatible. The process above was adequate for that task.

Success in this case, while gratifying, does not address the more complex needs for other, more sophisticated, interfaces. For example, the Workbook Navigator add-in (http://www.tushar-mehta.com/excel/software/utilities/wb_nav.html) uses a custom commandbar that (a) one can position anywhere on the monitor and (b) updates the commandbar as the user opens/closes/adds/deletes workbooks/worksheets. From what I can tell, it will be impossible to port that capability over to 2007 using just RibbonX. I will have to look into whether one can have multiple custom task panes. Alternatively, by dropping support for Excel 97 I might be able to use an userform displayed modeless. But, by and large, porting a simple add-in from 2003 to 2007 wasn’t too bad.

Posted in Uncategorized

11 thoughts on “Hello! and a report of an initial UI-specific experience porting an Excel add-in to 2007

  1. Nice post, Tushar.

    Updating my PUP add-in for Excel 2007 has been in the back of my mind for the past few months. I think I’ve pretty much decided to make the next PUP version for Excel 2007 only. PUP has a fairly complicated UI and I don’t think it’s worth the effort to try to make it backward compatible.

    My main concern is that PUP has several utilities that are based on CommandBars. For example, the date picker:


    Obviously, they don’t work anymore, so I need to choose between a modeless UserForm or a task pane. My understanding is that a custom task pane can’t be done with VBA, so I guess that makes my decision easier.

  2. Great post Tushar. You have clearly described my exact feeling of uneasy relief when I tested my add-ins in E07 for the first time.

    I tell people that RibbonX is kind of like having a salad for dinner. It does the trick, but it leaves you feeling unsatisfied. I could use a Snickers right about now.

  3. John, Mike: Thanks.

    John: About the hobbyist comment. I was absolutely certain I saw that word in one of four places but cannot find a specific reference now.

    Nonetheless, one can easily infer that sense from comments like:
    “VBA is generally used by internal developers or non-developers to complete business solutions. VSTO, like COM Add-ins is for the professional developer”

    “VBA was originally introduced to support non-professional development…It was never intended to be used for highly sophisticated professional solutions, but has been used in that role for lack of a good alternative. Visual Studio Tools for Office is positioned at the other end of the spectrum. It is a professional development tool…”

    “As much as we would like to get .NET penetration for the ad-hoc kind of Office development the tools are not there yet. We do recognize that and we do not pitch to hobbyist developers yet – (put a button there, write a macro here). Instead VSTO targets professional developers.”

    The other thing that stands out, and I can find only one reference to this is the reference to VBA as good for “document-specific solutions.”

    “…provided document-based solutions where the automation was handled in-process by using Visual Basic for Applications (VBA) embedded in the document…

    “Deployment is really a problem only with document-based solutions. Most documents are useless unless users can modify them, but changing documents opens the possibility of changing the VBA…You can rapidly end up with multiple versions of the solution, and tracking them becomes a nightmare, VBA doesn’t lend itself to source control, and auditing and regulatory compliance become all but impossible.”

    The above was co-authored by Andrew Whitechapel, Program Manager Technical Lead for the VSTO team at Microsoft at the time of the article.

    I imagine one can find more comments in the same vein by searching blogs.msdn.com.

  4. Run down VBA, it’s not quite the same story MS where selling back in 1997! Oh and anyone that uses VBA is clearly just a wanna be, we all know that proper programers use VSTO, so get your wallets out! What do you mean you dont need it, you wont be saying that when we make it impossible for you to do any development with out next version of office…

  5. John, Tushar,

    While it is correct to say that in Excel 2007, custom task panes can’t be created directly with VBA, I have written a VB6 COM Addin that can create custom task panes which host normal (modeless) VBA userforms. That gives us the choice to use modeless forms in prior versions and custom task panes in 2007 (if my COM Addin is installed).

  6. Tushar –

    Nice post. You’ve helped to alleviate concerns about forwards compatibility between existing solutions and the strange and wonderful Office 2007 user interface.

  7. Jon: Thanks.

    The new UI is not all that different from the existing one. It’s just implemented in a totally different way. It has some positives (and, no, being prettier doesn’t count). But, in its current incarnation it is incomprehensibly and arrogantly inflexible. It’s hard to compare it with anything but the Model T. To paraphrase the legendary quote, “You can have it in any color you want as long as it is black.”

    One thing that I should have mentioned in my initial report but forgot and then figured I’d wait until you posted a reply. {grin}

    The Plot Manager userform uses, other than the usual buttons, only the RefEdit control (6 of them) to solicit user input. And, it works just fine in 2007. {whew!}

  8. “The new UI is not all that different from the existing one. It’s just implemented in a totally different way.”

    This is what I was referring to. The UI looks fine, and almost works fine, except the wrong tab is always displayed. But you’re dead on when you call it “incomprehensibly and arrogantly inflexible.”

    I meant to suggest you drop 97 support altogether so you can use your modeless dialogs, although that would risk your amazing string of bugless RefEdit usage.

  9. Great post Tushar.
    Just a couple of things from my experience.
    Anyone copying and pasting the xml code will get the wrong sort of quotes. You need ” not ” (doh!).
    I just can’t see how the same add-in could work on Excel 2003 and Excel 2007, since the latter is necessarily a compressed xlm file (much smaller), and I don’t thing XL03 knows what to do with that.
    Frank Rice prefaces his tab ID with ‘:’ – you didn’t, I didn’t, I don’t know what his is for.
    Also, make sure you use Window’s own zip program, not a third party one, because you need to retain the directory structure (thats why Frank is particular about copying things to and from the desktop).
    I found I had to rename myfile.xlsm to myfile.zip in dos, because the zip bit has to go on the end.
    You can sort of check if your xml is ok by opening the xml file (double click ) in Internet Explorer.
    I think we are going to have fun and games with all this!

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.