Collection Item: Parent / NextItem

Paul_NL


Hi,

I am struggling with classes. This is what I want to do, in VBA (test it in Excel please):

I want to add a property/function to my class, named NextItem and PreviousItem. These return the next and previous items in the collection.

I have several separate collections where I keep my items in. So if I want to determine the next and previous item, I need to figure out in which collection the item resides before I can start to loop through that collection.

Unfortunately there is no collection.item.parent. That is what I need to simulate I thought about adding a property ParentCollection to the class, but how can I use that property as a pointer to a collection and retrieve the collection from that pointer.

Help!

Andy Pope


Hi,

It's a little difficult to know whether this will work in your code as you haven't posted any and the description is a little light on detail.

You will need to use the key to access an item, especially if the collections contain duplicate values.

Code Module:
Sub Test()

Dim clsX As Class1

Set clsX = New Class1

With clsX
With .ColA
.Add 11, "A1"
.Add 12, "A2"
.Add 13, "A3"
End With
With .ColB
.Add 21, "B1"
.Add 22, "B2"
.Add 23, "B3"
End With
With .ColC
.Add 31, "C1"
.Add 32, "C2"
.Add 33, "C3"
End With
End With

Debug.Print clsX.ColA.Item(1), clsX.ColA.Item("A3")
Debug.Print clsX.ColB.Item(2), clsX.ColB.Item("B2")
Debug.Print clsX.ColC.Item(3), clsX.ColC.Item("C1")

Debug.Print clsX.ParentCollection("B2").Item(1)
Debug.Print clsX.ParentCollection("A3").Item(1)
Debug.Print clsX.ParentCollection("C1").Item(1)

End Sub

Class1 code module:
Private m_colA As Collection
Private m_colB As Collection
Private m_colC As Collection
Public Function ColA() As Collection
Set ColA = m_colA
End Function
Public Function ColB() As Collection
Set ColB = m_colB
End Function
Public Function ColC() As Collection
Set ColC = m_colC
End Function
Function ParentCollection(ColItem) As Collection

Dim lngIndex As Long
Dim vntTest As Variant

For lngIndex = 1 To 3
Select Case lngIndex
Case 1
On Error Resume Next
vntTest = m_colA.Item(ColItem)
If Err.Number = 0 Then
Set ParentCollection = m_colA
Exit Function
End If
On Error GoTo 0
Case 2
On Error Resume Next
vntTest = m_colB.Item(ColItem)
If Err.Number = 0 Then
Set ParentCollection = m_colB
Exit Function
End If
On Error GoTo 0
Case 3
On Error Resume Next
vntTest = m_colC.Item(ColItem)
If Err.Number = 0 Then
Set ParentCollection = m_colC
Exit Function
End If
On Error GoTo 0
End Select
Next

End Function
Private Sub Class_Initialize()

Set m_colA = New Collection
Set m_colB = New Collection
Set m_colC = New Collection

End Sub