Suggestions | 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 |
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.
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.
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" ThisWorkbook.VBProject.References.AddFromguid "{BED7F4EA-1A96-11D2-8F08-00A0C9A6186D}", 2, 4 You can create the link at the moment when you need the SortedList ('late binding'). This VBA code does this: createobject("System.Collections.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'
without assignment to a variable: With new SortedList .Add "aa1"
end withdim a_00 as New SortedList a_00.Add "aa1" set a_00 = New SortedList a_00.add "aa1" dim a_00 as Object set a_00= new SortedList a_00.add "aa1" dim a_00 as SortedList set a_00= new SortedList a_00.add "aa1" 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 Set a_00 = New SortedList Dim a_00 As New SortedList Private a_00 as New SortedList 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.
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
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 Assign a new instance of the SortedList to the declared variable. set a_00 = new SortedList
without a variable: With CreateObject("System.Collections.SortedList") .Add "aa1", 12
end withset a_00 = CreateObject("System.Collections.SortedList") a_00.Add "aa1", 20 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 Withset a_00 = GetObject("New:{026CC6D7-34B2-33D5-B551-CA31EB6CE345}") ' sortedlist a_00.Add "aa1", 45 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 Set a_00 = CreateObject("System.Collections.SortedList") 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 set a_00 = CreateObject("System.Collections.SortedList") In this page I will only use the With ... End With method combined with 'late binding'. 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. 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
End With.Add "2",123 .Add "now", Date .Add "last", Array("red", "white", "green") 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
End With.Item("aa2")= "text" .Item("aa4")= new Collection .Item("aa3")= Array("red", "white", "green" The elements can be varied: numbers, string, dates, arrays, ranges, variables, collections, dictionaries, an empty string, nothing or objects Some illustrations: With CreateObject("System.Collections.SortedList")
.Add "c_00" ,vbNullString
End With.Add "c_01","" With CreateObject("System.Collections.SortedList")
.Add "d_00", "abcde"
End With.Item("d_01")="fghi"
With CreateObject("System.Collections.SortedList")
.Add "e_00", vbTab
End With.Item("e_01 ")=vbLf With CreateObject("System.Collections.SortedList")
.Add "f_00", 12345
End With.Item("f_01")= RGB(23, 45, 678) ' typename: Integer
' typename: Long With CreateObject("System.Collections.SortedList")
.Add "g_00", Date
End With.Add "g_01", CDate("23-04-2012") .Item ("g_02")= DateSerial(2013, 10, 12)
With CreateObject("System.Collections.SortedList")
.Add "h_00", Array("aa1", "aa2", "aa3")
End With.Add "h_01", Split("bb1_cc1_dd1", "_") .Item("h_02")=Array("aa1", "aa2", "aa3")
With CreateObject("System.Collections.SortedList")
ReDim sn(6, 10)
End With.Add "i_00", sn .Item("i_01") = sn With CreateObject("System.Collections.SortedList")
.Add "j_00", Range("A1:K10")
End With.Item("j_01") = Range("A1:K10")
With CreateObject("System.Collections.SortedList")
For Each it In Controls
End With.Add it.name, it
Next
With CreateObject("System.Collections.SortedList")
For Each it In Sheets("sheet1").OLEObjects
End With.Add it.name, it
Next
With CreateObject("System.Collections.SortedList")
For Each sh In Sheets
End With.Add sh.name, sh
Next.Item(.count)= sh 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
End With.Add sh.name, sh
Next.Item(.count) = sh msgbox .Count msgbox .Values.Count msgbox .Keys.Count
With CreateObject("System.Collections.SortedList") For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")
End WithIf Not .contains(it) Then .Add it, 1200
NextMsgBox .Contains("aa4") MsgBox .Contains("aa9")
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")
End WithIf Not .ContainsKey(it) Then .Add it, 388
Next
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")
End WithIf Not .ContainsValue(it) Then .Add .count, it
Next
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")
End WithIf Not .contains(it) Then .Add it, 2*.count
Nextmsgbox .IndexOfKey("aa4")
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")
End WithIf Not .contains(it) Then .Add it, 2*.count
Nextmsgbox .IndexOfValue(6)
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")
End With.Add it, 3*.count
Nextmsgbox .GetByIndex(0) msgbox .GetByIndex(.count-1) msgbox .GetByIndex(3) msgbox .GetValueList(0) msgbox .GetValueList(.count-1) msgbox .GetValueList(3) ' the first item
' the last item ' the 4th element in the SortedList
With CreateObject("System.Collections.SortedList") For Each it In Array("aa1", "aa2", "aa3", "aa5", "aa4", "aa6")
End With.Add it, 3*.count
Nextmsgbox .Item("aa4")
The methods .GetKey and .GetKeylist do exactly this. With CreateObject("System.Collections.SortedList") For Each it In Array("aa1", "aa2", "aa3", "aa5", "aa4", "aa6")
End With.Add it, 6*.count
NextMsgbox .getkey(2) MsgBox .getkeylist()(2)
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")
End With.Add it, 6*.count
NextFor j = 0 To .Count - 1 c00 = c00 & .GetByIndex(j)
Nextc01 = c01 & .GetValueList()(j) c02 = c02 & .GetByIndex(.count-1-j) c03 = c03 & .GetValueList()(.count-1-j) Msgbox c00 Msgbox c01 Msgbox c02 Msgbox c03
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")
End With.Add .count, it
Nextfor j = 0 to .count-1
msgbox c00c00 = c00 & vblf & .getkey(j)
nextc01 = c01 & vbLf & .GetKeylist()(j) c02 = c02 & .GetKey(.count-1-j) c03 = c03 & .GetKeyList()(.count-1-j) msgbox c01 Msgbox c02 Msgbox c03
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")
End With.Add it, it
Nextmsgbox .Item("aa3") .Item("aa3")=1235 msgbox .Item("aa3")
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")
End With.Add it
Nextmsgbox .GetByIndex(3) .SetByIndex(3)="bb20" msgbox .GetByIndex(3) 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. sorting: textcompare With CreateObject("System.Collections.SortedList")
For Each it In Array("aa1", "aa2", "aa3", "aa113", "aa27", "aa6")
End With.Add it, .count
Nextfor j = 0 to .count-1 c00 = c00 & vblf & .getkey(j)
nextmsgbox c00 With CreateObject("System.Collections.SortedList")
For Each it In Array(12, 112, 2, 34, 305, 302)
End With.Add .count, it
NextFor j = 0 to .count-1 c00 = c00 & vblf & .getkey(j)
nextmsgbox c00
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")
End With.Add it, .count
Nextfor j = .count-1 to 0 step -1 c00 = c00 & vblf & .GetByIndex(j)
nextmsgbox c00
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")
End With.Add it, .count
NextFor j = .count-1 to 0 step -1 c00 = c00 & vblf & .GetKey(j)
nextmsgbox c00
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")
End With.Add it, .count*5
NextMsgBox .Count .Remove "aa3" MsgBox .Count
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")
End With.Add it
NextMsgBox .Count .RemoveAt 4 MsgBox .Count
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")
End With.Add it, .Count * 5
NextMsgBox .Count For j = .Count – 1 to 0 Step -1 If .getbyIndex(j) = 25 Then .RemoveAt j
NextMsgBox .Count With CreateObject("System.Collections.SortedList")
For Each it In Array("aa1", "aa2", "aa8", "aa3", "aa12", "aa6")
.
End WithAdd it, .Count * 5
NextMsgBox .Count For j = .Count - 1 to 0 Step -1 If .getbyIndex(j) = 25 Then .Remove .getkey(j)
NextMsgBox .Count
With CreateObject("System.Collections.SortedList")
For Each it In Array("aa1", "aa2", "aa3", "aa8", "aa12", "aa6")
End With.Add it, .count*3
Next.Clear MsgBox .Count
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")
End With.Add it, it
Nextset c_00=.Clone msgbox c_00.Count c_00.item(2)="~" MsgBox .Item("aa2") & vbTab & c_00.Item("aa2")) |