VBA for smarties: Collection

Collection

1 What is a Collection

2 Create a Collection
2.1 Method Dim
2.2 Method Set
2.3 Method With ... End With
2.4 Naming convention

3 Elements in the Collection
3.1 Add elements
3.2 Add using keys

3.3 Order of elements

3.3.1 Order by Index
3.3.2 Order by Key
3.3.3 Order by Content

4 Collection size

5 Retrieve an Element

5.1 By indexnumber
5.2 By Key
5.3 By Content

6 Element's availablity
6.1 By Key
6.2 By Content

7 Filtering Elements
7.1 By Indexnumbers
7.2 By Keys
7.3 By Content

8 Delete Elements
8.1 By Indexnumber
8.2 By Key
8.3 By Content
8.4 Delete all Elements

9 Sorting a Collection

10 Example

1 What is a Collection

Collection is part of the standard VBA-library.

A Collection is a set of several elements.
You can store all kinds of elements in a Collection, comparable to an Array or a Dictionary.
Elements in a Collection can be strings, numbers, dates, VBA-objects, arrays, instances of Classes, etc.
You can approach and manipulate elements in a Collection separately: read, write, calculate, change, replace, save, etc.

The Collection is in the RAM memory and can therefore be approached very fast.
That can be an advantage compared to reading/writing to an Office document, that might trigger other events: screenupdating, calculation, document_events.

2 Create a Collection

3 Methods are available to create a Collection.

In two methods you explicitly declare an object variable that contains the Collection (and all its elements).
That offers the possibility to use the Collection in different procedures in the same codemodule ('Private scope') or in several codemodules: 'Public scope'.

The third method is implicit, without any variable and is by definition constrained to 1 procedure (macro or function): 'Local scope'.

2.1 Method Dim

The method Dim, in combination with the instruction 'New' declares the variable and creates the Collection simultaneously:

Dim c_00 as New Collection

Local scope

If this line is part of a procedure (macro or function) you can use the Collection only in the same procedure.

Sub M_snb()

Dim c_00 as New Collection

End Sub

Private scope

If you put this line into the declaration area of a codemodule (document, worksheet, workbook, macromodule, classmodule, userform) you can use the Collection in all procedures in this codemodule.

Private c_00 as New Collection

Dim c_00 as New Collection

Public scope

If you put this line into the declaration area of a codemodule of a macromodule you can use the Collection in all procedures in all codemodules.

Public c_00 as New Collection

2.2 Method Set

The method 'Set' assigns an instance of the object 'Collection' to an object variable.
The instruction 'Set' can only be part of a procedure ( not of a declaration area).
That means that for Private scope and Public scope the declaration of the variable and the assigning of the Collection to the variable are two separate instructions.
Only in Local scope 'Set' can declare the variable and assign the new Collection instance simultaneously.

Local scope

The method Set declares the variable and creates the Collection simultaneously (that's called 'auto-instancing').
The Collection can be used exclusively in the procedure that also contains this 'Set' instruction.

Sub M_snb()

Set c_01 = New Collection
c_01.Add "item_1"

End Sub

Private scope

If you want to use the Collection in every procedure of a codemodule you need to declare the variabele in the declaration area first.
After using the 'Set' instruction you can add elements to the Collection.
In the declaration area you can use 'Private' or 'Dim'.
The Typename of a variable, declared as Collection or Object, is 'Nothing'; the VarType is 9.
After the 'Set' instruction the variable's Typename is 'Collection'.
Now you can add elements to the variable.

The Typename of an unspecified variable is 'Empty'; the VarType 0.
Also here the 'Set' instruction changes the TypeName into 'Collection', after which elements can be added to the variable.

In the declaration area

Dim c_03 As Collection
Dim c_04 As Object
Dim c_05

Private c_06 As Collection
Private c_07 As Object
Private c_08

In a procedure

x0 = TypeName(c_03) ' Nothing
x1 = VarType(c_03) ' 9
Set c_03 = New Collection
x2 = TypeName(c_03) ' Collection
c_03.Add "item_1"

Set c_04 = New Collection
c_04.Add "item_1"

x0 = TypeName(c_05) ' Empty
x1 = VarType(c_05) ' 0
Set c_05 = New Collection
x2 = TypeName(c_05) ' Collection
c_05.Add "item_1"

Set c_06 = New Collection
c_06.Add "item_1"

Set c_07 = New Collection
c_07.Add "item_1"

Set c_08 = New Collection
c_08.Add "item_1"

Public scope

To make the Collection available for all procedures in the VBA project (Document) you will have to declare the object variable as Public in the declaration area of the codemodule of a macromodule.

If you declare the variable as Collection or Object, its Typename is 'Nothing' and the VarType 9.
After the 'Set' instruction its Typename is 'Collection', after which you can add elements to the variable.

If you declare an unspecified variabele its TypeName is 'Empty' and the VarTpe 0.
You can add elements after you assigned a new Collection to the variable, using the statement 'Set'.

In the declaration area

Public c_03 As Collection
Public c_04 As Object
Public c_05

In a procedure

2.3 Method With .... End With

You can use the method With ... End With to create a Collection without a variable.

With New Collection

.add "example"

End With

Anything happening with/in this Collection happens only between With ... End With.
Its 'scope' is therefore 'local' by definition.

2.4 Naming conventions

It can be practical to have a consistent naming convention for variables containing a Collection.
So it's clear in one glance what kind of variable you are confronting.
Avoid any name that refers to a reserved word or concept in the application or VBA.
The use of an underscore is therefore a guarantee.
In this page variables containing a Collection will be named 'c_00', 'c_01', 'c_02', etc.

3 Elements in the Collection

The contents of elements in a Collection are not related.
Elements can be similar (strings, numbers, dates, arrays, objects) or consist of every possible mix.
The only relation between them is their membership of the same Collection, their having an index number and possibly a unique key.

3.1 Add elements to a Collection

There is only one method to put elements into a Collection: .Add
The method .Add has 1 required argument: the content of the element.
The method .Add adds the index numbers automatically in the order in which elements are being added.

Dim c_00 as New Collection

c_00.add "abc"
c_00.add 123
c_00.add date
c_00.add array("aac","bbb","ccc")

With new Collection

.add "abc"
.add 123
.add date
.add array("aac","bbb","ccc")

End With

3.2 Add elements to a Collection using keys

A key must consist of a unique string.
If you add an element with a key that already exists in the Collection, VBA will generate an error and stop the execution of the code.

Dim c_00 as new Collection

c_00.add "abc","key_1"
c_00.add 123,"key_2"
c_00.add date,"key_3"
c_00.add array("aac","bbb","ccc"),"key_4"

With new Collection

.add "abc","key_1"
.add 123,"key_2"
.add date,"key_3"
.add array("aac","bbb","ccc"),"key_4"

End With

Since you can't retrieve an item's key in the Collection it can be practical to link the index number to the key.

Dim c_00 as new Collection

c_00.add "abc","K_" & c_00.count
c_00.add 123,"K_" & c_00.count
c_00.add date,"K_" & c_00.count
c_00.add array("aac","bbb","ccc"),"K_" & c_00.count

With new Collection

for j=1 to 4

.add choose(j,"abc",123,date, array("aac","bbb","ccc")),"K_" & j

End With

If you want to create a list of unique (string) items based on the content you can use the content as a key

On Error Resume Next
sn = Array("aa", "bb", "cc", "dd", "aa", "bb", "cc", "dd")
With New Collection

For Each it In sn

.Add it, it

MsgBox .Count
End With

Since a key can't consist of a number, you can convert numbers into strings to create a Collection of unique numbers

On Error Resume Next
sn = Array(5, 4, 3, 2, 3, 4, 5, 2)
With New Collection

For Each it In sn
.

.Add it, format(it)

Next
MsgBox .item(1) & vbtab & .item(2) & vbtab & .item(3) & vbtab & .item(4)

End With

3.3 The elements order in a Collection

Every element in a Collection has a unique index number.
The order in which elements are being added to the Collection determines the index numbers.
The first element gets index number 1, the last index number is equal to the result of the method .count

If you add an element to a Collection you can indicate in which order (before or after) which existing element in the Collection.
The method .add therefore contains 2 arguments: 'before' and 'after'.
The 'before' argument is the third argument, 'after' the fourth.
The arguments before and after require the index number of the item before respectively after which a new element should be inserted.
The retrieval of the index numbers requires a separate loop of all the Collection's items.

3.3.1 The order of elements based on index number

Use an index number to indicate where the new element should be inserted.

With New Collection

.Add "A1"
.Add "A2"
.Add "A3", , 1 ' insert this element after the first item in the Collection

.Add "A4", 2 ' insert this element before the second item in the Collection

MsgBox .item(1) & vbtab & .item(2) & vbtab & .item(3) & vbtab & .item(4)

End With

3.3.2 The order of elements based on the keys

If you use keys you can use the key to indicate where the new element should be inserted.

With New Collection

.Add "A4", "L1"
.Add "A5", "L2"
.Add "A6","L3" , ,"L2" ' insert this element after the item with key "L2"

.Add "A6","L4" , "L1" ' insert this element before the item with key "L1"

MsgBox .item(1) & vbtab & .item(2) & vbtab & .item(3) & vbtab & .item(4)

End With

3.3.3 The order of elements based on content

If content is the criterion where to insert a new element you will have to check each existing item in the Collection first.
The index number that fits the criterion can be applied to insert the new element.

With New Collection

.Add "A1"
.Add "D2"
.Add "G3"
.Add "H4"

c00 = "F9"
For j = 1 To .Count

If j = 1 And c00 < .Item(j) Then Exit For
If j > 1 Then

If c00 > .Item(j - 1) And c00 < .Item(j) Then Exit For

If j = 1 And StrComp(c00, .Item(j)) = -1 Then Exit For ' alternative method

If j > 1 Then

If StrComp(c00, .Item(j - 1)) & StrComp(c00, .Item(j)) = "1-1" Then Exit For ' alternative method

End If

Next

If j <= .Count Then .Add c00, , j ' before
If j > .Count Then .Add c00, , , j ' after

MsgBox .Item(1) & vbLf & .Item(2) & vbLf & .Item(3) & vbLf & .Item(4) & vbLf & .Item(5)

End With

4 The size of a Collection

You can retrieve the Collection's size with the property .Count

Msgbox c_00.count

If the Collection is empty the result of .Count is 0.

5 Retrieve an element from the Collection

Every element in a Collection has a:
- content
- unique index number

An element in a Collection can be linked to a unique key.
It's impossible to retrieve the keys in a Collection.
The link between a key and an index number can't be retrieved either.
To overcome that you can link the key and the index number when filling the Collection; cfr. link key and index number
You can search for an element in the Collections in three ways.

5.1 Retrieve the content of an element using its index number

Every integer between 1 and .count is a valid index number.
The result shows the content of the element (not it's key).

The first element

Dim c_00 as new Collection
y=c_00(1)
y=c_00.item(1)

The last element

Dim c_00 as new Collection
y=c_00(c_00.count)
y=c_00.item(c_00.count)

The one to last element

Dim c_00 as new Collection
y=c_00(c_00.count-1)
y=c_00.item(c_00.count-1)

The fourth element:

Dim c_00 as new Collection
y=c_00(4)
y=c_00.Item(4)

with createobject new Collection

y=.item(4)

end with

5.2 Retrieve an element using its key

To retrieve the content of the element that has the key "key_4":

Dim c_00 as New Collection
c_00.add 12,"key_4"

MsgBox c_00("key_4")
or
MsgBox c_00.Item("key_4")

with new Collection

.add "abc","Key_4"
MsgBox .item("Key_4")

end with

Since it's not possible to retrieve all keys in the Collection, you can only apply this method if you know which keys are present in the Collection.
For that purpose you can store all keys when adding them to the Collection in your code or in an object (workbook, document, userform).

5.3 Retrieve an element using its content

In this case you will have to loop through all elements in the Collection to check whether the content matches your search criterion.
Then you can determine the corresponding index number.
The key of an element can't be found in such a loop.

Dim c_00 as new Collection

for j= 1 to c_00.count

if c00(j)= "example" then exit for

next
if j < c00.count+1 then msgbox "index " & j

6 Check the existence of an element in a Collection

6.1 Check by key

The Collection has no built-in method to determine the existence of a certain key in the Collection.
You will have to create it yourself.
For instance:

on error resume next
y=c_00("Key_12")
msgbox "the key 'Key_12' exists" & iif(err.number=0,""," not")

6.2 Check by content

The Collection has no built-in method to establish whether an element contains a certain content.
You can use the same method as the one to retrieve an item by content. 5.3

Dim c_00 as new Collection

for j= 1 to c_00.count

if c00(j)= "example" then exit for

next
if j < c00.count+1 then msgbox "an element with content 'example' exists" & j

7 Filter elements from a Collection

The Collection has no built-in filter method.
You will have to design your own.
Some example code to filer by index number, by key or the content of elements.

7.1 Filter elements by index number

Populate an array with index numbers that have to be filtered and loop through that array:

With New Collection

For j = 1 To 7

.Add String(5, Chr(65 + j)), "L_" & .Count

Next
For Each it In Array(1, 3, 6, 7, 3, 1)

c00 = c00 & vbLf & .Item(it)

End With

MsgBox c00

7.2 Filter elements by key

Populate an array with the keys that have to be filtered and loop through that array:>

With New Collection

For j = 1 To 7

.Add String(5, Chr(65 + j)), "L_" & .Count

Next
For Each it In Array("L_6", "L_4", "L_3", "L_1", "L_0")

c00 = c00 & vbLf & .Item(it)

End With

MsgBox c00

7.3 Filter elements by content

The only way is to loop through all the Collection's items and check by content criterion:

With New Collection

For j = 1 To 7

.Add choose(j,"one","two minds","three","four","two hands","six","two towels"), "K_" & .Count

Next
For j = 1 To .Count

If instr(.Item(j),"two") Then c00 = c00 & vblf & .item(j)

End With

MsgBox c00

If the Collection is assigned to a variable you can also use the method For Each ... Next.

Set c_00 = New Collection

For j = 1 To 7

c_00.Add choose(j, "one","two minds","three","four","two hands","six","two towels"), "K_" & c_00.Count

Next
For Each it In c_00

If instr(it,"two") Then c00 = c00 & vblf & it

Next

MsgBox c00

8 Delete elements in a Collection

The method 'Remove' deletes an item by index number of key.
There's one caveat: every time an element has been deleted all the index numbers of the remaining items will be re-indexed.

If you want to delete an item based on its contents, you will have to loop through all elements of the Collection and check the condition; after that you can delete the item only by its index since the key can't be retrieved.

8.1 Delete an element by index

With New Collection

For j = 1 To 7

.Add String(5, Chr(65 + j)), "K_" & .Count

Next
.remove 5

End With

8.2 Delete an element by key

If the key is known to be existent in the Collection

With New Collection

For j = 1 To 7

.Add String(5, Chr(65 + j)), "K_" & .Count

Next
.remove "K_5"

End With

8.3 Delete an element by content

To prevent complications due to re-indexing loop through the Collection form the last index to the first.

With New Collection

For j = 1 To 7

.Add choose(j, "one","two minds","three","four","two hands","six","two towels"), "K_" & .Count

Next
For j = .Count to 1 step -1

If instr(.Item(j),"two") Then .Remove j

End With

8.4 Delete all elements

The Collection has no built-in method to delete all items simultaneously (like e.g. 'Clear' in a Dictionary or an Array).

If you assigned the Collection to a variable you can imitate 'clear' by assigning a new instance of the Collection to the variable.

set c_00 = New Collection

If the Collection has not been assigned to a variable you will have to delete each item separately.
After every deletion the remaining items will be re-indexed.
To prevent unexpected re-indexation results you can either remove the first item or the last item in every loop.

With New Collection

For j = 1 To 7

.Add String(5, Chr(65 + j)), "K_" & .Count

Next
For j = 1 To .Count

.remove 1
.remove .count ' alternative method

End With

9 Sorting elements in a Collection

The Collection lacks a built-in sorting method.
When adding elements you can use the arguments 'before' or 'after' of the method .Add to create an order in the elements of the CXollection.
You can only sort the elements of an existing Collection using a sorting method outside the Collection (e.g. Arraylist, Sortedlist, sorting in Excel or Word, etc.) and using the sorting result to add to a new Collection.

10 Example how to apply a Collection

For an example how to apply a Collection cfr. Check entry behaviour in a Userform