List Processing

Excel tables frequently contain lists. In the past, the DATA menu has offered a host of commands for processing such lists (such as sorting or the display of an input mask). New in Excel 2003 is the possibility of marking a range of cells explicitly as a list (DataListCreate List). The range of cells is then framed in blue, and simultaneously , the toolbar List and XML, with which the various processing steps can be simply carried out (for example, deleting a column or row from a list), appears. At the bottom of the list, a blue star shows the position where new data can be input. This behavior is reminiscent of the database program Access.

For VBA programmers there are various new objects for manipulation of such lists; their names begin with List ( ListObject, ListColumn , etc.).

To transform a range of cells into a list, execute ListObjects.Add . As result you obtain a ListObject . (A new ListObject can also be generated on the basis of an existing XML file or an external data source. In this case, the constant xlSrcXml or xlSrcExternal must be passed to Add . Moreover, the data source must be described in the additional parameters.)

 Dim lo As ListObject Set lo = ActiveSheet.ListObjects.Add( _   xlSrcRange, Range("$A:$D"), HasHeaders := True) lo.Name = "list1" 

The size of a list can be changed with the Resize method. (Another possibility is to add or delete columns or rows, which will be described later.)

 lo.Resize Range("$A:$D") 

A significant advantage of a ListObject as compared to direct processing of a range of cells is that the individual rows and columns can be easily addressed with ListRows ( n ) and ListColumns ( n ). (Here n=1 , as usual in VBA, denotes the first row or column.)

The following lines of code demonstrate how a new row and a new column can be added and then deleted. Noteworthy here is that cells located to the right of (respectively beneath ) the list are automatically moved out of the way to make room for the expansion of the list or to fill up empty space that has been created.

 lo.ListRows.Add 3                'insert new row lo.ListColumns.Add 4             'insert new column lo.ListRows(3).Delete            'delete the new row lo.ListColumns(4).Delete         'delete the new column 

The properties of the ListRow and ListColumn objects make it possible to change certain properties of the rows and columns. For example, you can use the property TotalsCalculation to specify for each column which function (sum, count, minimum, maximum, etc.) should be used to calculate the result field. Whether the result field should be displayed beneath the list is controlled by the ListObject property showTotals .

To transform a list back into a normal range of cells, apply the method Unlist to the ListObject . The data are not changed. The range is no longer represented internally as a list and no longer surrounded by a blue border.

 lo.Unlist 

On the other hand, if you want to delete all the data, execute Delete :

 lo.Delete 

To determine whether a particular cell is part of a list, you can evaluate the property rangeobject . ListObject . The property contains either Nothing or refers to the relevant ListObject .