VBA voor smarties: SortedList

SortedList

1. What is a SortedList ?

2. Purpose of a SortedList

3. Origin of a SortedList

4. Create a SortedList
4.1 Early binding
4.1.1 Local scope
4.1.2 Private scope
4.1.3 Public scope
4.2 Late binding
4.2.1 Local scope
4.2.2 Private scope
4.2.3 Public scope

5. Populate a SortedList
5.1 add items
5.1.1 Method .Add
5.1.2 Method .Item
5.3 Kinds of Items
5.3.1 empty string
5.3.2 string
5.3.3 non-printable character
5.3.4 number
5.3.5 date
5.3.6 1-dimensional array
5.3.7 multi-dimensional array
5.3.8 object
5.3.9 controls in Userform
5.3.10 ActiveX controls
5.3.11 all worksheets

6. Size of a SortedList

7. Check an item's occurrence
7.1 Check by key
7.2 Check by value

8.1 A key's index number
8.2 A value's index number

9. Retrieving elements
9.1 1 element by index
9.2 1 element by key
9.3 1 element by key index
9.4 all values by index
9.5 all keys by index

10 Replace an element

11 Sorting
11.1 Sorting by string
11.2 Sorting by number
11.3 Sorting elements descendingly
11.4 Sorting keys descendingly

12 Delete elements
12.1 1 element by key
12.2 1 element by index
12.3 elements by content
12.4 all elements

13. Copy a SortedList

14. Entries

1. What is a SortedList ?

A SortedList in VBA is a collection object: you can store all kinds of data in it: numbers, string, dates, arrays, ranges, variables and objects.
VBA has several other storing objects:
- a dictionary
- a collection
- an array variabele
- an ActiveX ComboBox
- an ActiveX ListBox
- a Userform ComboBox
- a Userform ListBox
- an Arraylist

Which object to use is dependent of the goals you want to achieve.
This page will not compare all these methods.
The properties and methods of a SortedList will be the subject.

Almost all collection objects lack a built in sorting method except for the ArrayList.
That can be decisive to make use of a SortedList.
Another distinctive feature of a SortedList is the key.
The Arraylist sorts the content of the elements, the Sortedlist sorts the keys.

2. The purpose of a SortedList

You can use the SortedList to store data from several sources and operate on the elements in RAM.
Instead of data manipulation in an Excel-worksheet, a Word Document, a Powerpoint presentation you can use the RAM.
No time loss by screenrefreshing, recalculation etc.

3. The origin of the SortedList

The SortedList is not an element of the regular VBA-library.
The SortedList is part of the library System.Collections.
You can find the library in ....\WINDOWS\Microsoft.NET\Framework\v4.0.30319\mscorlib.tlb or in a comparable directory

In the VBEditor you can make a direct link to this library manually by checking mscorlib.dll in the References (Tools/references...)
If you distribute a file in which the link to the library is active, that link will be distributed too.

In VBA you can create the links in 2 different ways

ThisWorkbook.VBProject.References.AddFromFile "C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319\mscorlib.tlb"

or by

ThisWorkbook.VBProject.References.AddFromguid "{BED7F4EA-1A96-11D2-8F08-00A0C9A6186D}", 2, 4

A link to the SortedList ('early binding') isn't required to use the SortedList.
You can create the link at the moment when you need the SortedList ('late binding').
This VBA code does this:

createobject("System.Collections.SortedList")

4. Create a SortedList

There are several methods to create a SortedList.
It's dependent of:
- the way you want to link to the library: 'early binding' or 'late binding'
- the 'scope' of the SortedList: do you want to use the SortedList in 1 procedure or in several procedures:'local scope','private scope','public scope'

4.1 Reference to the library: 'early binding'

4.1.1 Local scope

If you only need the SortedList in 1 macro or function you can restrict its definition to that procedure.

without assignment to a variable:

With new SortedList

.Add "aa1"

end with

declaration and assignment at the same time

dim a_00 as New SortedList
a_00.Add "aa1"

implicit declaration by assignment

set a_00 = New SortedList
a_00.add "aa1"

declaration (as Object) and assignment separately

dim a_00 as Object
set a_00= new SortedList
a_00.add "aa1"

declaration (as SortedList) and assignment separately

dim a_00 as SortedList
set a_00= new SortedList
a_00.add "aa1"

4.1.2 Private scope

If you want to use a SortedList in several procedures (macros or function) in the same worksheet module, workbook module, Userform module, Macromodule or Classmodule you have to use Private Scope.
In that case the use of a variable is required.
You will have to declare that variable in the declaration area of the codemodule.

A variable for a SortedList can be declared as Object, SortedList or Variant.
Since Variant is the default you can refrain from declaring 'as Variant'.

In the declaration area of the codemodule

Dim a_00

Private a_00

Dim a_00 as Variant

Private a_00 as Variant

Dim a_00 as Object

Private a_00 as Object

Dim a_00 as SortedList

Private a_00 as SortedList

In a procedure (macro or function) the next code is required

Set a_00 = New SortedList

It can be done very compactly in the declaration area by:

Dim a_00 As New SortedList

Private a_00 as New SortedList

Now the SortedList is available in every procedure in the codemodule as the variable a_00.

4.1.3 Public scope

If the SortedList should be accessible in all procedures (macros or functions) in the workbook you need Public Scope.
Public Scope requires the use of a variable.

4.1.3.1 Declaration and assignment simultaneously

In order to make a variable accessible in all procedures in the workbook you need to declare that variable in the declaration area in a Macromodule.
And you will have to add a new instance of the SortedList.
In 'early binding' it can be done very neatly:

Public a_00 As New SortedList

Now the SortedList is available in every procedure in the VBProject (= all procedures in the Workbook) as the variable a_00.

4.1.3.2 Declaration in the declaration area and assignment in a procedure

Declare the variable in the declaration area in a macromodule.

The variable for a SortedList can be of the type Object, SortedList or Variant.
Since Variant is the default Vartype you can omit 'as Variant'.

In the declaration area

Public a_00

Public a_00 as Variant

Public a_00 as Object

Public a_00 as SortedList

In a procedure (macro or function)

Assign a new instance of the SortedList to the declared variable.

set a_00 = new SortedList

Now the SortedList is available in every procedure in the VBProject (= all procedures in the Workbook) as the variable a_00.

4.2 Without reference to the library: 'late binding'

4.2.1 Local scope

If you need the SortedList only in 1 macro or function you can restrict its definition to that procedure.

without a variable:

With CreateObject("System.Collections.SortedList")

.Add "aa1", 12

end with

assigning to a variable:

set a_00 = CreateObject("System.Collections.SortedList")
a_00.Add "aa1", 20

There is an alternative way to create 'late binding'.
You can use the Class Identification Number CLSID in combination with the Getobject method.
Every CLSID is stored in the Windows Registry.

without a variable:

With GetObject("New:{026CC6D7-34B2-33D5-B551-CA31EB6CE345}") ' sortedlist

.Add "aa1", 45

End With

assigning to a variable:

set a_00 = GetObject("New:{026CC6D7-34B2-33D5-B551-CA31EB6CE345}") ' sortedlist
a_00.Add "aa1", 45

4.2.2 Private scope

In order to make a SortedList accessible in several procedures (macros or functions) in the same worksheet module, workbook module, Userform module, Macromodule or Classmodule you'll have to use 'Private Scope'.
In that case a variable is required.
That variable has to be declared in the declaration area of the codemodule.

A variable that contains a SortedList can be declared as Object or Variant.
Since Variant is the default variable type you can omit the specification 'as Variant'.

In a procedure (macro or function) you assign a new instance of the SortedList to the variable.

In the declaration area of the codemodule

Dim a_00

Private a_00

Dim a_00 as Variant

Private a_00 as Variant

Dim a_00 as Object

Private a_00 as Object

In a procedure (macro or function)

Set a_00 = CreateObject("System.Collections.SortedList")

Now the SortedList is available in every procedure in the codemodule by variable a_00.

4.2.3 Public scope

To make a SortedList accessible in all procedures (macros or functions) in the entire workbook you need Public Scope.
A variable in which to store the SortedList is required in that case.
The variable has to be declared in the declaration area of a (any) Macromodule.

The variable type of the SortedList can be Variant of Object.
Since there's no link to the SortedList library the type 'SortedList' can't be used.

In a procedure (macro or function) you assign a new instance of the SortedList to the declared variable.

In the declaration area of a macromodule

Public a_00

Public a_00 as Variant

Public a_00 as Object

In a procedure (macro or function)

set a_00 = CreateObject("System.Collections.SortedList")

The SortedList is now available in every procedure in the VBproject ( = the workbook).

In this page I will only use the With ... End With method combined with 'late binding'.

5. Populate a SortedList

Every element in a SortedList get a key and has a value.
A key is unique.
The elements in the SortedList will be ascendingly sorted by their keys.
The DortedList has 2 methods to add elements to the SortedList: .Add and .Item("key").
The method .Add generates an error if a key already exists.
The method .Item("key") replaces the element that is linked to an existing the key.

The content of an element can be 'anything': number, string, dates, array, range, variable, collection, dictionary, an empty string, nothing or an object.

You can only add distinct elements item by item.

5.1 Add elements

5.1.1 method .Add

The first argument of the .Add method contains the key, the second argument the element itself.
The method .Add puts the elements into the SortedList and sorts the element based on its key.
The sorting affects the index number of the element, not its key or its content.

With CreateObject("System.Collections.SortedList")

.Add "floor","ceiling" .Add "1", New Collection
.Add "2",123
.Add "now", Date
.Add "last", Array("red", "white", "green")

End With

5.1.2 method .Item

The argument of teh method .Item contains the key.
The method .Item puts an element in the SortedList and sorts the new element by its key.
The sorting affects the index number of the element, not its key or its content.

With CreateObject("System.Collections.SortedList")

.Item("aa1")= 1234
.Item("aa2")= "text"
.Item("aa4")= new Collection
.Item("aa3")= Array("red", "white", "green"

End With

5.3 Elements in a SortedList

New elements will be sorted immediately when added to the SortedList.
The elements can be varied: numbers, string, dates, arrays, ranges, variables, collections, dictionaries, an empty string, nothing or objects
Some illustrations:

5.3.1 an empty string

With CreateObject("System.Collections.SortedList")

.Add "c_00" ,vbNullString
.Add "c_01",""

End With

5.3.2 a normal string

With CreateObject("System.Collections.SortedList")

.Add "d_00", "abcde"
.Item("d_01")="fghi"

End With

5.3.3 a non-printable character

With CreateObject("System.Collections.SortedList")

.Add "e_00", vbTab
.Item("e_01 ")=vbLf

End With

5.3.4 a number

With CreateObject("System.Collections.SortedList")

.Add "f_00", 12345
.Item("f_01")= RGB(23, 45, 678)

End With

' typename: Integer
' typename: Long

5.3.5 a date (typename: Date)

With CreateObject("System.Collections.SortedList")

.Add "g_00", Date
.Add "g_01", CDate("23-04-2012")
.Item ("g_02")= DateSerial(2013, 10, 12)

End With

5.3.6 a 1-dimensional Array (typename: Variant())

With CreateObject("System.Collections.SortedList")

.Add "h_00", Array("aa1", "aa2", "aa3")
.Add "h_01", Split("bb1_cc1_dd1", "_")
.Item("h_02")=Array("aa1", "aa2", "aa3")

End With

5.3.7 a multi-dimensional Array (typename: Variant())

With CreateObject("System.Collections.SortedList")

ReDim sn(6, 10)
.Add "i_00", sn
.Item("i_01") = sn

End With

5.3.8 an object

With CreateObject("System.Collections.SortedList")

.Add "j_00", Range("A1:K10")
.Item("j_01") = Range("A1:K10")

End With

5.3.9 the controls in a userform

With CreateObject("System.Collections.SortedList")

For Each it In Controls

.Add it.name, it

End With

5.3.10 the ActiveX-controls in a worksheet

With CreateObject("System.Collections.SortedList")

For Each it In Sheets("sheet1").OLEObjects

.Add it.name, it

End With

5.3.11 all worksheets in a workbook

With CreateObject("System.Collections.SortedList")

For Each sh In Sheets

.Add sh.name, sh
.Item(.count)= sh

End With

6. The size of a SortedList

The property .Count represents the number of elements in a SortedList.
This property simultaneously represents the amount of keys in the SortedList.

The SortedList has two equivalent properties: .Values.Count and .Keys.Count.

With CreateObject("System.Collections.SortedList")

For Each sh In Sheets

.Add sh.name, sh
.Item(.count) = sh

Next

msgbox .Count
msgbox .Values.Count
msgbox .Keys.Count

End With

7.1.1 Check the occurrence of a certain key: .Contains

The property .Contains indicates the occurrence of a specific key: True or False

With CreateObject("System.Collections.SortedList")

For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")

If Not .contains(it) Then .Add it, 1200

Next

MsgBox .Contains("aa4")
MsgBox .Contains("aa9")

End With

7.1.2 Check the occurrence of a certain key: .ContainsKey

The property .ContainsKey indicates whether a SortedList contains a specific key: True or False.
You can use .ContansKey to prevent the replacement of a value or the generation of an error.

With CreateObject("System.Collections.SortedList")

For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")

If Not .ContainsKey(it) Then .Add it, 388

End With

7.2 Check the occurrence of a specific value: .ContainsValue

The property .ContainsValue indicates whether the SortedList contains a certain value: True of False.
You can use .ContainsValue to prevent that a certain value will be connected to 2 different keys.

With CreateObject("System.Collections.SortedList")

For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")

If Not .ContainsValue(it) Then .Add .count, it

End With

8.1 Check the position of a key: .IndexOfKey

The property .IndexOfKey returns the index number of a certain key.
The first key has index nummer 0.
If a key doesn't exist the resulting value is -1.

With CreateObject("System.Collections.SortedList")

For Each it In Array("aa1", "aa2", "aa3", "aa4", "aa5", "aa6")

If Not .contains(it) Then .Add it, 2*.count

Next

msgbox .IndexOfKey("aa4")

End With

8.2 Check the position of a element: .IndexOfValue

The property .IndexOfValue returns the index number of a certain element.
The first element has index number 0.
If the element doesn't exist the resulting value is -1.

With CreateObject("System.Collections.SortedList")

For Each it In Array("aa1", "aa2", "aa3", "aa4", "aa5", "aa6")

If Not .contains(it) Then .Add it, 2*.count

Next

msgbox .IndexOfValue(6)

End With

9. Retrieving elements

9.1 Retrieving 1 element by its index number: .GetByIndex and .GetValueList

You can use the index number to return the corresponding element in the SortedList.
The first item in the SortedList has index number 0.
2 methods are applicable: .GetByIndex en .GetValueList

With CreateObject("System.Collections.SortedList")

For Each it In Array("aa1", "aa2", "aa3", "aa5", "aa4", "aa6")

.Add it, 3*.count

Next

msgbox .GetByIndex(0)
msgbox .GetByIndex(.count-1)
msgbox .GetByIndex(3)
msgbox .GetValueList(0)
msgbox .GetValueList(.count-1)
msgbox .GetValueList(3)

End With

' the first item
' the last item
' the 4th element in the SortedList

9.2 Retrieving 1 element by its key: .Item

Based on the key the method .Item can retrieve the corresponding element.

With CreateObject("System.Collections.SortedList")

For Each it In Array("aa1", "aa2", "aa3", "aa5", "aa4", "aa6")

.Add it, 3*.count

Next

msgbox .Item("aa4")

End With

9.3 Retrieving a key corresponding by index number: .GetKey en .GetKeyList

If you want to know which key has which rank in a SortedList you can use these methods.
The methods .GetKey and .GetKeylist do exactly this.

With CreateObject("System.Collections.SortedList")

For Each it In Array("aa1", "aa2", "aa3", "aa5", "aa4", "aa6")

.Add it, 6*.count

Next

Msgbox .getkey(2)
MsgBox .getkeylist()(2)

End With

9.4 Retrieving all elements based on index number: .GetByIndex and .GetValueList

The ArrayList has a an own method to retrieve all elements: .ToArray.
The Dictionary also has such a method: .Items.
The SortedList misses such a method.
In the SortedList you will need a loop to read all separate elements.
The methods .GetByIndex and .GetValueList are the appropriate methods to do this.
The result is a list containing all elements, ascendingly sorted by their keys.
The descending order you can get by retrieving the elements by descending index number.

With CreateObject("System.Collections.SortedList")

For Each it In Array("aa1", "aa2", "aa3", "aa5", "aa4", "aa6")

.Add it, 6*.count

Next
For j = 0 To .Count - 1

c00 = c00 & .GetByIndex(j)
c01 = c01 & .GetValueList()(j)
c02 = c02 & .GetByIndex(.count-1-j)
c03 = c03 & .GetValueList()(.count-1-j)

Next

Msgbox c00
Msgbox c01
Msgbox c02
Msgbox c03

End With

9.5 Retrieving all keys: .GetKey en .GetKeyList

The Dictionary has a method to retrieve all keys: .Keys.
In the SortedList you will need a loop to read all separate keys.
The methods .GetKey and .GetKeyList are the appropriate methods to do so.
The result is a list containing all ascendingly sorted keys.
The descending order you can get by retrieving the keys by descending index number.

With CreateObject("System.Collections.SortedList")

For Each it In Array("aa1", "aa2", "aa3", "aa3", "aa2", "aa6")

.Add .count, it

for j = 0 to .count-1

c00 = c00 & vblf & .getkey(j)
c01 = c01 & vbLf & .GetKeylist()(j)
c02 = c02 & .GetKey(.count-1-j)
c03 = c03 & .GetKeyList()(.count-1-j)

msgbox c00
msgbox c01
Msgbox c02
Msgbox c03

End With

10.1 Change/Replace an element by its key: .Item

You can change/replace an element in a Sorted by assigning a new value or element.
If the key is know you can use the method .Item.

With CreateObject("System.Collections.SortedList")

For Each it In Array("aa1", "aa2", "aa3", "aa8", "aa12", "aa6")

.Add it, it

Next

msgbox .Item("aa3")
.Item("aa3")=1235
msgbox .Item("aa3")

End With

10.2 Change/Replace an element by its indexnumber: .SetByIndex

You can change/replace an element in a SortedList by assigning a new value or element.
If the index number is known you can use the method .SetByIndex.

With CreateObject("System.Collections.SortedList")

For Each it In Array("aa1", "aa2", "aa3", "aa8", "aa12", "aa6")

.Add it

Next

msgbox .GetByIndex(3)
.SetByIndex(3)="bb20"
msgbox .GetByIndex(3)

End With

11. Sorting the elements

The SortedList sorts the elements ascendingly based on the keys.
The SortedList evaluates whether the sorting should be numerical or by string.
If the keys have not the same type (numerical or string) an error occurs.

11.1 Sorting by string

sorting: textcompare

With CreateObject("System.Collections.SortedList")

For Each it In Array("aa1", "aa2", "aa3", "aa113", "aa27", "aa6")

.Add it, .count

Next

for j = 0 to .count-1

c00 = c00 & vblf & .getkey(j)

next

msgbox c00

End With

11.2 Sorting by number

sorting: numerical

With CreateObject("System.Collections.SortedList")

For Each it In Array(12, 112, 2, 34, 305, 302)

.Add .count, it

Next

For j = 0 to .count-1

c00 = c00 & vblf & .getkey(j)

next

msgbox c00

End With

11.3 sorting elements descendingly

The SortedList sorts all elements ascendingly.
To retrieve the elements in descending order you will heave to read/retrieve the elements descendingly using .GetByIndex.

With CreateObject("System.Collections.SortedList")

For Each it In Array("aa1", "aa2", "aa3", "aa39", "aa22", "aa6")

.Add it, .count

Next

for j = .count-1 to 0 step -1

c00 = c00 & vblf & .GetByIndex(j)

next

msgbox c00

End With

11.4 sorting keys descendingly

The SortedList sorts all keys ascendingly.
To retrieve the keys in descending order you will heave to read/retrieve the keys descendingly, using .GetKey.

With CreateObject("System.Collections.SortedList")

For Each it In Array("aa1", "aa2", "aa9", "aa8", "aa12", "aa6")

.Add it, .count

Next

For j = .count-1 to 0 step -1

c00 = c00 & vblf & .GetKey(j)

next

msgbox c00

End With

12. Deleting elements

12.1 Delete 1 element by key: .Remove

The method .Remove deletes 1 element in the SortedList.
The argument between the brackets is the key value.
If the key doesn't exist nothing happens; VBA does not generate an error.

Delete the item that has the key "aa3":

With CreateObject("System.Collections.SortedList")

For Each it In Array("aa1", "aa2", "aa8", "aa3", "aa12", "aa6")

.Add it, .count*5

Next

MsgBox .Count
.Remove "aa3"
MsgBox .Count

End With

12.2 delete 1 element by index number: .RemoveAt

The method .RemoveAt deletes the element in the position of the index number in the argument.
If the index number doesn't exist VBA creates an error message.
The first element has index number 0

Delete the 5th element ( = index number 4)

With CreateObject("System.Collections.SortedList")

For Each it In Array("aa1", "aa2", "aa3", "aa4", "aa5", "aa6")

.Add it

Next

MsgBox .Count
.RemoveAt 4
MsgBox .Count

End With

12.3 delete an element based on its content

The SortedList has no special method to do this.
Looping through the sortedlist and comparing the contents of elements to a criterion is the way to go.
You can use both .Remove and .RemoveAt to delete items.
Every time an element has been deleted, all index numbers will be changed also.
Therefore the best way to delete several items is to use a descending loop. Delete by index number

With CreateObject("System.Collections.SortedList")

For Each it In Array("aa1", "aa2", "aa8", "aa3", "aa12", "aa6")

.Add it, .Count * 5

Next

MsgBox .Count

For j = .Count – 1 to 0 Step -1

If .getbyIndex(j) = 25 Then .RemoveAt j

Next

MsgBox .Count

End With

Delete by key

With CreateObject("System.Collections.SortedList")

For Each it In Array("aa1", "aa2", "aa8", "aa3", "aa12", "aa6") .

Add it, .Count * 5

Next

MsgBox .Count

For j = .Count - 1 to 0 Step -1

If .getbyIndex(j) = 25 Then .Remove .getkey(j)

Next

MsgBox .Count

End With

12.4 Delete all elements: .Clear

The method .Clear deletes all elements from the SortedList.

With CreateObject("System.Collections.SortedList")

For Each it In Array("aa1", "aa2", "aa3", "aa8", "aa12", "aa6")

.Add it, .count*3

Next
.Clear

MsgBox .Count

End With

13. Copy the SortedList: .Clone

You can make a copy of a SortedList with the method .Clone.
Changes in the copy do not affect the original SortedList.
Since the copy is also an Object ( = SortedList) is, you need the instruction 'Set' to assign the copy to a variable.

With CreateObject("System.Collections.SortedList")

For Each it In Array("aa1", "aa2", "aa3", "aa8", "aa12", "aa6")

.Add it, it

Next

set c_00=.Clone
msgbox c_00.Count

c_00.item(2)="~"
MsgBox .Item("aa2") & vbTab & c_00.Item("aa2"))

End With