如何使用表格名称和列引用循环Excel 2010表格？

Question

自从Excel 2010之后，我经常在Excel中使用表格。例如，我有一个名为“tabWorkers”的表格，其中包含3列：“ID”、“Firstname”和“Lastname”。

我已经发现可以使用[]在VBA中引用表格。

例如：

Dim row As Range
For Each row In [tabWorkers].Rows
    MsgBox (row.Columns(2).Value)
Next

这将会给我每一行的Firstname，非常好用。但是我想让它更具有动态性，通过使用它列的名称来实现：

Dim row As Range
For Each row In [tabWorkers].Rows
    MsgBox (row.Columns("Firstname").Value)
Next

当然，我可以创建某种查询来将列索引“2”绑定到类似于FirstnameIndex的变量，但我想要正确的语法。我确定这是可能的，但只是没有很好地记录（就像[tabWorkers].Rows一样）。

- Tiele Declercq

---

你所说的“列名”具体指什么？因为这可能意味着不同的事情——是单元格内的名称吗？还是除了通常的A-B-C...标签之外的其他名称？或者是一个命名区域？ - Jook

你知道吗，你可以选择一个列，然后给它命名，这样你就得到了一个命名范围，比如“Firstname”。然后你可以使用 Range("Firstname").value 来访问该列。 - Jook

@Jook：所谓列名，是指表格列的名称。Excel在其公式中使用tablename[@columnname]来引用它。 - Tiele Declercq

@Jook：为工作表列定义名称可能可行，但这会创建另一个问题.. 我想直接访问表的列。 - Tiele Declercq

Answer 1

我对缩写方式的表格引用不是很熟悉。如果你没有得到答案，你可能会发现这种使用ListObject模型的长写法非常有用：

Sub ListTableColumnMembers()
Dim lo As Excel.ListObject
Dim ws As Excel.Worksheet
Dim lr As Excel.ListRow

Set ws = ThisWorkbook.Worksheets(2)
Set lo = ws.ListObjects("tabWorkers")

For Each lr In lo.ListRows
Debug.Print Intersect(lr.Range, lo.ListColumns("FirstName").Range).Value
Next lr
End Sub

- Doug Glancy

Answer 2

尝试这个：

Dim row as Range
For Each row in [tabWorkers[first name]].Rows
     MsgBox row.Value
Next

- Spencer Hutton

---

我认为你的解决方案非常棒。你能指向任何说明如何干净地迭代电子表格中的表格的文档吗？谢谢！ - YSD

Answer 3

我也曾思考过这个问题，并进行了大量搜索，但没有找到一个真正好的答案。

最终在今天，我恍然大悟并找到了一些东西，我想分享给那些遇到同样问题的人。简而言之，问题是：如何使用表格的标准名称在vba中引用表格的行。原来很容易。你可以引用表格的特定行，并通过它们的实际名称调用列，而无需重新定义任何变量。

构造方式如下：Range("TableName[ColumnName]")(RowNumber)

其中，TableName和ColumnName是Excelside的标准表格和列名称，RowNumber是从1到Range("Table1").Rows.Count的简单整数索引行号。

对于一个名为Table1的两列表格，其中包含ID和Data两列，您可以使用此构造方法循环遍历表格元素。

For Rw = 1 To Range("Table1").Rows.Count
     MsgBox(Range("Table1[ID]")(Rw) & "has value" & Range("Table1[Data]")(Rw)    )
Next Rw

这与Excel中使用的非常相似，并且不需要额外的命名。而且它非常易读！您还可以在(Rw)部分之后添加适当的范围修饰符，如.Text、.Value、.Formula等。

请注意，如果您有一个单行表格（例如用于参数），或者希望引用较大表格的第一行，则可以跳过(Rw)部分并直接使用范围。因此，名为Params的表格，其中包含称为颜色、大小和高度的列，可以使用Range("Params[Colour]")或Range("Params[Height]")等进行引用。您喜欢吗？ :-)

这个发现真的让我着迷于表格。它们现在以一种可读和兼容的方式为我提供了vba和工作表之间的非常整洁的链接。

谢谢微软！

Bob JordanB

- user2092957

Answer 4

更简洁的方式，在Excel 2007中测试通过：

Dim row As Range
For Each row In [tabWorkers].Rows
    MsgBox Intersect (row,[tabWorkers[FirstName]]).Value
Next

- Antoni Gual Via

---

如果您没有实际列名称，并且想通过列索引访问它，该怎么办？ - Naz

@isquared-KeepitReal，那就和OP的例子完全一样了。 - Mojimi

Answer 5

我通常会这样做。

Dim rng as Range
Set rng = Application.Range("Tablename[Columnname]")

你只需要引用工作簿，因为表格名称在整个工作簿中是唯一的。但是，如果在不同的打开工作簿中有相同命名的表格，则仅会影响活动工作簿，而不是两个或后台中的一个。为了防止这种情况，您应该调用实际工作表中的范围对象，其中包含您的表格。

希望这能展示我上面描述的方法：

例如，我在Access数据库中有一个方法，它会对我使用CopyFromRecordSet创建和填充的Excel文档中的表格应用条件格式。

该方法的签名为：

Private Function HighlightBlankOrZeroColumn(RangeToFormat As Range, HighlightColor As Long)

我这样调用

HighlightBlankOrZeroColumn ApXL.Range("Table1[" & SoucreRst.Fields(intCount).Name & "]"), BlankOrZeroColor

这里的ApXL是一个新的Excel.Application对象，而SoucreRst则是一个ADO记录集。

在此之前，我已经通过调用以下两个方法将我的Recordset --> Range转换为表格：

xlWSh.ListObjects.Add(xlSrcRange, xlWSh.UsedRange, , xlYes).Name = "Table1"
xlWSh.ListObjects("Table1").TableStyle = "TableStyleLight16"

- Brad

---

此工作簿没有范围属性。 - Doug Glancy

哦，我想我真正做的是使用Application.Range，但我认为可能太宽泛了。对我来说很有效，因为每当我这样做时，我都会创建自己的实例。但我会更新。 - Brad

有道理。受到你的回答启发，我尝试使用 Thisworkbook.Names("Tablename[Columnname]").RefersToRange 但是无法使其工作。 - Doug Glancy

Application.Range() 对我很有用，正是我所需要的。 - KurzedMetal

Answer 6

这也是我的第一篇文章。 虽然问题已经被提出很久了，但我仍希望能对你有所帮助。

主要思路是让代码尽可能保持整洁。

Set selrange = Selection.EntireRow

For Each rrow In selrange.Rows
        selrange.Parent.Parent.Names.Add "rrow", rrow
        name = [table1[name] rrow].Text
        age = [table1[age] rrow].Text
        gender = [table1[gender] rrow].Text
Next
selrange.Parent.Parent.Names("rrow").Delete

- Ciro Rocha

Answer 7

谢谢你，Doug！那对我帮助很大。
一个可运行的例子：

Dim row As Range
For Each row In [tabWorkers].Rows
    MsgBox (row.Columns(row.ListObject.ListColumns("Firstname").Index).Value)
Next

- Tiele Declercq

Answer 8

感谢所有回答这个帖子的人，经过一些额外的研究，以下是我编写的代码版本。基本上，您需要在代码中先定义列，在循环遍历行时，提取每行所定义列的值。

Sub xlTableLoop_namedColumns()
Dim lst As ListObject
Dim rw As ListRow
Dim colSubject As ListColumn
Dim colDate As ListColumn
Set lst = ActiveSheet.ListObjects("mlist")  'the name of Table 
Set colSubject = lst.ListColumns("Subject") 'the name of column 1 in table header 
Set colDate = lst.ListColumns("date")       'the name of column 2 in table header
For Each rw In lst.ListRows                 'loop through all the rows
    Debug.Print colSubject.DataBodyRange.Rows(rw.Index).Value  'get value of column 1
    Debug.Print colDate.DataBodyRange.Rows(rw.Index).Value     'get value of column 2
Next
End Sub

希望能对某些人有所帮助。

- Uttam