vba 如何在visual basic中将带有字符串的excel单元格分配给数组值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19795585/
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 to assign excel cell with string to array value in visual basic?
提问by user2780563
I'm pretty new to visual basic, but I'm having trouble assigning cell values to members of an array. Basically what I am doing is looping through a column and assigning each cell to a new part of the array. Test code is posted below:
我对visual basic还很陌生，但是我无法将单元格值分配给数组的成员。基本上我正在做的是遍历一列并将每个单元格分配给数组的新部分。测试代码贴在下面：
Sub Test()
Dim List(5) As String
Dim celltext As String
For i = 1 To 5
celltxt = ActiveSheet.Range("K" & i).Text
List(i) = celltext
MsgBox List(i)
Next i
End Sub
Each time the message box pops up though, it is blank, meaning that the assignment didn't work. The cells at those coordinates DO have values. Any clues?
但是，每次弹出消息框时，它都是空白的，这意味着该分配不起作用。这些坐标处的单元格具有值。有什么线索吗？
回答by Joe
You are assigning to "celltxt" but reading from "celltext".
您正在分配给“celltxt”，但从“celltext”中读取。
Add Option Explicitat the top of every module -- that will make these types of errors more obvious.
添加Option Explicit在每个模块的顶部——这将使这些类型的错误更加明显。
回答by Siddharth Rout
When you
Dim List(5) As String. The lowest element in the array is0and not1. You might want to change that toDim List(1 to 5) As Stringelse your first element will always be blank in the array.You are using
ActiveSheet. Are you sure it is the right sheet?
当你
Dim List(5) As String. 数组中的最低元素是0和 not1。您可能希望将其更改为Dim List(1 to 5) As String否则您的第一个元素在数组中将始终为空白。您正在使用
ActiveSheet. 你确定这是正确的表吗？
Try this code
试试这个代码
Sub Test()
Dim List(1 To 5) As String
Dim ws As Worksheet
Dim i as Long
Set ws = ThisWorkbook.Sheets("Sheet1")
For i = 1 To 5
List(i) = ws.Range("K" & i).Value
MsgBox List(i)
Next i
End Sub
回答by user2140261
You might also with to try:
你也可以尝试：
Dim List As Variant
Dim i As Long
List = ActiveSheet.Range("K1:K5")
For i = 1 To UBound(List)
MsgBox List(i, 1)
Next i
This will add performance by only reading from the worksheet once instead of each time the loop is looped.
这将通过仅从工作表读取一次而不是每次循环时读取来提高性能。
