如何使用 VBA 在 Excel 中引用表格？

How do I reference tables in Excel using VBA?

提问by 1212__Hello

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

是否可以在 Excel VBA 中引用命名表？

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.

我看到有人提到表是列表对象，但我不确定这是否是同一回事。

采纳答案by Bgvv1983

Converting a range to a table as described in this answer:

如本答案所述，将范围转换为表格：

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

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

回答by GlennFromIowa

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

OP 询问，是否可以引用表，而不是如何添加表。所以工作相当于

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!

说真的，这是在 SO 中在 VBA 中引用表的唯一问题？Excel 中的表格非常有意义，但在 VBA 中很难使用它们！

回答by AndrewD

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

Excel 中的“表格”确实被称为 ListObject。

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

引用表的“正确”方法是从其工作表中获取其 ListObject，即SheetObject.ListObjects(ListObjectName).

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

如果要在不使用工作表的情况下引用表格，可以使用 hack Application.Range(ListObjectName).ListObject。

NOTE:This hack relies on the fact that Excel always creates a named range for the table's DataBodyRangewith the same name as the table. However this range name canbe 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.

注意：此 hack 依赖于 Excel 始终为表的DataBodyRange创建一个与表同名的命名范围的事实。然而，这个范围名称可以更改...虽然这不是您想要做的事情，因为如果您编辑表名称，名称将会重置！你也可以获得一个没有关联ListObject的命名范围。

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

鉴于 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

Also some good ListObject info here.

这里还有一些不错的 ListObject 信息。

回答by sancho.s ReinstateMonicaCellio

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:$D"), , xlYes)
    lo.Name = "Table1"
    lo.TableStyle = "TableStyleLight2"
    ...
End Sub

You will probably find it advantageous at once.

您可能会立即发现它的优势。

回答by Hugh Seagraves

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

除了上述之外，您还可以这样做（其中“YourListObjectName”是您的表的名称）：

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.

我发现您的问题是因为我想引用一个工作表上的列表对象（一个表格），而另一个工作表上的数据透视表则引用该列表对象。由于列表对象是 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

Maybe someone knows a more direct way.

也许有人知道更直接的方法。

回答by GollyJer

Adding a third option. The "shorthand" version of @AndrewD's second option.

添加第三个选项。@AndrewD 的第二个选项的“速记”版本。

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

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

^{Yes, there are no quotes in the bracket reference.}

^{是的，括号参考中没有引号。}