Why to Use Dictionary Object in VBA

VBA has two types of storing collection of datas:

1.Array

2.VBA collection

When you want to compare large data set(A key and Value),Dictionary object is much quicker. Take a reference of Microsoft Scripting Runtime as mentioned below

Advantages of dictionary over Collection are:

1. You can use any value for keys, including numbers. Only requirement is that the key value can be contained in a variant.

2. The Dictionary object's Item member is a read-write property, not a method.

3. The Dictionary object has an Exists method to allow you to check if a key has already been used.

Sub test()

' Declare the dictionaries.

Dim Dict1 As Dictionary

Dim Dict2 As Dictionary

' Create a variant to hold the object.

Dim vContainer1

Dim vContainer2

' Create the dictionary instances.

Set Dict1 = New Dictionary

Set Dict2 = New Dictionary

With Dict1

  'set compare mode

  .CompareMode = BinaryCompare

  ' Add items to the dictionary.

  .Add 1, "Item 1"

  .Add 2, "Item 2"

  .Add 3, "Item 3"

End With

With Dict2

  'set compare mode

  .CompareMode = BinaryCompare

  ' Add items to the dictionary.

  .Add 1, "Item 1a"

  .Add 2, "Item 2"

  .Add 3, "Item 4"

End With

' Compare the two dictionaries.

For Each vContainer1 In Dict1

  If Not Dict2.Exists(vContainer1) Then

    MsgBox vContainer1 & " is in Dict1 but not in Dict2"

  Else ' Item exists so lets check the size.

    If Dict2.Item(vContainer1) <> Dict1.Item(vContainer1) Then

    MsgBox "Key item " & vContainer1 & " is different"

    End If

  End If

Next

End Sub