Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

The first thing you notice about Excel 2007 is its new look. The time-honored menu-and-toolbar user interface has been scrapped and replaced with a new tab-and-Ribbon interface. Although the new interface kind of resembles the old-fashioned menus -and- toolbars interface, you'll find that it's radically different.

Long-time Excel users have probably noticed that the menu system has become increasingly complicated with each new version. In addition, the number of toolbars has become almost overwhelming. After all, every new feature must be accessible. In the past, this access meant adding more items to the menus and building new toolbars. The Microsoft designers set out to solve this overcrowding problem, and the new Ribbon interface is their solution.

Time will tell how users will accept the new Ribbon interface. As I write this book, the reaction can best be described as mixed. As with anything new, some people love it, and others hate it.

I think many experienced Excel users will suffer from a mild case of bewilderment when they realize that all their familiar command sequences no longer work. Beginning users, on the other hand, will be able to get up to speed much more quickly because they won't be overwhelmed with irrelevant menus and toolbars.

Because the Ribbon UI is new, I provide some additional user-oriented information in the sections that follow.

Ribbon tabs

The commands available in the Ribbon vary, depending on which tab is selected. The Ribbon is arranged into groups of related commands. Here's a quick overview of Excel's tabs:

• Home: You'll probably spend most of your time in the Home tab. This tab contains the basic Clipboard commands, formatting commands, style commands, commands to insert and delete rows and columns , plus an assortment of worksheet-editing commands.

• Insert: Select this tab when you need to insert something in a worksheet - a table, a diagram, a chart, a symbol, and so on.

• Page Layout: This tab contains commands that affect the overall appearance of your worksheet, including settings that deal with printing.

• Formulas: Use this tab to insert a formula, name a range, access the formula-auditing tools, or control how Excel performs calculations.

• Data: Excel's data-related commands are on this tab.

• Review: This tab contains tools to check spelling, translate words, add comments, and protect sheets.

• View: The View tab contains commands that control various aspects of how a sheet is viewed . Some commands on this tab are also available on the status bar.

• Developer: This tab isn't visible by default. It contains commands that are useful for programmers. To display the Developer tab, choose Office Excel Options and then select Popular. Place a check mark next to Show Developer Tab in the Ribbon.

• Add-Ins: This tab is visible only if you've loaded a workbook or add-in that customizes the menu or toolbars (by using the CommandBars object). Because menus and toolbars are no longer available in Excel 2007, these customizations appear in the Add-Ins tab.

Excel 97 introduced a completely new way of handling toolbars and menus. These UI elements are CommandBar objects. What's commonly called a toolbar is actually one of three types of command bars:

• Toolbar: This is a bar with one or more clickable controls.

• Menu bar:The two built-in menu bars are Worksheet menu bar and Chart menu bar.

• Shortcut menu: This is the menu that pops up when you right-click an object.

For compatibility purposes, Excel 2007 still supports the CommandBar object - but its functionality has been significantly deprecated. It's no longer possible for an end user to create a custom toolbar. However, a VBA programmer can still create and work with CommandBar objects (see "Creating an Old-Style Toolbar," later in this chapter). The problem, however, is that many of the CommandBar properties and methods are simply ignored in Excel 2007. For example, every toolbar or customized menu appears in the Add-Ins tab of the Ribbon. Properties that control a toolbar's dimensions and position no longer work. In addition, floating toolbars are no longer possible.

The accompanying figures show a customized menu and toolbar in Excel 2003, and the same menu and toolbar in Excel 2007. Although these UI elements are still functional in Excel 2007, it's clearly not what the developer (me!) had in mind. Needless to say, many VBA developers will want to redo the UI for their applications.

In this chapter, I present a simple example of creating a custom toolbar by using the CommandBar object (see "Creating an Old-Style Toolbar"). For complete details on creating custom menus and toolbars with the CommandBar object, consult the previous edition of this book.

Customizing shortcut menus is still supported in Excel 2007, and I cover that topic in Chapter 23.

The appearance of the commands on the Ribbon varies, depending on the width of the Excel window. When the window is too narrow to display everything, the commands adapt and may seem to be missing, but the commands are still available. Figure 22-1 shows three views of the Home tab of the Ribbon. In the top image, all controls are fully visible. In the middle image, Excel's window is made narrower. Notice that some of the descriptive text is gone, and some of the icons are smaller. The bottom image shows the extreme case in which the window is very narrow. Some groups display a single icon. However, if you click the icon, all the group commands are available to you.

Figure 22-1: The Home tab of the Ribbon, with varying widths of the Excel window.