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, Jul 21 '15 at 09:35
[**1**](http://stackoverflow.com/a/19908375/2140173) and [**2**](http://stackoverflow.com/a/19379641/2140173) to get you started on interfaces and oop in VBA — , Jul 21 '15 at 10:18

score 3 · Accepted Answer · answered Jul 21 '15 at 09:39

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 '15 at 09:39

mielk

3,890
12
19

1

Thank you that works just fine. Thank you stakx for the prompt reply as well – Jonathan Tan Jul 21 '15 at 09:47

score 2 · Answer 2 · answered Jul 21 '15 at 09:34

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.

mm i do not really see how i can apply what you have answered in to a loop for iterative data mining — Jonathan Tan, Jul 21 '15 at 09:46

Adding variable number of objects to collection

2 Answers2