This Access Excel tutorial explains how to use Scripting.Dictionary in VBA to store unique array item.
You may also want to read:
Remove duplicates in text using Dictionary
Excel VBA filter value then copy data to new worksheet
Access Excel VBA Scripting.Dictionary to store unique array item
An associative array, map, symbol table, or dictionary is an abstract data type composed of a collection of (key, value) pairs, such that each possible key appears just once in the collection.
Put it simply, you can avoid duplicate values to be added in the associative array and make sure the array items are unique.
In this article, I will explain the use of Dictionary, which is a standard object provided by the Microsoft Scripting Runtime (scrrun.dll) dependency.
If you are looking for tutorial for standard Array which allows duplicates, click here.
Access Excel VBA Scripting.Dictionary – Add Item
To begin, create the Dictionary Object using the below code.
Dim objDict As Object
Set objDict = CreateObject("Scripting.Dictionary")
The next step is to add an item to Dictionary using Add Method.
objDict.Add (Key, Item)
| Argument | Description |
| Key | The unique identifier in the array, can be String or Number. You may not add the same Key more than one times. |
| Item | The value to store in array for particular Key |
For example, you can add two names with employee ID 001 and 002 as Key.
objDict.Add "001", "John" objDict.Add "002", "Mary"
Before you add another new item, you should check if key already exists in Dictionary.
objDict.exists(Key)
Access Excel VBA Scripting.Dictionary – Remove Item
You can delete specific item using Remove Method.
objDict.Remove Key
Or delete all items using RemoveAll Method.
objDict.RemoveAll
Access Excel VBA Scripting.Dictionary – Modify Item
You can rename a Key using Key Property
objDict.Key(old_key_name)= new_key_name
You can modify an Item using Item Property
objDict.Item(Key) = Item
Access Excel VBA Scripting.Dictionary – Return all Item or Key
To return all Items, use
objDict.Items()
To return all Keys, use
objDict.Keys()
You can count number of Items using Count Method
objDict.Count
Use For Each Loop to loop through all Items, for example
For Each k In objDict.Keys MsgBox (objDict.Item(k)) Next
Count Unique Value in a Range
This Function stores unique values in Dictionary and then counts how many unique values in a Range (case sensitive).
Public Function countUnique(rng As Range)
Dim objDict As Variant
Set objDict = CreateObject("Scripting.Dictionary")
For Each rng1 In rng
If Not objDict.exists(rng1.Value) Then
objDict.Add rng1.Value, rng1.Value 'I don't care what key it is, I just want to store item in the key so that it becomes unique
End If
Next rng1
countUnique = objDict.Count
End Function
I have a more practical use of dictionary where I add unique value in a column to the dictionary and then add Autofilter to filter data, finally export data to separate worksheet. Click here to read more.
Access Excel VBA Scripting.Dictionary – Example
Below is the sample code to remove duplicate in Text using Dictionary. Click here if you want to read details of this Function.
Public Function wUniqueStr(sinput As String, delimiter As String, Optional Compare As Integer = 0) As String
Dim objDict As Object
Dim arrInput As Variant
Dim uniqStr As String
arrInput = Split(sinput, delimiter)
Set objDict = CreateObject("Scripting.Dictionary")
If Compare = 0 Then 'case insensitive
For i = 0 To UBound(arrInput)
If objDict.exists(UCase(Trim(arrInput(i)))) Then
Else
objDict.Add UCase(Trim(arrInput(i))), i
uniqStr = uniqStr & arrInput(i) & delimiter
End If
Next i
wUniqueStr = Left(uniqStr, Len(uniqStr) - Len(delimiter))
Else 'case sensitive
For i = 0 To UBound(arrInput)
If objDict.exists(Trim(arrInput(i))) Then
Else
objDict.Add Trim(arrInput(i)), i
uniqStr = uniqStr & arrInput(i) & delimiter
End If
Next i
wUniqueStr = Left(uniqStr, Len(uniqStr) - Len(delimiter))
End If
End Function
