Working with Tables in Excel 2007 (VBA)

Working with Tab= les in Excel 2007 (VBA)

Jan Karel Pieterse is a self-employed Excel developer and = the owner of JKP Application Development Services, founded in 2003. He has ov= er 20 years of corporate business experience and is an expert in Excel and V= BA. Visit his website at http://www.jkp-ad= s.com for information on his services, many in-depth articles on Excel, and free utilities.

Introduction<= o:p>

In Worki= ng with Tables in Excel 2007 I promised to add a page about working with t= hose tables in VBA too. Well, here you go.

It's a ListOb= ject!

On the VBA side there seems to be nothing new about Tables. They are addressed as ListObjects, a collection that was introduced with Excel 2003.= But there are significant changes to this part of the object model and I am only going to touch on the basic parts here.

Creating a ta= ble

Converting a range to a table starts with the same code as in Excel 2003= :

Sub CreateTable()
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$B$1:$D$16"), , xlYes).Name =3D _
        "Table1"
        'No go in 2003
    ActiveSheet.ListObjects("Table1").TableSt= yle =3D "TableStyleLight2"
End Sub<= /span>

But the new stuff is right there already: TableStyles. A collection of objects which are a member of the Workbook object. This gives rise to some oddities. You can change the formatting of a tableStyle, e.g. like this:

Sub ChangeTableStyles()
    'No Go in Excel 2003<= /span>
    ActiveWorkbook.TableStyles(2).TableStyleElements(xl= WholeTable) _
        .Borders(xlEdgeBottom).LineStyle= =3D xlDash
End Sub<= /span>

This changes the linestyle of the bottom of your table. But hold your horses! If you have any other workbook open, all tables with the same tablestyle appear in your changed style! But if you save your file, close E= xcel and open Excel again with the file, the changes are gone. This is because you've just changed a built-in tablestyle. If you ask me, I find it strange that the Workbook is a tablestyles' parent, whereas built-in table styles behave as if being bound to the Application object.

If you want full control over your table style, you'd better duplicate a built-in style and modify and apply that style to your table.

Listing the t= ables

Let's start with finding all tables on the active worksheet:

Sub FindAllTablesOnSheet()
    Dim oSh As Worksheet
    Dim oLo As ListObject
    Set oSh =3D ActiveSheet
    For Each oLo In oSh.ListObjects
        Application.Goto oLo.Range<= br>         MsgBox "Table found: " & oLo.Name & ", " & oLo.Range.Address
    Next
End Sub<= /span>

This snippet of code works exactly the same in Excel 2003, so nothing new there (well, that is, in 2003 those tables ARE called Lists).

Selecting par= ts of tables

You might need to work with specific parts of a table. Here is a couple = of examples on how to achieve that. The code comments show you where Excel 2003 differs from 2007.

Sub SelectingPartOfTable()
    Dim oSh As Worksheet
    Set oSh =3D ActiveSheet
    '1: with the listobje= ct
    With oSh.ListObjects("Table1")
        MsgBox .Name
        'Select entire table
        .Range.Select
        'Select just the data of the entire table
        .DataBodyRange.Select
        'Select third column
        .ListColumns(3).Range.Selec= t
        'Select only data of first column
        'No go in 2003
        .ListColumns(1).DataBodyRan= ge.Select
        'Select just row 4 (header row doesn't count!)
        .ListRows(4).Range.Select     End With

    'No go in 2003=
    '2: with the range ob= ject
    'select an entire col= umn (data only)
    oSh.Range("Table1[Column2]").Select
    'select an entire col= umn (data plus header)
    oSh.Range("Table1[[#All],[Column1]]").Sel= ect
    'select entire data s= ection of table
    oSh.Range("Table1").Select
    'select entire table<= /span>
    oSh.Range("Table1[#All]").Select
    'Select one row in ta= ble
    oSh.Range("A5:F5").Select
End Sub<= /span>

As you may have spotted, Excel 2007 handles tables like they are range names. Well, that is exactly what is going on. After inserting a table, a r= ange name is defined automatically. These range names are special though. Excel controls them entirely. You cannot delete them and they get renamed automatically when you change a table's name. Remove a table (convert back = to range) and the defined name is removed as well.

Inserting row= s and columns

Another part in which lists already had most of the functionality. Just a few new things have been added, like the "AlwaysInsert" argument = to the ListRows.Add method:

Sub TableInsertingExamples()
'insert at specific position
    Selection.ListObject.ListColumns.Add Position:=3D4<= br> 'insert right
    Selection.ListObject.ListColumns.Add
'insert above
    Selection.ListObject.ListRows.Add (11)
'NoGo in 2003
'insert below
    Selection.ListObject.ListRows.Add AlwaysInsert:=3D<= span style=3D'color:#00007F'>True
End Sub<= /span>

Adding a comm= ent to a table

This is something Excel 2003 cannot do and is related to the fact that a table is a range name. Adding a comment to a table through the UI is a challenge, because you have to go to the Name Manager to do that. In VBA the syntax is:

Sub AddComment2Table()
    Dim oSh As Worksheet
    Set oSh =3D ActiveSheet
    'NoGo in 2003<= br>     'add a comment to the= table (shows as a comment to
    'the rangename that a= table is associated with automatically)
    'Note that such a ran= ge name cannot be deleted!!
    'The range name is re= moved as soon as the table is converted to a range
    oSh.ListObjects("Table1").Comment =3D "This is a table's comment"
End Sub<= /span>

Convert a tab= le back to a normal range

That is simple and uses the identical syntax as 2003:

Sub RemoveTableStyle()
    Dim oSh As Worksheet
    Set oSh =3D ActiveSheet
    'remove table or list= style
    oSh.ListObjects("Table1").Unlist
End Sub<= /span>

Special stuff: Sorting and filtering

With Excel 2007 we get a whole new set of filtering and sorting options.= I'm only showing a tiny bit here, a Sort on cell color (orangish) and a filter = on the font color. The code below doesn't work in Excel 2003. A List in 2003 o= nly has the default sort and autofilter possibilities we have known since Excel= 5 and which had hardly been expanded at all in the past 12 years or so.

Sub SortingAndFiltering()
'NoGo in 2003
    With ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1"= ;)

        .Sort.SortFields.Clear
        .Sort.SortFields.Add( _
            &nb= sp;   Range("Table1[[#All],[Column2]]"), xlSortOnCellColor, xlAscending, , _
            &nb= sp;   xlSortNormal).SortOnValue.Color =3D RGB(255, 235, 156)
        With .Sort
            .He= ader =3D xlYes
            .Ma= tchCase =3D False
            .Or= ientation =3D xlTopToBottom
            .So= rtMethod =3D xlPinYin
            .Ap= ply
        End With
    End With
    'Only old autofilter = stuff works in 2003
    ActiveSheet.ListObjects("Table1").Range.A= utoFilter Field:=3D2, _
        Criteria1:=3DRGB(156, 0, 6), Operator:=3DxlFilterFontColor
End Sub<= /span>

Wrap Up<= /o:p>

Of course there is more to learn and know about tables and lists. A good= way to come acquainted with the VBA behind them is by recording macro's while fooling around with them. Luckily Microsoft did include the table object if= it comes to recording your actions, unlike the omission on the charting side..= .

Comments or suggestions for this article? Please = send= us your feedback!