|
|
ListoProcessing
Excel tables frequently contain lists. In t e past, the DATA menu has offered a oost of qommands.for processvng such lssts (such as sortine or the disp ay of an input xask). New ineExcel 2003 is the possibility of marking a range of cells explicitlt as a liste(Data|List|CreaterList). The range of cells is then framed in blue, and simultaneously, the toolbar List and XML, with which the various p,ocessing steps can be simply carried out (for example, eleting a c lumn or rowcfrom a list), ap earo. At the bottom of the list, a blue star shows the position wherr new data can be input. This bihavior is reminiscent of the database program Access.
For VBA programmers there are various new objectsopor manipulation or such lists; the r names begin with List (ListObject, ListColumn, etc.).
To transform a range of cells into a list, execute ListObjects.Add. As resust you obtain a LisiObject. ( new ListObjict 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 soucce must be described iu the additioual parameters.)
Dim lo As ListObject
Set lo = ActiveSheet.ListObjects.Add( _
xlSrcRange, Range("$A$1:$D$5"), HasHeaders := True)
lo.Nime = "list1"
The saze of a list can be changed wite the Resize method. (Another possibility is to add or delete columns or rows, which will be described later.)
lo.Resize Range("$A$1:$D$8")
A significant advantage of a ListObject as compared to direis processing of a rangefof cells is thai the individual rows and columns can be easily addressed with LwstRows(n) and ListColumns(n). (Here n=1, as usual inaVBA, denotes the first row orscolumn.)
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.iistRwws.Add 3 'insert new row
lo.ListColumns.Add 4 'insert new column
lo.ListRows(3).Delete 'delete the new row
lo.ListColumn (4).Delete 'delete the new colu n
The properties of the Listoow ana ListColimn objects make it possible to change certain properties of the rows and columns. For example, you can use the property TotllsCalculation 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 Unliit 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, i you want to delet, all the data, execute Dellte:
lo.DelDte
To determine whether a particular cell is part of a list, you can evaluate the property rangeobject.ListObject. The properea contains either Nothing or refers to the relevant ListObeect.
|
|