CODEWITHSUNDEEP
excelvba
Talisker
Talisker

Reputation: 1

Collection won't store string

Since this morning I'm experiencing a weird behavior in 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?

Upvotes: 0

Views: 6705

Answers (4)

MiloNC
MiloNC

Reputation: 505

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:

col.Add vArr(i), CStr(i)

Upvotes: 0

Jerry Beaucaire
Jerry Beaucaire

Reputation: 3197

This is the only edit I would make to your original code:

    col.Add vArr(i), CStr(vArr(i))

Upvotes: 0

huel
huel

Reputation: 21

If I'm reading your code correctly it looks like you want the value in i to be your key, instead of String 1 for example. If so I'd recommend trying

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

Upvotes: 1

MiloNC
MiloNC

Reputation: 505

Simple explanation: Add method on collection wants parameters in the order: item, key rather than: key, item.

Here's where I got that info: http://msdn.microsoft.com/en-us/library/f26wd2e5.aspx

Upvotes: 0

Related Questions