vba 按变量引用表列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11569288/
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
Reference a table column by variable
提问by diakonos1984
This should be simple, but I'm a VBA noob. I've read lots of forums, and found nothing but overly complicated code that I couldn't decipher to fit my application.
这应该很简单，但我是 VBA 菜鸟。我已经阅读了很多论坛，发现除了过于复杂的代码外，我无法解读以适应我的应用程序。
In Excel 2007 I have a Table already defined. I can't post a picture because I am new to the forum, but the table has 3 columns, with header rows named 1 through 3, and one data row as crudely shown below:
在 Excel 2007 中，我已经定义了一个表。我无法发布图片，因为我是论坛的新手，但该表有 3 列，标题行从 1 到 3，还有一个数据行，如下所示：
Table1
+------+------+-------+
|1 | 2 | 3 |
+------+------+-------+
|Alpha |Bravo |Charlie|
+------+------+-------+
With this simple Table the following works, and returns the text "Alpha".
使用这个简单的表，下面的工作，并返回文本“Alpha”。
Sub works()
Dim item As String
Sheets("Sheet1").Select
item = ActiveSheet.Range("Table1[1]")
MsgBox (item)
End Sub
But I want to be able to refer to table column headers with an adjustable variable. Why doesn't this work:
但我希望能够使用可调变量引用表列标题。为什么这不起作用：
Sub doesntwork()
Dim item As String
Dim i As String
i = 1
Sheets("Sheet1").Select
item = ActiveSheet.Range("Table1[i]")
MsgBox (item)
End Sub
It's got to be a syntax thing, but I'm having no luck sorting through all the various iterations of VBA syntax in the last 10+ years...
这一定是一个语法问题，但我没有运气在过去 10 多年里对 VBA 语法的所有各种迭代进行排序......
Please help! Thanks.
请帮忙！谢谢。
回答by Tim Williams
If you want to refer to the Headers of a structured table then this would get you a reference to the range containing the headers:
如果您想引用结构化表的标题，那么这将使您引用包含标题的范围：
Dim rng as Range
Set rng = Activesheet.Range("Table1[#Headers]")
...and if you want to refer to a specific header by position:
...如果您想按位置引用特定标题：
Dim rngHdr2
Set rngHdr2 = rng.Cells(2) '2nd header cell
回答by LittleBobbyTables - Au Revtheitroad
You need to use concatenation to build the table name as a string:
您需要使用串联将表名构建为字符串：
item = ActiveSheet.Range("Table1[" & i & "]")
回答by user3564289
combining both answers
结合两个答案
Sub Demo()
子演示()
Dim rng As Range
Dim celda As Range
Dim nroTabla As Integer
Dim nroFila As Integer
Dim nroCol As Integer
nroTabla = 1
nroFila = 2
nroCol = 3
Set rng = ActiveSheet.Range("Tabla" & nroTabla & "[#Headers]")
Set celda = rng.Cells(nroFila, nroCol)
celda.Value = 700
End Sub
结束子
