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:

Remove duplicates in text using Dictionary

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.

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 Items already exists in Dictionary.

objDict.exists(Item)

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 Function

objDict.Count

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

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

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

Outbound References

http://www.stealthbot.net/wiki/Scripting.Dictionary

Access Excel Scripting Dictionary store unique array item