VB Dictionaries

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.

2 What is the purpose of a Dictionary ?

You can group all kinds of data in a Dictionary to get easy and quick access to them since they are temporarily stored in memory.
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).

3 Where does the Dictionary derive from ?

The Dictionary isn't part of the standard VBA library.
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.

4 How to create a Dictionary ?

There are several methods to do so.
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

It reads as: create a new object based on the scripting.Dictionary library.
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 With

The method .Add adds a new item to the Dictionary with the key "first name"; the item contains the string "contents"

or e.g.:

With CreateObject("scripting.dictionary")
MsgBox .Count
End With

The message box shows the property .Count of the Dictionary: i.e. the number of items in the Dictionary.

5 How to populate a Dictionary

A Dictionary can only be populated one by one.
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

5.1 method .Add

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 With

You can add an Object in the same way.
This item in the Dictionary has all the object's properties:

With CreateObject("scripting.dictionary")

.Add "range", sheet1.range("A1:K10")
Msgbox .item("range").rows.count

End With

5.2 method .Item( )=

Add an item to the Dictionary; its content is the string "contents", its key is the string "first"

With CreateObject("scripting.dictionary")

.Item("first") = "content"

End With

You can only add an Object with the instruction 'Set' in this method.
This item in the Dictionary has all the object's properties:

With CreateObject("scripting.dictionary")

Set .Item("range") = sheet1.range("A1:K10")
Msgbox .item("range").rows.count

End With

5.3 method =.Item()

With CreateObject("scripting.dictionary")

x0 = .Item("first")

End With

Put the contents of Item("first") into variable x0
If 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.

5.4 method using an objectvariable

Assign a Dictionary to an objectvariable

Set dict_snb = CreateObject("scripting.dictionary")
dict_snb("first") = Date

Add an item to the Dictionary: its content is a Date and its key is "first"
Written alternatively:

Set dict_snb = CreateObject("scripting.dictionary")
dict_snb.Item("first") = Date

6 Add or replace ?

6.1 The key doesn't exist in the Dictionary

If a key doesn't exist a new one is being created and the item will be added.
All methods (.Add, .Item()=, =Item() and the use of an objectvariable()= ) do this identically.

6.2 The key exists in the Dictionary

6.2.1 method .Add

If the keys exists in the Dictionary and if you use the method .Add to add an item to the same key the code errors out.
This is similar to the use of a Collection

With CreateObject("scripting.dictionary")

.Add "first", Date
.Add "first", "new"

End With

6.2.2 method .Item()=

If the key exists the corresponding item will be overwritten/replaced.
Contrary to the method .Add no error is being reported.

With CreateObject("scripting.dictionary")

.Item("first") = Date
MsgBox .Item("first")
.Item("first") = "new"
MsgBox .Item("first")

End With

6.2.3 method =.Item()

The item's content will be read into a variable. Whether the item is empty or not doesn't make a difference.
If the key exists no change will take place in the Dictionary.

With CreateObject("scripting.dictionary")

x0 = .Item("first")

End With

6.2.4 method using an objectvariable

If a key exists the corresponding item will be replaced.
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")

Aternative syntax:

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")

7 Keys

New keys will be added in the order of the adding process. No other order in keys or items will be made.
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

7.1 string key

With CreateObject("scripting.dictionary")

.Items("aa1") = "example 1 "
.Add "aa2", "example 2"
x = .Item("aa3")
dict_snb("aa4") = "example 3"

end with

7.2 number key

With CreateObject("scripting.dictionary")

.Items(2) = "example 4"
.Items(1234589) = "example 5"
.Add 23, "example 6"
x = .Item(45)
dict_snb(56788) = "example 7"

end with

7.3 date key

With CreateObject("scripting.dictionary")

.Items(Date) = "example 8"
.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"

end with

7.4 object key

With CreateObject("scripting.dictionary")

.Items(TextBox1) = "example 12"
.Items(TextBox2) = "example 13"
.Add ListBox1, "example 14"
x = .Item(Sheet1)
dict_snb(ComboBox1) = "example 15"

end with

7.5 ActiveX-controls in a worksheet

With CreateObject("scripting.dictionary")
For Each it In Sheets("sheet1").OLEObjects

.Item(it) = it.Name

Next
end with

7.6 Several kinds of keys in 1 Dictionary

With CreateObject("scripting.dictionary")
For Each it In Array("bb1", 12, 45673, Date + 10, sheet4)

.Item(it) = "Item 1" & .Count

Next
end with

8 When is a key unique ?

There are 2 options:
- 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")

y = .Item(it)

Next

MsgBox .Count'8 unique keys
MsgBox Join(.Keys, vbLf)
End With

With CreateObject("scripting.dictionary")
.CompareMode = 0
For Each it In Array("aa1", "AA1", "Aa1", "aA1", "bb1", "BB1", "Bb1", "bB1")

y = .Item(it)

Next

MsgBox .Count' 8 unique keys
MsgBox Join(.Keys, vbLf)
End With

With CreateObject("scripting.dictionary")
.CompareMode = 1
For Each it In Array("aa1", "AA1", "Aa1", "aA1", "bb1", "BB1", "Bb1", "bB1")

y = .Item(it)

Next

MsgBox .Count' 2 unique keys
MsgBox Join(.Keys, vbLf)
End With

9 Create unique keys automatically

Sometimes all items have to be added to a Dictionary.
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")

.Item(.Count) = it & "_contents"

Next
End With

Examples when all items need to be added to the Dictionary:
- to convert a string into a 2-dimensional array: Example 23.9
- to group all ActiveX controls: Example 23.7

10 A list of unique elements

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)

y = .Item(it)

Next
MsgBox .Count ' 5 unique keys because of the replication of 44 and 22
MsgBox Join(.Keys, vbLf)
End With

11 Items

Items will be added sequentially. No ordering of keys or items will take place.
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.

11.1 an empty item

With CreateObject("scripting.dictionary")

x0 = .Item("aa1")

End With

11.2 an empty string

With CreateObject("scripting.dictionary")

.Item("aa2") = vbNullString
.Item("aa3") = ""
.Add "aa4", ""
dict_snb("aa5") = vbNullString

End With

11.3 a string

With CreateObject("scripting.dictionary")

.Item("aa6") = "abcde"
.Add "aa7", "abcde"
dict_snb("aa8") = "abcde"

End With

11.4 a non-printable character

With CreateObject("scripting.dictionary")

.Item("aa9") = vbTab
Add "aa10", vbLf
dict_snb("aa11") = vbCrLf

End With

11.5 a number

With CreateObject("scripting.dictionary")

.Item("aa12") = 12345' typename: Integer
.Add "aa13" = 1234589' typename: Long
dict_snb("aa14") = RGB(23, 45, 678) ' typename: Long

End With

11.6 a date (typename: Date)

With CreateObject("scripting.dictionary")

.Item("aa15") = Date
.Add "aa16", CDate("23-04-2012")
dict_snb("aa17") = DateSerial(2013, 10, 12)

End With

11.7 a 1-dimensional array (typename: Variant()

With CreateObject("scripting.dictionary")

.Item("aa18") = Array("aa1", "aa2", "aa3")
Add "aa19", Split("bb1_cc1_dd1", "_")
dict_snb("aa20") = Array("aa1", "aa2", "aa3")

End With

11.8 a multi-dimensional array (typename: Variant()

With CreateObject("scripting.dictionary")

ReDim sn(6, 10)
.Item("aa21") = sn
.Add "aa22", Range("A1:K10")
dict_snb("aa23") = Range("A1:K10").Formula

End With

11.9 an object

The method .Add puts the object into the Dictionary directly.
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
Set dict_snb("aa25") = Range("A1:K10")
.Add "aa26", Range("A1:K10")

End With

11.10 userformcontrols

With CreateObject("scripting.dictionary")
For Each it In Controls

Set .Item(it.Name) = it
.Add it.name& "_", it

Next
End With

11.11 ActiveX-controls

With CreateObject("scripting.dictionary")
For Each it In Sheets("sheet1").OLEObjects

Set .Item(it.Name) = it
.Add it.name & "_", it

Next
End With

11.12 all sheets in a workbook

With CreateObject("scripting.dictionary")
For Each sh In Sheets

Set .Item(sh.Name) = sh
.Add sh.Name & "_", sh
Set dict_snb(sh.Name & "_#") = sh

Next
End With

11.13 A User Defined Type (UDT)

You can store a User Defined Type (UDT) in an array.
For instance

Public Type translation

dutch As String
german As String
french As String
italian As String

End Type

Sub filling_type_array()
ReDim sn(2) As translation
For j = 0 To 2

sn(j).dutch = Choose(j + 1, "tafel", "wijn", "water")
sn(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")

Next

MsgBox sn(2).german & " = " & sn(2).italian
end sub

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

next

You can't put a UDT into a Dictionary (nor in a collection).
The code

Dim woord As translation

With CreateObject("scripting.dictionary")

.Add "overzicht", woord

End With

Will return an error code.

With 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

- create new instances of the class; assign values to their properties and store these instances in a Dictionary.
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")

Set sn = New trans
sn.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

End With
End Sub

12 The number of elements in a Dictionary

The Dictionary keeps track of the amount of elements.
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")

y = .Item(it)

Next

MsgBox .Count
MsgBox UBound(.Keys) + 1
MsgBox UBound(.Items) + 1
End With

13 Check the existence of a Key / Item in the Dictionary

If you add an item to a Dictionary using the method .Add and applying an existing key the code will error out.
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")

If Not .Exists(it) Then .Add it, it & "_content"

Next
End With

If you use the method .Item = to add an item to a Dictionary no error message pops up if the keys already exists, the existing item will be replaced.
If 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")

If Not .Exists(it) Then .Item(it) = it & "_content"

Next
End With

If you want to change the content of an item in the Dictionary it can be very practical to check its existence beforehand.
If the key doesn't exist it will be created automatically when using this code to read the item's content.

y = .Item("first")

If you want to prevent the adding of empty items to the Dictionary checking is required:

With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")

.Item(it) = it & "_content"

Next
If .Exists("aa2") Then .Item("aa2") = "new value"
End With

14 How to retrieve 1 element from the Dictionary ?

14.1 The unique key

A Dictionary has been designed to access (reading /writing) an item directly by key.
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")

.Item(it) = it & "_content"

Next
MsgBox .Item("aa2")
End With

The property .Item has only the key as argument.

14.2 Use the item's indexnumber

The function .Items is a 1-dimensional array.
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"

Next

MsgBox .Items()(1) ' the first item
MsgBox .Items()(2) ' the second item
MsgBox .Items()(.count) ' the last item
End With

14.3 The indexnumber of a key

The function .Keys is a 1-dimensional array.
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")

.Item(it) = it & "_content"

Next

MsgBox .item(.keys()(0)) ' the first key
MsgBox .item(.keys()(1)) ' the second key
MsgBox .item(.keys()(.count-1)) ' the last key
End With

14.4 Loop through all keys in the Dictionary and check a condition

Using the function .Keys that contains all keys in a 1-dimensional array

With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")

.Item(it) = it & "_content"

Next

For Each it In .Keys

If it = "aa4" Then MsgBox .Item(it)

Next
End With

Using an object variable:

set dict_snb as CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")

dict_snb.Item(it) = it & "_content"

Next

For Each it In dict_snb

If it = "aa4" Then MsgBox .Item(it)

Next
End With

14.5 Filter the keys in the Dictionary

We can use the function .Keys that contains all keys in a 1-dimensional array

With CreateObject("scripting.dictionary")
For Each it In Array("aa14", "bb345", "cc392", "rrr987")

.Item(it) = it & "_content"

Next

for each it in Filter(.Keys, "cc")

MsgBox .Item(it)

next
End With

15 How to use the array .Keys ?

15.1 Write all keys to a worksheet

With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")

.Item(it) = it & "_content"

Next

Cells(1, 1).Resize(, .Count) = .Keys ' in a row
Cells(1, 1).Resize(.Count) = Application.Transpose(.Keys) ' in a column
End With

15.2 Put all keys into a variable

The variable is an array variable with lower bound (lbound) 0

With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")

.Item(it) = it & "_content"

Next

sn = .Keys
End With

15.3 Put all keys into a string

With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")

.Item(it) = it & "_contents"

Next

MsgBox Join(.Keys, vbLf)
End With

15.4 Do calculations with the keys

Many Excel worksheetformulae can be applied to the array .keys if the keys consist of numbers.
- the highest keynumber

- the lowest keynumber

- the third highest key

15.5 Filter keys on a certain condition

E.g. filter the year 2012
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)

.Item(it) = it & "_content"

Next

MsgBox Join(Filter(.Keys, "-2012"), vbLf)
End With

If you want to use the filtered keys to retrieve the corresponding items you will have to reconvert the keys to their original type.

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)

.Item(it) = it & "content"

Next

For Each it In Filter(.Keys, "-2012")

MsgBox .Item(CDate(it))

Next
End With

16 How to use the array .Items ?

16.1 Write the array to a worksheet

NB. This requires the items to be numbers, strings or dates.

With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")

.Item(it) = it & "_content"

Next

Cells(1, 1).Resize(, .Count) = .Items' in a row
Cells(1, 1).Resize(.Count) = Application.Transpose(.Items) ' in a column
End With

16.2 Put all items into a variable

The variable is an array variable with lower bound(lbound) 0

With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")

.Item(it) = it & "_content"

Next

sn = .Items
End With

16.3 Put all items into a string

With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa1", 22, 3, 22, DateSerial(2013, 12, 3), 44)

.Item(it) = it & "_content"

Next

MsgBox Join(.Items, vbLf)
End With

16.4 Do calculations on the items

Many Excel worksheetformulae are applicable to the array .Items if the items contain numbers.
- the highest item

MsgBox Application.Max(.Items)

- the smallest item

MsgBox Application.Min(.Items)

- the third largest item

MsgBox Application.Large(.Items, 3)

16.5 Filter items on a certain condition

Filter the year 2012
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)

.Item(it) = it & "_content"

Next

MsgBox Join(Filter(.Items, "-2012"), vbLf)
End With

17 Change the key of an item

You can also consider this as a 'replacement' of an item in the Dictionary.

With CreateObject("scripting.dictionary")

.Item("aa") = "this is the first item"
MsgBox .Item("aa")

.Key("aa") = "bb"
MsgBox .Item("bb")

End With

18 Copy an item within the Dictionary

You can connect an item to another / new key.

With CreateObject("scripting.dictionary")

.Item("aa") = "The first item"
.Item("bb") = .Item("aa")

MsgBox .Item("bb")

End With

19 Remove an item from the Dictionary

You need to use the key of an item to remove it with this method.

With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa3", "aa4", "aa2")

.Item(it) = it & "_content"

Next

.Remove "aa3"
End With

20 Change the contents of an item in the Dictionary

20.1 Replace the contents of an item in the Dictionary

The method .Item()= replaces an existing item by a new one with the same key.

With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa3", "aa4", "aa5", "aa6")

.Item(it) = it & "_content"

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

.Item(it) = it & "_new content"

Next

MsgBox Join(.Items, "|")
End With

If you sort the items before adding them to a Dictionary you can restrict the items to the most/least recent item, or the item containing the maximum / minimum value.
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.

20.2 Add data to items in the Dictionary

This method applies only to items that contain single numbers, strings or dates.

With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")

.Item(it) = "aa"

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

.Item(it) = .Item(it) & "_bb"

Next

MsgBox Join(.Items, "|")
End With

20.3 Perform calculations

This method applies only to items that contain single numbers, strings, dates or booleans.

20.3.1 a number

With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa1", "aa3", "aa4", "aa2")

.Item(it) = 10

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

.Item(it) = .Item(it) + 40

Next

MsgBox Join(.Items, "|")
End With

20.3.2 a date

With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa3", "aa4")

.Item(it) = Date + .Count

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

.Item(it) = DateAdd("m", 1, .Item(it))

Next

MsgBox Join(.Items, vbLf)
End With

20.3.3 a boolean

With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa3", "aa4", "aa2")

.Item(it) = False

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

.Item(it) = Format(.Item(it), "Yes/No")

Next

MsgBox Join(.Items, "|")
End With

20.4 Change an item that contains an array

20.4.1 a 1-dimensional array

To read an item that contains an array:

With CreateObject("scripting.dictionary")

.Item("aa") = Array("zz1", "zz2", "zz3", "zz4")
MsgBox .Item("aa")(3)

End With

I couldn't find a method to adapt an element in an array of a Dictionary Item directly.
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")
MsgBox .Item("aa")(3)

.Item("aa")(3) = "the fourth item is " & .Item("aa")(3)
MsgBox .Item("aa")(3)

End With

You can use a workaround:
- 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")

sn = .Item("aa")
sn(3) = "the fourth element is " & sn(3)
.Item("aa") = sn

MsgBox .Item("aa")(3)

End With

20.4.2 a 2-dimensional array

To read an element from a 2-dimensional array in a Dictionary Item:

With CreateObject("scripting.dictionary")
ReDim sn(3, 4)
For j = 0 To UBound(sn)
For jj = 0 To UBound(sn, 2)

sn(j, jj) = j + 5 * jj

Next
Next

.Item("aa") = sn
MsgBox .Item("aa")(2, 3)
End With

I couldn't find a method to adapt an element in an array of a Dictionary Item directly.

With CreateObject("scripting.dictionary")
ReDim sn(3, 4)
For j = 0 To UBound(sn)
For jj = 0 To UBound(sn, 2)

sn(j, jj) = j + 5 * jj

Next
Next
.Item("aa") = sn

MsgBox .Item("aa")(2, 3)

.Item("aa")(2, 3) = 10 * .Item("aa")(2, 3)
MsgBox .Item("aa")(2, 3)
End With

You can use a workaround
- 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)
For j = 0 To UBound(sn)
For jj = 0 To UBound(sn, 2)

sn(j, jj) = j + 5 * jj

Next
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)
End With

21 Remove all items from the Dictionary

With CreateObject("scripting.dictionary")
For Each it In Array("2", "33", "aa3", "aa4", "aa2")

.Item(it) = it & "_content"

Next
.RemoveAll
End With

22 Early binding and late binding

Dictionary isn't part of the standard VBA library.
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

22.1 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")

This code performs 2 actions:
- 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.

22.1.1 implicit object

You can make an implicit object using With.... End With

With CreateObject("scripting.dictionary")

.Add "aa1", "today"

End With

22.1.2 object variable

Or you can define an object variable

Set d_snb_ = CreateObject("scripting.dictionary")
d_snb.Add "aa1", "today"

22.2 early binding

The Dictionary is part of the Microsoft Scripting Runtime library
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 Sub

This connection will be stored in the Excel file.
If 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.

 22.2.1 early binding using an implicit object

Make an implicit object using With ... End With

With New Dictionary

.Add "aa1", "today"

End With

 22.2.2. early binding using an objectvariable

The instruction 'Set' is required to assign an object to a variable.

Set d_snb = New Dictionary
d_snb.Add "aa1", "today"

22.2.3.1 early binding using a declared private objectvariable

You can assign a new instance to the objectvariable in the declaration part of a codemodule.
(e.g. the workbook, a worksheet, a userform or a macromodule).

d_snb.Add "aa1", "PPP"

22.2.3.2 early binding using a declared public objectvariable

If you need the objectvariable to be accessible in the whole project (workbook) you will have to declare it in a macromodule

Public d_snb As New Dictionary

or
In the declaration part:

Public d_snb As Dictionary

In a macro:

Set d_snb = New Dictionary

23 Examples

23.1 Delete duplicate rows/arrays

Column A contains the record keys, some of which are duplicates.
Columns B:F etc, contain the data of the records.

Sub M_delete_duplicates()
sn = Sheets("Sheet1").Cells(1).CurrentRegion.resize(,5)

With CreateObject("scripting.dictionary")
For j = 1 To UBound(sn)

.Item(sn(j, 1)) = Application.Index(sn, j, 0)

Next

Sheets("Sheet1").Cells(1, 4).Resize(.Count, UBound(sn, 2)) = Application.Index(.items, 0, 0)
End With
End Sub

23.2 Filter records containing the highest values in column B

Column A contains the record keys.
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)
sn = Sheets("Sheet1").Cells(1).CurrentRegion

With CreateObject("scripting.dictionary")
For j = 1 To UBound(sn)

.Item(sn(j, 1)) = Application.Index(sn, j, 0)

Next

Sheets("Sheet1").Cells(1, 4).Resize(.Count, UBound(sn, 2)) = Application.Index(.items, 0, 0)
End With
End Sub

23.3 Filter records that contain the smallest value in Column B

Column A contains the record keys, some of which are duplicates.
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
sn = Sheets("Sheet1").Cells(1).CurrentRegion

With CreateObject("scripting.dictionary")
For j = 1 To UBound(sn)

.Item(sn(j, 1)) = Application.Index(sn, j, 0)

Next

Sheets("Sheet1").Cells(1, 4).Resize(.Count, UBound(sn, 2)) = Application.Index(.items, 0, 0)
End With
End Sub

23.4 records : adding new data

Column A contains the keys of the records.
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)
sp = Sheets("sheet1").Cells(1, 5).CurrentRegion

With CreateObject("scripting.dictionary")
For j = 1 To UBound(sn)

.Item(sn(j, 1)) = Application.Index(sn, j, 0)

Next

For j = 1 To UBound(sp)

st = .Item(sp(j, 1)) st(3) = sp(j, 2) .Item(sn(j, 1)) = Application.Index(st, 0, 0)

Next

Sheets("Sheet1").Cells(1, 10).Resize(.Count, UBound(sn, 2)) = Application.Index(.items, 0, 0)
End With
End Sub

23.5 Integrate several worksheets into one

Sub M_integration()
With createobject("scripting.dictionary")
For each sh in Sheets

.item(sh.name)=sh.usedrange

Next
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
end with
End Sub

23.6 Integrate data from several csv files

You will have to adapt the path G:\OF\

Sub M_integratie_csv()
sn = Split(CreateObject("wscript.shell").exec("cmd /c Dir ""G:\OF\*.csv"" /b").stdout.readall, vbCrLf)

With CreateObject("scripting.dictionary")
For j = 0 To UBound(sn)

.Item(sn(j)) = GetObject("G:\OF\" & sn(j)).Sheets(1).UsedRange.Value
GetObject("G:\OF\" & sn(j)).Close False

Next

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
End With
End Sub

23.7 Create a list of unique elements

The adding of items to a Dictionary creates a list of unique keys.
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")

y = .Item(it)

Next
Sheets("sheet1").Cells(1, 10).Validation.Add 3, , , Join(.Keys, ",")

Sheets("sheet1").OLEObjects("Combobox1").Object.List = .Keys
Sheets("sheet1").ListBox1.List = .Keys
End With

In a userform:

Private Sub Userform_initialize()
With CreateObject("scripting.dictionary")
For Each it In Array("aa1", "aa2", "aa3", "aa2", "aa2", "aa4", "aa5")

y = .Item(it)

Next

ComboBox1.List = .Keys
ListBox1.List = .Keys
Me("ComboBox2").List = .Keys
Me("ListBox2").List = .Keys
Controls("ComboBox3").List = .Keys
Controls("Listbox3").List = .Keys
End With
End Sub

23.8 check whether all ActiveX controls have some data

Make the ActiveX commandbutton visible if all ActiveX controls are not empty.

Private dict As Dictionary

Sub M_vul()
Set dict = CreateObject("scripting.dictionary")
For Each it In Array("TextBox1", "TextBox2", "textbox3", "combobox4", "combobox5")

Set dict(it) = Sheets("sheet1").OLEObjects(it)

Next
End Sub

Private Sub Textbox1_change()

M_controle

End Sub

Private Sub M_controle()
y = True
For Each it In dict.Items

y = y * (it.Object.Value <> "")

Next
Sheets("sheet1").CommandButton1.Visible = y
End Sub

23.9 check the existence of a worksheet

In a macromodule:

Public dict_sheets As Dictionary

Sub M_sheet_exists()
Set dict_sheets = CreateObject("scripting.dictionary")
For Each it In Sheets

x0 = dict_sheets(it.name)

Next
End Sub

Now you can use everywhere in the VBproject (Workbook):

Sub M_continue()

' - - - If Not dict_sheets.Exists("Sheet10") Then Sheets.Add.Name = "Sheet10"
' - - -

End Sub

23.10 convert a string into a 2-dimensional array

With CreateObject("scripting.dictionary")
For Each it In Split("aa1_aa2_aa3_aa4_aa5|bb1_bb2_bb3_bb4_bb5|cc1_cc2_cc3_cc4_cc5", "|")

.Item(.Count) = Application.Index(Split(it, "_"), 1, 0)

Next

Cells(10, 1).Resize(.Count, UBound(.Item(1))) = Application.Index(.Items, 0, 0)
End With