A lull in the action this week gives me time to talk about the new features in Excel 2010 that strike me as real improvements.
Today, we'll take a look at Customizing the Ribbon in Excel 2010.
In Excel 2007, Microsoft introduced the Ribbon which, for the most part, could not be altered in any non-programmatic way. Developers could use XML and RibbonX to customize the Ribbon, but standard Excellers were out of luck. This left lots of power users out in the cold, without a practical way to add customizations to their Excel Interface (insert violins here).
Excel 2010 changes all that…well…at least some of it.
In Excel 2010, we can now find the Customize Ribbon function under Excel Options.
The broad summary of this function is that it lets us do two basic things: Customize the native Ribbon elements (tabs and groups that come with Excel), and add your own Custom Tabs/Groups.
Customizing Native Ribbon Elements
Excel 2010 allows you to hide/remove the native tabs/groups you don't want shown.
In this screenshot, you can see that I removed the check from each main tab except for the Home tab. This effectively hides everything but the home tab.
You can also add your own custom group to a native tab.
Here, you can see that I've added a new group called Mike's New Stuff to the native Developer tab.
This is what it looks like on the Ribbon.
Adding Your own Custom Ribbon Elements
With Excel 2010, we now have the ability to add our own custom tabs and groups.
Here, you can see that I've created a new DataPig tab. This new tab automatically comes with a new group which I've renamed My Macros.
Now I can turn my newly created 'My Macros' group into a place where I can run my macros directly from the ribbon.
Here, you can see that I can choose to add macros to my custom group.
Once my commands are added, I can change their icons. The icon selection Microsoft gives us is bit limited, but it'll do.
Keep in mind that we're not limited to only adding Macros to custom groups. We can also utilize existing commands.
Here, you can see that I've added a new group and renamed it 'Useful Functions'. This will be a place I can go to easily call up functions that are normally hidden from the Ribbon.
I just chose to see the 'Commands Not in the Ribbon' category and started adding a few of the existing commands to my newly created 'Useful Functions' group.
Here is my final work of art. This is fairly exciting considering the fact that in Excel 2007, you had to resort to XML and RibbonX to do something as simple as create a new custom tab with some buttons on it.
In Excel 2010, your own custom tab is just a few clicks away.
The Caveats
Like any new functionality, this new "Customizing your Ribbon" business comes with a couple caveats.
First, it looks like Excel 2010 will only allow you to customize native Ribbon elements at the group or tab level.
That is to say, you can only remove existing tabs/groups and add your own groups.
You can't add new commands to existing tabs or groups. This basically means you can't rearrange the commands on the Ribbon.
You can't, for example, place the PivotTable command into to the Data Tools group under the Data tab.
If you try, you get this polite admonition.
Second, any Ribbon Customization you create only applies to your instance of Excel. In other words, your Ribbon customizations don't travel with the workbook.
This functionality is really designed for you to "personalize" your Excel interface. It is not designed to build interfaces for Add-ins or workbooks that will be shared. You will still need to use XML and RibbonX for those.
That being said, it looks like Microsoft has given us a way to export and share customizations.
Exporting Customizations will create an .exportedUI file that you can use to share your customizations with others, or transfer your customizations to another computer.
To import, you would simply select the Import option and point to the .exportedUI file.
All in all, I do think the new Customize Ribbon functionality is a real improvement for many who want to personalize their Excel experience.
I wonder how long it will take for someone to customize their Ribbon to emulate the old Excel 2003 menu.
That end-user Ribbon customization is nice. But for me, the question is: Why can't you do it with VBA?
It would take a Microsoft guy about two weeks to add that functionality. Or maybe two months. Whatever. It's do-able, and it wasn't done.
Or maybe there's a good reason why Ribbon customization can't be done using VBA, and I haven't thought of it.
John: I remember the Excel team demonstrating this Ribbon Customization two years ago at the summit.
The first question we asked the team is if this new functionality would be exposed through the Object model. They were perplexed. They couldn't understand why anyone would want to apply Ribbon customizations via VBA.
I guess they never did get the concept.
So they gave us the ribbon, but now we can customize it. You make it sound like a great improvement, and maybe it is. But it's reminiscent of polishing a turd. I can make it nice and shiny, but I still don't like it.
And John – They introduced the Ribbon as a great tool for dot net users. Those guys are all over XML, but they'd never heard of VBA, and in 2005 couldn't conceive of anyone ever wanting to edit the user interface in VBA.
In a Seinfeld episode some time back, George complained how "worlds are colliding" when talking about his friends (old VBA code) meeting his girlfriend (XML, RibbonX).
The funny thing is that MS doesn't like the old friends because of it "enable content" reminds you that maybe you shouldn't answer the door because they can't be trusted.
But you need the old friends to
Sub Callback (control as IRibbonControl).
Cobol vs. Java
Yin and Yang of life.
One of my IT guys (Chris Biles) came up with this Excel 2003 to Excel 2007 button Migration method:
Attach your toolbars to an empty workbook in Excel 2003 (or earlier). Excel menu: Tools -> Customize -> Click 'Attach…' button in the Toolbars-tab, select the toolbars in left panel, then click 'Copy>>' and OK.
Next save this empty workbook as an Add-In: select File -> Save As… and select 'Microsoft Excel Add-In (*.xla)' in the 'Save as type' box.
Now you have an Excel add-in, which you can activate in Excel 2007 (Office button -> Excel-options -> Add-Ins -> Go) and you have all your buttons in the Add-Ins tab of the ribbon. They will now work with the Personal.xls macros in you XLSTART folder.
… [sigh]. I'm pretty sure you can add the pivot table command under the data tab. There is a youtube video for that; however, I'm so disappointed that there is no programmatic way to do this w/o xml and ribbon x.