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

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:

Click here to view code image

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:

Click here to view code image

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:

Click here to view code image

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:

Click here to view code image

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 function

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

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:

Click here to view code image

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.

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.

Pivot tables

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:

Click here to view code image

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

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.

Icons

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.

3D models

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:

Click here to view code image

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:

Click here to view code image

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

TypeScript

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.

Using the Version property

The 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:

Click here to view code image

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

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:

Click here to view code image

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