How to create multi-column dictionary and get data from it in VBA Excel

Question

I want to load dictionary table of following structure to an array (dictionary? UDT class?), say "dictCompany", CompanyName is unique key.

CompanyName | PersonName | PersonPhone
ABC Ltd     | Nick       | +12345
XYZ Co      | Alice      | +78901

And then I need to find entries by CompanyName and refer to other columns like dictCompany.PersonName or dictCompany.PersonPhone, something like this pseudo-code:

Dim i as Long
Dim dictIndex as Long
 
For i = 0 To UBound(dictCompany)
    If dictCompany(i).CompanyName = "ABC Ltd" Then
        dictIndex = i
    End If
Next i

debug.print dictCompany(dictIndex).PersonName 'should get "Nick"
debug.print dictCompany(dictIndex).PersonPhone 'should get "+12345"

I don't know what technology to use - array, dictionary, UDT class or whatever VBA has, so I would appreciate even directional answers with keywords for furhter search.

You need a dictionary of persons. The person will be a class consisting of two properties, the name and phone. See something similar: https://stackoverflow.com/questions/44965938/creating-nested-class/44968814#44968814 — Kostas K., Aug 16 '21 at 13:28

score 0 · Accepted Answer · answered Aug 16 '21 at 15:49

Create a class called clsPerson and in your class place the following:

Public PersonName as String, PersonPhone as String, CompanyName as String

Then in your Module which loads the Dictionary use:

Option Explicit
Public Sub Demo()
    Dim dictCompany As Object
    Dim Emp As clsPerson
    Dim i As Long

    Set dictCompany = CreateObject("Scripting.Dictionary")

    ' Load Data into Dictionary
    For i = 2 To 3
        Set Emp = New clsPerson

        Emp.CompanyName = Range("A" & i).Value2
        Emp.PersonName = Range("B" & i).Value2
        Emp.PersonPhone = Range("C" & i).Value2
        
        If dictCompany.Exists(i) Then
            Set dictCompany(i) = Emp
        Else
            dictCompany.Add i, Emp
        End If
    Next i

    ' Read back
    For i = 2 To 3
        If dictCompany(i).CompanyName = "ABC Ltd" Then
            Debug.Print dictCompany(i).PersonName 'should get "Nick"
            Debug.Print dictCompany(i).PersonPhone 'should get "+12345"
            Exit For
        End If
    Next i

End Sub

You could expand on this as well to use the Company Name as your key or use a Collection instead of a Dictionary

I have recreated solution and it does what I want, thanks! To help my understanding could you please elaborate on following? — Evgeny Ivanov, Aug 16 '21 at 17:14
1) Your code creates standart dictionary, where object stored is actually an instance of a class with all it's structure - right? 2) How does it differ from class where I define structure with Type command? 3) Is there a quick way to add big range to dictionary in one move, like with array, or it should be by-element-iteration? Thanks in advance! — Evgeny Ivanov, Aug 16 '21 at 17:21

How to create multi-column dictionary and get data from it in VBA Excel

1 Answers1