excel - VBA Dictionary.Exists 方法从字符串数组返回假负给定字符串

Question

我将位于字符串数组中的字符串类型的键传递给返回假阴性的 scripting.dictionary.exists(key) 方法。我希望它正确返回一个正 bool 值，以便我可以确定 key 是否对每个业务流程有效。有效值存储在字典中。

我已经检查以确保字典的比较模式是 TextCompare 这无关紧要，因为事实证明字典和数组中字符串的大小写是相同的。我还确保变量类型都是字符串 (8)。此外，我确保字符串数组和字典中的字符串是相同的。最后，我检查以确保如果将字符串直接输入参数而不是通过数组通过引用传递给参数，该方法也会返回误报。

Function fvalidatedata(saInput() As String) As String()

    Dim dInvalidRecords As Scripting.Dictionary, _
        dValidDisputeStatuses As Scripting.Dictionary, dDisputeStatusErrorMap As Scripting.Dictionary, _
        dValidReasonCodes As Scripting.Dictionary, dReasonCodeAbbrevMap As Scripting.Dictionary, _
        saListBoxValues() As String, i As Long, ii As Long, frmIRC As New ufInvalidReasonCode

    Set dInvalidRecords = New Scripting.Dictionary
    Set dValidDisputeStatuses = New Scripting.Dictionary
    Set dDisputeStatusErrorMap = New Scripting.Dictionary
    Set dValidReasonCodes = New Scripting.Dictionary
    Set dReasonCodeAbbrevMap = New Scripting.Dictionary

    dInvalidRecords.CompareMode = BinaryCompare
    dValidDisputeStatuses.CompareMode = TextCompare
    dDisputeStatusErrorMap.CompareMode = TextCompare
    dValidReasonCodes.CompareMode = BinaryCompare
    dReasonCodeAbbrevMap.CompareMode = BinaryCompare

    Set dValidDisputeStatuses = fValidDisputeStatusMap
    Set dDisputeStatusErrorMap = fDisputeStatusErrorMap
    Set dValidReasonCodes = fValidReasonCodes
    Set dReasonCodeAbbrevMap = fReasonCodeAbbrevMap

    For i = 2 To UBound(saInput, 1)
        'Dispute Status Validation
            If Not dValidDisputeStatuses.Exists(saInput(i, 12)) Then
                Debug.Print dValidDisputeStatuses.Exists(saInput(i, 12))
                If dDisputeStatusErrorMap.Exists(saInput(i, 12)) Then
                    saInput(i, 12) = dDisputeStatusErrorMap(saInput(i, 12))
                Else
                    ReDim saListBoxValues(0 To dValidDisputeStatuses.Count - 1)
                    For ii = 0 To dValidDisputeStatuses.Count - 1
                        saListBoxValues(ii) = dValidDisputeStatuses.Keys(ii)
                    Next ii
                    frmIRC.ListBox1.List = saListBoxValues
                    frmIRC.l1 = "Please select valid dispute status from the list below for record " & saInput(i, 3) & " and submit once complete."
                    frmIRC.Show vbModeless

                End If
            End If
        'Reason Code Validation
    Next i

End Function
Function fValidDisputeStatusMap() As Scripting.Dictionary

    Dim dMap As Scripting.Dictionary, lo As ListObject, i As Long

    Set dMap = New Scripting.Dictionary
    dMap.CompareMode = TextCompare

    Set lo = Application.Workbooks("RnR_Dispute_Process_Workbook.xlsx").Sheets("Update Dictionary").ListObjects("Valid_Statuses")

    For i = 1 To lo.ListColumns("Valid Statuses").DataBodyRange.Count
        If Not dMap.Exists(lo.ListColumns("Valid Statuses").DataBodyRange(i)) Then
            dMap.Add lo.ListColumns("Valid Statuses").DataBodyRange(i), vbNullString
        End If
    Next i

    Set fValidDisputeStatusMap = dMap

    Set dMap = Nothing

End Function

如果字典中不存在 saInput(i,12)，我希望 (Not dValidDisputeStatuses.Exists(saInput(i, 12))) 的输出为 TRUE，但它确实存在。

Answer 1

I am passing a key of type string

但你不是 :) 你实际上传递了一个 Range对象在这里:

If Not dMap.Exists(lo.ListColumns("Valid Statuses").DataBodyRange(i))

大多数情况下(例如，在分配给 Range 时)恰好是 Range对象将返回其默认属性( Cells )，该属性返回其默认属性( Value )，但是在获取范围时，这并不那么可靠。
你偶然发现了一个有趣的案例!而 Keys在字典中可以是除数组之外的任何东西，正如您无意中观察到的，当您将复杂对象用作 Keys 时，可能会出现奇怪的事情。 .

A Dictionary object is the equivalent of a PERL associative array. Items, which can be any form of data, are stored in the array. Each item is associated with a unique key. The key is used to retrieve an individual item and is usually an integer or a string, but can be anything except an array.

这是一个怪癖:每次你引用一个 Range对象，它被赋予了不同的内存位置。您可以很容易地验证这一点:

Dim i as Long
Dim r as Range
For i = 1 to 3
    Set r = Range("A1")
    Debug.Print(ObjPtr(r))
Next

在您的情况下，这意味着 Exists方法显然会失败(即，返回 False 当它似乎应该返回 True 时)，例如，扩展上述内容:

Dim i As Long
Dim r As Range
Dim d as Object
Set d = CreateObject("Scripting.Dictionary")
For i = 1 to 3
    Set r = Range("A1")
    d.Add r, i
Next

这将产生一个具有 3 个唯一键的字典，它们都是指向同一个 Range 的指针目的!
如果相反， Set分配在循环之外，它将在第二次迭代中按预期失败，因为它现在正试图添加一个已经存在于字典中的指针。

Set r = Range("A1")
For i = 1 to 3
    d.Add r, i
Next

这个故事的寓意是:在分配 Object 时要小心。类型为 Keys在 Dictionary .
我们可以使用 Object类型为 Dictionary.Keys ?
是的，但它似乎是一个更复杂的实现。可能还有其他方法，但一个明显的(至少对我而言)解决方案是首先将键构建为数组或集合，然后在测试时迭代该列表 Dictionary.Exists .

Sub foo2()

Dim d As Dictionary
Dim r As Range, w As Worksheet
Dim i As Long
Dim keys(1 To 3) As Range
Set d = CreateObject("Scripting.Dictionary")

Set r = Range("A1:A3")
' Create our keys in one place
For i = 1 To r.Cells.Count
    Set keys(i) = r.Cells(i)
Next

' Iterate the KEYS rather than the range
For i = LBound(keys) To UBound(keys)
    d.Add keys(i), i
Next
Debug.Print "The dictionary initially contains " & d.Count & " keys."
'If we test Exist against our keys array, results are as expected:
For i = LBound(keys) To UBound(keys)
    If Not d.Exists(keys(i)) Then
        d.Add r.Cells(i), i
    End If
Next
Debug.Print "The dictionary still contains " & d.Count & " keys."

' Iterate the RANGE now and no error occurs!
For i = 1 To r.Cells.Count
    If Not d.Exists(r.Cells(i)) Then
        d.Add r.Cells(i), i
    End If
Next
' But, our dictionary now has 6 keys, instead of 3!!!
Debug.Print "The dictionary now contains " & d.Count & " keys!"
End Sub