VBA创建一个嵌套字典 [英] VBA create a nested dictionary

问题描述

我有以下代码集

Dim dic As Dictionary
Dim dataArray() As Variant
Dim headerRow(1 To 4) As Variant
Dim mySheet As Worksheet
Dim loopCounter As Long
Dim endRow As Long
Dim endColumn As Long
Dim keyColumn As Long
Dim x As Integer
Dim wsName2 As String
Dim duplicateDictionary As Dictionary

wsName2 = ActiveSheet.Name
Set duplicateDictionary = New Dictionary

Set dic = New Dictionary

With Worksheets(wsName2).Range("A1",Worksheets(wsName2).Range("A1").End(xlDown))
    keyColumn = 1
    endColumn = 4
    endRow = .Range("A1").End(xlDown).Row
    dataArray = Range(.Cells(1, 1), .Cells(endRow, endColumn)).Value
End With

For x = 1 To endColumn
    headerRow(x) = dataArray(1, x)
Next x

For loopCounter = 2 To endRow
    Dim storeKey As Variant
    Dim lineArray()
    Dim itemNumber As String
    Dim itemNumberAndDescription As String
    Dim q As Variant

    ReDim lineArray(1 To endColumn)

    For x = 1 To endColumn
        lineArray(x) = dataArray(loopCounter, x)
    Next x

    storeKey = lineArray(keyColumn)

    If Not dic.Exists(storeKey) Then
        dic.Add storeKey, New Collection
    End If

    'create duplicate dictionary if doesn't exist
    If Not duplicateDictionary.Exists(storeKey) Then
        duplicateDictionary.Add storeKey, New Dictionary
    End If

    dic(storeKey).Add lineArray

    itemNumber = UCase(Trim(Left(lineArray(2), InStr(1, lineArray(2), " -", vbBinaryCompare))))

    'add item in the duplicate dictionary based on the storekey
    duplicateDictionary(storeKey).Add itemNumber, itemNumber
Next loopCounter

我要使用以下列表作为示例，尝试创建一个嵌套字典，每个字典都有自己的键。这样做的目的是因为我有一个报告，该报告将在添加之前将上面创建的字典检查为重复项。

What i'm trying to do, using the list below as an example, is to create a nested dictionary and each with their own key. The purpose for this is because I have a report that will checks the dictionary created above for duplicate item before adding to it.

城市-团队

LA ----湖人

CHI ----公牛

CHI ----Bulls

NY ----尼克斯

NY ----Knicks

DAL ----小牛

DAL ----Mavericks

BOS ----凯尔特人队

BOS ----Celtics

我能够创建第一个词典（城市），但似乎无法为基于城市的团队名称创建第二个（嵌套的）词典。帮助会很棒。谢谢！

I am able to create the first dictionary(city), but I can't seem to created a second (nested) dictionary for the team name based on the city. Help would be great. Thanks!

推荐答案

首先创建-然后将新字典添加到外部字典中。以下子句应该让您有所了解：

First create -- then add the new dictionary to the outer dict. The following sub should give you some idea:

Sub Test()
    Dim DOD As New Dictionary
    Dim InnerDict As Dictionary

    'create one inner dictionary
    Set InnerDict = New Dictionary
    InnerDict.Add "A", 1
    InnerDict.Add "B", 2
    'add it to the dictionary of dictionaries
    DOD.Add "dict1", InnerDict

    'create another inner dictionary
    Set InnerDict = New Dictionary
    InnerDict.Add "A", 3
    InnerDict.Add "B", 4
    InnerDict.Add "C", 5
    'add it to DOD
    DOD.Add "dict2", InnerDict

    'access like:
    Debug.Print DOD("dict1")("A") 'prints 1
    Debug.Print DOD("dict2")("A") 'prints 3

    'can add new keys to inner dicts:
    DOD("dict1").Add "C", 10
    Debug.Print DOD("dict1")("C") 'prints 10

End Sub

这篇关于VBA创建一个嵌套字典的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！