How do I reference tables in Excel using VBA?

Question

Is it possible in Excel VBA to reference a named table?

Hypothetically this could be...

Sheets("Sheet1").Table("A_Table").Select

I have seen some mention of tables being a list object but I'm not sure if that is the same thing.

Answer 1

The OP asked, is it possible to reference a table, not how to add a table. So the working equivalent of

Sheets("Sheet1").Table("A_Table").Select

would be this statement:

Sheets("Sheet1").ListObjects("A_Table").Range.Select

or to select parts (like only the data in the table):

Dim LO As ListObject
Set LO = Sheets("Sheet1").ListObjects("A_Table")
LO.HeaderRowRange.Select        ' Select just header row
LO.DataBodyRange.Select         ' Select just data cells
LO.TotalsRowRange.Select        ' Select just totals row

For the parts, you may want to test for the existence of the header and totals rows before selecting them.

And seriously, this is the only question on referencing tables in VBA in SO? Tables in Excel make so much sense, but they're so hard to work with in VBA!