CHAPTER 28
What’s new in Excel 2019 and what’s changed
In this chapter, you will:
Understand ways to purchase Excel 2019
Get to know newer features of Excel
Learn how to look up information about the new objects and methods
Ensure your code works in different versions of Excel
This chapter reviews changes since Excel 2007—2016. In conjunction with reviewing those sections, you should also review information in this book on tables, sorting, and conditional formatting.
Office 365 subscription versus Excel 2019 perpetual
There are two ways you can purchase Excel 2019. One way is with an Office 365 subscription, which will always provide you with the latest and greatest changes in Excel. The other way is a one-time payment for Excel 2019. Although you will still receive patches, Microsoft might choose not to provide certain new features. Keep in mind that if you have an Office 365 subscription but are writing programs for Excel 2019 users, they may not have access to a feature you are coding.
If it has changed in the front end, it has changed in VBA
If you were using Excel 2003 (or older) before Excel 2019, 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, 2013, or 2016, 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 2019. 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.
Note see Chapter 25, “Customizing the ribbon to run macros,” for more information.
Single-document interface
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 (SDI). 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
Modern array formulas
With the introduction to Office 365 of SEQUENCE, SORT, SORTBY, UNIQUE, FILTER, SINGLE, and RANDARRAY, creating array formulas becomes easier. You will not need to use .FormulaArray for these functions. Simply use .Formula or .FormulaR1C1 to build the array formulas. For example, to fill A1:A10 with the numbers 1 to 10, use this code:
Range("A1").Formula = "=SEQUENCE(10)"
Note that only cell A1 will report having a formula. The other cells will report having a value.
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 2019, Microsoft introduced two new chart styles: funnel charts and filled map charts. In early 2019, Office 365 will begin supporting custom visuals from Power BI. As this book goes to press, there is no VBA code available for these charts, but it will likely be offered.
In Excel 2016, there was a bug so only new charts introduced in Excel 2016 would use the new Ivy charting engine. If you were creating an old style chart, you had to use the old programming method. This bug has been fixed in Excel 2019 and for Office 365 subscription users, but if you are an Excel 2016 Perpetual user, it has not been fixed. see Chapter 15, “Creating charts,” for more information on this chart compatibility issue.
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, 2016, and 2019 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.
In Excel 2019, you can change cell formatting for a specific cell in a pivot table, and Excel will remember that formatting even if the shape of the pivot table changes. There is no special property introduced for this—simply format the cell as usual.
Excel 2019 offers pivot table defaults. These can be set in VBA using Application.DefaultPivotTableLayoutOptions. For example, to build future pivot tables in classic drag-and-drop mode, use this:
Application.DefaultPivotTableLayoutOptions.InGridDropZones = True
Note 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.
Note see Chapter 12 for more information on pivot table slicers.
Icons
Microsoft added the Icons button to the Insert tab of the ribbon between Excel 2016 and Excel 2019. Although it is a new command in the ribbon, the code to insert an icon uses Pictures.Insert with the download argument being a location from Office.net. Use the Macro recorder to discover the correct path to the particular icon.
3D Models
Support for displaying and rotating 3D Models was introduced in June 2017. You can insert and rotate most files used by 3D Printers. Support for VBA was added later. You can apply new methods .IncrementRotationX, .IncrementRotationY, and .IncrementRotationZ to the Model3D object. The following code rotates an object called Bennu by 10 degrees along the X axis.
ActiveSheet.Shapes.Range(Array("Bennu")).Model3D.IncrementRotationY 10
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 VB Editor, you’re brought to Microsoft’s online Help resource. Select Excel VBA Reference, Object Model to view a list of all objects, properties, methods, and events in the Excel 2019 object model.
Compatibility mode
With the changes in Excel 2019, 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
Creating a Compatibility mode workbook can be problematic. Most code will still run in legacy versions of Excel, as long as the program doesn’t run into an item from the Excel 2007 or newer object models. If you use any items from the newer object models, however, the code will not compile in legacy versions. To work around this, comment out the specific lines of code, compile, and then comment the lines back in.
If your only Excel compatibility issue is the use of constant values, partially treat your code as if you were doing late binding to an external application. If you have only constant values that are incompatible, treat them like late-binding arguments, assigning a variable the numeric value of the constant. The following section shows an example of this approach.
Note See “Using constant values,” in Chapter 20 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 and 2019, 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:="Excel2019Version", _
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 CheckWorkbookCompatibility()
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.
For assistance with any Excel VBA questions, post your question as a New Thread at the MrExcel Message Board. It's free to post, and the passionate community answers about 10,000 Excel VBA questions every year. To get started, use the Register link at the top right of the page at https://www.mrexcel.com/forum/index.php.