Excel Tables

by Zack Barresse

  ThisWorkbook.Worksheets("Sheet1").ListObjects(1)

  The index (or position) of a ListObject in the ListObjects collection is determined by the order in which the ListObject objects were created on the worksheet. The ListObject object can be assigned to a variable that has been defined as a ListObject type. The ListObject object has a number of properties and methods used to access and manipulate Tables. The following sections present the most commonly used of these properties and methods.

  Table Part Properties

  Five properties represent the main parts of a Table. Each of these properties is a Range object. Two additional properties or collections provide access to the rows and columns in a Table. Each collection provides access to all the ListRow and ListColumn objects in the Table. Each ListRow and each ListColumn object has properties and methods.

  Range Property

  The Range property returns the entire Table, including the header and total rows. It is a Range object type. The property cannot be set.

  HeaderRowRange Property

  The HeaderRowRange property returns the Table's header row. It is a Range object type. The property cannot be set. The range is always a single row—the header row—and extends over all Table columns. When the header row is disabled, this property is set to Nothing.

  DataBodyRange Property

  The DataBodyRange property returns the Table's body. It is a Range object type. The property cannot be set. The range is every row between the header and the total row and extends over all Table columns.

  When a Table does not contain any rows, the DataBodyRange property returns Nothing (and ListRows.Count returns 0). This is the only case when the property InsertRowRange returns a Range object that can be used to insert a new row. When in this state, the Table appears to have one row, without any values in it. As soon as one cell in the Table is set to a value, InsertRowRange is set to Nothing, and DataBodyRange is set to the data rows in the Table.

  TotalRowRange Property

  The TotalRowRange property returns the Table's total row. It is a Range object type. The property cannot be set. The range is always a single row—the total row—and extends over all Table columns. When the total row is disabled, this property is set to Nothing.

  InsertRowRange Property

  The InsertRowRange property returns the Table's current insertion row. It is a Range object type. The property cannot be set. While this range was always the first free row below the Table in Excel 2003 (the row with the asterisk), it was partially deprecated in Excel 2007 and remains so in 2013. In Excel 2007 and later versions, InsertRowRange only returns the first data row and only when the Table does not contain any data. Otherwise, it returns Nothing and is effectively useless.

  ListRows Property

  The ListRows property returns a collection of all rows in the Table's DataBodyRange. It is a ListRows object type that behaves very much like a Collection object and contains a collection of ListRow objects. The property cannot be set. Rows are referenced only by a one-based index number relative to the first row. An empty Table has no rows. The ListRows object's Add method is used to insert one new row at a time.

  ListColumns Property

  The ListColumns property returns a collection of all columns in the Table. It is a ListColumns object type that behaves very much like a Collection object and contains a collection of ListColumn objects. The property cannot be set. Columns are referenced by a one-based index number relative to the first column. A Table always contains at least one column.

  Table Structure Properties

  The following sections describe properties that you use to manipulate a Table's structure. All these properties are members of the ListObject object.

  ShowAutoFilter Property

  The ShowAutoFilter property returns or sets whether the Table's AutoFilter is enabled. It is a Boolean type. When it is set to True, the Table's AutoFilter is enabled. When it is set to False, the AutoFilter is disabled. This property is presented in the Excel user interface as the button DATA | Sort & Filter | Filter.

  ShowAutoFilterDropDown Property

  The ShowAutoFilterDropDown property returns or sets whether the Table's AutoFilter drop-down controls are displayed. It is a Boolean type. When it is set to True, the Table's AutoFilter drop-down controls are displayed. When it is set to False, they are hidden. If ShowAutoFilter is set to False, the ShowAutoFilterDropDown property cannot be changed. This property is presented in the Excel user interface as the button {TABLE TOOLS} DESIGN | Table Style Options | Filter Button.

  ShowHeaders Property

  The ShowHeaders property returns or sets whether the Table's header row is enabled. It is a Boolean type. When it is set to True, the Table's header row is enabled. When it is set to False, it is disabled. This property is presented in the Excel user interface as the check box {TABLE TOOLS} DESIGN | Table Style Options | Header Row.

  ShowTotals Property

  The ShowTotals property returns or sets whether the Table's total row is enabled. It is a Boolean type. When it is set to True, the Table's total row is enabled. When it is set to False, it is disabled. This property is presented in the Excel user interface as the check box {TABLE TOOLS} DESIGN | Table Style Options | Total Row.

  Table Style Properties

  The following sections describe properties that you use to manipulate a Table's style. All these properties are members of the ListObject object.

  TableStyle Property

  The TableStyle property returns or sets the Table's style name. It is a Variant type. To change the Table's style, you set the value to the desired style's name as a string. When you assign a Table style to a Table, only the style elements defined in that style are applied; see Chapter 7 for more information. To determine a style's name, move the mouse curser over the desired style in the Table Styles gallery until Excel displays that style's name, as shown in the next figure.

  Determining a Table style's name.

  This figure shows the Table's style displayed as "Table Style Light 1". To assign that style name to the StyleName property, you just remove the spaces:

  ListObject.TableStyle = "TableStyleLight1"

  NOTE

  The internal names of all built-in styles do not contain spaces even though the names displayed on the ribbon do contain spaces.

  The TableStyle property is presented in the Excel user interface as the array of buttons in the group {TABLE TOOLS} DESIGN | Table Styles.

  ShowTableStyleColumnStripes Property

  The ShowTableStyleColumnStripes property returns or sets whether the Table's odd columns are formatted differently than the even columns. It is a Boolean type. When it is set to True, the Table's odd columns are formatted differently than the even columns, as defined in the assigned Table style; the odd columns are formatted using the Table style's First Column Stripe settings, and the even rows are formatted using the Table style's Second Column Stripe settings. When ShowTableStyleColumnStripes is set to False, the Table's columns are not formatted using the assigned Table style. The ShowTableStyleColumnStripes property is presented in the Excel user interface as the check box {TABLE TOOLS} DESIGN | Table Style Options | Banded Columns.

  Note that the default number of columns in each stripe is one, but you can change this to larger numbers for the First Column Stripe and Second Column Stripe elements independently. See Chapter 7 for more information.

  ShowTableStyleRowStripes Property

  The ShowTableStyleRowStripes property returns or sets whether the Table's odd rows are formatted differently than the even rows. It is a Boolean type. When it is set to True, the Table's odd rows are formatted differently than the even rows, as defined in the assigned Table style; the odd rows are formatted using the Table style's First Row Stripe settings, and the even rows are formatted using the Table style's Second R
ow Stripe settings. When the ShowTableStyleRowStripes property is set to False, the Table's rows are not formatted using the assigned Table style. This property is equivalent to the check box {TABLE TOOLS} DESIGN | Table Style Options | Banded Rows.

  Note that the default number of columns in each stripe is one, but you can change this to larger numbers for the First Column Stripe and Second Column Stripe elements independently. See Chapter 7 for more information.

  ShowTableStyleFirstColumn Property

  The ShowTableStyleFirstColumn property returns or sets whether the Table's first column is formatted differently. It is a Boolean type. When it is set to True, the Table's first column is formatted as defined in the assigned Table style's First Column settings. When it is set to False, the Table's first column is not formatted using the assigned Table style. This property is presented in the Excel user interface as the check box {TABLE TOOLS} DESIGN | Table Style Options | First Column.

  ShowTableStyleLastColumn Property

  The ShowTableStyleLastColumn property returns or sets whether the Table's last column is formatted differently. It is a Boolean type. When it is set to True, the Table's last column is formatted as defined in the assigned Table style's Last Column settings. When it is set to False, the Table's last column is not formatted using the assigned Table style. This property is presented in the Excel user interface as the check box {TABLE TOOLS} DESIGN | Table Style Options | Last Column.

  Other Table Object Properties

  The following sections cover Table object properties to use in VBA. These are different than methods.

  Active Property

  The Active property returns True if the active cell is within the Table's range, including the header and total rows; it returns False otherwise. It is a Boolean type. The property cannot be set.

  AlternativeText Property

  The AlternativeText property returns or sets the Table's alternative text. It is a String type. Setting this property overwrites any previous value. This property is presented in the Excel user interface in the Alternative Text dialog box, which you access by right-clicking anywhere in the Table and selecting Table | Alternative Text. This property is displayed and edited in the Title text box.

  AutoFilter Property

  The AutoFilter property is an AutoFilter object with its own properties and methods. It can be used to inspect the AutoFilter settings and reapply or clear the AutoFilter settings to the Table. It is not used to set filters; you use the Range object's AutoFilter method for that. Both the Table object's AutoFilter object and the Range object's AutoFilter method are covered in more detail later in this chapter.

  Comment Property

  The Comment property returns or sets the Table's comment. It is a String type. This property, which was added in Excel 2007, is presented in the Excel user interface in the Name Manager dialog box, which you access by selecting FORMULAS | Defined Names | Name Manager. The comment is displayed in the rightmost column, and you change it by editing the name.

  DisplayName Property

  The DisplayName property returns or sets the Table's name. It is a String type. When you assign a value to this property, the same restrictions apply as when you change a Table's name from the Excel user interface; for example, it can't already be in use, and it can't contain any spaces. This property, which was added in Excel 2007, behaves almost the same as the Name property, but with the DisplayName property, the name being assigned must conform to Table name restrictions, or an error occurs. This property is presented in the Excel user interface as the text entry box {TABLE TOOLS} DESIGN | Properties | Table Name.

  Name Property

  The Name property returns or sets the Table's name. It is a String type. Unlike with the DisplayName property, when you assign a value to the Name property, Excel changes the name so that it follows the Table name rules. For example, it changes spaces to underscores and, if the name already exists, it appends an underscore followed by a number to the name. This property is presented in the Excel user interface as the text entry box {TABLE TOOLS} DESIGN | Properties | Table Name.

  TIP

  To avoid problems, use the DisplayName property instead of the Name property to name a Table. The DisplayName property generates an error if the name is illegal or already defined elsewhere. On the other hand, Excel will mangle the value assigned to the Name property to make it legal, and thus the name may not end up being exactly what you intended.

  Parent Property

  The Parent property returns the Table's parent. It is an Object type but is always a Worksheet object. The property cannot be set.

  QueryTable Property

  The QueryTable property returns the QueryTable object that links to a list server. It is a QueryTable object type. The property cannot be set. The QueryTable object exposes properties and methods that enable you to manipulate the Table.

  The following code publishes an existing Table to a SharePoint server and names the published list "Register". It then recovers the QueryTable object for the Table and sets the Table's MaintainConnection property to True:

  Dim Table As ListObject

  Dim QueryTable As QueryTable

  Dim PublishTarget(4) As String

  Dim ConnectionString As String

  Set Table = ThisWorkbook.Worksheets("Register").ListObjects("tblRegister")

  PublishTarget(0) = "0"

  PublishTarget(1) = "http://myserver/myproject"

  PublishTarget(2) = "1"

  PublishTarget(3) = "Register"

  ConnectionString = Table.Publish(PublishTarget, True)

  Set QueryTable = Table.QueryTable

  QueryTable.MaintainConnection = True

  SharePointURL Property

  The SharePointURL property returns the URL of the SharePoint list. It is a String type. The property is set when you create or maintain a SharePoint connection, and you can't change it. This property is presented in the Excel user interface as the button {TABLE TOOLS} DESIGN | External Table Data | Export | Export Table to SharePoint List. The following code publishes an existing Table to a SharePoint server by using SharePointURL and names the published list "Register":

  Dim Table As ListObject

  Dim QueryTable As QueryTable

  Dim PublishTarget(4) As String

  Dim ConnectionString As String

  Set Table = ThisWorkbook.Worksheets("Register").ListObjects("tblRegister")

  PublishTarget(0) = "0"

  PublishTarget(1) = Table.SharePointURL

  PublishTarget(2) = "1"

  PublishTarget(3) = "Register"

  ConnectionString = Table.Publish(PublishTarget, True)

  Slicers Property

  The Slicers property returns the collection of Slicers associated with the Table. It is a Slicers object type that behaves very much like a Collection object and contains a collection of Slicer objects. The property cannot be set. You use the Slicers object to add, manipulate, and delete Slicers associated with the Table. Each Slicer object exposes properties and methods that enable you to manipulate of the Slicer. The Slicers property was added to the ListObject object in Excel 2013. This section provides examples that illustrate how to add, manipulate, and delete a Slicer for a Table.

  In the following example, a Slicer is added and placed on the same worksheet as the Table. Note that adding a Slicer is not quite as straightforward as adding other objects to collections; you need to create a SlicerCache object at the same time for each Slicer:

  Dim Table As ListObject

  Dim SlicerCache As SlicerCache

  Dim Slicer As Slicer

  Set Table = ThisWorkbook.Worksheets("Register").ListObjects("tblRegister")

  Set SlicerCache = ThisWorkbook.SlicerCaches.Add(Table, "Category")

  SlicerCache.RequireManualUpdate = False />
  Set Slicer = SlicerCache.Slicers.Add(Table.Parent, ,

  "tblRegisterCategory", "Category", 100, 400)

  Note that the SlicerCache object is tied to the Table and the column being filtered. The Slicer itself is the visual representation of the Slicer cache and has a parent, a name, a caption, and a position; it also has a size, but the default size is used in the above example. The SlicerCache object's RequireManualUpdate property is set to False to avoid the "Out-Of-Date" message in the Slicer.

  In the following example, the Slicer is configured to show the category "Expense" and hide the category "Income":

  Dim Table As ListObject

  Dim Slicer As Slicer

  Set Table = ThisWorkbook.Worksheets("Register").ListObjects("tblRegister")

  Set Slicer = Table.Slicers("tblRegisterCategory")

  With Slicer.SlicerCache

  .SlicerItems("Expense").Selected = True

  .SlicerItems("Income").Selected = False

  End With

  In the following example, the Slicer is configured to show only one category. This technique is useful when there are many different values and you want only one of them:

  Dim Table As ListObject

  Dim Slicer As Slicer

  Dim SlicerItem As SlicerItem

  Set Table = ThisWorkbook.Worksheets("Register").ListObjects("tblRegister")

  Set Slicer = Table.Slicers("tblRegisterCategory")

  With Slicer.SlicerCache

  .ClearManualFilter

  For Each SlicerItem In .SlicerItems

  If SlicerItem.Name <> "Expense" Then

  SlicerItem.Selected = False

  End If

  Next SlicerItem

  End With

  In the following example, the Slicer's filter is cleared:

  Dim Table As ListObject

  Dim Slicer As Slicer

  Set Table = ThisWorkbook.Worksheets("Register").ListObjects("tblRegister")

  Set Slicer = Table.Slicers("tblRegisterCategory")

  Slicer.SlicerCache.ClearManualFilter

  In the following example, the Slicer is deleted. Note that the Slicer cache is also deleted when the Slicer is deleted: