Data Dictionary in VBA - Complete Syntax Documentation

posted Sep 30, 2013, 11:53 AM by Craig Hatmaker   [ updated Jun 20, 2016, 6:17 PM ]
Data Dictionaries are like enhanced Collections.  I use them anywhere I would have used a collection except when using as a class collection (more on that later).  Unfortunately, I haven't found a single source that documents the entire object.  So, for my own documentation, I decided to write this.  See bottom of a list or sources used in this post.

Advantages of Dictionary Objects over Collections 1

  • CompareMode property specifies case sensitivity for Keys.
  • A method for determining if an object exists in a Dictionary.
  • A method for extracting all of the Keys into an Array.
  • A method for extracting all of the Items into an Array.
  • A method for changing a Key value.
  • A method for removing all items from the Dictionary.
  • Dictionary Keys are not limited to String datatype.
NOTE: One important difference between the Collection object and the Dictionary object is the behavior of the Item property. If you use the Item property to reference a nonexistent Key in a Collection, you will receive an error. If you use the Item property to reference a nonexistent Key in a Dictionary, that Key will be added to the Dictionary. Use the Exists method to determine whether or not a Key is present in a Dictionary.


Create a Dictionary Object on any version of XL
This uses a late binding example which I prefer to simplify deployment to other PCs.
Dim dicMyDictionary as Object
Set dicMyDictionary = CreateObject("Scripting.Dictionary")
Set Key Sensitivity
dicMyDictionary.CompareMode = vbBinaryCompare 'Case Sensitive
dicMyDictionary.CompareMode = vbTextCompare   'Case Insensitive
Add Item
dicMyDictionary.Add ItemKey, myItem
Add or Change Item
This is sometimes called the "Silent Add" method.  If a key doesn't exist, it gets added. 
dicMyDictionary(ItemKey) = myItem
Set dicMyDictionary(ItemKey) = myObject
Determine if an Item Exists
If you don't want the "Silent Add" feature, use this before attempting to change items.
If dicMyDictionary.Exists(ItemKey) Then ...
Get Number of Items
Debug.Print = dicMyDictionary.Count
Get an Item by Key
MyItem = dicMyDictionary(ItemKey)
- or -
Set MyObject = dicMyDictionary(ItemKey)
Get an Item by Number
Note! Dictionary Items start at 0
MyItem = dicMyDictionary.Items()(ItemNumber)
- or -
Set MyObject = dicMyDictionary.Items()(ItemNumber)
Get a Key by Number
MyKey = dicMyDictionary.Keys()(ItemNumber)
Load all Items into an Array
Dim vArray as Variant
vArray = dicMyDictionary.Items()
Load all Keys into an Array
Dim vArray as Variant
vArray = dicMyDictionary.Keys()
Remove an Item
Remove All Items


Eliminate Duplicates
Dictionaries are perfect for creating unique lists using their "silent add" function.
Function GetDistinct(ByVal oTarget as Range) as Variant
    Dim vArray          as Variant
    Dim dicMyDictionary as Object
    Dim v               as Variant
    Set dicMyDictionary = CreateObject("Scripting.Dictionary")
    vArray = oTarget
    For Each v in vArray
        dicMyDictionary(v) = v
    GetDistinct = dicMyDictionary.Items()
End Function
Code Table Lookup
If we plan to reference a table repeatedly and "randomly" (as opposed to "sequentially"), putting the table into a dictionary is easy and quick as well as easy and quick to use.
Public Function Tbl2Dic(ByVal oTarget As Range) As Object
    Dim dicMyDictionary As Object
    Dim lRow            As Long
    Dim lCols           As Long
    Set dicMyDictionary = CreateObject("Scripting.Dictionary")
    lCols = oTarget.Columns.Count - 1
    For lRow = 1 To oTarget.Rows.Count
        With oTarget.Cells(lRow, 1)
            Set dicMyDictionary(.Value) = .Offset(0, 1).Resize(1, lCols)
        End With
    Set Tbl2Dic = dicMyDictionary
End Function
'   Get column 2 from 2 column table called "States" where column 1 = "VA"
    Dim dicStates as Object
    Set dicStates = Tbl2Dic([States])
    Debug.print dicStates("VA")
'   Get column 3 from 3 column table called "States" where column 1 = "VA"  
    Dim dicStates as Object
    Set dicStates = Tbl2Dic([States])
    Debug.print dicStates("VA")(1,2)
Class Container
I use dictionaries to hold class objects in memory because dictionaries can hold anything.
Public dicWorksheets as Object
Sub InitMyClass()
   Dim MyWorksheet    As clsWorksheet
   Dim oWks           As Worksheet
   If dicWorksheets is nothing then _
       Set dicWorksheets = CreateObject("Scripting.Dictionary")  
   For each oWks In ThisWorkbook.Worksheets
       Set MyWorksheet = New clsWorksheet
       Set MyWorksheet.Worksheet = oWks
       Set dicWorksheets(oWks.Name) = MyWorksheet
End Function

Discuss this post or other BXL topics at: