Tables Archives - Page 2 of 2

Filter as you type with FilterMatic!

Posted on October 27, 2013 by Doug Glancy

Whenever possible I employ lookups, crosswalks and whining to avoid correcting data by hand. Sometimes my best efforts fail, and I end up fixing lists one funky cell at a time. The horror! Happily I’ve created FilterMatic to ease the pain.

Say for instance, you’re giving a birthday party for a hundred or so of your oldest friends, all born on October 26. You’ve got this handy reference list of names, years of birth and notable accomplishment:

source:http://www.historyorb.com/today/birthdays.php

Meanwhile you’ve got another, very similar, list that calculates their age – with a lookup to the “year” column on the first list – so you can embarrass them by putting it on their place setting. The problem is some names are misspelled and you’re getting #N/A’s.

So you filter to just the #N/A’s and start to fix them. Wouldn’t it be nice if, as you fixed the errors, they were automatically filtered away? It’s true that in modern Excel tables you can do this with right-click>Filter>Re-Apply. But now with FilterMatic your corrections are instantly whisked from sight!

In the example below, I’m fixing the names, at first without FilterMatic running. After the third fix, I start it up. The first thing that happens is the filter is re-applied. After that the filter is re-applied with every change to the table and each fixed row is filtered away.

FilterMatic™ works on worksheet and table filters, re-applying them on the active sheet whenever you change a cell within a filtered area. And with FilterMatic™ there’s no confusing buttons or messy dropdowns. Just turn it on to start filtering! Close it when you’re done! But wait, there’s more! Download now and receive a free puppy!

Here’s the code:

Private WithEvents app As Excel.Application
Private WithEvents wsActive As Excel.Worksheet
'My ShowModal Property must be set to False

Private Sub UserForm_Activate()
Set app = Application
If Not ActiveSheet Is Nothing Then
Set wsActive = ActiveSheet
FilterMatic
End If
End Sub

Private Sub app_SheetActivate(ByVal Sh As Object)
Set Sh = wsActive
End Sub

Private Sub wsActive_Change(ByVal Target As Range)
FilterMatic
End Sub

Private Sub lblFilterMatic_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'apply changes by double-clicking the form
FilterMatic
End Sub

Sub FilterMatic()
Dim lo As Excel.ListObject

Set lo = Selection.ListObject
'if the selection overlaps a table
If Not lo Is Nothing Then
With lo
'Table is in filter mode
If .ShowAutoFilter Then
.AutoFilter.ApplyFilter
End If
End With
Else
'It will only re-apply a worksheet-level filter if
'there's no tables on the sheet.
With wsActive
'if sheet is filtered
If .FilterMode = True Then
'if the selection overlaps the worksheet's filtered area
If Not Intersect(Selection, .AutoFilter.Range) Is Nothing Then
.AutoFilter.ApplyFilter
End If
End If
End With
End If
End Sub

When the form is opened an application object is created. This object monitors all “SheetActivate” events in Excel and sets the activated worksheet to wsActive. There are three events that trigger the filtering code: opening the form, changing something in wsActive or clicking the label in the center of the form.

The FilterMatic sub checks for both ListObject and worksheet-level filters. If the selection at the time of the change intersects one, then that filter is updated. Note that if a listobject is found, it won’t go on to re-apply a worksheet filter. I tried that and couldn’t imagine a reasonable worksheet design that would have both filter types, and anyways the results were weird. Also note that if you change two listobjects at once, the filter would only be applied to one of them: whichever is the Selection.ListObject.

Here’s a workbook with the code and the lists.

And be sure to join the email list so you won’t miss my next exciting product: PivotMasher!

Prompt to Add New Items to ComboBox or Data Validation

Posted on June 2, 2013 by Doug Glancy

Microsoft Access ComboBoxes have a handy NotinList event which allow you to check whether a value entered in a combobox is already in its list. If it’s not you can ask the user whether to add it. This post shows how to mimic that functionality in a combobox on a VBA userform. I also show how to do the same thing with a data validation list.

Creating a ComboBox NotInList Event

The key to doing this is checking the value of the ComboBox’s “MatchFound” property in its Exit event. If no match is found, we ask the user whether to add the item to the list of valid items (hats in this case). If the answer is “Yes” then a row with the hat is added to the table. If not, we clear the combobox and keep the focus on it. You can see this in action in the video above.

Here’s the code for the combobox’s Exit event:

Private Sub cboHats_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim loValidationSource As Excel.ListObject
Dim loRow As Excel.ListRow

'the Table with the list of valid hats
Set loValidationSource = wsTables.ListObjects("tblValidationSource")
With Me.cboHats
'We're only interested if these aren't true
If .MatchFound Or .Value = "" Or .Value = STARTING_VALUE Then
Exit Sub
End If
'If the hat entered isn't in list, prompt to add it
If MsgBox(.Value & " is not in the list. Add it?", vbYesNo + vbDefaultButton2 + vbQuestion) = vbYes Then
Set loRow = loValidationSource.ListRows.Add
loRow.Range.Cells(1).Value = .Value
SortSourceTable
RefreshComboList
Else
'if "no", keep focus on the ComboBox and set it's value to "Choose a hat"
Cancel = True
Me.cboHats.Value = STARTING_VALUE
End If
End With
End Sub

One important thing is that the combobox’s “MatchRequired” property must be set to False (which is the default). Otherwise the Exit will be preempted by an “Invalid Property Value” message from Excel.

Creating a Data Validation NotInList Event

As with the combobox version, we use an event to prompt the user whether to add an item that’s not in the list. This time we use our own “MatchFound” function to check against the data validation’s source list. Similar to setting the “Match Required” combobox property to False, the data validation version requires that the “Show error alert after invalid data is entered” is unchecked in the data validation setup dialog. This is obviously not the default:

Since I’m working in Excel 2010, I’ve created a single-column table (listobject) to hold the valid items. I then simply pointed the data validation’s Source property at the column, excluding the header. Because the source is in a table, it’s dynamic – it adjusts when you add or remove items from the column. No dynamic ranges are required, just select the cells:

Here’s the code from the ThisWorkbook module, which contains the Workbook_SheetChange event and the MatchFound function:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim cell As Excel.Range
Dim loValidationSource As Excel.ListObject
Dim loHatCollection As Excel.ListObject
Dim loRow As Excel.ListRow

'wsTables is the sheet's CodeName
If Not Sh Is wsTables Then
Exit Sub
End If
Set loValidationSource = wsTables.ListObjects("tblValidationSource")
Set loHatCollection = wsTables.ListObjects("tblHatCollection")
'only continue if change is in column with data validation
If Intersect(Target, loHatCollection.ListColumns("Hat Type").DataBodyRange) Is Nothing Then
Exit Sub
End If
With Intersect(Target, loHatCollection.ListColumns("Hat Type").DataBodyRange)
For Each cell In .Cells
If MatchFound(cell.Value) = False And cell.Value <> "" Then
If MsgBox(cell.Value & " is not in the list. Add it?", vbYesNo + vbDefaultButton2 + vbQuestion) = vbYes Then
Set loRow = loValidationSource.ListRows.Add
loRow.Range.Cells(1).Value = cell.Value2
Else
cell.ClearContents
End If
End If
Next cell
End With
SortSourceTable
End Sub

Function MatchFound(ValueToCheck As Variant) As Boolean
Dim loValidationSource As Excel.ListObject
Dim ValidationList As Excel.Range

Set loValidationSource = wsTables.ListObjects("tblValidationSource")
Set ValidationList = loValidationSource.ListColumns("Hats Validation List").DataBodyRange
MatchFound = Application.WorksheetFunction.CountIf(ValidationList, ValueToCheck) > 0
End Function

And here’s what it looks like in action:

The Sort object – Excel 2007 Onwards

My code uses VBA’s Sort object, which appeared in Excel 2007. I like the way it works. You add Sort Fields, just as you do in the user interface, and then apply the sort when needed. If you are using Excel 2003 or earlier you’d need to re-write the two sorting procedures to work with your version.

Also, if you are using Excel 2003 or earlier, see this Contextures post for a non-table way of automatically adding items to a data validation list. You could easily add the code to prompt the user whether to do so.

Download

Here’s a workbook with all the code for both versions.

Index/Sumproduct or Index/Match for Multiple Criteria Lookups?

Posted on June 4, 2012 by Doug Glancy

You can use an INDEX/MATCH formula to look up an item in a list, as explained in this Contextures page. It’s trickier than a VLOOKUP formula, but it can look to the left and adjusts well when data columns are added or deleted. The generic layout of a single-criterion INDEX/MATCH is:

=INDEX(ColumnToIndex,MATCH(ItemToMatch, ColumnWithMatch, 0))

The MATCH section results in a row number that gets applied to the ColumnToIndex.

When looking up items with more than one criteria, I like to use an INDEX/SUMPRODUCT formula, replacing the MATCH part of the single criterion formula with SUMPRODUCT array multiplication, as descibed by Chandoo. Very generically that looks like:

=INDEX(ColumnToIndex,SUMPRODUCT(Multiply a bunch of columns and criteria))

This post started as an explanation of that approach. But in looking at the Contextures “INDEX/MATCH – Example 4” in the link above, I think it might be better. So this post is now a comparison of the two approaches.

Below is a live, downloadable worksheet with pairs of double-criteria lookup formulas – an INDEX/SUMPRODUCT and INDEX/MATCH in each case. Each row has two lookups: the first returns the number of home runs for that team and year, the second looks up a relative ranking.

I actually broke the formulas into two columns, which is what I’d do in a real project. Columns I and J contain INDEX formulas which refer to the lookup formulas in column H. The INDEX formulas first check if the lookup in column H actually returned a row. IF not “NA” is returned.

I break the lookup formula into two parts like this for a couple of reasons. The first is that if there’s no match the SUMPRODUCT in column H returns 0, which is important to see. The second is there’s often more than one formula referring to the calculated row, so it’s more efficient to only calculate it once and then use it in the different INDEX formulas. In this case the row is used for both the column I and column J INDEX formulas.

Here are the two lookup formulas, in cells H2 and H3, that are used to match the correct row. They are matching 2006 for the year and CHC for the team:

=SUMPRODUCT(($A$1:$A$33=$F2)*($B$1:$B$33=$G2)*(ROW($A$1:$A$33)-(ROW($A$1)-1)))
=MATCH(1,($A$1:$A$33=$F3)*($B$1:$B$33=$G3),0)

At their core they contain the same logic: ($A$1:$A$33=$F2)*($B$1:$B$33=$G2), which multiplies TRUEs and FALSES to return an array of Ones and Zeros. Here’s the SUMPRODUCT version with the above section evaluated by highlighting it and pressing F9. Note the 1 in the 19th position:

The SUMPRODUCT then multiplies that array times an adjusted row number, while the MATCH version finds the position of the number 1 in the array. Both result in row 19.

As mentioned above, the SUMPRODUCT formula returns 0 if no match is found. The INDEX formulas in columns I and J need to deal with that, otherwise they will return incorrect results. For example, if you have something like =INDEX({1,2,3,4,5},0), the result is 1. You might be surprised that it returns anything at all, but the nature of an INDEX formula is that if the row argument is set to 0 it evaluates the entire column. The actual row returned depends on the row of the formula. Confused? Me too.

The MATCH version, on the other hand, returns “#N/A” if no match is found, which is probably what you’d expect, and therefore good. And it’s shorter, also good. The only pitfall is that it’s an array formula and must be entered using Ctrl-Shft-Enter.

I’ve tried to make both sets of formulas more robust by including the headers in the ranges – $A1, $B1, etc. That way if a line in inserted below the header and before the first row of data, the formulas still work. I also want to ensure that the formulas still work if rows are inserted above the table’s header. This causes more complications in the SUMPRODUCT version, in the (ROW($A$1:$A$33)-(ROW($A$1)-1)) part. If you know the table will always start in Row 1 then it can be simplified to ROW($A$1:$A$33)-1. The MATCH version doesn’t have this issue.

Below the first pair of formulas are two more pairs, showing the results if no match is found, and if multiple matches are found. When there’s no match, the INDEX formula result in “NA” in both cases.

If there’s more than one match the SUMPRODUCT version adds together the matched rows. This results in 41 in row 12. Since 41 is outside the ColumnToIndex range, the result is #REF!. The MATCH version returns the first match. Obviously some kind of check for duplicates is good, such as the conditional formatting used here to highlight rows 20 and 21 of the data.

This being Excel, there are other ways to skin this cat. Here’s a post by JP Pinto, with his explanation of these two approaches, plus a LOOKUP version. (There’s a small error in the SUMPRODUCT version which is addressed in the comments.)

So which do I prefer now? Well, even though the MATCH version is simpler in most respects, I tend to avoid array formulas when possible, mainly because I too often forget that they are array formulas, do some editing, don’t hit Ctrl-Shft-Enter and have a moment, or several, of disorientation staring at the #N/A result. So I’ll probably stick with INDEX/SUMPRODUCT.

What do you think?

Copy Table Data While Not Breaking References

Posted on April 5, 2012 by Doug Glancy

I’ve mentioned before I’m a big fan of tables in Excel 2010. One way I use them is in models, where each table represents a different scenario. The models let users create new scenarios, based either on an existing one or a blank template. Each version is stored in a table in its own worksheet. These tables always have the same fields (columns) but the values in the variable fields are different. The number of rows can differ from table to table.

I often want to copy, in VBA, the contents from a “source” to a “target” table. If I just copy the whole thing, the target table will be overwritten and renamed – something like “tblSource1” (adding a “1” to the source table name). That breaks any formulas referring to “tblTarget.” They’ll show a #REF error because they can’t find “tblTarget.” So I need code that copies the table data from tblSource to tblTarget without completely replacing tblTarget.

I’ve been writing code on a case-by-case basis, but thought that I’d generalize it a bit more. In addition to keeping the table’s identity intact, it should copy the source’s totals row if there is one, and turn off the target’s total row if there isn’t. The number of rows should increase or decrease to match the source. And, although I’ve only ever copied tables as values, I want the option to copy formulas.

I thought about dealing with a different number of columns but, at least in my uses so far, that shouldn’t happen. If I ever do try to accommodate models with changing numbers of fields, I think I’d do some testing before ever calling this code, and adjust the headers in another procedure.

So here’s what I came up with:

Sub CopyTableData(loSource As Excel.ListObject, loTarget As Excel.ListObject, Optional CopyFormulas As Boolean = False)
Dim FormulaCells As Excel.Range

With loTarget
If .DataBodyRange.Rows.Count <> loSource.DataBodyRange.Rows.Count Then
'have to clear target otherwise old table content may be outside new table
.DataBodyRange.Cells.Clear
'set target rows count to source rows count
.Resize .Range.Cells(1).Resize(loSource.HeaderRowRange.Rows.Count + _
loSource.DataBodyRange.Rows.Count, loSource.Range.Columns.Count)
End If
loSource.DataBodyRange.Copy Destination:=.DataBodyRange.Cells(1)
If CopyFormulas Then
On Error Resume Next
'any formulas?
Set FormulaCells = .DataBodyRange.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
'if yes, then replace any references to source table with target
If Not FormulaCells Is Nothing Then
FormulaCells.Replace what:=loSource.Name, replacement:=.Name, lookat:=xlPart
End If
Else
.DataBodyRange.Value2 = .DataBodyRange.Value2
End If

'turn target Totals row on or off to match Source
If loSource.ShowTotals Then
.ShowTotals = True
loSource.TotalsRowRange.Copy Destination:=.TotalsRowRange
Else
.ShowTotals = False
End If
End With

End Sub

One thing I learned is that there are two Resizes in a table (listobject). The first type, the Range property, was familiar, e.g.,

Range("A1").Resize(20,1)

… which yields a range object whose address is A1:A20.

The second is the Listobject.Resize method, which allows you to modify a table’s range, e.g.,

loTarget.Resize(Range("A1:F20")

which will change loTarget’s range to A1:F20.

Both of these types of Resizes are used in the code above, in the same line, happily enough.

Data Normalizer

Posted on March 28, 2012 by Doug Glancy

Sometimes I get data like this…

that needs to be like this…

The goal here is to roll up all the home runs into one, much longer, column. The data will then be pivot-worthy.

Generally, I need to keep one or more leftmost column headers, in this case “League” and “Year.” I need a new column to describe the rolled-up category (“Team”) and one for the data itself (“Home Runs”). I’ve written code a couple of times to handle specific cases and thought I’d try to generalize it. Here’s the result:

'Arguments
'List: The range to be normalized.
'RepeatingColsCount: The number of columns, starting with the leftmost,
' whose headings remain the same.
'NormalizedColHeader: The column header for the rolled-up category.
'DataColHeader: The column header for the normalized data.
'NewWorkbook: Put the sheet with the data in a new workbook?
'
'NOTE: The data must be in a contiguous range and the
'rows that will be repeated must be to the left,
'with the rows to be normalized to the right.

Sub NormalizeList(List As Excel.Range, RepeatingColsCount As Long, _
NormalizedColHeader As String, DataColHeader As String, _
Optional NewWorkbook As Boolean = False)

Dim FirstNormalizingCol As Long, NormalizingColsCount As Long
Dim ColsToRepeat As Excel.Range, ColsToNormalize As Excel.Range
Dim NormalizedRowsCount As Long
Dim RepeatingList() As String
Dim NormalizedList() As Variant
Dim ListIndex As Long, i As Long, j As Long
Dim wbSource As Excel.Workbook, wbTarget As Excel.Workbook
Dim wsTarget As Excel.Worksheet

With List
'If the normalized list won't fit, you must quit.
If .Rows.Count * (.Columns.Count - RepeatingColsCount) > .Parent.Rows.Count Then
MsgBox "The normalized list will be too many rows.", _
vbExclamation + vbOKOnly, "Sorry"
Exit Sub
End If

'You have the range to be normalized and the count of leftmost rows to be repeated.
'This section uses those arguments to set the two ranges to parse
'and the two corresponding arrays to fill
FirstNormalizingCol = RepeatingColsCount + 1
NormalizingColsCount = .Columns.Count - RepeatingColsCount
Set ColsToRepeat = .Cells(1).Resize(.Rows.Count, RepeatingColsCount)
Set ColsToNormalize = .Cells(1, FirstNormalizingCol).Resize(.Rows.Count, NormalizingColsCount)
NormalizedRowsCount = ColsToNormalize.Columns.Count * .Rows.Count
ReDim RepeatingList(1 To NormalizedRowsCount, 1 To RepeatingColsCount)
ReDim NormalizedList(1 To NormalizedRowsCount, 1 To 2)
End With

'Fill in every i elements of the repeating array with the repeating row labels.
For i = 1 To NormalizedRowsCount Step NormalizingColsCount
ListIndex = ListIndex + 1
For j = 1 To RepeatingColsCount
RepeatingList(i, j) = List.Cells(ListIndex, j).Value2
Next j
Next i

'We stepped over most rows above, so fill in other repeating array elements.
For i = 1 To NormalizedRowsCount
For j = 1 To RepeatingColsCount
If RepeatingList(i, j) = "" Then
RepeatingList(i, j) = RepeatingList(i - 1, j)
End If
Next j
Next i

'Fill in each element of the first dimension of the normalizing array
'with the former column header (which is now another row label) and the data.
With ColsToNormalize
For i = 1 To .Rows.Count
For j = 1 To .Columns.Count
NormalizedList(((i - 1) * NormalizingColsCount) + j, 1) = .Cells(1, j)
NormalizedList(((i - 1) * NormalizingColsCount) + j, 2) = .Cells(i, j)
Next j
Next i
End With

'Put the normal data in the same workbook, or a new one.
If NewWorkbook Then
Set wbTarget = Workbooks.Add
Set wsTarget = wbTarget.Worksheets(1)
Else
Set wbSource = List.Parent.Parent
With wbSource.Worksheets
Set wsTarget = .Add(after:=.Item(.Count))
End With
End If

With wsTarget
'Put the data from the two arrays in the new worksheet.
.Range("A1").Resize(NormalizedRowsCount, RepeatingColsCount) = RepeatingList
.Cells(1, FirstNormalizingCol).Resize(NormalizedRowsCount, 2) = NormalizedList

'At this point there will be repeated header rows, so delete all but one.
.Range("1:" & NormalizingColsCount - 1).EntireRow.Delete

'Add the headers for the new label column and the data column.
.Cells(1, FirstNormalizingCol).Value = NormalizedColHeader
.Cells(1, FirstNormalizingCol + 1).Value = DataColHeader
End With
End Sub

You’d call it like this:

Sub TestIt()
NormalizeList ActiveSheet.UsedRange, 2, "Team", "Home Runs", False
End Sub

It runs pretty fast. The sample sheet above – 109 years of data by 16 teams – completes instantly. 3,000 rows completes in a couple of seconds.

If I also run the routine on some American League data and put all the new rows in one sheet (with the same column headers) I can generate a pivot table that looks like this, which I couldn’t have done with the original data:

You can download a zip file with a .xls workbook that contains the data and code. Just click on the “normalize” button.

Two-dimensional Index/Match Formula with Variable-Length Lookup

Posted on March 2, 2012 by Doug Glancy

When I was a housing developer I used Excel for budgets and pro formas. I wrote PPMT formulas, did lots of Goal Seeking, and never used pivot tables. Now I use them all the time, and my favorite formula is an Index/Match. It’s gotten to where I can type a fancy lookup pretty quickly, though my array formulas generally take a bit of hacking. I’m no Barry Houdini, but I do all right.

Yesterday I realized you can do a Match into an array of substrings of cell values. For example, in this baseball stats sheet (I think it’s “rhubarbs” per year) you can match against just the beginning few characters of each column heading, like “SF” or “STL.”

Here’s the formula in cell I3. Note that it’s an array formula, entered with Ctrl-Shft-Enter:

=INDEX(tblRhubarb,
MATCH($G$3,tblRhubarb[YEAR],0),
MATCH(TEXT($H$3,"0"),LEFT(tblRhubarb[#Headers],LEN($H$3)),0))

The first part, “INDEX(Table2”, says to index into the whole table. In other words, it’s a two-dimensional lookup.
The second part, “MATCH($G$3,tblRhubarb[YEAR],0),” says to look in the year column for a match to the year in G3.
The third part, “MATCH(TEXT($H$3,”0″),LEFT(tblRhubarb[#Headers],LEN($H$3)),0))” says to look in the column headers for the team abbreviation in H3.

I wasn’t sure that last section would work. It says to look only in the left part of each header. When you analyze this last part by highlighting it and hitting F9 it looks like…

MATCH(TEXT($H$3,"0"),{"YE","LA"," P","SF","ST"},0))

… which is pretty cool.

The final thing to mention is about this part of the formula:

LEN($H$3)

This says to look at the leftmost number of characters equal to the length of the string in H3. That’s important because not all the team abbreviations are the same length. It would be important even if they were, because you don’t want to rely on “magic numbers” in formulas or code.

If you’re interested in these types of formulas be sure to go back to the top of the post and click the barry houdini link. Not only does he have a great name, he could write a formula that would find your car keys, and it wouldn’t even have to be array-entered.

Building A Workbook Table Class

Posted on January 30, 2012 by Doug Glancy

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
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:

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.