Access Excel Scripting Dictionary store unique array item

This Access Excel tutorial explains how to use Scripting.Dictionary in VBA to store unique array item.

You may also want to read:

Access Excel VBA Array

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.


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.


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


To return all Keys, use


You can count number of Items using Count Method


Use For Each Loop to loop through all Items, for example

For Each k In objDict.Keys
    MsgBox (objDict.Item(k))

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

Outbound References

Leave a Reply

Your email address will not be published.