excel - 更改字典中集合中项目的值

Question

我正在尝试创建一个字典，其中包含每个键的集合。这样做的原因是我想稍后从同一个键中检索多个值。在此示例中，我希望获得唯一键的总值 (val) 以及出现次数 (n):

sub update()
Dim dict As Dictionary
Dim coll As Collection
Set dict = New Dictionary
Set coll = New Collection

coll.Add 100, "val"
coll.Add 3, "n"
dict.Add "coll", coll

Debug.Print dict.item("coll")("val")
Debug.Print dict.item("coll")("n")

到目前为止一切正常，当我尝试更新集合中的值时出现问题(对象不支持此操作):

dict.item("coll")("val") = dict.item("coll")("val") + 100

我尝试过的:

If I use an array instead of the collection, there is no error but the value doesn't change. It only works if I read out the collection to variables, change the value, create a new collection, remove the old from the dictionary and add the new collection.

有没有办法像我上面的方法一样在一行中完成？我也很乐意为该任务提供替代解决方案。

Answer 1

一旦将项目添加到集合中，就无法轻易更改它。这样的表达:

coll("n") = 5

将导致运行时错误“424”:需要对象。

您可以通过下面的简单示例自行检查:

Sub testCol()
    Dim col As New VBA.Collection
    Call col.Add(1, "a")

    col("a") = 2  '<-- this line will cause Run-time error '424'

End Sub

---

更改分配给给定集合中指定键的值的唯一方法是删除该值并添加具有相同键的另一个值。

下面是一个简单的例子，如何将分配给键 [a] 的集合的值从 1 更改为 2:

Sub testCol()
    Dim col As New VBA.Collection
    With col
        Call .Add(1, "a")
        Call .Remove("a")
        Call .Add(2, "a")
    End With
End Sub

---

以下是您修改的代码，以允许您更改分配给集合中给定键的值:

Sub update()
    Dim dict As Dictionary
    Dim coll As Collection
    Set dict = New Dictionary
    Set coll = New Collection

    coll.Add 100, "val"
    coll.Add 3, "n"
    dict.Add "coll", coll

    Debug.Print dict.Item("coll")("val")
    Debug.Print dict.Item("coll")("n")
    'This works fine so far, the problem occurs when I try to update the value in the collection (object doesn't support this):

    Dim newValue As Variant
    With dict.Item("coll")
        newValue = .Item("val") + 100
        On Error Resume Next '<---- [On Error Resume Next] to avoid error if there is no such key in this collection yet.
        Call .Remove("val")
        On Error GoTo 0
        Call .Add(newValue, "val")
    End With

End Sub