28. What’s New in Excel 2016 and What’s Changed
In This Chapter
If It Has Changed in the Front End, It Has Changed in VBA
Learning the New Objects and Methods
This chapter reviews changes since Excel 2007–2013. In conjunction with reviewing those sections, you should also review information in this book on tables, sorting, and conditional formatting.
If It Has Changed in the Front End, It Has Changed in VBA
If you were using Excel 2003 (or older) before Excel 2016, almost everything you knew about programming Excel objects has changed. Basic logic still works (for loops, for example), but most objects have changed.
If you have been using Excel 2007, 2010, or 2013, there are still a few changes to consider, and they are noted in this chapter. For most items, the changes are 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 2016. 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 Excel 2013. With Excel 2013, Excel changed from a multiple-document interface to a single-document interface. This means 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 don’t affect any previously opened windows. To see this in action, 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 Example Option appears right where it should. 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.
Note
Don’t worry about having to delete all instances of the sample menu item. It was created to be temporary and will be gone when you restart Excel.
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
Introduced in 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 there have also been changes to the object model. In Excel 2013, Microsoft introduced a completely new interface and a new method, AddChart2, which is not backward compatible—not even to Excel 2010. With Excel 2016, Microsoft has introduced six new-style charts; they are not supported by the macro recorder, and the VBA code is buggy. Although Microsoft has promised to update the old charts to the modern charting system and fix the VBA bugs, we don’t know when this will happen. Until then, the safest solution is to create your code carefully. With these compatibility issues in mind, Chapter 15, “Creating Charts,” provides examples for Excel 2003, 2007–2010, 2013, and 2016.
Excel 2010 introduced a type of minichart, called a sparkline. A sparkline is different from a standard chart in that it can be inserted within a cell. Sparklines are not backward compatible.
Pivot Tables
Excel 2007, 2010, 2013, and 2016 have offered many new features in pivot tables. If you use code for a new feature, the code works in the current version but crashes in previous versions of Excel. 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 because they allow 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 that includes 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 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 in legacy versions of Excel. Recording is very limited, but it helps 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. In this code I changed schemecolor for the middle chevron and left 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’s VBEVB Editor, you’re brought to Microsoft’s online Help resource. On the left side of the browser window, select What’s New for Excel 2016 Developers to open an article that provides an overview of some of the changes. Select Excel VBA Reference, Object Model to view a list of all objects, properties, methods, and events in the Excel 2016 object model.
Compatibility Mode
With the changes in Excel 2016, it’s important to verify an application’s version. Two properties you can use to do this are Version and Excel8CompatibilityMode.
Dealing With Compatibility Issues
See “Using Constant Values,” p. 411, for more information on using constant values.
Using the Version Property
The Version property returns a string that contains the active Excel application version. For 2016, this is 16.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 WorkbookSave()
Dim xlVersion As String, 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", "16.0" '12.0 is 2007, 14.0 is 2010
ActiveWorkbook.SaveAs Filename:="Excel2016Version", _
FileFormat:=myxlOpenXMLWorkbook
End Select
End Sub
Caution
Note that for the FileFormat property of the Excel 12.0 and newer 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 constant xlOpenXMLWorkbook, the code would not even compile.
Using the Excel8CompatibilityMode Property
The Excel8CompatibilityMode 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 that uses conditional formatting that you don’t want the user to try to use on the workbook. The CompatibilityCheck function 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", "16.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 2007 or newer 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 each 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 in other organizations.
If you have found any sections of the book confusing or thought they could have been spelled out better, we welcome your comments and will give them consideration as we prepare the next edition of this book. Write to us:
[email protected] to contact Bill or
[email protected] to contact Tracy
Whether your goal is 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, you can use this book as your training manual. Master the topics, and you will be qualified to join us.