Data Dictionary in VBA - Complete Syntax Documentation
Post date: Sep 30, 2013 6:53:07 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.
Syntax
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
-or-
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
dicMyDictionary.Remove(ItemKey)
Remove All Items
dicMyDictionary.RemoveAll
Usages
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
Next
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
Next
Set Tbl2Dic = dicMyDictionary
End Function
Examples:
' 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
Next
End Function
