Referencing ListObjects (tables) with VBA

This is about how to the reference a table (a.k.a. ListObject) and its columns when coding in VBA. Worksheets, tables and their columns can be referenced by their name or index number. As these can be quite easily changed, making your VBA code robust can be quite a challenge. This blog post gives you the solutions those problems.

• If you are referencing a table by its name:

In most cases

Use:

Range("TableName").ListObject

Do not use:

Sheets("Sheet1").ListObjects("TableName")

Because there will be a VBA error if the sheet changes it’s name

Do not use:

Sheets(1).ListObjects("TableName")

Because there will be a VBA error if the sheet changes it’s position

I can see that the first example uses the Range object. How does this work?

Two things to know:

1) The Range object is able to take an address as an input. “A1:B3”, “SomeNamedRange”, “SomeTable” are all examples of an address. A cell formula referring to “SomeTable” doesn’t require the sheet name and so neither does the Range object.

2) Range(SomeExampleRange).ListObject will return the first ListObject that intersects with the range.

A question I often get: If tables are always scoped to the workbook then why is the parent of the ListObject the worksheet?

Tables, unlike named ranges are always scoped to the whole workbook (this is the reason why all table names within a workbook have to be unique). People writing VBA then wonder why the parent of the ListObject is the worksheet. If it was, then we wouldn’t have to use the range “trick” to get around this problem.

Luckily: the range “trick” is a very easy work around.

The actual reason: This might sound obvious, but a specific table cannot span multiple worksheets. Named ranges on the other hand, can reference multiple sheets and hence the parent of a Name object is the workbook.

Some good practical reasons: One reason is that you may want refresh all the tables on a sheet.  Another reason is that you may want to use some tables on a specific sheet or a set of sheets. Like below…

• If you are referencing a table by its index number:

For example:

Scenario 1: A button that starts a macro that uses the tables on that sheet:

 With Activesheet.ListObjects(1)
'Do some stuff
End With

Scenario 2: Looping through a set of similar/duplicated sheets and using ListObjects(1) and ListObjects(2) on each sheet.

These scenarios really pertain to situations where a specific sheet is duplicated many times with its structure kept intact but each sheet will contain different data. When sheets are duplicated, the table names will have to change e.g. “Products” to “Products3”.  The index number of the table will not change however, as the index number depends on the order of it’s creation.

• Referencing tables with the use of named ranges

A very robust solution is to create a named range that references the whole table. If the name or index of sheet or table changes, the named range will still point to that table but its name will not change. This is a property we can take advantage of with the Range object.

What happens if I duplicate a sheet? Won’t it contain a named range with the same name?

Yes, but the scope will differ. The new named range’s scope will be confined to the sheet that it is in. You can see a named range’s scope in the name manager.

How does the Range object know which named range to use?

Let’s use the Range object without any reference to a sheet in front of it, e.g:

Range("ProductTableRng").ListObject.DataBodyRange.Copy

It will work, even if there are two named ranges with the same name. How does the Range object know which sheet to look at?

If the code is in a code module, it will choose the named range depending on how it is scoped. It will look at the named ranges scoped to the ActiveSheet first and then after that, look through the named ranges scoped to the workbook. As we can see, the Range object behaves the same way a cell does with an address.

If the code is in a sheet module however, not only does it need to be scoped to that sheet or workbook, it also needs to exist on that sheet.

If in either of these scenarios, the named range still cannot be found then there will be a run-time error.

As a table’s name and index are unlikely to change, this technique is probably excessive but this technique is very useful in referencing table columns.

• Referencing columns with the use of named ranges

The examples below (which does not use a named range) copy the data of the “Production” column of the table called “TableFarm”.

Range("TableFarm").ListObject.ListColumns("Production").DataBodyRange.Copy

Range("TableFarm").ListObject.ListColumns(5).DataBodyRange.Copy

These code snippets are not very robust. The name of a ListColumn which is defined by the value in its header can be accidentally changed if data is pasted into the table and the ListColumn index will change if new table columns are inserted to the left. However, by using named ranges, a robust solution can be created (to break it, someone would have to go into the name manager to change the name of the named range).

Let’s say I create a named range called “Production” and it refers to “=TableFarm[Production]”.

Now, to copy the data in the Production column I can use:

Range("Production").Copy

Now, to order on the production column I can do:

Call SortTableOnRange(Range("Production"))

Sub SortTableOnRange(Rng As Range)
With Rng.ListObject.Sort
 .SortFields.Clear
 .SortFields.Add Rng
 .Apply
End With
End Sub

Sometimes however, we want to directly refer to the ListColumn, the ListColumn being a column (and child object) within the ListObject.

Although I can write “Range(“Production”).ListObject” to get the intersecting table, I cannot write “Range(“Production”).ListColumn” to get the intersecting ListColumn. I have written a simple custom function so that this can be achieved in a very similar way. This function returns the first column of the first table that intersects the range:

Private Function GetListColumn(RngNam As String) As ListColumn
Dim ColIndx As Long
Dim Rng As Range
Dim Tbl As ListObject

Set Rng = Range(RngNam)

If Rng.ListObject Is Nothing Then
 Set GetListColumn = Nothing 'Or apply error handling
 Exit Function
End If
Set Tbl = Rng.ListObject

Set Rng = Intersect(Rng, Tbl.Range)
ColIndx = Rng.Column - Tbl.Range.Column + 1
Set GetListColumn = Tbl.ListColumns(ColIndx)
End Function

The “TableFarm[Production]” totals calculation can now be changed using the code below:

GetListColumn("Production").TotalsCalculation=xlTotalsCalculationSum

For a general guide to the different objects and methods of a ListObject refer to:

https://www.thespreadsheetguru.com/blog/2014/6/20/the-vba-guide-to-listobject-excel-tables.