Reply to comment

The Dictionary object in the Microsoft Scripting library is an associative array that allows values to be stored against a key. It works in much the same way as a Perl hash.

Early binding it within a VBA module is simply a case of adding a reference to the Microsoft Scripting Runtime library, then declaring a new Dictionary object.

Dim dicColour As New Dictionary

Occassionally you may have need to late bind this object. Late binding is simply creating an on-the-fly reference to an object stored in an object library that is present on the computer, but not enabled in the Available References (Tools->References menu in VBA IDE). Here is the code for late binding the Microsoft Scripting Runtime Dictionary object:


  Dim dicColour as Object
  Set dicColour = CreateObject("Scripting.Dictionary")


Here is some code to try out on the newly created Dictionary object:


  dicColour.Add "Blue", 100
  Debug.Print dicColour.Exists("Red")
  Debug.Print dicColour.Exists("Blue")
  Debug.Print dicColour.Item("Blue")
  dicColour.Remove ("Blue")
  Debug.Print dicColour.Exists("Blue")


  dicColour.Add "Green", 100
  dicColour.Add "Purple", "Text value"
  dicColour.Add "Yellow", 80.2


  Dim vKey As Variant
  For Each vKey In dicColour.Keys
    Debug.Print vKey & ": " & dicColour.Item(vKey)
  Next