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_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
Refresh
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:
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.
Pingback: Daily Dose of Excel » Blog Archive » Extending Built-in Classes
Hi! Doug thanks for this clear explanation. 10 years forward and it’s still useful!
If others read this, I found that the comments you added to note the attributes caused the VBIDE to ignore them.
So when testing it, remove the commented lines like:
‘the following line, added in a text editor,
‘sets Item as the default property of the class
‘Attribute Item.VB_UserMemId = 0