7. What Is New in Excel 2010 and What Has Changed
IN THIS CHAPTER
If It Has Changed in the Front End, It Has Changed in VBA
Learning the New Objects and Methods
If It Has Changed in the Front End, It Has Changed in VBA
Thankfully, not too much of VBA doesn’t work anymore, but a few things in the object model have changed. For most items, it’s obvious that, because the Excel user interface changed, the VBA has changed.
The Ribbon
If you have been working with a legacy version of Excel, the Ribbon is one of the first changes you’ll notice when you open Excel 2010. Although the CommandBars object does still work to a point, if you want to flawlessly integrate your custom controls into the Ribbon, you need to make some major changes.
Charts
Charts have many new features that are not backward-compatible with legacy versions of Excel. There’s also a new type of mini-chart called Sparklines that are inserted within a cell.
Sparklines are not backward-compatible, not even to Excel 2007.
Pivot Tables
Pivot tables have a few new features available that aren’t backward-compatible, such as subtotals at the top and the report layout options.
→ Tables 13.1 and 13.2 in Chapter 13, “Using VBA to Create Pivot Tables,” list the new methods and properties in Excel 2010 that you have to watch out for if you need to make a backward-compatible workbook.
Slicers
A slicer is a new feature in Excel 2010 that is not backward-compatible, not even to Excel 2007. It’s useful in pivot tables, allowing for an easy to see and use filtering option. If you open a workbook with a slicer in an older version of Excel, the slicer is replaced with a shape, including text explaining what the shape is there for and that the feature is not available.
Conditional Formatting
Conditional formatting has been completely reinvented. Where we were once limited to three conditions and changing a few cell formatting options, it seems now the sky is the limit.
Note
To get an idea of how much the conditional formatting feature has changed, consider this: Excel 2010 in Depth (Sams, ISBN 0789743086) has a 30-page chapter just to review the options available. Compare that to almost any Excel 2003 or earlier book, in which conditional formatting coverage was just a footnote or two scattered throughout the book.
This feature has come a long way, which means so has the code. Compare the following two recorded macros. They both are relatively simple. A cell’s fill is changed to red if the value in the cell is between 1 and 5. Notice, however, how much more code is involved with the new options that you now need to set in 2010.
Excel 2003 recorded macro:
Tables
Tables are a convenient way to deal with data that is already set up as tables (multiple records set up beneath a row of column headers). For this new functionality, there are corresponding new objects, properties, and methods.
→ To learn more, see “Referencing Tables,” p. 77 and “Tables,” p. 153.
Sorting
Because of the increased sorting options such as sorting by color, sort code has gone through a few changes. Instead of a single line of code with a few options to set, you need to configure the sort options and then do the sort, as shown here:
SmartArt
SmartArt is the new function that has replaced the Diagram feature of legacy versions of Excel. Recording is very limited, but it will help you find the correct schema. After that, the recorder doesn’t capture text entry or format changes.
The following example created the art in Figure 7.1. The name of the schema used is hChevron3. I changed the schemecolor for the middle chevron, leaving the other two with the default colors:
Figure 7.1. The macro recorder is limited when recording the creation of SmartArt. You’ll need to trace through the object’s properties to find what you need.
Learning the New Objects and Methods
Excel’s VBA Help files have several tables of changes for objects and methods in Excel to which you can refer. They’re even broken up by version number, as shown in Figure 7.2. To access these tables, click the Help icon in the VB Editor toolbar and select What’s New from the dialog that appears.
Figure 7.2. Excel’s VBA Help has several sections to help you find what will and won’t work in the new Excel.
When you review the “Object Model Changes Since” sections, you may be wondering what Microsoft means when they say an item’s status is Hidden (see Figure 7.3). If you use one of these items in your code, such as FileSearch, the program will compile just fine, but it won’t run in Excel 2010.
Figure 7.3. Some items appear as Hidden in the Object Model Changes reference tables. This means Excel will compile them for use in legacy versions, but they won’t actually work in Excel 2010.
Caution
Microsoft has allowed you to include a Hidden item in your code for legacy usage and will compile it, but when you try to run it, the program won’t know what to do with it. Unless you have some kind of compatibility mode check in your code, your program will debug at runtime.
Compatibility Mode
With all the changes in Excel 2010, now more than ever it’s important to verify the application’s version. Two ways you can do this are Version and Excel8CompatibilityMode.
Version
The Version property returns a string containing the active Excel application version. For 2010, this is 14. This can prove useful if you’ve developed an add-in to use across versions; but some parts of it, such as saving the active workbook, are version-specific:
Caution
Note that for the FileFormat property of the Excel 2010 case, I had to create my own variable, myxlOpenXMLWorkbook, to hold the constant value of xlOpenXMLWorkbook. If I were to try to run this in a legacy version of Excel just using the Excel 2010 constant, xlOpenXMLWorkbook, the code would not even compile.
Excel8CompatibilityMode
This property returns a Boolean, to let you know whether a workbook is in Compatibility mode—that is, saved as an Excel 97-2003 file. You use this, for example, if you have an add-in using the new conditional formatting, but you wouldn’t want the user to try and use it on the workbook. The following function, CompatibilityCheck, returns True if the active workbook is in Compatibility mode and False if it is not. The procedure, CheckCompatibility, uses the result to inform the user of an incompatible feature, as shown in Figure 7.4:
Figure 7.4. Use Excel8-Compatibility-Check to inform a user that certain features in your add-in won’t work in a 97–2003 Excel file opened in Excel 20010.
Next Steps
Now that you have an idea about the differences you might run into in Excel 2010, you’re ready to move on to the next chapter, which discusses using named ranges to simplify your coding including one of my favorite new methods—the Table method.