The Ribbon is one of the first changes you’ll notice when you open Excel 2007. 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 have many new features that are not backward compatible with earlier versions of Excel. And although the macro recorder does record most of your actions on the Design and Layout ribbons, it won’t record action on the Format ribbon in the Format dialog boxes. This doesn’t mean the VBA code isn’t available; it’s just that you can’t learn it by recording it.

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 2007 that you have to watch out for if you need to make a backward-compatible workbook.

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’s the limit. To get an idea of how much this feature has changed, consider this: Special Edition Using Microsoft Office Excel 2007 (ISBN 0-7897-3611-X) has a 40-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 2007.

Excel 2003 recorded macro:

Sub Macro2()
'
' Macro2 Macro
'

'
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="1", Formula2:="5"
    Selection.FormatConditions(1).Interior.ColorIndex = 3
    ActiveCell.FormulaR1C1 = "2"
    Range("A2").Select
End Sub

Excel 2007 recorded macro:

Sub Macro2()
'
' Macro2 Macro
'

'
    Selection.FormatConditions.AddColorScale ColorScaleType:=2
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _
        xlConditionValueNumber
    Selection.FormatConditions(1).ColorScaleCriteria(1).Value = 1
    With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _
        xlConditionValueNumber
    Selection.FormatConditions(1).ColorScaleCriteria(2).Value = 5
    With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor
        .Color = 255
        .TintAndShade = 0
    End With
    ActiveCell.FormulaR1C1 = "2"
    Range("A2").Select
End Sub

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.

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:

Sub Macro2()
'
' Macro2 Macro
'

'
    Range("A1:A4").Select
    'clear current sort options
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    'set the new sort option - this is just a simple A-Z sort
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A1"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    'do the actual sort
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:A4")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

SmartArt is the new function that has replaced the Diagram feature of earlier versions of Excel. Although in the past you couldn’t record diagram actions, you could write code for them. In Excel 2007, you can’t record or code it. The original diagram code won’t work either.

The Version property returns a string containing the active Excel application version. For 2007, this is 12. 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:

Sub wkbkSave()
Dim xlVersion As String
Dim myxlOpenXMLWorkbook As String

myxlOpenXMLWorkbook = "51"

xlVersion = Application.Version

Select Case xlVersion
    Case Is = "9.0", "10.0", "11.0"
        ActiveWorkbook.SaveAs Filename:="LegacyVersionExcel.xls"
    Case Is = "12.0"
        ActiveWorkbook.SaveAs Filename:="Excel2007Version", _
        FileFormat:=myxlOpenXMLWorkbook
End Select
End Sub

Note that for the FileFormat property of the Excel 2007 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 an earlier version of Excel just using the Excel 2007 constant, xlOpenXMLWorkbook, the code would not even compile.

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.3:

Function CompatibilityCheck() As Boolean
Dim blMode As Boolean

If Application.Version = "12.0" Then
    blMode = ActiveWorkbook.Excel8CompatibilityMode
    If blMode = True Then
        CompatibilityCheck = True
    ElseIf blMode = False Then
        CompatibilityCheck = False
    End If
End If
End Function

Sub CheckCompatibility()
Dim xlCompatible As Boolean

xlCompatible = CompatibilityCheck

If xlCompatible = True Then
    MsgBox "You are attempting to use an Excel 2007 function " & Chr(10) & _
    "in a 97-2003 Compatibility Mode workbook"
End If
End Sub

Figure 7.3. Use Excel8CompatibilityCheck to inform a user certain features in your add-in won’t work in a 97-2003 Excel file opened in Excel 2007.