vba 集合不会存储字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9194106/
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
Collection won't store string
提问by Talisker
Since this morning I'm experiencing a weird behavior in VBA (Excel 2003).
从今天早上开始，我在 VBA (Excel 2003) 中遇到了一个奇怪的行为。
Here's the code:
这是代码：
Sub TestCollection()
Dim i As Single, col As New Collection
Dim vArr(1 To 3) As String
'For i = LBound(vArr) To UBound(vArr)
' vArr(i) = Sheets("Sheet2").Cells(1, i)
'Next i
vArr(1) = "String 1"
vArr(2) = "String 2"
vArr(3) = "String 3"
For i = LBound(vArr) To UBound(vArr)
Debug.Print vArr(i)
col.Add i, vArr(i)
Debug.Print col(i)
Next i
End Sub
Now, I'd expect my collection to be filled with Item/Key pairs like (e.g. in the Locals window):
现在，我希望我的收藏中充满项目/密钥对，例如（例如在本地窗口中）：
Item 1 -> "String 1"
Item 2 -> "String 2"
Item 3 -> "String 3"
but unfortunately I'm getting all items like:
但不幸的是，我收到了所有项目，例如：
Item 1 -> 1
Item 2 -> 2
Item 3 -> 3
Does anybody have a clue why the collection doesn't store the strings as a Key value?
有没有人知道为什么集合不将字符串存储为 Key 值？
回答by huel
If I'm reading your code correctly it looks like you want the value in ito be your key, instead of String 1for example. If so I'd recommend trying
如果我正确阅读了您的代码，那么您似乎希望将 in 中的值i作为您的键，而不是String 1例如。如果是这样，我建议尝试
vArr(1) = "String 1"
vArr(2) = "String 2"
vArr(3) = "String 3"
For i = LBound(vArr) To UBound(vArr)
col.Add vArr(i), CStr(i)
Sheet1.Cells(1, i) = col.Item(i)
Next i
Based on the example given a simpler method would be
基于给出的示例，一个更简单的方法是
vArr(1) = "String 1"
vArr(2) = "String 2"
vArr(3) = "String 3"
For i = LBound(vArr) To UBound(vArr)
col.Add vArr(i)
Sheet1.Cells(1, i) = col.Item(i)
Next i
where you just look items up by their index, but this may not work with your actual data.
您只需按索引查找项目，但这可能不适用于您的实际数据。
Edit: I can look up the column number based on the column name using this code:
编辑：我可以使用以下代码根据列名查找列号：
Dim i As Single, col As New Collection
Dim vArr(1 To 3) As String
vArr(1) = "Column 1"
vArr(2) = "Column 2"
vArr(3) = "Column 3"
For i = LBound(vArr) To UBound(vArr)
col.Add i, vArr(i)
Next i
For i = LBound(vArr) To UBound(vArr)
Dim columnNumber As Integer
columnNumber = col("Column 2")
Sheet1.Cells(1, columnNumber).Value = "Found it"
Next i
回答by MiloNC
Simple explanation: Add method on collection wants parameters in the order: item, key rather than: key, item.
简单说明：集合上的Add方法要参数的顺序是：item，key而不是：key，item。
Here's where I got that info: http://msdn.microsoft.com/en-us/library/f26wd2e5.aspx
这是我获得该信息的地方：http: //msdn.microsoft.com/en-us/library/f26wd2e5.aspx
回答by Jerry Beaucaire
This is the only edit I would make to your original code:
这是我对您的原始代码所做的唯一编辑：
col.Add vArr(i), CStr(vArr(i))
回答by MiloNC
The key must be a string, though the item can be an int, string, or probably any object. If you want the keys to be simple integers, rather than "item 1" etc., just cast them, similar to Jerry Beaucaire's suggestion:
键必须是字符串，但项可以是整数、字符串或任何对象。如果您希望键是简单的整数，而不是“项目 1”等，只需投射它们，类似于 Jerry Beaucaire 的建议：
col.Add vArr(i), CStr(i)
