In this chapter, you will:
Understand ways to purchase the latest version of Excel
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–2019. In conjunction with reviewing those sections, you should also review information in this book on tables, sorting, and conditional formatting.
There are two ways you can purchase the latest version of Excel. 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 2021. 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 2021 users, they may not have access to a feature you are coding.
If you were using Excel 2003 (or older) before Excel for Office 365, 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 or newer, 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.
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 Office 365. 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.
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.
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
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. Also, if you were to manually enter the formula on a sheet, you no longer need to press Ctrl+Shift+Enter to tell Excel it’s an array formula.
LAMBDA
functionLAMBDA
functions fill in that gap between complex workbook functions and using VBA to create UDFs. They allow you to design a custom function in the name manager using Excel’s native functions and other custom LAMBDA
functions. You then call that function similar to how you would call a UDF—but there is no VBA needed.
Refer to “Creating LAMBDA
functions” in Chapter 14 for more information.
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 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.
Since Excel 2016, Microsoft has introduced several new chart styles. These chart styles are not backward compatible. See Chapter 15, “Creating charts,” for more information on programming issues between versions.
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.
Since Excel 2007, many new features in pivot tables have been introduced. If you use code for a new feature, the code works in the current version but crashes in previous versions of Excel.
Beginning in Excel 2019, you could change cell formatting for a specific cell in a pivot table, and Excel would 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 also introduced 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
With Office 365, you can assign a formatting rule to the intersection of labels in a pivot table. See Chapter 12, “Using VBA to create pivot tables,” for more information.
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.
Microsoft added the Icons button to the Insert tab of the ribbon between Excel 2016 and Excel 2019. 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.
Support for displaying and rotating 3D models was introduced in 2017. You can insert and rotate most files used by 3D printers. 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 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() Dim myFFXIV_FC As Shape With ActiveSheet Set myFFXIV_FC = .Shapes.AddSmartArt(Application.SmartArtLayouts( _ "urn:microsoft.com/office/officeart/2005/8/layout/hChevron3")) With myFFXIV_FC .GroupItems(1).TextEffect.Text = "War" .GroupItems(3).TextEffect.Text = "Shab" With .GroupItems(2) .Fill.BackColor.SchemeColor = 7 .Fill.Visible = True .TextEffect.Text = "Chraz" End With End With End With Set myFFXIV_FC = Nothing End Sub
Microsoft says VBA will never be supported in Excel Online. Instead, they created a new macro language called TypeScript, which is based off of JavaScript. TypeScript is currently limited to Office 365 customers with a commercial or educational license. That excludes everyone with a Home license.
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 object model.
With the changes in Office 365, it’s important to verify an application’s version. Two properties you can use to do this are Version
and Excel8CompatibilityMode
.
Version
propertyThe Version
property returns a string that contains the active Excel application version. For Excel 2016, 2019, and Office 365, 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:="ExcelLatestVersion", _ FileFormat:=myxlOpenXMLWorkbook End Select End Sub
Excel8CompatibilityMode
propertyThe 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
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/board.