Как ссылаться на таблицы в Excel с помощью VBA?

Преобразование диапазона в таблицу, как описано в этом ответе:

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

ОП спросил, можно ли сослаться на таблицу, а не как добавить таблицу. Таким образом, рабочий эквивалент

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

будет это утверждение:

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

или для выбора частей (вроде только данные в таблице):

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

Для частей вы можете проверить наличие строк заголовков и итогов перед их выбором.

А если серьезно, то это единственный вопрос по ссылкам на таблицы в VBA в SO? Таблицы в Excel имеют такой смысл, но с ними так сложно работать в VBA!

«Таблица» в Excel действительно известна как ListObject.

«Правильный» способ сослаться на таблицу - получить ее ListObject из ее рабочего листа, то есть SheetObject.ListObjects(ListObjectName).

Если вы хотите сослаться на таблицу, не используя лист, вы можете использовать хак Application.Range(ListObjectName).ListObject.

ПРИМЕЧАНИЕ. Этот прием основан на том факте, что Excel всегда создает именованный диапазон для DataBodyRange таблицы с тем же именем, что и таблица. Однако это имя диапазона можно изменить... хотя это не то, что вы хотели бы делать, поскольку имя будет сброшено, если вы отредактируете имя таблицы! Также вы можете получить именованный диапазон без связанного ListObject.

Учитывая не очень полезное сообщение об ошибке 1004 Excel, когда вы неправильно называете имя, вы можете создать оболочку...

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

Также некоторая полезная информация о ListObject здесь< /а>.

Кроме того, удобно определять переменные, ссылающиеся на объекты. Например,

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

Вы, вероятно, найдете это выгодным сразу.

В дополнение к вышесказанному вы можете сделать это (где «YourListObjectName» — это имя вашей таблицы):

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

Но я думаю, что это работает, только если вы хотите сослаться на объект списка, который находится на активном листе.

Я нашел ваш вопрос, потому что хотел сослаться на объект списка (таблицу) на одном листе, на который ссылается сводная таблица на другом листе. Поскольку объекты списка являются частью коллекции Worksheets, вам необходимо знать имя рабочего листа, на котором находится объект списка, чтобы ссылаться на него. Таким образом, чтобы получить имя рабочего листа, на котором находится объект списка, я получил имя объекта исходного списка сводной таблицы (опять же, таблицы) и перебрал рабочие листы и их объекты списка, пока не нашел рабочий лист, содержащий список объект, который я искал.

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

Может кто знает более прямой способ.

Добавление третьего варианта. «Сокращенная» версия второго варианта @AndrewD.

1. ЛистОбъект.СписокОбъектов("ИмяТаблицы")
2. Application.Range("ИмяТаблицы").ListObject
3. [ИмяТаблицы].ListObject

^{Да, в скобках нет кавычек.}