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

Question

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

46

在Excel VBA中，是否可以引用一个已命名的表格？

假设这可能是...

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

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

- 1212__Hello

6个回答

34

在Excel中，“表格”实际上被称为ListObject。

引用表格的“正确”方式是从其工作表获取其ListObject，即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的好信息，在这里。

- AndrewD

2

顺便提一下，在Application.Names中不要期望找到表格的DataBodyRange范围名称！但是它们可以使用Application.Range(...)访问。 - AndrewD

2

这种方式似乎只在包含所需表的工作簿当前处于活动状态时有效。如果您以某种方式使用另一个文件运行代码，则此方法将失败。与 ActiveSheet 对象相同的方法。 - tm-

不是很喜欢你提出的VBA，但你分享的外部链接总结了如何引用表格和表格部分，谢谢。 - Pork

1

点赞支持Hack和NOTE。实际上，我在刚发布的一个新问题中添加了一个参考链接：https://dev59.com/KKTja4cB1Zd3GeqPJOvQ @AndrewD，也许你能帮忙？ - Egalth

2

抛开你对于范围名称与表名混淆的不满，这个功能不仅适用于表格，而且适用于所有命名范围...它似乎能够处理用户无意中重命名工作表的情况...或者重新设计时各种名称单元格被重新映射到不同的工作表...当一个函数与多个工作表上的内容进行交互时[甚至如果它们最初都在ActiveSheet上，但后来可能不是]，而不需要VB编辑。这似乎是一种灵活性（也许在我不熟悉VBA的情况下，我不知道如何处理这样的变化？），看起来至关重要。 - JeopardyTempest

显示剩余2条评论

7

此外，定义指向对象的变量也非常方便。例如：

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

您马上可能会发现它有利。

- sancho.s ReinstateMonicaCellio

6

除了上述方法，您还可以执行以下操作（其中“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

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

- Hugh Seagraves

1

考虑到@AndrewD提到的应用程序范围方法的潜在缺点，这似乎是仅使用表名获取表的ListObject的可靠方法。 - ChrisB

除了在您的代码中创建“数据透视表”（PivotTable），这可能不是您的意图，我认为您的方法是最直接的。我尝试添加一个通用函数到您的答案中，以便根据“数据透视表”名称获取“列表对象”（ListObject），但显然这是不合适的。因此，我决定创建一个新的问题，并将该代码作为答案提供。但我在这里给您的答案点赞，因为能够从“数据透视表”名称获取“列表对象”是可预见的需求。 - GlennFromIowa

5

添加第三个选项。

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

^{是的，在方括号引用中没有引号。}

- GollyJer

4

按照这个答案中所述，将范围转换为表格：

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

- Bgvv1983

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- GlennFromIowa · Accepted Answer

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

对于部分内容，您可能希望在选择它们之前测试标题行和总计行的存在。

而且， SO 上关于 VBA 引用表格的唯一问题就是这个吗？在 Excel 中使用表格非常有意义，但在 VBA 中却非常难处理！