Building A Workbook Table Class

Building A Workbook Table Class

Tables in Excel 2010 are powerful tools that help me hugely in my work as a data analyst. I use them every day.

Excel 2003 tables (called lists) were worksheet-level objects.  You could give two tables the same name if they were on different sheets, and not hear a word of complaint out of Excel.  In XL 2010 they are workbook-level objects, so you can use any given table name only once in a workbook.  They are also workbook-level in that you can reference a table from any formula in any sheet just by beginning to type its name, same as with a function or a named range.

In VBA, tables, or listobjects, as they continue to be known, are still worksheet-level objects.  You can declare a Worksheet.Listobject but not a Workbook.Listobject.

I’m working on a project where I wanted a Workbook.Listobject class, so I built one.  To do so, I created a new class called cWorkbookTables and added the following code:

Dim m_wb As Excel.Workbook
Dim m_Tables As Collection

Public Property Get NewEnum() As IUnknown
'the following line, added in a text editor,
'creates the ability to cycle through the items with For Each
'Attribute NewEnum.VB_UserMemId = -4
Set NewEnum = m_Tables.[_NewEnum]
End Property

Public Function Initialize(WbWithTables As Excel.Workbook)
Set m_wb = WbWithTables
End Function

Public Sub Refresh()
Dim ws As Excel.Worksheet
Dim lo As Excel.ListObject

Set m_Tables = New Collection
For Each ws In m_wb.Worksheets
  For Each lo In ws.ListObjects
    m_Tables.Add lo, lo.Name
  Next lo
Next ws
End Sub

Public Property Get Item(Index As Variant) As Excel.ListObject
'the following line, added in a text editor,
'sets Item as the default property of the class
'Attribute Item.VB_UserMemId = 0
Set Item = m_Tables(Index)
End Property

Public Property Get Count()
Count = m_Tables.Count
End Property

Property Get Exists(Index As Variant) As Boolean
Dim test As Variant
On Error Resume Next
Set test = m_Tables(Index)
Exists = Err.Number = 0
End Property

Note that the code above includes two lines that have to be added in a text editor. The commented lines set the default property for the class, which is Item, and add the ability to enumerate through the class members in a For Each loop. The processes for adding these very nice features are described in various places on the web, including these instructions at Chip Pearson’s site.

In the same workbook I added two tables and ran the code below in a regular module:

Sub TestTableClass()
Dim clsTables As cWorkbookTables
Dim lo As Excel.ListObject
Dim i As Long

Set clsTables = New cWorkbookTables
With clsTables
  .Initialize ThisWorkbook
  Debug.Print "Number of tables in workbook: " & .Count
  For i = 1 To .Count
    Debug.Print "clsTables(" & i & ") name: " & .Item(i).Name
  Next i
  For Each lo In clsTables
    Debug.Print lo.Name & " " & lo.DataBodyRange.Address
  Next lo
End With
Debug.Print "There is a Table1: " & clsTables.Exists("Table1")
Debug.Print "There is a Table3: " & clsTables.Exists("Table3")
End Sub

The results in the Immediate Window look like this:

Number of tables in workbook: 2
clsTables(1) name: Table1
clsTables(2) name: Table2
Table1 $A$2:$B$4
Table2 $A$2:$B$4
There is a Table1: True
There is a Table3: False

For the reasons mentioned at the beginning of this post, this class only works in XL 2007 and 2010.

Workbook Tables Class Intellisense

One thought on “Building A Workbook Table Class

  1. Pingback: Daily Dose of Excel » Blog Archive » Extending Built-in Classes

Speak Your Mind

Your email address will not be published. Required fields are marked *

To post code, do this: <code> your vba here </code>