28. What Is New in Excel 2013 and What Has Changed

If It Has Changed in the Front End, It Has Changed in VBA

If you were using Excel 2003 (or older) before Excel 2013, almost everything you knew about programming Excel objects has changed. Basic logic still works (for loops, for example), but most objects have changed. This chapter reviews changes since Excel 2007–2010. In conjunction with reviewing those sections, you should also review information in this book on tables, sorting, and conditional formatting.

If you have been using Excel 2007 or 2010, there are still a few changes to consider, and they are noted in this chapter. For most items, it’s obvious, because if the Excel user interface has 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 2013. 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.

→ See Chapter 25, “Customizing the Ribbon to Run Macros,” for more information.

Single Document Interface (SDI)

For years, if you had multiple documents open in Word, you could drag each document to a different monitor. This capability was not available in Excel, until now. With Excel 2013, Excel changes from a multiple document interface to a single document interface. What this means is that the individual workbook window no longer resides within a single application window. Instead, each workbook is in its own standalone window, separate from any other open workbook.

Changes to the layout of one window won’t affect any previously opened windows. For example, open two workbooks. In the second workbook, enter and run the following code, which adds a new item, Example Option, to the bottom of the right-click menu:

Sub AddRightClickMenuItem()
Dim cb As CommandBarButton
Set cb = CommandBars("Cell").Controls.Add _
    (Type:=msoControlButton, temporary:=True)
cb.Caption = "Example Option"
End Sub

Right-click a cell in the second workbook and the option appears. Right-click a cell in the first workbook and the option does not appear. Return to the second workbook and press Ctrl+N to add a new workbook. Right-click a cell in this third workbook and the menu item appears. Go to the first workbook, create a new workbook, and check the right-click menu. The option does not appear.

Now, delete the custom menu. Go to the third workbook, and paste and run the following code:

Sub DeleteRightClickMenuItem()
CommandBars("Cell").Controls("Example Option").Delete
End Sub

The menu item is removed from the third workbook, but when you check the right-click menu of the second workbook, the item is still there. Although Excel copied the menu from the active workbook when creating new workbooks, the logic to remove the menu item does not propagate.

Another change to keep in mind is that making a change to the window of one workbook, such as minimizing it, doesn’t affect the other workbooks. If you want to minimize all windows, you need to loop through the application’s windows, like this:

Sub MinimizeAll()
Dim myWin As Window
For Each myWin In Application.Windows
myWin.WindowState = xlMinimized
Next myWin
End Sub

Quick Analysis Tool

New to Excel 2013, the Quick Analysis tool appears in the lower-right corner when a range of data is selected. This tool suggests what the user could do with the data, such as apply conditional formatting or create a chart. You can activate a specific tab, such as Totals, when the user selects a range, like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.QuickAnalysis.Show (xlTotals)
End Sub

Charts

Charts have gone through a few incarnations since Excel 2003, and with those changes to the interface have been changes to the object model. The greatest change is in Excel 2013, with a completely new interface and a new method, AddChart2, which is not backward compatible, not even to Excel 2010. With this compatibility issue in mind, the chapter on charts provides examples for Excel 2003, 2007–2010, and 2013.

There’s also a new type of minichart, called Sparklines, that can be inserted within a cell. Sparklines are available only in Excel 2010 and 2013.

→ See Chapter 15, “Creating Charts,” for more information.

PivotTables

Each of the previous three versions of Excel offered many new features in PivotTables. If you use code for a new feature, the code works in the current version but crashes in previous versions of Excel.

• Excel 2013 introduced the PowerPivot Data Model. You can add tables to the Data Model, create a relationship, and produce a pivot table. This code does not run in Excel 2010 or earlier. The function xlDistinctCount is new. Timelines are new.

• Excel 2010 introduced slicers, Repeat All Item Labels, Named Sets, and several new calculation options: xlPercentOfParentColumn, xlPercentOfParentRow, xlPercentRunningTotal, xlRankAscending, and xlRankDescending. These do not work in Excel 2007.

• Excel 2007 introduced ConvertToFormulas, xlCompactRow layout, xlAtTop for the subtotal location, TableStyles, and SortUsingCustomLists. Macros that include this code fail in previous versions.

→ See Chapter 12, “Using VBA to Create Pivot Tables,” for more information.

Slicers

Slicers were a new feature in Excel 2010 for use on pivot tables. They aren’t backward compatible, not even to Excel 2007. They’re useful in pivot tables, allowing for easy-to-see and -use filtering options. 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.

In Excel 2013, slicers were added to Tables. The functionality is the same as that of the slicers for pivot tables, but these new slicers are not backward compatible, not even to Excel 2010.

→ See Chapter 12 for more information on pivot table slicers.

SmartArt

SmartArt was introduced in Excel 2007 to replace 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 shown in Figure 28.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.

Sub AddDiagram()
With ActiveSheet
    Call .Shapes.AddSmartArt(Application.SmartArtLayouts( _
        "urn:microsoft.com/office/officeart/2005/8/layout/hChevron3")).Select
    .Shapes.Range(Array("Diagram 1")).GroupItems(1).TextEffect.Text = "Bill"
    .Shapes.Range(Array("Diagram 1")).GroupItems(3).TextEffect.Text = "Tracy"
    With .Shapes.Range(Array("Diagram 1")).GroupItems(2)
        .Fill.BackColor.SchemeColor = 7
        .Fill.Visible = True
        .TextEffect.Text = "Barb"
    End With
End With
End Sub

Figure 28.1. The macro recorder is limited when recording the creation of SmartArt. You need to trace through the object’s properties to find what you need.

Learning the New Objects and Methods

When you click the help button in Excel, you’re brought to Microsoft’s online help resource. On the left side of the browser window, select What’s New for Excel 2013 Developers to open an article reviewing some of the changes. Select Welcome to the Excel 2013 Developer Reference then Object Model Reference to view a list of all objects, properties, methods, and events in the Excel 2013 object model.

Compatibility Mode

With all the changes in Excel 2013, now more than ever it’s important to verify the application’s version. Two ways you can do this are Version and Excel8CompatibilityMode.

→ See “Using Constant Values,” p. 456, for more information on using constant values.

Version

The Version property returns a string containing the active Excel application version. For 2013, this is 15.0. 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" 'non-macro enabled workbook

xlVersion = Application.Version

Select Case xlVersion
    Case Is = "9.0", "10.0", "11.0"
        ActiveWorkbook.SaveAs Filename:="LegacyVersionExcel.xls"
    Case Is = "12.0", "14.0", "15.0" '12.0 is 2007, 14.0 is 2010
        ActiveWorkbook.SaveAs Filename:="Excel2013Version", _
        FileFormat:=myxlOpenXMLWorkbook
End Select
End Sub

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 to 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.

Function CompatibilityCheck() As Boolean
Dim blMode As Boolean

Dim arrVersions()

arrVersions = Array("12.0", "14,0", "15.0")

If Application.IsNumber(Application.Match(Application.Version, _
    arrVersions, 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 2013 function " & Chr(10) & _
    "in a 97-2003 Compatibility Mode workbook"
End If
End Sub

Next Steps

If we as authors have done our job correctly, you now have the tools you need to design your own VBA applications in Excel. You understand the shortcomings of the macro recorder yet know how to use it as an aid in learning how to do something. You know how to use Excel’s power tools in VBA to produce workhorse routines that can save you hours of time per week. You’ve also learned how to have your application interact with others so that you can create applications to be used by others in your organization or other organizations.

If you have found any sections of the book that you thought were confusing or could have been spelled out better, we welcome your comments, and they will be given consideration as we prepare the next edition of this book. Write to us:

[email protected] and

[email protected]

Whether your goal was to automate some of your own tasks or to become a paid Excel consultant, we hope that we’ve helped you on your way. Both are rewarding goals. With 500 million potential customers, we find that being Excel consultants is a friendly business. If you are interested in joining our ranks, this book is your training manual. Master the topics and you will be qualified to join the team of Excel consultants.