Excel VBA - 在表格中选择、获取和设置数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7872504/
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
Excel VBA - select, get and set data in Table
提问by Kenny Bones
I've got a worksheet with a lot of tables in them and I'm just starting to use tables because they seem pretty handy. But I've never manipulated content in an Excel table before. And these tables are basically lists of columns with Firstname and Lastname. Based on the values on these columns, I want to generate a username. But I'm trying to write a generic Sub that takes arguments, such as worksheet and name of the table.
我有一个工作表，里面有很多表格，我刚刚开始使用表格，因为它们看起来很方便。但我以前从未操作过 Excel 表格中的内容。这些表基本上是带有名字和姓氏的列列表。根据这些列上的值，我想生成一个用户名。但是我正在尝试编写一个带有参数的通用 Sub，例如工作表和表格名称。
Previously I've done this when the data has not been in a table:
以前，当数据不在表中时，我已经这样做了：
Cells(2, 2).Select
Do
strFirstName = ActiveCell.Value
strLastName = ActiveCell.Offset(0, 2).Value
strFirstName = Left(strFirstName, 1)
strUserName = strFirstName & strLastName
strUserName = LCase(strUserName)
ActiveCell.Offset(0, 5).Value = strUserName
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell)
And now I'm trying to do the exact same thing, only with data from a Table. Any ideas? I've added a watch for "ActiveSheet" to see if I can find the tables, and they seem to be in ActiveSheet.ListObjects, but I couldn't see any .Selectoption there. Perhaps I don't need to select the Table in order to manipulate it's content?
现在我正在尝试做完全相同的事情，只使用表格中的数据。有任何想法吗？我为“ActiveSheet”添加了一个监视，以查看是否可以找到这些表格，它们似乎在 中ActiveSheet.ListObjects，但我在.Select那里看不到任何选项。也许我不需要选择表格来操纵它的内容？
回答by chris neilsen
When looping over a range (whether in a table or in a range) it is usually faster to copy the data to a variant array, manipulate that array, and then copy the result back to the sheet.
在循环范围（无论是在表中还是在范围内）时，将数据复制到变体数组、操作该数组，然后将结果复制回工作表通常会更快。
Sub zz()
Dim oUsers As ListObject
Dim v As Variant
Dim vUserName() As Variant
Dim i As Long
Dim colFirst As Long
Dim colLast As Long
Dim colUser As Long
Set oUsers = ActiveSheet.ListObjects(1)
colFirst = oUsers.ListColumns("FirstName").Index
colLast = oUsers.ListColumns("LastName").Index
colUser = oUsers.ListColumns("UserName").Index
v = oUsers.DataBodyRange
ReDim vUserName(1 To UBound(v, 1), 1 To 1)
For i = 1 To UBound(v, 1)
vUserName(i, 1) = LCase(Left(v(i, colFirst), 1) & v(i, colLast))
Next
oUsers.ListColumns("UserName").DataBodyRange = vUserName
End Sub
If you really want to loop over the range itself:
如果您真的想遍历范围本身：
For i = 1 To oUsers.ListRows.Count
oUsers.ListColumns("UserName").DataBodyRange.Rows(i) = LCase(Left( _
oUsers.ListColumns("FirstName").DataBodyRange.Rows(i), 1) & _
oUsers.ListColumns("LastName").DataBodyRange.Rows(i))
Next
For this situation you could also just use a formula in the UserNamecolumn itself, with no vba required
对于这种情况，您也可以只在UserName列本身中使用公式，而无需 vba
=LOWER(LEFT([@FirstName],1)&[@LastName])
EDIT
编辑
Sorry, don't know of a Formula way to remove any of a list of characters from a string. You might have to revert to vba for this. Here's a user defined function to do it. Your formula will become
抱歉，不知道从字符串中删除任何字符列表的公式方法。为此，您可能必须恢复到 vba。这是一个用户定义的函数来做到这一点。你的公式会变成
=DeleteChars([@UserName],{"$","#"})
To Deletethe characters replace {"$","#"}with a array list of characters you want to remove (you can make the list as long as you need)
To replacethe characters use {"$","#";"X","X"}where the list up to the ; is the old characters, after the ; the new. Just make sure the listsa are the same length.
删除字符替换为要删除的字符{"$","#"}数组列表（您可以根据需要制作列表）
要替换字符，请使用{"$","#";"X","X"}列表到 ; 的位置。是旧字符，在 ; 之后 新的。只要确保 lista 的长度相同。
UDF code:
UDF代码：
Function DeleteChars(r1 As Range, ParamArray c() As Variant) As Variant
Dim i As Long
Dim s As String
s = r1
If UBound(c(0), 1) = 1 Then
For i = LBound(c(0), 2) To UBound(c(0), 2)
s = Replace(s, c(0)(1, i), "")
Next
Else
For i = LBound(c(0), 2) To UBound(c(0), 2)
s = Replace(s, c(0)(1, i), c(0)(2, i))
Next
End If
DeleteChars = s
End Function
