Как мне ссылаться на таблицы в Excel vba?

Question

Можно ли в Excel VBA ссылаться на именованную таблицу?

Гипотетически это может быть...

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

Я видел упоминание о том, что таблицы являются объектами списков, но я не уверен, что это одно и то же...

Answer 1

Может быть, это может помочь вам

Создание таблицы

Преобразование диапазона в таблицу начинается с того же кода, что и в Excel 2003 (как описано в этом ответе):

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 2

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

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!

Answer 3

"Таблица" в Excel действительно называется ListObject.

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

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

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

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

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 здесь.

Answer 4

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

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

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

Answer 5

В дополнение к вышесказанному вы можете сделать это (где "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

Может быть, кто-то знает более прямой путь.

Answer 6

Добавляем третий вариант. "Сокращенная" версия второго варианта @AndrewD.

1. SheetObject.ListObjects("TableName")
2. Application.Range("TableName").ListObject
3. [TableName].ListObject

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