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

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.

Create(scripting.dictionary Exists For Mac)

Windows Scripting 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.