Excel Tables Read online

  Dim Table As ListObject

  Dim Slicer As Slicer

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

  Set Slicer = Table.Slicers("tblRegisterCategory")

  Slicer.Delete

  Table.ShowAutoFilter = False

  Note that the Table's ShowAutoFilter property is set to False to hide the drop-down that remains after the Slicer is deleted. If the Table's AutoFilter was enabled when the Slicer was created, then this step isn't necessary. If the Table's AutoFilter was not enabled before the Slicer was added, then only the column being sliced has the AutoFilter drop-down control remain after the Slicer is deleted.

  Sort Property

  The Sort property returns the Table's Sort object. It is a Sort object type. The property cannot be set. The Sort object exposes properties and methods that enable the sorting of a Table. The Sort property was added in Excel 2007.

  The following example sorts a Table by date and description:

  Dim Table As ListObject

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

  With Table

  .Sort.SortFields.Add .ListColumns("Date").DataBodyRange,_

  xlSortOnValues, xlAscending

  .Sort.SortFields.Add .ListColumns("Description")._

  DataBodyRange, xlSortOnValues, xlAscending

  .Sort.Apply

  .Sort.SortFields.Clear

  End With

  CAUTION

  On a protected worksheet, you cannot sort a Table unless all cells in the Table’s header and data body are unlocked or unless the worksheet is unprotected and then protected again.

  SourceType Property

  The SourceType property returns the Table's current source. It is an XlListObjectSourceType type. The property cannot be set. The following are XlListObjectSourceType constants:

  xlSrcExternal or 0—The source is an external data source, such as a Microsoft SharePoint Foundation site.

  xlSrcModel or 4—The source is a Power Pivot model.

  xlSrcQuery or 3—The source is a query.

  xlSrcRange or 1—The source is a Range object.

  xlSrcXml or 2—The source is XML.

  Summary Property

  The Summary property returns or sets the descriptive text used for the alternate text when the Table is published. It is a String type and was introduced in Excel 2010. 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. It is displayed and edited in the Description text box.

  TableObject Property

  The TableObject property returns the Table's TableObject object. It is a TableObject object type. The property cannot be set. The TableObject object exposes properties and methods that enable you to manipulate the Table's objects. TableObject is an object built from data retrieved from a Power Pivot model. It was introduced in Excel 2013.

  XmlMap Property

  The XmlMap property returns the Table's XmlMap object, which provides access to the Table's XML schema map. It is an XmlMap object type. The XmlMap object exposes properties and methods that enable you to manipulate the XML map. The property cannot be set.

  Other Properties

  The following sections describe other common Table properties.

  ListColumn Property

  The ListColumn property is an item in the ListColumns property or collection. ListColumn is an object with a number of useful properties, including these:

  Range—References the cells in a column, including the header and total rows, if enabled.

  DataBodyRange—A Range object type that references the column's range, excluding the header row and the totals row. It is the intersection of the ranges represented by the ListObject's Range property and the ListObject's DataBodyRange range.

  Index—The relative index number of a column represented by the ListColumn object.

  Parent—The ListObject that owns a column.

  ListColumn also has a useful method:

  Delete—Deletes the column from the Table.

  ListRow Property

  Each ListRow object in a collection of rows has three commonly used properties: a Range property that references the cells in that row, an Index property that is the relative index number of that row, and a Parent property that refers to the ListObject that contains the row. The Row object also has one method, Delete, which deletes the row from the Table.

  Table Object Methods

  The following sections describe properties for performing actions with ListObject.

  Delete Method

  The Delete method deletes a Table, including all the Table's values, formulas, and formatting. Do not confuse this method with the Unlist method, which converts a Table to a regular range of cells and maintains the values and formulas in the Table.

  ExportToVisio Method

  The ExportToVisio method exports to and opens in Visio a dynamic PivotDiagram in a new drawing document. This method was added in Excel 2007. An error is generated if Visio is not installed on the same system on which the method is invoked. To export the Excel Table to Visio, the workbook must be saved with no pending changes.

  Publish Method

  The Publish method publishes the Table to a SharePoint service. It returns the URL of the published list on SharePoint as a string. Here is its syntax:

  expression.Publish(Target, LinkSource)

  Where:

  expression—A variable that represents a ListObject object.

  Target—A single-dimension Variant array that contains two to three elements: the URL of SharePoint server, the display name of the list, and, optionally, the description of the list.

  LinkSource—A Boolean that, when set to True, creates a new link to a new SharePoint list and that, when set to False, keeps the link to the current SharePoint list and replaces that list, or, if there is no current link, creates a new list on SharePoint without linking to it.

  Refresh Method

  The Refresh method refreshes the Table from its external source, if linked. If the Table is not linked to an external data source, an error is generated. Any changes made to the Table since the last refresh are lost.

  Resize Method

  The Resize method changes the Table range to the range provided. The method takes one parameter, Range, which specifies the new Table range. If the header row is enabled, the new range must include at least one header row cell. If the header row is disabled, the new range must include at least one cell in the first data body range row. Columns can be added and removed from either end, and rows can be added and removed from the bottom. When you remove rows and columns, any existing data in the removed rows and columns remains but is now outside the Table. When you add rows and columns, any data in the additional cells is added to the Table. After resizing, structured references in formulas in cells no longer in the Table are converted to absolute standard cell referencing.

  If a Table is linked to a SharePoint list, you can add and remove only rows. Trying to add or remove columns in a linked Table generates an error.

  Unlink Method

  The Unlink method removes any external data link if it exists.

  Unlist Method

  The Unlist method converts a Table to a regular range of cells. It is the equivalent of selecting {TABLE TOOLS} DESIGN | Tools | Convert to Range. Structured references in formulas are converted to absolute standard cell referencing.

  Other Methods

  The following sections describe more common methods used when writing code for Tables. These methods are often primary tools used when working with Tables in VBA. H
aving a basic understanding of how they work will help you write more robust and efficient code.

  ListObject Object's Add Method

  The ListObject object's Add method adds a new Table, using an existing list of data or another source. When you use an existing header, numeric values are converted to text, and any duplicate headers are made unique by appending integer values. Here is the syntax for this method:

  expression.Add(SourceType, Source, LinkSource, XlListObjectHasHeaders, Destination, TableStyleName)

  Where:

  expression—A variable that represents a ListObjects object.

  SourceType—Passes an XlListObjectSourceType constant that specifies the kind of source used to create the Table. It is optional. If omitted, xlSrcRange is assumed.

  Source—When SourceType is xlSrcRange, then pass a Range object that represents the range of data values to convert into a Table. It is optional. If omitted, the current selection is used. When SourceType is xlSrcExternal, pass an array of string values specifying a connection to the source, where the elements in the array are:0—The URL of the SharePoint site

  1—The SharePoint list name

  2—The ViewGUID

  LinkSource—A Boolean value that indicates whether an external data source is to be linked to the ListObject object. If SourceType is xlSrcExternal, then the default is True, and this parameter does not need to be passed. If a value is passed and SourceType is xlSrcRange, then an error is generated.

  XlListObjectHasHeaders—Passes an XlYesNoGuess constant type (xlYes, xlNo, or xlGuess), which indicates whether the data being imported has column labels. If Source does not contain headers, Excel automatically generates headers. It is optional. If omitted, xlGuess is assumed. Note that this parameter's name should be HasHeaders but, when the method was implemented, the developers mistakenly used XlListObjectHasHeaders instead.

  Destination—Passes a Range object that specifies a single-cell reference as the destination for the top-left corner of the Table. An error is generated if the range refers to more than one cell. This parameter must be specified if SourceType is xlSrcExternal. It is ignored if SourceType is set to xlSrcRange. The destination range must be on the worksheet that contains the ListObjects collection specified by the expression. Columns are inserted before the destination range to fit the new list, preventing existing data from being overwritten. It is optional.

  TableStyleName—Passes the name of the style name to be applied to the Table when created. It is optional. If TableStyleName is omitted, the default style is applied.

  This method returns a ListObject object that represents the new list object.

  The following are the XlListObjectSourceType constants:

  xlSrcExternal or 0—The source is an external data source (for example, Microsoft SharePoint Foundation site).

  xlSrcModel or 4—The source is a Power Pivot Data Model.

  xlSrcQuery or 3—The source is a query.

  xlSrcRange or 1—The source is a range of existing data.

  xlSrcXml or 2—The source is XML.

  ListRows Object's Add Method

  The ListRows object's Add method inserts one new row into the Table at the position specified. Here is the syntax for the method:

  expression.Add(Position, AlwaysInsert)

  Where:

  expression—A variable that represents a ListRows object.

  Position—An Integer that specifies the relative position of the new row. The new row is inserted above the current row at that position. It is optional. If omitted, the new row is appended to the bottom of the Table.

  AlwaysInsert—A Boolean that specifies whether to always shift data in cells below the last row of the Table when the new row is inserted, regardless of whether the row below the Table is empty. If True, the cells below the Table are shifted down one row. If False, if the row below the Table is empty, the Table expands to occupy (append) that row without shifting cells below it; if the row below the Table contains data, those cells are shifted down when the new row is inserted.

  The Add method returns a ListRow object that represents the new row.

  ListRow Object's Delete Method

  The ListRow object's Delete method deletes the Table row represented by the ListRow object.

  ListColumns Object's Add Method

  The ListColumns object's Add method inserts one new column into the Table at the position specified. Here's the syntax for this method:

  expression.Add(Position)

  Where:

  expression—A variable that represents a ListColumns object.

  Position—An Integer that specifies the relative position of the new column. The new column is inserted before the current column at that position. It is optional. If omitted the new column is appended to the right side of the Table.

  This method returns a ListColumn object that represents the new column.

  ListColumn Object's Delete Method

  The ListColumn object's Delete method deletes the Table column represented by the ListColumn object.

  Range Object's AutoFilter Method

  You can use the AutoFilter method to create AutoFilter criteria for a column, clear the filter criteria for a column, or toggle the AutoFilter status for the range. To inspect the AutoFilter status and settings, use the ListObject object's AutoFilter property, which has the following syntax:

  expression.AutoFilter(Field, Criteria1, Operator, Criteria2, VisibleDropDown)

  Where:

  expression—An expression that returns a Range object.

  Field—The Integer offset of the field on which to base the filter from the left of the list, where the leftmost field is field 1. It is optional. If omitted, the AutoFilter status (enabled/disabled) is toggled for the entire range. Disabling the AutoFilter removes the AutoFilter drop-down controls.

  Criteria1—The criteria as a number, a string, or an array—for example, 20 or Expense. Use = to find blank fields. Use <> to find nonblank fields. To find multiple strings, use the Array function with an Operator value of xlFilterValues—for example, Array("Value 1", "Value 2"). The wildcard characters * (one or more characters) and ? (any single character) can be used. If the Operator is xlTop10Items, Criteria1 specifies the number of items, such as 10. When Operator is xlFilterDynamic, Criteria1 is an XlDynamicFilterCriteria constant. (See the description below.) It is optional. If this argument is omitted, the criteria for the column is cleared.

  Operator—Passes an XlAutoFilterOperator constant specifying the type of filter. It is optional. If omitted, then 0 is assumed, which has no value, and Crtiteria1 is treated as a simple value to find; in this case, if an array of values is passed in Criteria1, then only the last value is used. When omitted and Criteria1 is not specified, the effect is to clear the filter for the specified column.

  Criteria2—The second criteria as a number, a string, or an array. It is used with Criteria1 and Operator to construct compound criteria or when Operator is xlFilterValues and date and times are filtered. (See the description for xlFilterValues below for details on filtering date and time values.) It is optional.

  VisibleDropDown—Passes True to display the AutoFilter drop-down arrow for the filtered field and False to hide the AutoFilter drop-down for the filtered field. It is optional. If omitted, True is assumed.

  If setting filter criteria, then the value returned is the Field value. If toggling the AutoFilter status, then True is returned.

  These are the XlAutoFilterOperator constants:

  xlAnd or 1—Filters with a logical AND of Criteria1 and Criteria2. Both Criteria1 and Criteria2 are strings specifying a condition—for example, ">0" and "<100" for greater than zero and less than 100.

  xlBottom10Items or 4—Finds the lowest-valued items displayed, where the number of items is specified as a number or string in Criter
ia1—for example, 5 or "5", meaning the five smallest items. The column specified by Field must contain at least one number, or an error occurs.

  xlBottom10Percent or 6—Find the lowest-valued items displayed, where the percentage is specified as a number or string in Criteria1—for example, 20 or "20" for the items in the bottom 20%. The column specified by Field must contain at least one number, or an error occurs.

  xlFilterCellColor or 8—Finds the color of the cell where the color is specified as an RGB value in Criteria1.

  xlFilterDynamic or 11—A dynamic filter where the filter criteria is specified as an XlDynamicFilterCriteria value in Criteria1. A dynamic filter is one that changes depending on some other value, such as today's date or the average of the values in the column.

  xlFilterFontColor or 9—Finds the color of the font, where the color is specified as an RGB value in Criteria1.

  xlFilterIcon or 10—Finds the icon, where the icon is specified in Criteria1. Icons are retrieved from ActiveWorkbook's IconSets property. The IconSets property is a collection of IconSets objects where each IconSet is a collection of a series of icons, such as the "3 Arrows (Colored)" set. The example below retrieves the first icon in the "3 Arrows (Colored)" set:ActiveWorkbook.IconSets(XlIconSet.xl3Arrows).Item(1)

  TIP

  Note the use of the enumerated list identifier XlIconSet, which provides an easy way to remember the name of each icon set: Entering “XlIconSet.” (including the period) invokes an IntelliSense pop-up list of all available icon set references.

  xlFilterValues or 7—Finds multiple values specified as an array in Criteria1 or Criteria2. Criteria1 is used when finding a simple list of values, such as "Value 1" and "Value 2". The values in the array must each be a string and must match exactly the displayed value if numeric; for example, if matching values displayed as "$25.00", the value passed must be "$25.00". Criteria2 is used when finding dates and times. When finding dates and times, the array is passed as an array of pairs of values, where the first value of each pair is a type of search and the second value is a date. The types of date/time searches are:0—Finds items in the same year as the following paired date/time value.