如何在 Excel 中使用 VBA 获取当前行和访问列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14714247/
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
How do you get current row and access columns using VBA in Excel?
提问by bpeikes
I have a table in an Excel spreadsheet. Let say it has two columns "Id" and "Name". Using VBA, I want to use the double click event on the worksheet to capture the "Id" and the "Name" in the current row.
我在 Excel 电子表格中有一个表格。假设它有两列“Id”和“Name”。使用VBA，我想使用工作表上的双击事件来捕获当前行中的“Id”和“Name”。
I use the Intersect function to figure out if the double click occurred in the table I'm interested in, but I don't know how to access the row data using table column names.
我使用 Intersect 函数来确定我感兴趣的表中是否发生了双击，但我不知道如何使用表列名访问行数据。
i.e.
IE
Private Sub Worksheet_BeforeDoubleClick(ByVal target As Range, Cancel As Boolean)
Dim user_rge as Range
Set user_rge = Me.Worksheets("Data").Range("Users")
If Not Intersects(user_rge, target) is Nothing Then
Dim id as Integer
Dim name as String
` What goes in here?
End
End Sub
采纳答案by bpeikes
This is what I eneded up doing. In this example, "Main" is the worksheet which has the table on it. The name of the table is "TableName", which has columns "columnXXXX", "columnYYYY" and "columnZZZZ"
这就是我最终要做的。在这个例子中，“Main”是上面有表格的工作表。表的名称是“TableName”，其中包含“columnXXXX”、“columnYYYY”和“columnZZZZ”列
I could probably create a helper function called, TableLookupByRange(tablename, columnname, range), where you could pass in the name of the table, the name of the column and a range which containg a cell anywhere in the row you want to do the lookup. Which would clean up the code as well.
我可能可以创建一个名为 TableLookupByRange(tablename, columnname, range) 的辅助函数，您可以在其中传递表名、列名和包含您想要执行的行中任何位置的单元格的范围抬头。这也会清理代码。
Dim table_lo as ListObject
Dim main_ws as Worksheet
Set main_ws = Me.Worksheets("Main")
Set table_lo = main_ws.ListObjects("TableName")
rownum = target.Row - 1
columnXXXX_colnum = table_lo.ListColumns("columnXXXX").index
columnYYYY_colnum = table_lo.ListColumns("columnYYYY").index
columnZZZZ_colnum = table_lo.ListColumns("columnZZZZ").index
Dim columnXXXX As String
Dim columnYYYY As String
Dim columnZZZZ As Double
columnXXXX = trades_rge.Cells(rownum, columnXXXX_colnum).Value
columnYYYY = UCase(trades_rge.Cells(rownum, columnYYYY_colnum).Value)
columnZZZZ = trades_rge.Cells(rownum, columnZZZZ_colnum).Value
回答by shahkalpesh
You can get the row using ActiveCell.Row. For columns, you will have to check whether user clicked on ID or Name by comparing it with the source range.
您可以使用ActiveCell.Row. 对于列，您必须通过将其与源范围进行比较来检查用户是否单击了 ID 或名称。
i.e.
IE
If ActiveCell.Column = users_rge.Columns(1) Then
ID = ActiveCell.Value
Name = ActiveCell.Offset(ColumnOffset:=1).Value
Else
Name = ActiveCell.Value
ID = ActiveCell.Offset(ColumnOffset:=-1).Value
End If
Note: I haven't tried this code, am writing this from what I have in mind.
注意：我没有尝试过这段代码，我是根据我的想法写的。
回答by bonCodigo
Try this as you are almost there with your code: use Range.Columns Propertyto compare Target against user_range columns 1 and 2.
试试这个，因为您的代码几乎就在那里：使用Range.Columns 属性将 Target 与 user_range 列 1 和 2 进行比较。
Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal target As Range, Cancel As Boolean)
Dim id as Integer
Dim name as String
Dim user_rge as Range
Set user_rge = Me.Worksheets("Data").Range("Users")
If Not Intersects(user_rge, target) is Nothing Then
Select Case Target.Column
Case user_rge.Columns(1)
id = Target.Offset(0,0).Value
name = Target.Offset(0,1).Value
Case user_rge.Columns(2)
id = Target.Offset(0,-1).Value
name = Target.Offset(0,0).Value
End Select
End If
End Sub
