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

Ответ 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

Ответ 2

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

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!

Ответ 3

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

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

ПРИМЕЧАНИЕ.. Этот хак полагается на то, что Excel всегда создает именованный диапазон для таблицы DataBodyRange с тем же именем, что и таблица. Однако это имя диапазона может быть изменено... хотя это не то, что вы хотели бы сделать, поскольку имя будет reset, если вы измените имя таблицы! Также вы можете получить именованный диапазон без связанного объекта 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 здесь.

Ответ 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

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

Ответ 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 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 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 name is the name of the pivot table 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

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