Class ListObjects (Excel VBA)

A collection of all the ListObject objects on a worksheet. Each ListObject object represents a table on the worksheet. To use a ListObjects class variable it first needs to be instantiated, for example

Dim los as ListObjects
Set los = ActiveSheet.ListObjects

For Each

Here is an example of processing the ListObjects items in a collection.

Dim lstobj As ListObject
For Each lstobj In Application.ListObjects
	
Next lstobj

Add

Creates a new list object.

When the list has headers, the first row of cells will be converted to Text, if not already set to text. The conversion will be based on the visible text for the cell. This means that if there is a date value with a Date format that changes with locale, the conversion to a list might produce different results depending on the current system locale. Moreover, if there are two cells in the header row that have the same visible text, an incremental Integer will be appended to make each column header unique.

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

Dim lstobj As ListObject
Set lstobj = ActiveSheet.ListObjects.Add

Arguments

Optional arguments

The following arguments are optional

SourceType (XlListObjectSourceType) - Indicates the kind of source for the query.

Possible values are

Source (Range) - When SourceType = xlSrcRange: A Range object representing the data source. If omitted, the Source will default to the range returned by list range detection code. When SourceType = xlSrcExternal: An array of String values specifying a connection to the source, containing the following elements: 0 - URL to SharePoint site 1 - ListName 2 - ViewGUID.

LinkSource (Boolean) - Indicates whether an external data source is to be linked to the ListObject object. If SourceType is xlSrcExternal, the default is True. Invalid if SourceType is xlSrcRange, and will return an error if not omitted.

XlListObjectHasHeaders (XlYesNoGuess) - An XlYesNoGuess constant that indicates whether the data being imported has column labels. If the Source does not contain headers, Excel will automatically generate headers. Default value: xlGuess.

Possible return values are xlGuess - Excel determines whether there is a header, and where it is, if there is one, xlNo - Default. The entire range should be sorted, xlYes - The entire range should not be sorted.

Destination (XlListObjectSourceType) - A Range object specifying a single-cell reference as the destination for the top-left corner of the new list object. If the Range object refers to more than one cell, an error is generated. The Destination argument must be specified when SourceType is set to xlSrcExternal. The Destination argument is ignored if SourceType is set to xlSrcRange. The destination range must be on the worksheet that contains the ListObjects collection specified by expression. New columns will be inserted at the Destination to fit the new list. Therefore, existing data will not be overwritten.

Possible values are

TableStyleName (String) - The name of a TableStyle; for example "TableStyleLight1".

Count

Returns an Integer value that represents the number of objects in the collection.

Dim lngCount As Long
lngCount = ActiveSheet.ListObjects.Count

Item

Returns a single object from a collection.

Item (Index)

Index: The name or index number of the object.

Dim lstobjItem As ListObject
Set lstobjItem = ActiveSheet.ListObjects(Index:=1)