如何在VBA Excel中创建多列字典并从中获取数据

Question

我想加载以下结构的字典表数组(字典?UDT类?)，写入"dictCompany"， CompanyName是唯一键。

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

然后我需要根据CompanyName查找条目，并引用其他列，如dictCompany。个人名称或公司名称。PersonPhone，像这样的伪代码:

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"

我不知道使用什么技术-数组，字典，UDT类或任何VBA有，所以我会欣赏甚至有关键字的定向答案，以便进一步搜索。

Answer 1

创建一个名为clsPerson的类，并在类中放置以下内容:

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

然后在加载字典的模块中使用:

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

您还可以将此扩展为使用公司名称作为关键字或使用Collection而不是Dictionary