excel - Excel VBA - 集合 = 没有什么比创建集合要长 100 倍

Question

抱歉，如果已经发布了类似的内容-我找不到。

我需要有一个类的集合，每个类都包含一个类的集合。预计收藏品将有几十万条。

我发现创建所有集合需要大约 1 秒的时间，但之后需要大约几分钟才能删除它们。

下面是一段随机代码，它产生了我所看到的问题。

这是预期的吗？为什么删除集合比创建集合要长约 100 倍。有什么方法可以加快速度吗？

谢谢

模块1：

Private p_main_collection As Collection

Sub main()

Debug.Print "Starting: " & Now()

    Set p_main_collection = New Collection

    Dim i As Long
    Dim j As Long

    Dim v_class1 As Class1
    Dim v_class2 As Class2

    For i = 1 To 8

        Set v_class1 = New Class1
        p_main_collection.Add v_class1

        v_class1.Num = i

        For j = 1 To 50000

            Set v_class2 = New Class2
            v_class2.Num = j

            v_class1.add_to_collection = v_class2

        Next j

    Next i

Debug.Print "Deleting: " & Now()
    Set p_main_collection = Nothing
Debug.Print "Deleted: " & Now()

End Sub

第一类

Private p_num As Integer
Private p_collection As Collection

Private Sub Class_Initialize()
    Set p_collection = New Collection
End Sub

Public Property Let Num(p_num_in As Integer)
    p_num = p_num_in
End Property

Public Property Get Num() As Integer
    Num = p_num
End Property

Public Property Let add_to_collection(p_collection_in As Class2)
    p_collection.Add p_collection_in
End Property

2 类

Private p_num As Long

Public Property Let Num(p_num_in As Long)
    p_num = p_num_in
End Property

Public Property Get Num() As Long
    Num = p_num
End Property

结果

Starting: 08/01/2019 23:15:01
Deleting: 08/01/2019 23:15:02
Deleted: 08/01/2019 23:16:19

标签： excelvba