Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Collections

Posted on 2009-03-31
14
Medium Priority
?
490 Views
Last Modified: 2013-11-27
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
Comment
Question by:JoseJalapeno
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 5
14 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 24027352
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 24027369
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 24027386
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:JoseJalapeno
ID: 24027452
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 24027599
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
 

Author Comment

by:JoseJalapeno
ID: 24027671
See bottom of code "'Fill collection with class instances"
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24027729
lol, sorry. I didnt scroll down
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24027757
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
 

Author Comment

by:JoseJalapeno
ID: 24027788
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 24027930
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
 

Author Comment

by:JoseJalapeno
ID: 24027979
Probably me that doesn't understand.
Can i not reference a collection item directly without looping through them?
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 2000 total points
ID: 24028058
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
 

Author Closing Comment

by:JoseJalapeno
ID: 31564748
Thankyou for your help!
I never thought to use the SET command...DUH!!
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24028138
Cool :)
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question