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

Populate VBA Collection with Custom Objects using Collection.Add

提问by Eric

I am trying to add a custom object (Transaction) through the sampleCollection.Addfrom within a For loop.

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

The code works if I add strings to the collection instead of objects.

如果我将字符串而不是对象添加到集合中，则代码有效。

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

The Debug.Print n & " - " & objTrans2.DESCline at the bottom of this code is outputting "Description 2" twice. I want it to output "Description 1" and "Description 2".

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

Here is the information in the Transaction class:

以下是 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

I only added the property declarations to the VB editor in Excel. I copy/pasted what was listed there.

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

采纳答案by Dick Kusleika

You need to make a new instance of objTrans. What you're doing is setting DESC to Description 1, adding to the collection, then changing DESC to Description 2 (not making a new objTrans instance), and adding that same instance to the collection a second time. Here's how I would do it.

您需要创建一个新的 objTrans 实例。您正在做的是将 DESC 设置为 Description 1，添加到集合中，然后将 DESC 更改为 Description 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

回答by Mitch Wheat

A slightly modified form (without the Transaction class) works as intended for me. I believe there is an error is your Transaction class. Can you post the code for it?

稍微修改的表单（没有 Transaction 类）按我的意图工作。我相信有一个错误是你的 Transaction 类。你能贴出它的代码吗？

回答by Joe

The simpler version would've been to set you n=0 to SampleCollection.Count as the indexes of an array starts with 0 and your n is starting with 1...

更简单的版本是将 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

回答by ReturnVoid

Can't populate VBA Collection with Custom Objects using Collection.add

code is outputting "Description 2" twice

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

代码输出“描述 2”两次

In basic simple terms, it is always better to do:

简单来说，最好这样做：

    Dim FooCollection As Collection
    Set FooCollection = New Collection

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

And not:

并不是：

    Dim FooCollection As New Collection
    etc

As the latter will cause very subtle & not so apparent issues, & may not generate any errors, as I recently discovered.

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