How do I reference tables in Excel 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

In addition, it's convenient to define variables referring to objects. For instance,

Sub CreateTable()
    Dim lo as ListObject
    Set lo = ActiveSheet.ListObjects.Add(xlSrcRange, Range("$B$1:$D$16"), , xlYes)
    lo.Name = "Table1"
    lo.TableStyle = "TableStyleLight2"
    ...
End Sub

You will probably find it advantageous at once.

Answer 2

Maybe this can help you

Creating a table

Converting a range to a table starts with the same code as in Excel 2003 (as described in this answer):

Sub CreateTable()
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$B$1:$D$16"), , xlYes).Name = _
        "Table1"
        'No go in 2003
    ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight2"
End Sub

Answer 3

A "table" in Excel is indeed known as a ListObject.

The "proper" way to reference a table is by getting its ListObject from its Worksheet i.e. SheetObject.ListObjects(ListObjectName).

If you want to reference a table without using the sheet, you can use a hack Application.Range(ListObjectName).ListObject.

NOTE: This hack relies on the fact that Excel always creates a named range for the table's DataBodyRange with the same name as the table. However this range name can be changed...though it's not something you'd want to do since the name will reset if you edit the table name! Also you could get a named range with no associated ListObject.

Given Excel's not-very-helpful 1004 error message when you get the name wrong, you may want to create a wrapper...

Public Function GetListObject(ByVal ListObjectName As String, Optional ParentWorksheet As Worksheet = Nothing) As Excel.ListObject
On Error Resume Next

    If (Not ParentWorksheet Is Nothing) Then
        Set GetListObject = ParentWorksheet.ListObjects(ListObjectName)
    Else
        Set GetListObject = Application.Range(ListObjectName).ListObject
    End If

On Error GoTo 0 'Or your error handler

    If (Not GetListObject Is Nothing) Then
        'Success
    ElseIf (Not ParentWorksheet Is Nothing) Then
        Call Err.Raise(1004, ThisWorkBook.Name, "ListObject '" & ListObjectName & "' not found on sheet '" & ParentWorksheet.Name & "'!")
    Else
        Call Err.Raise(1004, ThisWorkBook.Name, "ListObject '" & ListObjectName & "' not found!")
    End If

End Function

Also some good ListObject info here.

Answer 4

In addition to the above, you can do this (where "YourListObjectName" is the name of your table):

Dim LO As ListObject
Set LO = ActiveSheet.ListObjects("YourListObjectName")

But I think that only works if you want to reference a list object that's on the active sheet.

I found your question because I wanted to refer to a list object (a table) on one worksheet that a pivot table on a different worksheet refers to. Since list objects are part of the Worksheets collection, you have to know the name of the worksheet that list object is on in order to refer to it. So to get the name of the worksheet that the list object is on, I got the name of the pivot table's source list object (again, a table) and looped through the worksheets and their list objects until I found the worksheet that contained the list object I was looking for.

Public Sub GetListObjectWorksheet()
' Get the name of the worksheet that contains the data
' that is the pivot table's source data.

    Dim WB As Workbook
    Set WB = ActiveWorkbook

    ' Create a PivotTable object and set it to be
    ' the pivot table in the active cell:
    Dim PT As PivotTable
    Set PT = ActiveCell.PivotTable

    Dim LO As ListObject
    Dim LOWS As Worksheet

    ' Loop through the worksheets and each worksheet's list objects
    ' to find the name of the worksheet that contains the list object
    ' that the pivot table uses as its source data:
    Dim WS As Worksheet
    For Each WS In WB.Worksheets
        ' Loop through the ListObjects in each workshet:
        For Each LO In WS.ListObjects
            ' If the ListObject's name is the name of the pivot table's soure data,
            ' set the LOWS to be the worksheet that contains the list object:
            If LO.Name = PT.SourceData Then
                Set LOWS = WB.Worksheets(LO.Parent.Name)
            End If
        Next LO
    Next WS

    Debug.Print LOWS.Name

End Sub

Maybe someone knows a more direct way.

Answer 5

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!