Find the value of an array item which is in a collection

[csKanna]

Hi,

I have a collection and each and every item is an array of values.

I would like to find the value of items in that array in that collection. I can extract the values if I use For Each ... However, I would like to access directly and find the value.

The current code I have is:

Sub TestCollection()
    Dim oTests As New Collection
    Dim var As Variant

    oTests.Add Array("student 1", "Maths", "90")
    oTests.Add Array("student 2", "history", "50")
    oTests.Add Array("student 3", "science", "80")

    For Each var In oTests
        'var(0) 'Student
        'var(1) 'subject
        'var(2) 'marks
        Debug.Print var(0)
        Debug.Print var(1)
        Debug.Print var(2)
    Next var
End Sub

I would like to access and get the value "student 2" directly, not using for each.

Thank you

[Merri]

MsgBox oTests.Item(1)(0)
MsgBox oTests.Item(2)(0)
MsgBox oTests.Item(3)(0)

[Code Doc]

I designed a form with a command button and a label. Then I applied this code, preserving most of your notation:

Dim oTests As New Collection

Private Sub Command1_Click()
'Retrieve all the values for Student 2
For I = 0 To 2
    Label1.Caption = Label1.Caption & oTests.Item(2)(I) & " "
Next
End Sub

Private Sub Form_Load()
oTests.Add Array("student 1", "Maths", "90")
oTests.Add Array("student 2", "history", "50")
oTests.Add Array("student 3", "science", "80")
Label1.Caption = vbNullString
End Sub

I believe a loop is still the best way to pull in all the values within an item in the collection.

[MartinLiss]

Since I assume that a single student can be taking more than one subject you might like this. It's a bit complicated but it's a flexible technique. BTW it's a modification of some code that I got from Devx a few years ago.