在VBA中对对象集合进行排序 [英] Sorting a collection of objects in VBA

问题描述

我正在尝试编写一个对对象集合进行排序的函数。由于对象都是相同的类型（相同的用户定义的类），因此它们的属性集是相同的。是否有可能（通过代码）发现对象的属性，以便将集合放入一个二维数组中，每一行代表一个对象，每一列代表其属性之一？

I'm trying to write a function that would sort a collection of objects. Since the objects are all of the same type (the same user-defined class), their property set is the same. Is it possible to discover the object's properties (through code) so as to put the collection in a bi-dimensional array, each row being for an object, each column for one of its property?

另一种解决方案是将集合中的每个对象复制到对象数组，然后按其属性之一对它们进行排序，该属性的名称作为字符串传递给函数。但是我看不到如何使用通过字符串传递的属性名称来指向对象的属性。

Another solution would be to copy each object from the collection to an array of objects, and sort them by one of their property, whose name is passed to the function as a string. But I don't see how I can point to the object's property using the property's name passed as a string.

推荐答案

集合，最好按钥匙（这就是它们的用途）对其进行排序-但以防万一您没有钥匙清单（丢失钥匙！）：

For a collection, it is best to sort it by it's Keys (that's what they're there for) -- but in case you don't have the keys list (lost your keys!):

'Give an input "Data As Collection"

    Dim vItm As Variant
    Dim i As Long, j As Long
    Dim vTemp As Variant

    For i = 1 To Data.Count – 1
        For j = i + 1 To Data.Count
            If CompareKeys(Data(i).myMemberKey, Data(j).myMemberKey) Then
                'store the lesser item
                vTemp = Data(j)

                'remove the lesser item
                Data.Remove j

                're-add the lesser item before the greater Item
                Data.Add vTemp, , i
            End If
        Next j
    Next i

Com使用您自己的CompareKey函数，如果UDT成员变量>，<则返回true或false。或0到另一个。您必须删除并重新添加的原因是因为您不能交换 vb6 / vba集合对象中的内部成员。

Come up with your own CompareKey function which will return true or false if the UDT member variables are >, < or 0 to one another. The reason you have to delete and re-add is because you cannot 'swap' internal members in a vb6/vba collection object.

运气最好

编辑：

要以编程方式访问属性（名称为字符串），请使用VB的 CallByName 功能的格式：

To access a property you have the name of programmatically (as a string), use VB's CallByName function in the form:

 Result = CallByName(MyObject, "MyProperty", vbGet)

这篇关于在VBA中对对象集合进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！