• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 492
  • Last Modified:

Collections

I am a newbie to using classes in vba and i have a question.
I have created a class object with 4 properties.
I then populate a collection with instances of the class object specifying individual keys for collection entries.
I understand how to loop through the collection to extract the class object property values.
My question is how do i access the class object property values for a specified (by key) entry in the collection?
0
JoseJalapeno
Asked:
JoseJalapeno
  • 9
  • 5
1 Solution
 
rockiroadsCommented:
Not sure what you mean here, you want to identify a class instance by a key?

when you create the class, you could assign a value in that class. Then use the get method on it to get back the value. Would need to define a variable in that class to hold it

0
 
rockiroadsCommented:
eg in your class

private theKey as String


Public Property Get MyKey() As String
    MyKey = theKey
End Property

Public Property Let MyKey(ByVal sKey as String)
    theKey = sKey
End Property

0
 
rockiroadsCommented:
So now when you insantiate this class you can set the value like this

    Dim x As New Class1
   
'Set a value
    x.MyKey = "Hello"
   
'Get the value
    Debug.Print x.MyKey

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
JoseJalapenoAuthor Commented:
Maybe i am not doing it right in the first place.
Form module creates a collection filling it with instances of the class module as defined.
As you can see i can loop through cSerials in SerialNumbers to fill a listbox.
I want to be able to reference a particular cSerial in SerialNumbers by the key value perhaps.
Code as follows:
Option Compare Database
 
Dim mID As Integer
Dim mSN As String
Dim mItemID As Integer
Dim mDeliveryNoteID As Integer
 
Public Property Get ID() As Variant
    ID = mID
End Property
 
Public Property Let ID(ByVal vNewValue As Variant)
    mID = vNewValue
End Property
 
Public Property Get SN() As Variant
    SN = mSN
End Property
 
Public Property Let SN(ByVal vNewValue As Variant)
    mSN = vNewValue
End Property
 
Public Property Get ItemID() As Variant
    ItemID = mItemID
End Property
 
Public Property Let ItemID(ByVal vNewValue As Variant)
    mItemID = vNewValue
End Property
 
Public Property Get DeliveryNoteID() As Variant
    DeliveryNoteID = mDeliveryNoteID
End Property
 
Public Property Let DeliveryNoteID(ByVal vNewValue As Variant)
    mDeliveryNoteID = vNewValue
End Property
-------------------------------------------------------------
'Declarations for the form
Dim SerialNumbers As Collection
Dim cSerial As Serials
 
'In a form function
 
'Fill collection with class instances
Set SerialNumbers = New Collection
Do While Not rs.EOF
        Set cSerial = New Serials
            cSerial.ID = rs!ID
            cSerial.SN = rs!SN
            cSerial.ItemID = rs!ItemID
            cSerial.DeliveryNoteID = rs!DeliveryNoteID
        SerialNumbers.Add cSerial, "Key_" & CStr(SerialNumbers.count + 1)
        Set cSerial = Nothing
        rs.MoveNext
Loop
'loop through collection and fill listbox
For Each cSerial In SerialNumbers
        Me.List2.AddItem "" & cSerial.SN & ""
Next

Open in new window

0
 
rockiroadsCommented:
Why return as variant when you have that variable defined as a integer and string anyway

when u instantiate this class, set the id. Im assuming that is what identifies a unique value

can you show the bit where you instantiate the class and place in your collection? how do you determine the key to use?
0
 
JoseJalapenoAuthor Commented:
See bottom of code "'Fill collection with class instances"
0
 
rockiroadsCommented:
lol, sorry. I didnt scroll down
0
 
rockiroadsCommented:
ok here, assuming ID is unique, why cant you save the ID as the collection key?

so instead of
            SerialNumbers.Add cSerial, "Key_" & CStr(SerialNumbers.Count + 1)

do this
            SerialNumbers.Add cSerial, "Key_" & rs!ID


Now to get the class, get the key, remove the prefix Key_ then using whats left you can find your instance

if rs!SN is unique, you can use that instead of storing "Key_" & rs!ID

does that make sense?
0
 
JoseJalapenoAuthor Commented:
Yes it does but the issue isn't creating the key, it's referencing one item in the collection and extracting the class properties from that single item.
Hope the question is clearer.
0
 
rockiroadsCommented:
I think so. Basically you want to get at the class

Given a index you can get at the class
eg

        Set cSerial = SerialNumbers.Item(i)


Say the SN was unique and you want to find the class given a SN (in variable strSN)

    'Get Serial Number Apple2
    Dim bLoop As Boolean
    bLoop = True
    i = 1
    While bLoop

'
        Set cSerial = SerialNumbers.Item(i)
        If cSerial.SN = strSN Then
            MsgBox "found it"
            bLoop = False
        Else
            i = i + 1
            If i > SerialNumbers.Count Then
                MsgBox "cant find it"
                bLoop = False
            End If
        End If
    Wend


Is this what u mean? Sorry to sound so thick in not understanding
0
 
JoseJalapenoAuthor Commented:
Probably me that doesn't understand.
Can i not reference a collection item directly without looping through them?
0
 
rockiroadsCommented:
Well yes. given the key you can do this

    Dim clsSerial As Serials

    Set clsSerial = SerialNumbers.Item("KeyValue")

so lets go back to using either the SN or ID as the key

if SN

sSN = "MySerialNumber"

    Set clsSerial = SerialNumbers.Item(sSN)

if ID

sKey = "Key_" & 5

    Set clsSerial = SerialNumbers.Item(sKey)

the Item method can be used to pass in index or the keyname and returns you that class


0
 
JoseJalapenoAuthor Commented:
Thankyou for your help!
I never thought to use the SET command...DUH!!
0
 
rockiroadsCommented:
Cool :)
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 9
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now