Data Dictionaries

Whatever is in the first column will be the keys, which in this case, are country names. Tied to each key are the values. We can use this routine to index this table so we can retrieve values in a very similar fashion to VLOOKUP().

Public Function Tbl2Dic(ByVal oLo As ListObject) As Object

Dim oDictionary as Object 'Dictionary to hold table rows by key

Dim olr As ListRow 'Table row

Set oDictionary = CreateObject("Scripting.Dictionary")

For each olr In oLo.ListRows

oDictionary(olr.Range(1).Text) = Application.Transpose(Application.Transpose(olr.Range))

Next

Set Tbl2Dic = oDictionary

End Function

The routine places the values in a one dimensional array so if we click inside this table and call this routine like so:

Set v = Tbl2Dic(Selection.ListObject)

v becomes a data dictionary that can be accessed key, and then by column index. Thus, to get Egypt's Net Change (4th column) we can do so like this:

Debug.Print v("Egypt")(4)

Returning Ranges instead of ValuesThe above returns the cell's value, but not it's format. Nor can it be updated. If we want the value and format or to be able to update the cell from VBA we can change the line within the loop to store the ListRow with the key instead of just the values.

oDictionary(olr.Range(1).Text) = olr.Range

Now when we access Egypt's Yearly Change (3rd column) we can see its value in its proper format:

Debug.Print v("Egypt").Range(3).Text

And if we want to change the value, we can with this:

v("Egypt").Range(3) = 0.0200

Using Multiple Keys and Column Names
The prior functions work like VLOOKUP(). VLOOKUP() is designed for just one key field. But sometimes our data has more than one key. In the data sample below are employees. It is possible that two employees could have the same last name. But it is highly unlikely, in a small company, that two employees will have the same first and last name.

We can lookup values using multiple key fields by storing data dictionaries in data dictionaries. For example, we can have a data dictionary of Last Names. We can then store within each Last Name entry a data dictionary of First Names of people having the same last name. Likewise, we can store each row's values in a data dictionary keyed by column name and store that in the previous data dictionary. So when we want to retrieve Steven Buchanan's title we could execute this:

Debug.Print v("Buchanan")("Steven")("Title")

NOTE! v("Buchanan") is a data dictionary of last names. The data dictionary item keyed by "Buchanan" contains a data dictionary of first names associated with people having "Buchanan" as their last name. One of those people have the first name: "Steven". In that data dictionary item is a data dictionary keyed by column names containing all values from that row.

This sounds complicated but it is somewhat simplified using recursion (a program that calls itself). Once we create the routines we can use them very simply. For example, to key the employee table by the first two columns we call the first routine: CrtIDX (Create Index) like so:

Set v = CrtIDX(Selection.ListObject, 2)

CrtIDX sets up the next routine IdxRec (Index Record) which creates a data dictionary for the first key, they calls itself to create a data dictionary for the first key item containing the second key. Once all keys are built, it calls itself again to index all values.

Below are routines that are very similar to what I use in my work. The major difference is the error handling has been removed to make this easy for readers to copy and pasted into their own project. I highly recommend adding error handling.

Public Function CrtIDX(ByVal oLo As ListObject, _

Optional ByVal lKeyCols As Long = 1) As Object

' Description:Create Property Table Dictionary (Recursive)

' Inputs: oLo ListObject (Excel Table)

' lKeyCols Number of columns to use as keys

' Outputs: Me Success: Dictionary

' Failure: Nothing

' Requisites: Function: IdxRec

' Notes: See LstIDX for how to access IDX

' PDD ex: Set oIDX = CrtIDX([PDD],3)

' Example: CrtIDX(Selection.ListObject).Keys()(1)

' Date Ini Modification

' 11/29/21 CWH Initial Development

' Declarations

Const cRoutine As String = "CrtIDX"

Dim oRecords As Object 'Records to be processed in a dictionary

Dim vHdrs As Variant 'Record Field Names/Column Headers

Dim lRow As Long 'Records Row

Dim lCol As Long 'Records Column

Dim v As Variant

' Error Handling Initialization

Set CrtIDX = Nothing

' Initialize Variables

Set oRecords = CreateObject("Scripting.Dictionary")

vHdrs = Application.Transpose( _

Application.Transpose(oLo.HeaderRowRange))

' Procedure

If Not oLo.DataBodyRange Is Nothing Then

' Convert records to dictionary

ReDim v(1 To oLo.ListColumns.Count)

For lRow = 1 To oLo.ListRows.Count

For lCol = 1 To oLo.ListColumns.Count

v(lCol) = oLo.ListRows(lRow).Range(lCol)

Next

oRecords(lRow) = v

Next

' Index Records

Set CrtIDX = IdxRec(oRecords:=oRecords, _

lKeyColumns:=lKeyCols, _

vHdrs:=vHdrs, _

lCol:=1)

End If

End Function