VBA Excel Question

Here is some general information about using the Dictionary object.

The scripting dictionary has a couple of significant advantages over collections: it is faster for numbers of elements less than 40 or 50 thousand and it allows for testing whether or not a key exists. The scripting dictionary can store any type of variable or object but it is best suited for storing objects that need to be accessed with keys. Arrays are a much more efficient method for storing simple variables.

To use the scripting dictionary a reference must be established to the "Microsoft Scripting Runtime" library unless late binding is used (see below). The sample code below illustrates how to define, initialize, load, access, and clear a scripting dictionary using objects.

   Dim Dictionary As Scripting.Dictionary
   Dim Index As Long
   Dim ClassObject As clsClass

   ' Initialize the dictionary
   Set Dictionary = New Scripting.Dictionary

   ' Load the dictionary with objects using an index value for a key
   For Index = 1 To 100
      Set ClassObject = New clsClass
      Dictionary.Add CStr(Index), ClassObject
   Next Index

   ' Read a specific element
   Set ClassObject = Dictionary("23")
   
   ' Read each object - this requires an intermediate variant because the default type
   ' of a dictionary's item is not compatible with an object of a specific type
   Dim ElementObject As Variant
   For Each ElementObject In Dictionary.Items
      Set ClassObject = ElementObject
   Next ElementObject

   ' Test if a key exists
   If Dictionary.Exists("23") Then
      MsgBox "Key 23 exists"
   End If

   ' Set the compare mode to TextCompare
   Dictionary.CompareMode = TextCompare

   ' Change the key of an element
   Dictionary.Key("23") = "ABC"

   ' Extract items into an array
   Dim DictionaryItems As Variant
   DictionaryItems = Dictionary.Items

   ' Extract keys into an array
   Dim DictionaryKeys As Variant
   DictionaryKeys = Dictionary.Keys

   ' Clear an element from the dictionary
   Dictionary.Remove("23")

   ' Clear the entire dictionary
   Dictionary.RemoveAll

   ' Use late binding (no reference to the "Microsoft Scripting Runtime" library is required)
   Dim Dictionary As Object
   Set Dictionary = CreateObject("Scripting.Dictionary")

Kevin

   For Each k In dic.Keys
        Debug.Print "Key: " & k & "; Value: " & dic.Item(k)
    Next