Adding variable number of objects to collection

Question

I understand adding objects to a collection can be done by

Dim ItemList As Collection
Set ItemList = New Collection

Dim Item As New CItem

Set Shoe = New CItem
      With Shoe
           .Quantity
           .IDNumber 
           .Description
      End With
ItemList.Add Shoe

Set Bag = New CItem
      With Bag
           .Quantity
           .IDNumber 
           .Description
      End With
ItemList.Add Bag

I would be able to call data i want to use like (Cost = Bag.Quantity * 2)

However my problem is that my list of items will be user defined. Is there any way to add a variable number of objects into a collection and still be able to retrieve individual data by the item name?

for example, i am given a list of items: Shoe, Bag, Sunglasses, Pants

I would like to write a for loop to read all these objects under the class "Item" but still be able to calculate (xyz = Sunglasses.Quantity + Pants.Quantity - Bag.Quantity). I have tried to use counters, but it seems to only accept constant expressions.

Is this possible? If so, i would appreciate help in finding out how to do it.

I'm not an expert on OOP in VBA, so I'm not sure for the name retreveing, but you could create an Item class and define a Type property to do this (except if your items are far too different from each other and then it can be messy in the properties) — R3uK
– R3uK, Commented Jul 21, 2015 at 9:35

mielk · Accepted Answer · 2015-07-21 09:39:52Z

3

You can add an item to collection with key:

ItemList.Add Shoe, "Shoe"

and then retrieve it by specifying this key:

Dim Shoe As CItem
Set Shoe = ItemList.Item("Shoe")

answered Jul 21, 2015 at 9:39

mielk

3,94014 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jonathan Tan Over a year ago

Thank you that works just fine. Thank you stakx for the prompt reply as well

stakx - no longer contributing · Accepted Answer · 2015-07-21 09:34:41Z

2

Set Shoe = New CItem
      With Shoe
           .Quantity
           .IDNumber 
           .Description
      End With

Note that the CItem object you are creating here does not have a property that describes the object's type. That is, if you look only at the object, there is no way to tell that it "is a shoe". All you know about such an object is a quantity, an ID number, and a description.

You named the variable Shoe; but that is just the variable's name, not the referred object's.

What you need to do is to add some kind of .Type or .Category property so that your objects become self-descriptive. Your above code would be amended with one more line like this:

Shoe.Category = "Shoe"

This makes it possible to take a collection of CItems, iterate over them, and sum up the quantities per .Category.

But the objects in your list will no longer have a dedicated variable name each.

answered Jul 21, 2015 at 9:34

stakx - no longer contributing

85.1k20 gold badges176 silver badges277 bronze badges

1 Comment

Jonathan Tan Over a year ago

mm i do not really see how i can apply what you have answered in to a loop for iterative data mining

Collectives™ on Stack Overflow

Adding variable number of objects to collection

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related