使用命名范围 (Excel VBA) 中的特定列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41087855/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Work with specific column in Named Range (Excel VBA)
提问by Gonik
Please have a look at this table, which I have named "Tasks":
请看一下这张表，我将其命名为“任务”：
It is a very basic GANTT chart-like table. Using VBA I use data from this table in order to perform some tasks in other Worksheets.
这是一个非常基本的类似甘特图的表格。使用 VBA，我使用此表中的数据来执行其他工作表中的某些任务。
I have a Forloop which loops through each row like this:
我有一个For循环，循环遍历每一行，如下所示：
For i = 1 To Range("Tasks").Rows.Count
Worksheets("Calendar").Cells(i,2).Value = Range("Tasks").Cells(i,2)
End For
There are many other operations within the Forloop, but that goes beyond the scope of my question. Basically what this does is that it loops through the entire table and performs various operations and calculations (where applicable) which results in populating other cells in other worksheets.
For循环中还有许多其他操作，但这超出了我的问题范围。基本上，它的作用是遍历整个表格并执行各种操作和计算（如果适用），从而导致填充其他工作表中的其他单元格。
My question is this:
我的问题是这样的：
Since all columns in the table are labeled, I would like to somehow reference the Column name instead of column number in the loop, if it is possible of course.
由于表中的所有列都被标记，如果可能的话，我想以某种方式在循环中引用列名而不是列号。
For example:
例如：
Worksheets("Calendar").Cells(i, 2).Value = Range("Tasks").Cells(i, "Title")
This helps for code readability since then I would know that this references the "Title" column, instead of going back and forth to see which column is e.g. the number "2".
这有助于代码可读性，从那时起我就知道这引用了“标题”列，而不是来回查看哪一列是例如数字“2”。
I know that this kind of reference can be done in Excel by using
我知道这种参考可以在 Excel 中使用
=Tasks[Title]
(e.g. This expression can be used for Data Validation)
（例如此表达式可用于数据验证）
Is it possible to reference columns like this? I am relatevely new to VBA, so I'm not quite sure.
是否可以引用这样的列？我是 VBA 的新手，所以我不太确定。
Looking forward for your answer.
期待您的回答。
回答by MacroMarc
if it's an Excel Table, then you can use
如果是 Excel 表格，则可以使用
Dim lo As ListObject
Dim col As ListColumn
Dim r As Range
Set lo = ActiveSheet.ListObjects("Tasks")
Set col = lo.ListColumns.Item("Title")
r = col.DataBodyRange
For i = 1 To Range("Tasks").Rows.Count
Worksheets("Calendar").Cells(i,2).Value = r.Cells(i)
End For
