Collection Object

D

Dan

I'd welcome your assistance with a little probem I'm having with the
Collection object.

I'm trying to utilise the fact that the Collection item's key must be a
unique value to obtain a collection of unique items in a Collection object.
If a try to add an item that already exists then VBA throws an error which i
handle and proceed.

What i'd like to do though is maintain a count of the number of each unique
item so as to obtain frequency of each unique item.

To do this I'm trying to increment the value of the item of the collection
by one each time an error is thrown:

On Error Resume Next
For Each a In myRange
myCol.Add 1,a
If Err.Number <> 0 Then
'key exists so increment value by 1
myCol.Item(a) = myCol.Item(a)+ 1
End If
Err.Clear
Next a
On Error GoTo 0

They problem line of code is:

myCol.Item(a) = myCol.Item(a)+ 1

Does anyone know if i can actualy do what i'm trying to with the Collection
object?

Dan
 
P

Patrick Molloy

can I suggest an alternative? The Scripting Runtime Dictionary is basically a
collection object with one or two useful features, for example you can check
it a ket exists or not, which means you don't need to trap errors.

In the development environment, under the menu Tools / References select the
Microsoft Scripting Runtime dll

try this code:

Option Explicit

Sub Collect()
Dim a As Range
Dim MyRange As Range
Dim dic As Scripting.Dictionary
Dim key As String
Set MyRange = Range("A1:A10")

Set dic = New Scripting.Dictionary
For Each a In MyRange
key = a.Value
If dic.Exists(key) Then
dic.Item(key) = dic.Item(key) + 1
Else
dic.Add key, 1
End If


Next a
' output
Dim index As Long
For index = 0 To dic.Count - 1
Range("B1").Offset(index) = dic.Keys(index)
Range("B1").Offset(index, 1) = dic.Items(index)
Next

End Sub
 
D

Dan

Hi Patrick. Thanks for the reply. I should have mentioned that i was already
aware that dictionaries were available but that i wanted to know specifically
if collections are capable of doing what i asked...

I'm not against using the dicionary - i simply cant understand why i cant
alter a collection members value...

Ta

Dan
 
D

Dan

Ah ok I see what you've done. Does this mean that there is no way to change
the value in a coolection once its been added??

Dan
 
P

Patrick Molloy

no worries

Option Explicit

Sub test()
Dim cell As Range
Dim x As Class1
Dim col As Collection
Set col = New Collection
For Each cell In Range("A1:A10").Cells
Set x = New Class1
x.Count = 1
x.Item = cell.Value
On Error Resume Next
col.Add x, cell.Value
If Err.Number <> 0 Then
Err.Clear
On Error GoTo 0
Set x = col.Item(cell.Value)
x.Count = x.Count + 1

End If

Next
Dim index As Long
For index = 1 To col.Count
Set x = col.Item(index)
Range("B1").Offset(index) = x.Item
Range("B1").Offset(index, 1) = x.Count

Next

End Sub


add a class module, Class1
and add these two lines


Public Item As String
Public Count As Long

'purpose to create a counter for ket values
 
P

Patrick Molloy

no. you can see my earlier thread where we use existing items

you create an object ( from a class ) and in that class, you add two
porperties, one for the item name and one for the counter. asdding this to
the collection means that you can impelment not only a counter, but you can
track the key values as well.

frankly, the scripting.runtime dictionary does this for you - and you can
iterate through both the keys and the items...its extrem,ely useful, in IMHO
 
P

Patrick Molloy

Dan, was this helpful?

Patrick Molloy said:
no worries

Option Explicit

Sub test()
Dim cell As Range
Dim x As Class1
Dim col As Collection
Set col = New Collection
For Each cell In Range("A1:A10").Cells
Set x = New Class1
x.Count = 1
x.Item = cell.Value
On Error Resume Next
col.Add x, cell.Value
If Err.Number <> 0 Then
Err.Clear
On Error GoTo 0
Set x = col.Item(cell.Value)
x.Count = x.Count + 1

End If

Next
Dim index As Long
For index = 1 To col.Count
Set x = col.Item(index)
Range("B1").Offset(index) = x.Item
Range("B1").Offset(index, 1) = x.Count

Next

End Sub


add a class module, Class1
and add these two lines


Public Item As String
Public Count As Long

'purpose to create a counter for ket values
Click to expand...
 
D

Dan

Kind of. I'm not sure i understand what i'm doing though. i can get it to
work but not sure how it works...
 
C

Chip Pearson

You can do this with a class module. Insert a new Class named CMyObj
and paste in the following code:

'[CMyObj]
Public SName As String
Public Value As Long

Public Sub Increment()
Me.Value = Me.Value + 1
End Sub

Then, in Module1, use code like

'[Module1]
Dim MyColl As Collection

Sub AAA()
Dim R As Range
Dim Obj As CMyObj

Set MyColl = New Collection

For Each R In Range("A1:A10")
Set Obj = New CMyObj
Obj.SName = R.Text
Obj.Value = 1
On Error Resume Next
Err.Clear
MyColl.Add Item:=Obj, key:=R.Text
If Err.Number <> 0 Then
'<<<<<<<<<
'MyColl(R.Text).Value = MyColl(R.Text).Value + 1
' OR
MyColl(R.Text).Increment
'<<<<<<<<<
End If
Next R

' list them out
For Each Obj In MyColl
Debug.Print Obj.SName, Obj.Value
Next Obj

End Sub


This creates an instance of CMyObj whose key in the Collection is the
text in cell R. If an object with that key does not exist in the
MyColl Collection, it is added. If it already exists, the item's Value
property is incremented. The code reads the Value out, increments it,
and writes it back. An alternative method is to put an Increment
method in the class an call that method to increment the Value.

The code marked with <<<< is most relevant to your question.

If you are new to classes, see
http://www.cpearson.com/excel/Classes.aspx.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 
P

Patrick Molloy

basically you've created your object from the class Class1, which has just
two properties,,,Item and Count

when you save to a collection, you save objects. Even simple text is an
object.
In your case, if the item, identified by its key, isn't in the collection,
you create a new object, set the object's two values, then save that into the
collection.
When a key exists, you set the variable (defined as the Class1 oblect) to
that item in the collection...whatever you then do to that variable is done
to the item in the collection..ie incrementing its count property.

this is somewhat simplistic, but if you aren't used to classes, it pretty
much covers the bases.
was this clearer? Play around with class module and play around at building
your own objects. it can be fun. Am i really that sad :(
;)
 
P

Patrick Molloy

nice and clean. I like it, though I wanted to keep the object as simple as
possible and didn't even this of adding a method.
Thanks Chip.

regards
Patrick

Chip Pearson said:
You can do this with a class module. Insert a new Class named CMyObj
and paste in the following code:

'[CMyObj]
Public SName As String
Public Value As Long

Public Sub Increment()
Me.Value = Me.Value + 1
End Sub

Then, in Module1, use code like

'[Module1]
Dim MyColl As Collection

Sub AAA()
Dim R As Range
Dim Obj As CMyObj

Set MyColl = New Collection

For Each R In Range("A1:A10")
Set Obj = New CMyObj
Obj.SName = R.Text
Obj.Value = 1
On Error Resume Next
Err.Clear
MyColl.Add Item:=Obj, key:=R.Text
If Err.Number <> 0 Then
'<<<<<<<<<
'MyColl(R.Text).Value = MyColl(R.Text).Value + 1
' OR
MyColl(R.Text).Increment
'<<<<<<<<<
End If
Next R

' list them out
For Each Obj In MyColl
Debug.Print Obj.SName, Obj.Value
Next Obj

End Sub


This creates an instance of CMyObj whose key in the Collection is the
text in cell R. If an object with that key does not exist in the
MyColl Collection, it is added. If it already exists, the item's Value
property is incremented. The code reads the Value out, increments it,
and writes it back. An alternative method is to put an Increment
method in the class an call that method to increment the Value.

The code marked with <<<< is most relevant to your question.

If you are new to classes, see
http://www.cpearson.com/excel/Classes.aspx.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)












I'd welcome your assistance with a little probem I'm having with the
Collection object.

I'm trying to utilise the fact that the Collection item's key must be a
unique value to obtain a collection of unique items in a Collection object.
If a try to add an item that already exists then VBA throws an error which i
handle and proceed.

What i'd like to do though is maintain a count of the number of each unique
item so as to obtain frequency of each unique item.

To do this I'm trying to increment the value of the item of the collection
by one each time an error is thrown:

On Error Resume Next
For Each a In myRange
myCol.Add 1,a
If Err.Number <> 0 Then
'key exists so increment value by 1
myCol.Item(a) = myCol.Item(a)+ 1
End If
Err.Clear
Next a
On Error GoTo 0

They problem line of code is:

myCol.Item(a) = myCol.Item(a)+ 1

Does anyone know if i can actualy do what i'm trying to with the Collection
object?

Dan
Click to expand...
Click to expand...
 
D

Dan

EXCELLENT!!

Thanks Guys - most helpful.

Dan

Chip Pearson said:
You can do this with a class module. Insert a new Class named CMyObj
and paste in the following code:

'[CMyObj]
Public SName As String
Public Value As Long

Public Sub Increment()
Me.Value = Me.Value + 1
End Sub

Then, in Module1, use code like

'[Module1]
Dim MyColl As Collection

Sub AAA()
Dim R As Range
Dim Obj As CMyObj

Set MyColl = New Collection

For Each R In Range("A1:A10")
Set Obj = New CMyObj
Obj.SName = R.Text
Obj.Value = 1
On Error Resume Next
Err.Clear
MyColl.Add Item:=Obj, key:=R.Text
If Err.Number <> 0 Then
'<<<<<<<<<
'MyColl(R.Text).Value = MyColl(R.Text).Value + 1
' OR
MyColl(R.Text).Increment
'<<<<<<<<<
End If
Next R

' list them out
For Each Obj In MyColl
Debug.Print Obj.SName, Obj.Value
Next Obj

End Sub


This creates an instance of CMyObj whose key in the Collection is the
text in cell R. If an object with that key does not exist in the
MyColl Collection, it is added. If it already exists, the item's Value
property is incremented. The code reads the Value out, increments it,
and writes it back. An alternative method is to put an Increment
method in the class an call that method to increment the Value.

The code marked with <<<< is most relevant to your question.

If you are new to classes, see
http://www.cpearson.com/excel/Classes.aspx.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)












I'd welcome your assistance with a little probem I'm having with the
Collection object.

I'm trying to utilise the fact that the Collection item's key must be a
unique value to obtain a collection of unique items in a Collection object.
If a try to add an item that already exists then VBA throws an error which i
handle and proceed.

What i'd like to do though is maintain a count of the number of each unique
item so as to obtain frequency of each unique item.

To do this I'm trying to increment the value of the item of the collection
by one each time an error is thrown:

On Error Resume Next
For Each a In myRange
myCol.Add 1,a
If Err.Number <> 0 Then
'key exists so increment value by 1
myCol.Item(a) = myCol.Item(a)+ 1
End If
Err.Clear
Next a
On Error GoTo 0

They problem line of code is:

myCol.Item(a) = myCol.Item(a)+ 1

Does anyone know if i can actualy do what i'm trying to with the Collection
object?

Dan
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

vba collection object in Excel 6
How to track object index in object collection 4
Can a collection be wrapped in access? 2
Unique values in a Collection 4
Help with Evaluate/Eval 3
Problems in copying a Collection to another and removing elementsfrom two Collections 6
How to set range to autofiltered visible cells? 7
Strange error message when loading a collection with an item of userdefined type 1

Top