Find an object in a class collection

[Xalii]

I have defined a class module : clPipe with several properties.
I have then defined a collection aLinks to contains my pipes
I would like to know if I can define the stID as a default Value and then be able to look for an item of my collection so that I can find quickly a item of my collection without searching all items.

Basically I want to be able to write "alinks("Pipe1").inRow", is there a way to do that in VBA ?

Extract of the code :

Dim aLinks As Collection
Dim aElem As Variant
Set aLinks = New Collection

for inLine= 2 to 10
Set aElem = New clPipe
aElem.inRow = inLine
aElem.sbReadExcel "Feuil1", inLine
aLinks.Add aElem
next
...



class module clPipe:
Private pstID As String 'ID of the link
Private pstNode1 As String 'ID of 1st node
Private pstNode2 As String 'ID of 2nd node
Private psgLength As Single 'Length of the pipe
Private psgDiam As Single 'Diameter of the pipe
Private pinRow As Long 'Position of the link
Public Property Get stID() As String
stID = pstID
End Property
Public Property Let stID(Value As String)
pstID = Value
End Property
Public Property Get stNode1() As String
stNode1 = pstNode1
End Property
Public Property Let stNode1(Value As String)
pstNode1 = Value
End Property
Public Property Get stNode2() As String
stNode2 = pstNode2
End Property
Public Property Let stNode2(Value As String)
pstNode2 = Value
End Property
Public Property Get sgLength() As Single
sgLength = psgLength
End Property
Public Property Let sgLength(Value As Single)
psgLength = Value
End Property
Public Property Get sgDiam() As Single
sgDiam = psgDiam
End Property
Public Property Let sgDiam(Value As Single)
psgDiam = Value
End Property
Public Property Get inRow() As Long
inRow = pinRow
End Property
Public Property Let inRow(Value As Long)
pinRow = Value
End Property

Public Sub sbReadExcel(ByVal stWS As String, ByVal inCol As Integer)
With Worksheets(stWS)
pstID = CStr(.Cells(pinRow, inCol))
pstNode1 = CStr(.Cells(pinRow, inCol + 1))
pstNode2 = CStr(.Cells(pinRow, inCol + 2))
psgLength = CSng(Val(.Cells(pinRow, inCol + 3)))
psgDiam = CSng(Val(.Cells(pinRow, inCol + 4)))
End With
End Sub

 

[zigenz]

You will want to use a Dictionary

Firstly, reference the Scripting runtime and add a variable for the key in your declarations section, i.e.:

Set alinks = CreateObject("Scripting.Dictionary")
Dim key as String

Now each time you add an element to the array, you can specify a string key, i.e.:

for inLine= 2 to 10

Set aElem = New clPipe
aElem.inRow = inLine
aElem.sbReadExcel "Feuil1", inLine
' Add some code to generate a unique key here. It could be as simple as key = "Pipe" & CStr(inline-1)
If Not alinks.Exists(key) Then

alinks.Add key, new clPipe​

Else

' raise some error!​

End If​

next

You can not reference your elements using the string key as desired.

HTH

Z

 

[zigenz]

Sorry, I should have said "You can NOW reference your elements using the string key as desired."

 

[zigenz]

Apologies again, slight error in the code...

for inLine= 2 to 10

Set aElem = New clPipe
aElem.inRow = inLine
aElem.sbReadExcel "Feuil1", inLine
' Add some code to generate a unique key here. It could be as simple as key = "Pipe" & CStr(inline-1)
If Not alinks.Exists(key) Then

alinks.Add key, aElem​

Else

' raise some error!​

End If​

next​

 

[Xalii]

Thanks a lot Zigenz, this is exactly what I wanted.
I have found some further information on the Web, now I can proceed to use my class object.
Best regards