Excel VBA：将集合从类传递到模块问题 [英] Excel VBA: Passing a collection from a class to a module issue

问题描述

我一直试图将集合从一个类中的属性返回到正常模块中的例程。我遇到的问题是，该集合正在类（FetchAll）的属性中正确填充，但是当我将集合传回模块（测试）时，所有条目都填充列表中的最后一个项目。 p>

这是标准模块中的测试子例程：

  Sub Test（）
 Dim QueryType As New QueryType 
 Dim Item 
 Dim QueryTypes As Collections 
 Set QueryTypes = QueryType.FetchAll 
 
对于QueryTypes中的每个项目
 Debug.Print Item.QueryTypeID，_ 
 Left（Item.Description，4）
下一项
 End Sub 
  

这是QueryType类中的FetchAll属性：

 获取FetchAll（）作为集合
 
 Dim RS As Variant 
 Dim Row As Long 
 
 Dim QTypeList作为集合
设置QTypeList =新集合
 
 RS = .Run'从数据库（作为数组）填充RS记录集，
'删除一些代码
 
'遍历数组并设置对象对于每个条目
对于Row = LBound（RS，2）到UBound（RS，2）
 Dim QType As New QueryType 
 With QType 
 .QueryTypeID = RS ）
 .Description = RS（1，Row）
 .Priority = RS（2，Row）
 .QueryGroupID = RS（3，Row）
 .ActiveIND = RS ，Row）
 End With 
 
'向集合添加新的QType 
 QTypeList.Add Item：= QType，Key：= CStr（RS（0，Row））
 
 Debug.Print QTypeList.Item（QTypeList.Count）.QueryTypeID，_ 
 Left（QTypeList.Item（QTypeList.Count）.Description，4）
下一行
 
设置FetchAll = QTypeList 
 
结束属性
  

我从FetchAll的调试中获得：

  1 Numb 
 2 PBM 
 3 BPM 
 4 Bran 
 5 claw 
 6 FA C 
 7 HNW 
 8 HNW 
 9 IFA 
 10 Manu 
 11新
 12非
 13 Numb 
 14 Repo 
 15卖
 16 Sms 
 17短信
 18 SWPM 
  

这是我在测试中从调试中获得的输出：

  18 SWPM 
 18 SWPM 
 18 SWPM 
 18 SWPM 
 18 SWPM 
 18 SWPM 
 18 SWPM 
 18 SWPM 
 18 SWPM 
 18 SWPM 
 18 SWPM 
 18 SWPM 
 18 SWPM 
 18 SWPM 
 18 SWPM 
 18 SWPM 
 18 SWPM 
 18 SWPM 
  

任何人有任何想法？我可能完全忽视的东西！

感谢您，
Martin

解决方案

QueryType：

  Dim QType As New QueryType 
  

应为：

  Dim QType As QueryType 
 Set QType = New QueryType 
  

如果你不这样做，你将重用 QueryType的同一个实例（因为没有 Set ），因此将相同的引用添加到集合中，使每个项目引用您的类的单个实例。 （您添加的最后一个）

I have been trying to return a collection from a property within a class to a routine in a normal module. The issue I am experiencing is that the collection is getting populated correctly within the property in the class (FetchAll) but when I pass the collection back to the module (Test) all the entries are populated with the last item in the list.

This is the Test sub-routine in the standard module:

Sub Test()
    Dim QueryType As New QueryType
    Dim Item
    Dim QueryTypes As Collection
    Set QueryTypes = QueryType.FetchAll

    For Each Item In QueryTypes 
        Debug.Print Item.QueryTypeID, _
                    Left(Item.Description, 4)
    Next Item
End Sub

This is the FetchAll property in the QueryType class:

Public Property Get FetchAll() As Collection

    Dim RS As Variant
    Dim Row As Long

    Dim QTypeList As Collection
    Set QTypeList = New Collection

    RS = .Run ' populates RS with a record set from a database (as an array),
                      ' some code removed

    ' goes through the array and sets up objects for each entry
    For Row = LBound(RS, 2) To UBound(RS, 2)
        Dim QType As New QueryType
        With QType
            .QueryTypeID = RS(0, Row)
            .Description = RS(1, Row)
            .Priority = RS(2, Row)
            .QueryGroupID = RS(3, Row)
            .ActiveIND = RS(4, Row)
        End With

        ' adds new QType to collection                
        QTypeList.Add Item:=QType, Key:=CStr(RS(0, Row))

        Debug.Print QTypeList.Item(QTypeList.Count).QueryTypeID, _
                    Left(QTypeList.Item(QTypeList.Count).Description, 4)
    Next Row

    Set FetchAll = QTypeList

End Property

This is the output I get from the debug in FetchAll:

1 Numb
2 PBM 
3 BPM 
4 Bran
5 Claw
6 FA C
7 HNW 
8 HNW 
9 IFA 
10 Manu
11 New 
12 Non 
13 Numb
14 Repo
15 Sell
16 Sms 
17 SMS 
18 SWPM

This is the output I get from the debug in Test:

18 SWPM
18 SWPM
18 SWPM
18 SWPM
18 SWPM
18 SWPM
18 SWPM
18 SWPM
18 SWPM
18 SWPM
18 SWPM
18 SWPM
18 SWPM
18 SWPM
18 SWPM
18 SWPM
18 SWPM
18 SWPM

Anyone got any ideas? I am probably totally overlooking something!

Thanks, Martin

解决方案

Your creation of QueryType:

Dim QType As New QueryType

Should be:

Dim QType As QueryType
Set QType = New QueryType

If you don't do this you are reusing the same instance of QueryType (as there is no Set) so the same reference is being added to the collection, making each item reference a single instance of your class. (The last one you added)

这篇关于Excel VBA：将集合从类传递到模块问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！