vba - 将 Variant 设置为集合项总是会返回错误

Question

语境

我有一个自定义函数，其范围是输入一定范围的值(即可能重复的字符串列表，例如 "EUR/USD" 、 "USD/BRL" 、 "EUR/USD" 并输出类似 SQL 的字符串，如下所示:

not in ('EUR/USD', 'USD/BRL' ... )

由于电子表格中的值可能重复，在进行任何解析之前，我试图创建一个唯一值的集合(例如，如果 EUR/USD 在电子表格中出现 4 次，我只希望它出现在我的集合中一次)。

为了达到这个目的，我开发了这个简单的功能:

Public Function NOT_IN_SQL(ByVal values As Range) As String

    'parse list (get uniques only)
    Dim uniquesCollection As New Collection
    Dim rng As Range

    For Each rng In values

        If Not inCollection(uniquesCollection, rng.Value) Then
            uniquesCollection.Add rng.Value
        End If

    Next rng

    NOT_IN_SQL = "tmp crap value"

End Function

如您所见， If决定是否将某个值添加到集合中是否正在使用另一个自定义函数 inCollection ，定义如下:

Private Function inCollection(ByVal coll As Collection, ByVal element As String) As Boolean

    Dim var As Variant

    On Error Resume Next
        var = coll.Item(element)
        If Err.Number <> 0 Then
            inCollection = False
        Else
            inCollection = True
        End If
        Set var = Nothing
    On Error GoTo 0

End Function 

这个函数基本上是试图设置对象 var (声明为 Variant )为 collection.Item()与 index 当前分析范围的值。

如果出现错误，则表示在集合中未找到该元素，因此我返回 inCollection = False ，否则我返回 True [我知道这不是一个很好的实现方式，但它是我在 VBA 中能想到的最好的 try-catch 方法]。

问题

但是，我的第二个函数的代码似乎有问题。
事实上，不管元素是否在集合中，声明:

var = coll.Item(element)

总是会引发异常，所以它永远不会告诉我元素是否在集合中。

测试/调试案例

1)打开一个Excel电子表格，添加一个模块并复制/粘贴以上两个函数

2) 在任何电子表格的 A1 和 A2 范围内，键入值 test和 test (相同的值)。

3) 在 A3 范围内插入 =NOT_IN_SQL(A1:A2)在 Debug模式下添加一个观察者到对象 uniquesCollection看看它在函数结束时的样子。你会看到它是 ("test","test")即使我只期望 ("test") .

请注意:我已经进行了第一次分析，如上所述，问题在于 var = coll.Item(element)总是引发异常(所以 inCollection 总是返回 False )，但我不明白为什么。

谁能帮我理解我做错了什么？

Answer 1

在函数 NOT_IN_SQL将项目添加到集合时需要使用 key 。

Dim uniquesCollection As Collection
Dim rng As Range
Dim value

Set uniquesCollection = New Collection

For Each rng In values
    If Not IsError(rng.value) Then
        value = Trim(rng.value)
        If value <> "" Then
            If Not inCollection(uniquesCollection, value) Then
                uniquesCollection.Add value, value
            End If
        End If
    End If
Next rng

见 Add方法文档。