比较VBA中两个词典之间的数据

I am trying to compare the data in two dictionaries in order to identify keys which have different items. I'm having difficulty writing out the loop and the if statement to make it that happen.

我试图比较两个词典中的数据,以识别具有不同项目的键。我很难写出循环和if语句来实现它。

In the below example, it makes two dictionaries--BeginningData and EndingData. The Key "Jane Jones" has a different item in each dictionary.

在下面的示例中,它生成两个词典 - BeginningData和EndingData。密钥“简琼斯”在每个字典中都有不同的项目。

The output I am looking for in this situation is: "Jane Jones is 222222222 in the first dictionary, but is 444444444 in the second dictionary"

我在这种情况下寻找的输出是:“Jane Jones在第一本字典中是222222222,在第二本字典中是444444444”

Any ideas on how I would complete this code? I am trying to avoid arrays in this particular project. Thanks!

关于如何完成此代码的任何想法?我试图避免在这个特定项目中的数组。谢谢!

Sub MainProcess()

Dim BeginningData As Dictionary, EndingData As Dictionary

Dim StartItem As Variant, EndItem As Variant

Set BeginningData = New Dictionary
Set EndingData = New Dictionary

With BeginningData
    .Add "John Smith", 111111111
    .Add "Jane Jones", 222222222
    .Add "Jim Brown", 333333333
End With

With EndingData
    .Add "John Smith", 111111111
    .Add "Jane Jones", 444444444
    .Add "Jim Brown", 333333333
End With

For Each StartItem In BeginningData.Keys
    For Each EndItem In EndingData.Keys

        'Some sort of if statement here
            Debug.Print _
            GetKey(BeginningData, BeginningData.Item(EndItem)) & _
            " is " & _
            BeginningData.Item(StartItem) & _
            " in the first dictionary, but is " & _
            EndingData.Item(StartItem) & _
            " in the second dictionary."
        End If

    Next
Next

End Sub

Function GetKey(Dic As Dictionary, strItem As String) As String
    Dim key As Variant
    For Each key In Dic.Keys
        If Dic.Item(key) = strItem Then
            GetKey = CStr(key)
            Exit Function
        End If
    Next
End Function

2 个解决方案

#1

you don't need neither nested loops nor GetKey() function, just use Exists() method of Dictionary object to check for a dictionary to contain a given key:

你既不需要嵌套循环也不需要GetKey()函数,只需使用Dictionary对象的Exists()方法来检查包含给定键的字典:

Option Explicit

Sub WorkingProcess()
    Dim BeginningData As Dictionary, EndingData As Dictionary

    Dim BeginningItem As Variant

    Set BeginningData = New Dictionary
    Set EndingData = New Dictionary

    With BeginningData
        .Add "John Smith", 111111111
        .Add "Jane Jones", 222222222
        .Add "Jim Brown", 333333333
    End With

    With EndingData
        .Add "John Smith", 111111111
        .Add "Jane Jones", 444444444
        .Add "Jim Brown", 333333333
    End With

    For Each BeginningItem In BeginningData.Keys
        If EndingData.Exists(BeginningItem) Then ' if current "beginning" dict key is in "ending" dict
            If BeginningData.Item(BeginningItem) <> EndingData.Item(BeginningItem) Then ' if items don't match
                Debug.Print _
                BeginningItem & _
                " is " & _
                BeginningData.Item(BeginningItem) & _
                " in the first dictionary, but is " & _
                EndingData.Item(BeginningItem) & _
                " in the second dictionary."
            End If
        End If
    Next
End Sub

#2

The full working code to compare the two dictionaries and produce the output I needed is:

比较两个字典并生成我需要的输出的完整工作代码是:

Sub WorkingProcess()

Dim BeginningData As Dictionary, EndingData As Dictionary

Dim StartItem As Variant, EndItem As Variant

Set BeginningData = New Dictionary
Set EndingData = New Dictionary

With BeginningData
    .Add "John Smith", 111111111
    .Add "Jane Jones", 222222222
    .Add "Jim Brown", 333333333
End With

With EndingData
    .Add "John Smith", 111111111
    .Add "Jane Jones", 444444444
    .Add "Jim Brown", 333333333
End With

For Each StartItem In BeginningData.Keys
    For Each EndItem In EndingData.Keys

        If GetKey(BeginningData, BeginningData.Item(StartItem)) = _
        GetKey(BeginningData, BeginningData.Item(EndItem)) And _
        BeginningData.Item(StartItem) <> EndingData.Item(StartItem) Then
            Debug.Print _
            GetKey(BeginningData, BeginningData.Item(EndItem)) & _
            " is " & _
            BeginningData.Item(StartItem) & _
            " in the first dictionary, but is " & _
            EndingData.Item(StartItem) & _
            " in the second dictionary."
        End If

    Next
Next

End Sub

Function GetKey(Dic As Dictionary, strItem As String) As String
    Dim key As Variant
    For Each key In Dic.Keys
        If Dic.Item(key) = strItem Then
            GetKey = CStr(key)
            Exit Function
        End If
    Next
End Function