How to refer to a structured table without referring to its sheet in VBA

Question

In VBA I know that I can refer to a structured table via this way:

  Set Tbl = Sheets("MySheetName").ListObjects("MyTblName")

Then do Tbl.XXX where .XXX can be .Name, .Range, etc.

However, I want to refer to this table without referring to the Sheet name, so that the macro does not break if the sheet name changes.

Is this possible?

You could use the sheet code name which can only be changed via the VBE. — SJR, Nov 01 '18 at 09:52
@Wizhi That's worse. That will change not only if the sheetname changes, but also if the sheet position changes. — Ron Rosenfeld, Nov 01 '18 at 09:58
Is cycling through sheets to find the correct one an option, that's what I use when the sheet name and position are variable. — Ricards Porins, Nov 01 '18 at 10:00
@Wizhi No. Just use the `CodeName`. eg. `Sheet1.ListObjects("myTblName")` — Ron Rosenfeld, Nov 01 '18 at 10:03
Is it the same behavior if you want to use it for range? `Sheet1.Range(....)` instead of `ws.Range(...)`. Thanks for sharing knowledge! — Wizhi, Nov 01 '18 at 10:08
@Wizhi I suggest you read the VBA help topic for the `Worksheet.CodeName` property, to gain a better understanding. — Ron Rosenfeld, Nov 01 '18 at 10:10
How do you imagine referring to table without sheet name? 1) ListObject always belongs to some sheet, so you can't avoid referring to sheet. 2) What if two tables on different sheets have same name? — JohnyL, Nov 18 '18 at 10:55

Lun · Answer 1 · 2019-06-21T11:14:46.513

After some research I found something that is not a perfect solution. You can make use of the Range function in VBA like this:

Set tbl = Range("TableName[#All]")

However this is not a ListObject but a Range. You can also do other references like:

the body of the structured table (excluding headers)

Range("TableName")

Column called "MyColumn" of the body

Range("TableName[MyColumn]")

etc.

Then you call something like: tbl.ListObject to refer to the structured table where the range is found.

The cool thing is that Range() will always work on the ActiveWorkbook, so you can be in WorkBook B and open a macro in Workbook A and it will still run on Workbook B

Source: https://peltiertech.com/structured-referencing-excel-tables/

score 1 · Answer 2 · answered Nov 18 '18 at 09:20

1

Why not refer to the sheet's "internal" name instead of its visible name?

answered Nov 18 '18 at 09:20

Michal Rosa

2,459
2
15
28

score 0 · Answer 3 · answered Nov 01 '18 at 10:05

0

If your table is on the ActiveSheet, you can use it like:

Set Tbl = ActiveSheet.ListObjects("MyTblName")

answered Nov 01 '18 at 10:05

BenderIO

411
4
11

What if it's not on the active sheet? – SJR Nov 01 '18 at 10:09
1

I don't know if I can call it a "good pratice" but when I work with heavy VBA project. I assign all my sheets names in a separate module as public variable with others constants. After that, if a sheet name change I don't have to recode all the project and the good sheet stay activated if correctly updated in the declaration module. (I don't see how to avoid activation. Some basic function are very capricious with this, like the simple "Range") – BenderIO Nov 01 '18 at 12:47

score 0 · Answer 4 · answered Oct 10 '22 at 16:18

If you would like to refer to a table (name "tbl_Function") in an unknown worksheet, here is what you can do:

Set rng4= Range("tbl_Function[#All]")                      ' range of table
sh = rng4.Parent.Name                                      ' name of the worksheet where the table is placed
tbl = Sheets(sh).ListObjects("tbl_Function").Range.Value   ' take the whole table

Hope it helps. Piotr

How to refer to a structured table without referring to its sheet in VBA

4 Answers4