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?

Answer 1

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/

Answer 2

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