Does VBA have Dictionary Structure? Does VBA have Dictionary Structure? vba vba

Does VBA have Dictionary Structure?


Set a reference to MS Scripting runtime ('Microsoft Scripting Runtime'). As per @regjo's comment, go to Tools->References and tick the box for 'Microsoft Scripting Runtime'.

References Window

Create a dictionary instance using the code below:

Set dict = CreateObject("Scripting.Dictionary")


Dim dict As New Scripting.Dictionary 

Example of use:

If Not dict.Exists(key) Then     dict.Add key, valueEnd If 

Don't forget to set the dictionary to Nothing when you have finished using it.

Set dict = Nothing 

VBA has the collection object:

    Dim c As Collection    Set c = New Collection    c.Add "Data1", "Key1"    c.Add "Data2", "Key2"    c.Add "Data3", "Key3"    'Insert data via key into cell A1    Range("A1").Value = c.Item("Key2")

The Collection object performs key-based lookups using a hash so it's quick.

You can use a Contains() function to check whether a particular collection contains a key:

Public Function Contains(col As Collection, key As Variant) As Boolean    On Error Resume Next    col(key) ' Just try it. If it fails, Err.Number will be nonzero.    Contains = (Err.Number = 0)    Err.ClearEnd Function

Edit 24 June 2015: Shorter Contains() thanks to @TWiStErRob.

Edit 25 September 2015: Added Err.Clear() thanks to @scipilot.

VBA does not have an internal implementation of a dictionary, but from VBA you can still use the dictionary object from MS Scripting Runtime Library.

Dim dSet d = CreateObject("Scripting.Dictionary")d.Add "a", "aaa"d.Add "b", "bbb"d.Add "c", "ccc"If d.Exists("c") Then    MsgBox d("c")End If