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'.
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")
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