使用 Collection.Add 用自定义对象填充 VBA 集合

Question

我正在尝试通过 For 循环中的 sampleCollection.Add 添加自定义对象（事务）。

如果我将字符串而不是对象添加到集合中，该代码将起作用。

Public Function PopCollection()
    Dim sampleCollection As New Collection
    Dim objTrans As New Transaction
    Dim objTrans2 As New Transaction


    '********** SETUP ARRAY FOR LOOP *************
    Dim arrA(0 To 1) As String
    arrA(0) = "Description 1"
    arrA(1) = "Description 2"


    '********** POPULATE COLLECTION *************
    For n = 0 To 1
        objTrans.DESC = arrA(n)
        Call sampleCollection.Add(objTrans)
    Next n


    '********** ITERATE THROUGH COLLECTION *************
    For n = 1 To sampleCollection.Count
        Set objTrans2 = sampleCollection.Item(n)
        Debug.Print n & " - " & objTrans2.DESC
    Next n

End Function

Debug.Print n & “-”&此代码底部的 objTrans2.DESC 行输出“Description 2”两次。我希望它输出“描述 1”和“描述 2”。

以下是 Transaction 类中的信息：

Public PTXN As Integer
Public ACCTID As Integer
Public CHECKNUM As String
Public DESC As String
Public STATUS As String
Public TRANSACTIONDATE As String
Public SPLIT_DESC As String
Public SPLIT_AMT As Single
Public SPLIT_CATEGORY As Integer

我仅将属性声明添加到 Excel 中的 VB 编辑器中。我复制/粘贴了那里列出的内容。

Answer 1

您需要创建 objTrans 的新实例。您要做的是将 DESC 设置为“描述 1”，添加到集合中，然后将 DESC 更改为“描述 2”（不创建新的 objTrans 实例），然后再次将同一实例添加到集合中。我就是这样做的。

Public Function PopCollection()

    Dim sampleCollection As Collection
    Dim objTrans As Transaction
    Dim arrA As Variant
    Dim n As Long

    arrA = Array("Description 1", "Description 2")
    Set sampleCollection = New Collection

    For n = LBound(arrA) To UBound(arrA)
        Set objTrans = New Transaction
        objTrans.DESC = arrA(n)
        sampleCollection.Add objTrans
    Next n

    For n = 1 To sampleCollection.Count
        Set objTrans = sampleCollection.Item(n)
        Debug.Print n & " - " & objTrans.DESC
    Next n

End Function

Answer 2

稍微修改过的表单（没有 Transaction 类）可以按我的预期工作。我相信您的 Transaction 类有一个错误。你能发布它的代码吗？

Answer 3

更简单的版本是将 n=0 设置为 SampleCollection.Count，因为数组的索引以 0 开头，而 n 以 1 开头...

For n = 0 To sampleCollection.Count
    Set objTrans = sampleCollection.Item(n)
    Debug.Print n & " - " & objTrans.DESC
Next n

Answer 4

无法使用 Collection.add 用自定义对象填充 VBA 集合

代码输出“Description 2”两次

用基本的简单术语来说，这样做总是更好：

    Dim FooCollection As Collection
    Set FooCollection = New Collection
    
    Dim FooClass As classFoo
    Set FooClass = New classFoo
    FooCollection.Add FooClass

而不是：

    Dim FooCollection As New Collection
    etc

因为后者会导致非常微妙的问题。不那么明显的问题，&正如我最近发现的那样，可能不会产生任何错误。