vba 我如何使用他的名字和列引用循环一个 excel 2010 表？

How do i loop an excel 2010 table by using his name & column reference?

提问by Tiele Declercq

Since Excel 2010 I'm using a lot of tables within Excel. For example, I have a table "tabWorkers" with 3 columns: "ID", "Firstname", "Lastname".

从 Excel 2010 开始，我在 Excel 中使用了很多表格。例如，我有一个包含 3 列的表“tabWorkers”：“ID”、“Firstname”、“Lastname”。

I already found out I can refer to a table in VBA using []. For example:

我已经发现我可以使用 [] 引用 VBA 中的表格。例如：

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

This will give me the Firstname of every row which works great.. but I want to make it more dynamic by using the name of it's column like this:

这将为我提供每行的名字，这很好用..但我想通过使用它的列的名称来使其更加动态，如下所示：

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

Of course I could make some kind of lookup that binds the column index '2' to a var like FirstnameIndex, but I want the correct syntax. I'm sure it's possible but just not really documented (like with [tabWorkers].Rows)

当然，我可以进行某种查找，将列索引 '2' 绑定到像 FirstnameIndex 这样的 var，但我想要正确的语法。我确信这是可能的，但只是没有真正记录下来（比如 [tabWorkers].Rows）

回答by Doug Glancy

I'm not very familiar with the shorthand method of referring to tables. If you don't get an answer, you might find this longhand method, that uses the ListOject model, useful:

我对引用表格的速记方法不是很熟悉。如果你没有得到答案，你可能会发现这个使用 ListOject 模型的普通方法很有用：

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

回答by Spencer Hutton

try this:

尝试这个：

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

回答by user2092957

I too have pondered this question and searched and searched without finding a really good answer

我也思考过这个问题，搜了又搜，没有找到一个很好的答案

Finally today the penny dropped and I found something which I would like to share to help those following with the same question that was bugging me.

终于，今天一分钱掉了，我找到了一些我想分享的东西，以帮助那些有同样问题困扰我的人。

In brief the question was: how to reference the rows of a table in vba using the standard names of the parts of a table. It turns out to be quite easy. You can refer to particular rows of the table and call the columns by their true name without the need to redefine any further variables.

简而言之，问题是：如何使用表各部分的标准名称在 vba 中引用表的行。事实证明这很容易。您可以引用表的特定行并按其真实名称调用列，而无需重新定义任何其他变量。

The construct is like this: Range("TableName[ColumnName]")(RowNumber)

构造是这样的： Range("TableName[ColumnName]")(RowNumber)

where TableName and ColumnName are the standard table and column names from Excelside, and RowNumber is the simple integer index of the row number from 1 to Range("Table1").Rows.Count

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

For a two column table called Table1 with columns called ID and Data you can loop through the table elements using this construction

对于名为 Table1 的两列表，其中包含名为 ID 和 Data 的列，您可以使用此结构遍历表元素

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

This keeps to very similar constructs to those used in Excel and requires no extra naming. And it is so very readable! You can also append the appropriate range modifiers such as .Text, .Value, .Formula etc after that (Rw) part as needed.

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

Note that if you have a single row table (eg for parameters), or wish to refer to the first row of a larger table, you can skip the (Rw) bit and use the Range directly. So a Table called Params with columns called Colour, Size and Height can be referred to using Range("Params[Colour]") or Range("Params[Height]") etc. Don't you like that? :-)

请注意，如果您有一个单行表（例如用于参数），或者希望引用更大表的第一行，您可以跳过 (Rw) 位并直接使用范围。因此，可以使用 Range("Params[Colour]") 或 Range("Params[Height]") 等引用名为 Params 的表，其中包含名为 Colour、Size 和 Height 的列。您不喜欢这样吗？:-)

This discovery has really hooked me on Tables. They now provide for me a very tidy link between vba and the sheets in a readable and compatible way.

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

Thank you Microsoft!

谢谢微软！

Bob JordanB

鲍勃·乔丹 B

回答by Antoni Gual Via

More concise, tested in Excel 2007:

更简洁，在 Excel 2007 中测试：

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

回答by Brad

I typically do it like this

我通常这样做

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

You only need to refer to the workbook because the table names are unique across the whole workbook. But if you have identically named tables in different open workbooks then this will affect the active workbook, not both or the one in the background. To prevent that you should call the range object of the actual sheet in which your table resides.

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

Hopefully, this will show it can be done in the way I described above:

希望这将表明它可以按照我上面描述的方式完成：

For example I have a method in an Access database which applies conditional formatting to a table in an Excel document I would have just created and populated using CopyFromRecordSet.

例如，我在 Access 数据库中有一个方法，该方法将条件格式应用于我刚刚创建并使用CopyFromRecordSet.

Which has a signature of

其中有一个签名

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

and I call like this

我这样称呼

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

where ApXLis a New Excel.Applicationand SoucreRstis an ADO recordset.

其中ApXLaNew Excel.Application和SoucreRst是 ADO 记录集。

By that point I'd already made my Recordset --> Range a table by calling these two methods:

到那时，我已经通过调用这两个方法制作了我的 Recordset --> 范围表：

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

回答by Tiele Declercq

Thank you Doug! That helped a lot. a working example:

谢谢道格！那帮助很大。一个工作示例：

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

回答by Ciro Rocha

This is my first post also. It's been a long time since the question was asked, but I hope it to be of help anyway.

这也是我的第一篇文章。问这个问题已经很长时间了，但无论如何我希望它有帮助。

The main idea is to keep the piece of code as neat as it gets.

主要思想是保持代码片段尽可能整洁。

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

回答by Uttam

Thanks for all the answers to this thread, with some additional research, below is my version of the code. Basically you define the columns earlier in the code and while you loop through the rows, you extract the values of the defined columns for each row.

感谢您对此线程的所有答案，并进行了一些额外的研究，下面是我的代码版本。基本上，您在代码中较早地定义列，并在循环遍历行时，为每一行提取已定义列的值。

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

Hope it helps someone.

希望它可以帮助某人。