Does VBA have Dictionary Structure?
Yes.
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")
or
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