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.
You mentioned duplicating tables. I have a table I duplicate on separate tabs, that I may need to adjust later on use a loop in my macros. Currently, I use one as the template and just copy that once it’s done, but is there a way to have my code adjust accordingly for each instance, maybe based on the activesheet?
Mark also sent me an email which better explains his issue:
“I recently checked out your blog for a tip on better ways to handle table formatting in VBA. I currently have a sheet where I loop through the rows in a table to goal seek a certain number. However, I duplicate this table onto new worksheets and since tables are scoped to the workbook and get renamed the code cannot run for the newly duplicated tabs.
You mentioned this briefly in your blog here, and I’m curious if there is a way around this to maybe use the table index in order to get the same results but for whatever table is on the Activesheet.”
Answer:
How you want to reference the sheet is up to you, but lets say use the Activesheet.
If you want to select the data in the table that was created first (in the Activesheet) you can use:
Activesheet.Listobjects(1).DatabodyRange.Select
If you want to select the data in the table that was created last (in the Activesheet) you can use:
With ActiveSheet.ListObjects
.Item(.Count).DataBodyRange.Select
End With
Mark,
I have only one user getting an error on this code:
Sub InsertPressFilterStatusALL()
Dim ThisWeek As String
ThisWeek = “”””””
Activesheet.ListObjects(“Press_Table1”).Range.AutoFilter Field:=5, Criteria1:=ThisWeek
End Sub
Error:
Error -2147417848 (80010108) Automation error The object invoked has disconnected from its clients
Is the Range fix the solution?
I have a simple application but just can’t seem to find the code to do this.
I have a word doc (an invoice) and that invoice’s items are in an embedded excel table. I can write to the body of the invoice with the data I have but not to the table. The data is in an Excel workbook and I have no problem retrieving this data to use in the invoice via a query..
What I seem to be missing is a way to reference the Excel table from Word VBA in order to update the invoice item fields and perform the sums and other calculations. I can do it using a generic grid, but there are too many problems, like the fact that you can’t add two cells together cleanly. Apparently, the two references, one to a generic table and one to an Excel table, are referenced differently, the former one is working.
I’m looking for something like the following (pseudo code):
Dim exWb As Excel.Workbook
Set exWb = objExcel.Workbooks.Open(“file name”)
ThisDocument.ClientName.Caption = exWb.Sheets(“Worksheet”).Cells(2, 11) ‘get the client name from the query
Now, to write to the table:
Dim oTable As Table
Set oTable = WordDoc.Tables(1)
There’s the rub – that definition of oTable works fine for a generic grid, but not for an embedded excel table, which I’ve labeled by the way as “Invoice”.
Any clues? Thanks!
As there is no Range method for a Workbook object, what is the recommended way to reference a named table in a workbook that is not ActiveWorkbook? My guess would be [objWorkbook].Worksheets(1).Range(“MyTable”) where [objWorkbook] is the name of the Workbook object.