JKP Application Development Services.

                    Microsoft Office Application Development

Excel 2007 Tables (VBA)

Up • Defined Names • Corrupt Files • Create Addins • Startup Problems • Chart an Equation • Show Picture • Round2Digits • Control Events • Custom Find • FormulaWrapper • Disable Events • Workbook Open Bug • WebQuery • Register UDFs • Undo With Excel VBA • Select a range (VBA) • Transpose Table • Docking VBE Windows • Excel 2007 Tables • Excel 2007 Tables (VBA) • Update An Addin • Addin Installation • Object Lister • Excel 2007 FileFormat • Catch Paste • Listbox AutoSize • Fix Links to UDFs

•  •

Search Zoeken

WWW
This site

Go to Ga Naar
Home
Up

Donate Donaties
Did you find something helpful on my site? Consider a donation!
Heeft u iets gevonden waar u wat aan had? Overweeg dan een donatie!

 

Working with Tables in Excel 2007 (VBA)

Introduction

In Working with Tables in Excel 2007 I promised to add a page about working with those tables in VBA too. Well, here you go.

It's a ListObject!

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 table

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 = _
        "Table1"
        'No go in 2003
    ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight2"
End Sub

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
    ActiveWorkbook.TableStyles(2).TableStyleElements(xlWholeTable) _
        .Borders(xlEdgeBottom).LineStyle = xlDash
End Sub

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 Excel 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 tables

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

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

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 parts 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 = ActiveSheet
    '1: with the listobject
    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.Select
        'Select only data of first column
        'No go in 2003
        .ListColumns(1).DataBodyRange.Select
        'Select just row 4 (header row doesn't count!)
        .ListRows(4).Range.Select
    End With
    
    'No go in 2003
    '2: with the range object
    'select an entire column (data only)
    oSh.Range("Table1[Column2]").Select
    'select an entire column (data plus header)
    oSh.Range("Table1[[#All],[Column1]]").Select
    'select entire data section of table
    oSh.Range("Table1").Select
    'select entire table
    oSh.Range("Table1[#All]").Select
    'Select one row in table
    oSh.Range("A5:F5").Select
End Sub

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 range 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 rows 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:=4
'insert right
    Selection.ListObject.ListColumns.Add
'insert above
    Selection.ListObject.ListRows.Add (11)
'NoGo in 2003
'insert below
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
End Sub

Adding a comment 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 = ActiveSheet
    'NoGo in 2003
    'add a comment to the table (shows as a comment to
    'the rangename that a table is associated with automatically)
    'Note that such a range name cannot be deleted!!
    'The range name is removed as soon as the table is converted to a range
    oSh.ListObjects("Table1").Comment = "This is a table's comment"
End Sub

Convert a table back to a normal range

That is simple and uses the identical syntax as 2003:

Sub RemoveTableStyle()
    Dim oSh As Worksheet
    Set oSh = ActiveSheet
    'remove table or list style
    oSh.ListObjects("Table1").Unlist
End Sub

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 only 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( _
                Range("Table1[[#All],[Column2]]"), xlSortOnCellColor, xlAscending, , _
                xlSortNormal).SortOnValue.Color = RGB(255, 235, 156)
        With .Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
    'Only old autofilter stuff works in 2003
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=2, _
        Criteria1:=RGB(156, 0, 6), Operator:=xlFilterFontColor
End Sub

Wrap Up

Of course there is more to learn and know about tables and lists. A good way to come acquinted 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...

Feedback

Since you have managed to reach the end of this article, maybe you would care to write me a small message, expressing your opinion on this article?

Click here to write an email message to me.

You may also rate this article:

(Rated: 287 times, average rating: 6.3)

Comments

Showing last 15 comments of 18 in total (Show All Comments):


Comment by: Jan Karel Pieterse (11/10/2007 8:16:22 AM)

Selection.AutoFilter

Will turn on autofilter

ActiveSheet.AutoFilterMode = False

will turn it off.


Comment by: akj (11/15/2007 11:31:19 PM)

Excellent


Comment by: Johan Nordberg (12/8/2007 2:20:19 PM)

An important difference between Excel 2003 lists and Excel 2007 tables is that the InsertRowRange property of the ListObject only works when the table is empty. When the table has data InsertRowRange returns nothing.

In that case you have to get the last row of the table and move down one row from that.

If anyone has a better solution, please let me know...

// Johan Nordberg


Comment by: Jan Karel Pieterse (12/9/2007 3:47:59 AM)

Hi Johan,

Thanks for the comment. I think you have found the only solution to this problem indeed.


Comment by: Andrei Sheshka (1/9/2008 8:07:33 AM)

Hi Johan!

At first you must activate ListObject to get InsertRowRange in Excel 2003.

Function GetInsertRow(objList As ListObject) As Range

objList.Parent.Activate

objList.Range.Activate

Set GetInsertRow = objList.InsertRowRange

End Function

Sub Test_GetInsertRow()

Dim lo As ListObject

Dim objListRng As Range

Set lo = Worksheets("Sheet3").ListObjects(1)

Set objListRng = GetInsertRow(lo)

objListRng.Select

End Sub


Comment by: Jose Manuel (1/31/2008 12:57:16 PM)

Hi to all!

After 'insert below

Selection.ListObject.ListRows.Add AlwaysInsert:=True

How can I select the cell in the first column of the new row?

Thanks in advance


Comment by: Jan Karel Pieterse (2/3/2008 10:20:12 PM)

Hi Jose,

Like this:

Selection.ListObject.Range.End(xlDown).Select


Comment by: Aindril De (5/29/2008 12:46:09 AM)

This is real excellent stuff.

Can anyone advice any book that is available, that helps differentiate Excel 2003

VBA vs Excel 2007 VBA?

Thanks in advance


Comment by: Jan Karel Pieterse (5/29/2008 2:06:33 AM)

Hi Aindril,

I'd recommend "Excel 2007 VBA programming Reference" (Stephen Bullen et al)

and

"Excel 2007 Power Programming with VBA" (John Walkenbach)


Comment by: Adele Summers (6/9/2008 8:19:09 AM)

This may seem like a simple question, but can you set the data source of a table to

come from a sheet other than the current sheet that you are on?


Comment by: Jan Karel Pieterse (6/9/2008 10:18:44 AM)

Hi Adele,

I'm not sure what you're looking for. What do you mean by "the data source"? Which

option are you referring to?


Comment by: Martin (6/19/2008 8:26:16 AM)

i need call the dialog "Modify table Quick style"


Comment by: Jan Karel Pieterse (6/19/2008 10:51:14 AM)

Hi Martin,

I had a look at Application.Dialogs(xlDialog......), but I could not find it.


Comment by: Ann Marie (7/1/2008 6:03:09 AM)

How and where do you turn off screenupdating in Office Excel 2007?

Thank you.


Comment by: Jan Karel Pieterse (7/1/2008 10:21:56 AM)

Hi,

Application.ScreenUpdating=False

at the start of your code

and

Application.ScreenUpdating=True

at the end.


Add a comment too!!!

Please enter your name (required, will be shown):

Your E-Mail address (optional; will not be shown, nor be used to send you unsolicited information):

Your comments on this page (will be shown):

    Subscribe in a readerpowered by longhead.com

Use the contact page to issue questions or comments about this website.
Copyright © 2003-2008 JKP Application Development Services