Example File
Dictionaries
|
1 What's a Dictionary 2 The purpose of a Dictionary 3 Dictionary backgroud 4 Create a Dictionary 5 Dictionary: populating 5.1 .Add 5.2 .Item( )= 5.3 =.Item() 5.4 objectvariable 6 Add or replace 6.1 new key 6.2 existing key 6.2.1 .Add 6.2.2 .Item()= 6.2.3 =.Item() 6.2.4 objectvariable 7 Keys 7.1 string 7.2 numner 7.3 date 7.4 object 7.5 ActiveX-control 7.6 several keys 8 Unique key 9 create unique keys 10 Unique elements 11 Items 11.1 empty item 11.2 empty string 11.3 normal string 11.4 non-printable character 11.5 number 11.6 date 11.7 1-dimensional array 11.8 multi-dimensional array 11.9 object 11.10 userformcontrol 11.11 ActiveX-control 11.12 all worksheets 11.13 User Defined Type (UDT) 12 Dictionary: size 13 Search key / item 14 retrieve element 14.1 unique keys 14.2 indexnumber 14.3 indexnumber of it's key 14.4 keys: loop 14.5 Filter keys 15 key collection 15.1 store 15.2 in variable 15.3 in string 15.4 calculating with keys 15.5 Filtering keys 16 Item collection 16.1 store 16.2 in variable 16.3 in string 16.4 calculing with items 16.5 filtering items 17 Change key 18 Copy item 19 Delete item 20 Adapt item content 20.1 Replace content 20.2 Add to content 20.3 adapt / calculate 20.3.1 numbers 20.3.2 dates 20.3.3 boolean 20.4 adpt an array-item 20.4.1 1-dimensional array 20.4.2 2-dimensional array 21 Clear Dictionary 22 Early binding and late binding 22.1 Late binding 22.1.1 implicit object 22.1.2 object variable 22.2 early binding 22.2.1 implicit object 22.2.2. objectvariable 22.2.3.1 private objectvariable 22.2.3.2 public objectvariable 23 Examples 23.1 remove duplicates 23.2 Filter highest value 23.3 Filter lowest value 23.4 records autofill 23.5 worksheets integration 23.6 csv data integration 23.7 unique elementens 23.8 ActiveX controls: check polpulating 23.9 worksheeetcontrol 23.10 convert string to 2-dimensional array |
A Dictionary in VBA is a collectionobject: you can store all kinds of things in it: numbers, texts, dates, arrays, ranges, variables and objects. Every item in a Dictionary gets its own unique key. With that key you can get direct access to the item (reading/writing/adapting). VBA has several methods to store data: - a Dictionary - a Collection - an array (matrix) variable - an ActiveX ComboBox - an ActiveX ListBox - a Userform control ComboBox - a Userform control ListBox - a sortedlist - an arraylist Which one to use is dependent of your ultimate goal. This tutorial doesn't offer an exhaustive comparison of all these methods. What a Dictionary has to offer will be discussed in detail. With that knowledge it's easier to compare different methods and to make a choice between them. An important chracteristic of a Dictionary is the use of unique keys. It's not necessary to look for an item using a loop or by applying the Excelfunction application.match. That's an advantage of a Dictionary compared to an array/matrix-variable. The way in which the Dictionary stores items is comparable to the Collection object. The Dictionary has some properties/functions/methods like .keys, .items and .removeall, that can be advantageous compared to the use of a collection. So the preference for a Dictionary over a matrix variable or Collection is dependent of what you want to use it for.
Instead of manipulating data in an Excel-worksheet, a Word Document, or a Powerpoint presentation on the hard disk, you can do that in memory. No screenrefreshing, no autocalculation, no background backup is necessary, so it will speed up your code considerably. Generally speaking you can use a Dictionary to store/group data that have a common property (key). Scattered data can be integrated easily. A Dictionary can only contain unique keys. That's why you can use the property .keys to create a list of unique strings, numbers or dates. Although the Dictionary has not been designed for that purpose it's a nice side effect. The unique keys will not be sorted; their order matches the order in which they have been added to the Dictionary. If you need a sorted list of unique keys you will have to sort the data before adding them to a Dictionary, or you need to use methods from another VBA library (e.g. system.arraylist or system.orderedlist) You might need unique lists e.g. as validation list in Excel, the contents of an ActiveX-control (combobox or listbox) or a Userform control (combobox or listbox).
It's an element in the Microsoft Scripting Runtime library. That library is in the file ..\Windows\system32\scrrun.DLL or in a similar directory In the VBEditor you can make a reference to this library by enabling 'Microsoft Scripting Runtime' (Menu / Tools / References..) The reference will be stored in the workbook. If you distirbute the workbook the reference will be distributed simultaneously.
First I'll discuss what you can use a Dictionary for. In the last part (XXII) I will explain the several methods to create a Dictionary into detail. For simplicity's sake until then I will only use 1 method to illustrate the Dictionary. It's the simplest and least accident prone. To create a Dictionary this code suffices With CreateObject("scripting.Dictionary") End With All instructions / properties starting with a dot . between With ... End With refer to the newly created Dictionary e.g.: With CreateObject("scripting.dictionary") .Add "first name", "contents"
End Withor e.g.: With CreateObject("scripting.dictionary") MsgBox .Count
End With
To do so you need the contents of an item and a unique key. The contents can be anything: numbers, strings, dates, arrays, ranges, variables, collections, dictionaries, an empty string, nothing and objects A key can consist of a number, string, date or object, or a variable containing a number, string, date or object An array can't be used as a key (nor a 1-dimensional array, nor a multidimensional array). For simplicity's sake I will only use strings as keys to prevent confusion between keys and index numbers. There are 4 different methods to add an item to a Dictionary Add an item to the Dictionary; its content is the string "contents", its key is the string "first" With CreateObject("scripting.dictionary") .Add "first", "content"
End WithThis item in the Dictionary has all the object's properties: With CreateObject("scripting.dictionary") .Add "range", sheet1.range("A1:K10")
End WithMsgbox .item("range").rows.count With CreateObject("scripting.dictionary") .Item("first") = "content"
End WithThis item in the Dictionary has all the object's properties: With CreateObject("scripting.dictionary") Set .Item("range") = sheet1.range("A1:K10")
End WithMsgbox .item("range").rows.count With CreateObject("scripting.dictionary") x0 = .Item("first")
End WithIf the item with this key doesn't exist the item will be added to the Dictionary, using the indicated key. No contents will be assigned to the item.
Set dict_snb = CreateObject("scripting.dictionary") dict_snb("first") = Date Written alternatively: Set dict_snb = CreateObject("scripting.dictionary") dict_snb.Item("first") = Date
All methods (.Add, .Item()=, =Item() and the use of an objectvariable()= ) do this identically.
This is similar to the use of a Collection With CreateObject("scripting.dictionary") .Add "first", Date
End With.Add "first", "new" Contrary to the method .Add no error is being reported. With CreateObject("scripting.dictionary") .Item("first") = Date
End WithMsgBox .Item("first") .Item("first") = "new" MsgBox .Item("first") If the key exists no change will take place in the Dictionary. With CreateObject("scripting.dictionary") x0 = .Item("first")
End With
Contrary to the method .Add no error is being reported. Set dict_snb = CreateObject("scripting.dictionary") dict_snb("first") = Date MsgBox dict_snb("first") dict_snb("first") = "new" MsgBox dict_snb("first") Set dict_snb = CreateObject("scripting.dictionary") dict_snb.Item("first") = Date MsgBox dict_snb.Item("first") dict_snb.Item("first") = "new" MsgBox dict_snb.Item("first") A key can consist of a string, a number, a date or an object. A key can also consist of a variable that is a string, a number, a date, or an object. A Dictionary can contain several different kinds of keys (string, number, date, object). Several different keys illustrated With CreateObject("scripting.dictionary") .Items("aa1") = "example 1 "
end with.Add "aa2", "example 2" x = .Item("aa3") dict_snb("aa4") = "example 3" With CreateObject("scripting.dictionary") .Items(2) = "example 4"
end with.Items(1234589) = "example 5" .Add 23, "example 6" x = .Item(45) dict_snb(56788) = "example 7" With CreateObject("scripting.dictionary") .Items(Date) = "example 8"
end with.Items(CDate("12-03-2013")) = "example 9" .Add DateAdd("m", 2, 1), "example 10" x = .Item(DateSerial(2013, 5, 5)) dict_snb(56788) = "example 11" With CreateObject("scripting.dictionary") .Items(TextBox1) = "example 12"
end with.Items(TextBox2) = "example 13" .Add ListBox1, "example 14" x = .Item(Sheet1) dict_snb(ComboBox1) = "example 15"
With CreateObject("scripting.dictionary") For Each it In Sheets("sheet1").OLEObjects
end with.Item(it) = it.Name
Next
With CreateObject("scripting.dictionary") For Each it In Array("bb1", 12, 45673, Date + 10, sheet4)
end with.Item(it) = "Item 1" & .Count
Next- a distinction between lowercase and uppercase; ToDay is not identical to the key today - no distinction between lowercase and uppercase; ToDay is identical to the key today The property .comparemode indicates the way keys are being compared. - .comparemode unspecified: lowercase and uppercase are relevant - .comparemode = BinaryCompare (0) : lowercase and uppercase are relevant - .comparemode = TextCompare (1) : no relevance to lowercase or uppercase. With CreateObject("scripting.dictionary") For Each it In Array("aa1", "AA1", "Aa1", "aA1", "bb1", "BB1", "Bb1", "bB1")
End Withy = .Item(it)
NextMsgBox .Count'8 unique keys MsgBox Join(.Keys, vbLf) With CreateObject("scripting.dictionary") .CompareMode = 0
End WithFor Each it In Array("aa1", "AA1", "Aa1", "aA1", "bb1", "BB1", "Bb1", "bB1") y = .Item(it)
NextMsgBox .Count' 8 unique keys MsgBox Join(.Keys, vbLf) With CreateObject("scripting.dictionary") .CompareMode = 1
End WithFor Each it In Array("aa1", "AA1", "Aa1", "aA1", "bb1", "BB1", "Bb1", "bB1") y = .Item(it)
NextMsgBox .Count' 2 unique keys MsgBox Join(.Keys, vbLf)
It doesn't matter in that case which key will be used. It's necessary to generate a unique key for each item automatically. You can use the property .count of the Dictionary to create these unique keys. With CreateObject("scripting.dictionary") For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")
End With.Item(.Count) = it & "_contents"
Next- to convert a string into a 2-dimensional array: Example 23.9 - to group all ActiveX controls: Example 23.7 By adding items to a Dictionary you simultaneously create a list of unique keys. So you can use this adding process to create a list of unique values. It's not even necessary to add any content to items to create such a list. The method =Item("key") does that. If the key doesn't exist it wil be added, if it exists nothing will happen. This method triggers no errors, you should deal with. With CreateObject("scripting.dictionary") For Each it In Array(22, 33, 44, 22, 3, 22, 55, 44)
End Withy = .Item(it)
NextMsgBox .Count ' 5 unique keys because of the replication of 44 and 22 MsgBox Join(.Keys, vbLf) The contents of items can be: numbers, strings, dates, arrays, ranges, variables, collections, dictionaries, an empty string, nothing or objects As illustration of items with varying contents. Only a string will be used as key. With CreateObject("scripting.dictionary") x0 = .Item("aa1")
End WithWith CreateObject("scripting.dictionary") .Item("aa2") = vbNullString
End With.Item("aa3") = "" .Add "aa4", "" dict_snb("aa5") = vbNullString With CreateObject("scripting.dictionary") .Item("aa6") = "abcde"
End With.Add "aa7", "abcde" dict_snb("aa8") = "abcde"
With CreateObject("scripting.dictionary") .Item("aa9") = vbTab
End WithAdd "aa10", vbLf dict_snb("aa11") = vbCrLf With CreateObject("scripting.dictionary") .Item("aa12") = 12345' typename: Integer
End With.Add "aa13" = 1234589' typename: Long dict_snb("aa14") = RGB(23, 45, 678) ' typename: Long With CreateObject("scripting.dictionary") .Item("aa15") = Date
End With.Add "aa16", CDate("23-04-2012") dict_snb("aa17") = DateSerial(2013, 10, 12)
With CreateObject("scripting.dictionary") .Item("aa18") = Array("aa1", "aa2", "aa3")
End WithAdd "aa19", Split("bb1_cc1_dd1", "_") dict_snb("aa20") = Array("aa1", "aa2", "aa3")
With CreateObject("scripting.dictionary") ReDim sn(6, 10)
End With.Item("aa21") = sn .Add "aa22", Range("A1:K10") dict_snb("aa23") = Range("A1:K10").Formula The methods (.Items = ; objectvariable=) need the instruction 'Set' to assign the object to the Dictionary item. Otherwise the object's default property would be the content of the Dictionary item. With CreateObject("scripting.dictionary") Set .Items("aa24") = Range("A1:K10")' typename: Range
End WithSet dict_snb("aa25") = Range("A1:K10") .Add "aa26", Range("A1:K10") With CreateObject("scripting.dictionary") For Each it In Controls
End WithSet .Item(it.Name) = it
Next.Add it.name& "_", it With CreateObject("scripting.dictionary") For Each it In Sheets("sheet1").OLEObjects
End WithSet .Item(it.Name) = it
Next.Add it.name & "_", it
With CreateObject("scripting.dictionary") For Each sh In Sheets
End WithSet .Item(sh.Name) = sh
Next.Add sh.Name & "_", sh Set dict_snb(sh.Name & "_#") = sh
For instance Public Type translation dutch As String
End Typegerman As String french As String italian As String Sub filling_type_array() ReDim sn(2) As translation
end subFor j = 0 To 2 sn(j).dutch = Choose(j + 1, "tafel", "wijn", "water")
Nextsn(j).german = Choose(j + 1, "Tisch", "Wein", "Wasser") sn(j).french = Choose(j + 1, "table", "vin", "eau") sn(j).italian = Choose(j + 1, "tavola", "vino", "aqua") MsgBox sn(2).german & " = " & sn(2).italian For each array item you can retrieve the data of the type using: For j=0 to ubound(sn) MsgBox sn(j).german & " = " & sn(j).italian
nextThe code Dim woord As translation With CreateObject("scripting.dictionary") .Add "overzicht", woord
End WithWith the use of another method we can accomplish an analogous result: - create a classmodule (e.g named 'trans') - declare the properties of the class: Public german As String, french As String, italian As String
For illustration purposes 3 different methods to do so: NB. the Dutch words serve as keys in the Dictionary. Sub type_class_in_Dictionary()
With CreateObject("scripting.dictionary")
End SubSet sn = New trans
End Withsn.german = "Tisch" sn.french = "table" sn.italian = "tavola" .Add "tafel", sn Set .Item("wijn") = New trans .Item("wijn").german = "Wein" .Item("wijn").french = "vin" .Item("wijn").italian = "vino" .Add "water", New trans .Item("water").german = "Wasser" .Item("water").french = "eau" .Item("water").italian = "aqua" MsgBox .Item("tafel").french MsgBox .Item("wijn").italian
The property .Count indicates the number of items in a Dictionary. Since every item has its own unique key the property .Count also indicates the amount of unique keys. The function .Items contains all items in the Dictionary, represented by a 1-dimensional array. The array's property Ubound() indicates the number of elements in an array. Since the lower limit of a 1-dimensional array is 0, Ubound(.items)+1 also represents the number of elements in a Dictionary. The function .Keys contains all keys in the Dictionary, represented by a 1-dimensional array. The array's property Ubound() indicates the number of elements in an array. Since the lower limit of a 1-dimensional array is 0, Ubound(.Keys)+1 also represents the number of elements in a Dictionary. Equivalent methods to determine the size of a Dictionary: With CreateObject("scripting.dictionary") For Each it In Array("aa1", "aa2", "aa3", "aa4", "aa2")
End Withy = .Item(it)
NextMsgBox .Count MsgBox UBound(.Keys) + 1 MsgBox UBound(.Items) + 1
To prevent this error you can check the key's existence in the Dictionary. With CreateObject("scripting.dictionary") For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")
End WithIf Not .Exists(it) Then .Add it, it & "_content"
NextIf that's not what you want you can check the key's existence. With CreateObject("scripting.dictionary") For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")
End WithIf Not .Exists(it) Then .Item(it) = it & "_content"
NextIf the key doesn't exist it will be created automatically when using this code to read the item's content. y = .Item("first") With CreateObject("scripting.dictionary") For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")
End With.Item(it) = it & "_content"
NextIf .Exists("aa2") Then .Item("aa2") = "new value"
Using .item("aa2") you get the contents of the item stored with the key "aa2" With CreateObject("scripting.dictionary") For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")
End With.Item(it) = it & "_content"
NextMsgBox .Item("aa2")
The population order of the Dictionary determines the indexnumbers in the array .items. The first element gets indexnumber 1, the last indexnumber is identical to the property .count. You'd expect that .items(2) would result in the second item in the Dictionary. This syntax however doesn't work. Instead you need a slightly different one: .items()(2). With CreateObject("scripting.dictionary") For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2") .Item(it) = it & "_inhoud"
NextMsgBox .Items()(1) ' the first item MsgBox .Items()(2) ' the second item MsgBox .Items()(.count) ' the last item End With The population order of the Dictionary determines the indexnumbers in the array .items. The first element gets indexnumber 0, the last indexnumber is identical to the property .count-1. You'd expect that .Keys(2) would result in the third key in the Dictionary. This syntax however doesn't work. Instead you need a slightly different one: .keys()(2). With CreateObject("scripting.dictionary") For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")
End With.Item(it) = it & "_content"
NextMsgBox .item(.keys()(0)) ' the first key MsgBox .item(.keys()(1)) ' the second key MsgBox .item(.keys()(.count-1)) ' the last key
With CreateObject("scripting.dictionary") For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")
End With.Item(it) = it & "_content"
NextFor Each it In .Keys If it = "aa4" Then MsgBox .Item(it)
Nextset dict_snb as CreateObject("scripting.dictionary") For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")
End Withdict_snb.Item(it) = it & "_content"
NextFor Each it In dict_snb If it = "aa4" Then MsgBox .Item(it)
Next
With CreateObject("scripting.dictionary") For Each it In Array("aa14", "bb345", "cc392", "rrr987") .Item(it) = it & "_content"
Nextfor each it in Filter(.Keys, "cc") MsgBox .Item(it)
nextEnd With
With CreateObject("scripting.dictionary") For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")
End With.Item(it) = it & "_content"
NextCells(1, 1).Resize(, .Count) = .Keys ' in a row Cells(1, 1).Resize(.Count) = Application.Transpose(.Keys) ' in a column
With CreateObject("scripting.dictionary") For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")
End With.Item(it) = it & "_content"
Nextsn = .Keys
With CreateObject("scripting.dictionary") For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")
End With.Item(it) = it & "_contents"
NextMsgBox Join(.Keys, vbLf)
- the highest keynumber MsgBox Application.Max(.Keys) MsgBox Application.Min(.Keys) MsgBox Application.Large(.Keys, 3)
The method 'filter' converts all keys to strings. So the filtering condition has to be a string too. With CreateObject("scripting.dictionary") For Each it In Array("aa1", "aa2", DateSerial(2012, 12, 3), DateSerial(2012, 12, 4), DateSerial(2012, 12, 8), 22, DateSerial(2013, 12, 3), 44)
End With.Item(it) = it & "_content"
NextMsgBox Join(Filter(.Keys, "-2012"), vbLf) With CreateObject("scripting.dictionary") For Each it In Array("aa1", "aa2", DateSerial(2012, 12, 3), DateSerial(2012, 12, 4), DateSerial(2012, 12, 8), 22, DateSerial(2013, 12, 3), 44)
End With.Item(it) = it & "content"
NextFor Each it In Filter(.Keys, "-2012") MsgBox .Item(CDate(it))
Next
With CreateObject("scripting.dictionary") For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")
End With.Item(it) = it & "_content"
NextCells(1, 1).Resize(, .Count) = .Items' in a row Cells(1, 1).Resize(.Count) = Application.Transpose(.Items) ' in a column
With CreateObject("scripting.dictionary") For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")
End With.Item(it) = it & "_content"
Nextsn = .Items
With CreateObject("scripting.dictionary") For Each it In Array("aa1", "aa2", "aa1", 22, 3, 22, DateSerial(2013, 12, 3), 44)
End With.Item(it) = it & "_content"
NextMsgBox Join(.Items, vbLf)
- the highest item MsgBox Application.Max(.Items) MsgBox Application.Min(.Items) MsgBox Application.Large(.Items, 3)
Since the method Filter converts all items to text the filtercondition also has to be a string. With CreateObject("scripting.dictionary") For Each it In Array("aa1", "aa2", DateSerial(2012, 12, 3), DateSerial(2012, 12, 4), DateSerial(2012, 12, 8), 22, DateSerial(2013, 12, 3), 44)
End With.Item(it) = it & "_content"
NextMsgBox Join(Filter(.Items, "-2012"), vbLf) With CreateObject("scripting.dictionary") .Item("aa") = "this is the first item"
End WithMsgBox .Item("aa") .Key("aa") = "bb" MsgBox .Item("bb")
With CreateObject("scripting.dictionary") .Item("aa") = "The first item"
End With.Item("bb") = .Item("aa") MsgBox .Item("bb")
With CreateObject("scripting.dictionary") For Each it In Array("aa1", "aa2", "aa3", "aa4", "aa2")
End With.Item(it) = it & "_content"
Next.Remove "aa3"
With CreateObject("scripting.dictionary") For Each it In Array("aa1", "aa2", "aa3", "aa4", "aa5", "aa6")
End With.Item(it) = it & "_content"
NextFor Each it In Array("aa1", "aa2", "aa5", "aa6") .Item(it) = it & "_new content"
NextMsgBox Join(.Items, "|") Since every item is overwritten, only the last item in the sorting order will be kept. A prerequisite is a sortable property. See example 23.2 and 23,3.
With CreateObject("scripting.dictionary") For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")
End With.Item(it) = "aa"
NextFor Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2") .Item(it) = .Item(it) & "_bb"
NextMsgBox Join(.Items, "|") With CreateObject("scripting.dictionary") For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")
End With.Item(it) = 10
NextFor Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2") .Item(it) = .Item(it) + 40
NextMsgBox Join(.Items, "|") With CreateObject("scripting.dictionary") For Each it In Array("aa1", "aa2", "aa3", "aa4")
End With.Item(it) = Date + .Count
NextFor Each it In Array("aa1", "aa2", "aa3", "aa4") .Item(it) = DateAdd("m", 1, .Item(it))
NextMsgBox Join(.Items, vbLf) With CreateObject("scripting.dictionary") For Each it In Array("aa1", "aa2", "aa3", "aa4", "aa2")
End With.Item(it) = False
NextFor Each it In Array("aa1", "aa2", "aa3", "aa4", "aa2") .Item(it) = Format(.Item(it), "Yes/No")
NextMsgBox Join(.Items, "|")
With CreateObject("scripting.dictionary") .Item("aa") = Array("zz1", "zz2", "zz3", "zz4")
End WithMsgBox .Item("aa")(3) Although the following code doesn't error out, the 4th element in the array isn't been changed by the line .Item("aa")(3) = "the fourth item is " & .Item("aa")(3). With CreateObject("scripting.dictionary") .Item("aa") = Array("zz1", "zz2", "zz3", "zz4")
End WithMsgBox .Item("aa")(3) .Item("aa")(3) = "the fourth item is " & .Item("aa")(3) MsgBox .Item("aa")(3) - read the array into a variable - change one or more elements of this array variable - replace the Dictionary item's content by the array variable With CreateObject("scripting.dictionary") .Item("aa") = Array("zz1", "zz2", "zz3", "zz4")
End Withsn = .Item("aa") sn(3) = "the fourth element is " & sn(3) .Item("aa") = sn MsgBox .Item("aa")(3) With CreateObject("scripting.dictionary") ReDim sn(3, 4)
End WithFor j = 0 To UBound(sn) For jj = 0 To UBound(sn, 2)
Nextsn(j, jj) = j + 5 * jj
Next.Item("aa") = sn MsgBox .Item("aa")(2, 3) With CreateObject("scripting.dictionary") ReDim sn(3, 4)
End WithFor j = 0 To UBound(sn) For jj = 0 To UBound(sn, 2)
Nextsn(j, jj) = j + 5 * jj
Next.Item("aa") = sn MsgBox .Item("aa")(2, 3) .Item("aa")(2, 3) = 10 * .Item("aa")(2, 3) MsgBox .Item("aa")(2, 3) - read the array into a variable - change one or more elements in this array variable - replace the Dictionary item's content by this array variable With CreateObject("scripting.dictionary") ReDim sn(3, 4)
End WithFor j = 0 To UBound(sn) For jj = 0 To UBound(sn, 2)
Nextsn(j, jj) = j + 5 * jj
Next.Item("aa") = sn MsgBox .Item("aa")(2, 3) sp = .Item("aa") sp(2, 3) = 10 * sp(2, 3) .Item("aa") = sp MsgBox .Item("aa")(2, 3)
With CreateObject("scripting.dictionary") For Each it In Array("2", "33", "aa3", "aa4", "aa2")
End With.Item(it) = it & "_content"
Next.RemoveAll
It's in the Microsoft scripting runtime library It means you have to make a connection to the library in order to be able to use its methods. The technical term for that connection is 'binding' You can - establish the connection to the library before using its code: early binding - or to connect only if you need the code: late binding Until now I only used late binding. That means you create a new object (alternatively called 'instance'), based on a library using the method CreateObject. The reference to the several VBA libraries isn't very well documented. In the case of a Dictionary it is: Createobject("scripting.dictionary") - it makes a connection to the library Microsoft Scripting Runtime - it creates an new object (instance) based on this library. Both methods (early and late binding) are equivalents. Your preference determines which method to use. You need to create a new object to be able to work with it. You can make an implicit object using With.... End With With CreateObject("scripting.dictionary") .Add "aa1", "today"
End WithSet d_snb_ = CreateObject("scripting.dictionary") d_snb.Add "aa1", "today" You can make the conection manually in the VBEditor (Alt-F11): - VBEditor/tools/references: check 'Microsoft Scripting runtime' You could also use a macro: Sub M_link_ref_scripting() ThisWorkbook.VBProject.References.AddFromFile "C:\windows\system32\scrrun.dll"
End SubIf you distribute the workbook the file will connect automatically to the library if the other system also contains Office. We need to create a new object (instance), based on the library to be able to work with it.
With New Dictionary .Add "aa1", "today"
End With
Set d_snb = New Dictionary d_snb.Add "aa1", "today"
(e.g. the workbook, a worksheet, a userform or a macromodule). Private d_snb As New Dictionary d_snb.Add "aa1", "PPP"
Public d_snb As New Dictionary In the declaration part: Public d_snb As Dictionary Set d_snb = New Dictionary
Columns B:F etc, contain the data of the records. Sub M_delete_duplicates() sn = Sheets("Sheet1").Cells(1).CurrentRegion.resize(,5)
End SubWith CreateObject("scripting.dictionary") For j = 1 To UBound(sn)
End With .Item(sn(j, 1)) = Application.Index(sn, j, 0)
NextSheets("Sheet1").Cells(1, 4).Resize(.Count, UBound(sn, 2)) = Application.Index(.items, 0, 0)
Column A contains then record keys, some of which are duplicates. Columns B:F ..... contain the data of the records. Column B contains the data that can be sorted. Sub M_filter_records_highest_date_in_columnB() Sheets("Sheet1").Cells(1).CurrentRegion.Sort Sheets("Sheet1").Cells(1, 2)
End Subsn = Sheets("Sheet1").Cells(1).CurrentRegion With CreateObject("scripting.dictionary") For j = 1 To UBound(sn)
End With.Item(sn(j, 1)) = Application.Index(sn, j, 0)
NextSheets("Sheet1").Cells(1, 4).Resize(.Count, UBound(sn, 2)) = Application.Index(.items, 0, 0)
Columns B: ..... contain the data of the records. Column B contains the data to be sorted. Sub M_filter_records_smallestvalue_in_columnB() Sheets("Sheet1").Cells(1).CurrentRegion.Sort Sheets("Sheet1").Cells(1, 2),2
End Subsn = Sheets("Sheet1").Cells(1).CurrentRegion With CreateObject("scripting.dictionary") For j = 1 To UBound(sn)
End With.Item(sn(j, 1)) = Application.Index(sn, j, 0)
NextSheets("Sheet1").Cells(1, 4).Resize(.Count, UBound(sn, 2)) = Application.Index(.items, 0, 0)
Column B contains data of the records. Column E contains (some) keys of the same records as those in column A. Column F contains data that have to be added to the records in column A. Sub M_records_aanvullen() sn = Sheets("Sheet1").Cells(1).CurrentRegion.Resize(, 3)
End Subsp = Sheets("sheet1").Cells(1, 5).CurrentRegion With CreateObject("scripting.dictionary") For j = 1 To UBound(sn)
End With.Item(sn(j, 1)) = Application.Index(sn, j, 0)
NextFor j = 1 To UBound(sp) st = .Item(sp(j, 1))
st(3) = sp(j, 2)
.Item(sn(j, 1)) = Application.Index(st, 0, 0)
NextSheets("Sheet1").Cells(1, 10).Resize(.Count, UBound(sn, 2)) = Application.Index(.items, 0, 0)
Sub M_integration() With createobject("scripting.dictionary")
End SubFor each sh in Sheets
end with.item(sh.name)=sh.usedrange
NextSheets.Add.Name="total" For each it in .items sheets("total").cells(rows.count,1).end(xlup).offset(1).resize(ubound(it),ubound(it,2))=it
next
Sub M_integratie_csv() sn = Split(CreateObject("wscript.shell").exec("cmd /c Dir ""G:\OF\*.csv"" /b").stdout.readall, vbCrLf)
End SubWith CreateObject("scripting.dictionary") For j = 0 To UBound(sn)
End With.Item(sn(j)) = GetObject("G:\OF\" & sn(j)).Sheets(1).UsedRange.Value
NextGetObject("G:\OF\" & sn(j)).Close False Sheets.Add.Name = "total" For Each it In .items Sheets("total").Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(UBound(it), UBound(it, 2)) = it
Next
It's not even necessary that the items have any content. In that case you need the method x0=.Item(key). If the key doesn't exist, the key will be added; if it exists nothing happens at all. The method also doesn't produce any error codes. You can use the unique elements list as validation list (Excel), or to populate a combobox or listbox. With CreateObject("scripting.dictionary") For Each it In Array("aa1", "aa2", "aa3", "aa2", "aa2", "aa4", "aa5")
End Withy = .Item(it)
NextSheets("sheet1").Cells(1, 10).Validation.Add 3, , , Join(.Keys, ",") Sheets("sheet1").OLEObjects("Combobox1").Object.List = .Keys Sheets("sheet1").ListBox1.List = .Keys Private Sub Userform_initialize() With CreateObject("scripting.dictionary")
End SubFor Each it In Array("aa1", "aa2", "aa3", "aa2", "aa2", "aa4", "aa5")
End Withy = .Item(it)
NextComboBox1.List = .Keys ListBox1.List = .Keys Me("ComboBox2").List = .Keys Me("ListBox2").List = .Keys Controls("ComboBox3").List = .Keys Controls("Listbox3").List = .Keys
Private dict As Dictionary Sub M_vul() Set dict = CreateObject("scripting.dictionary")
End SubFor Each it In Array("TextBox1", "TextBox2", "textbox3", "combobox4", "combobox5") Set dict(it) = Sheets("sheet1").OLEObjects(it)
NextPrivate Sub Textbox1_change() M_controle
End SubPrivate Sub M_controle() y = True
End SubFor Each it In dict.Items y = y * (it.Object.Value <> "")
NextSheets("sheet1").CommandButton1.Visible = y
Public dict_sheets As Dictionary Sub M_sheet_exists() Set dict_sheets = CreateObject("scripting.dictionary")
End SubFor Each it In Sheets x0 = dict_sheets(it.name)
NextSub M_continue() ' - - -
If Not dict_sheets.Exists("Sheet10") Then Sheets.Add.Name = "Sheet10"
End Sub' - - -
With CreateObject("scripting.dictionary") For Each it In Split("aa1_aa2_aa3_aa4_aa5|bb1_bb2_bb3_bb4_bb5|cc1_cc2_cc3_cc4_cc5", "|")
End With.Item(.Count) = Application.Index(Split(it, "_"), 1, 0)
NextCells(10, 1).Resize(.Count, UBound(.Item(1))) = Application.Index(.Items, 0, 0) |