excel - VBA-按属性筛选用户定义类的集合/词典的最有效方法

Question

我的问题是关于基于属性字段过滤vba集合或词典。我正在使用VBA处理大量数据提取，并为此目的使用了一系列自定义定义的类对象。一旦定义好它们并将它们填充到集合或字典中，就需要根据各种属性选择这些对象的子集。我的问题是，是否有比简单地遍历和测试条件更有效的方法？

下面是一些基本代码来说明此问题。由于我的工作场所政策，我什至无法上传示例Excel文件，但是数据并没有真正的意义。我的测试文件只是一堆randbetween函数，例如'= choose（randbetween（1,3），“ red”，“ green”，“ blue”）

'Simple Class definition
Option Explicit
'very simple test class
'One field is unique, the other three are simple strings that 
'fall into    groups (I don't always know what the groups will bee)

Private m_uniqueID As String
Private m_strTest1 As String
Private m_strTest2 As String
Private m_strTest3 As String

Public Property Get uniqueID() As String: uniqueID = m_uniqueID: End Property
Public Property Let uniqueID(ByVal NewValue As String): m_uniqueID = NewValue: End Property
Public Property Get strTest1() As String: strTest1 = m_strTest1: End Property
Public Property Let strTest1(ByVal NewValue As String): m_strTest1 = NewValue: End Property
Public Property Get strTest2() As String: strTest2 = m_strTest2: End Property
Public Property Let strTest2(ByVal NewValue As String): m_strTest2 = NewValue: End Property
Public Property Get strTest3() As String: strTest3 = m_strTest3: End Property
Public Property Let strTest3(ByVal NewValue As String): m_strTest3 =   NewValue: End Property

我的基准线过滤方法是：

Public Sub inefficientFilter()
    Dim oTest As cl_Test
    Dim colTest As Collection
    'assume it's populated

    Dim colMatches As Collection
    Set colMatches = New Collection

    For Each oTest In colTest
        If oTest.strTest1 = "Green" Then
            colMatches.Add Item:=oTest, Key:=oTest.uniqueID
        End If
    Next oTest
End Sub

这很好用，只是执行时间增长得非常快（现在，100,000行达到17秒）。我已经尝试了一段时间寻找这种方法，并且找到了很多用于过滤源表的参考。但是，这对于我的数据集是不切实际的，因为在读入数据后会对数据进行大量处理，并且我需要过滤的一些属性未在输入中定义。而且，我需要根据许多不同的属性对它进行过滤，其中一些我事先不会知道（我的意思是我知道一个字段将包含类别，但是直到这些类别是什么我才知道数据将被处理，并且它们可能随下一个数据集而改变）。

如果没有一种过滤字典或集合的方法比循环更有效，那么我比计划创建一个大型过滤器函数为每个分类字段创建一个集合的方法要多，所以我至少可以避免每次循环我需要应用过滤器并一次性处理所有问题。或者，可以将哈希表的内容写到单个excel工作表中，然后使用adodb.recordset查询来查找匹配项（我还没有进行足够的测试来知道哪个开销较小）。但是，在我去那里之前，我想过要问我是否缺少明显的东西。

谢谢！

加12/15

Mat Mug的第一条评论提到对字典的keys数组进行迭代，并建议使用for ... next循环代替。因此，我去修改了我的代码以测试不同迭代方法的时间。我认为我应该分享结果。我测试了7种方法，下面还介绍了Tim William的答案。我认为可以在不完全详细说明代码的情况下进行总结，因为这样做很简单。如果输入错误，则可以轻松添加。我在10,000个项目上运行该程序（因为如果我转至300k，有两种方法会导致我的计算机自杀）。因此，这里是结果，其中包含完成循环的时间（以秒为单位）（每个循环迭代集合或字典，然后测试给定条件的每个项目，如果匹配，则将该项目添加到结果中采集）：


0.00578对于每个Loop，遍历集合（对于col中的每个o）
0.20099对于Next Loop，使用计数器作为集合的索引，并且
然后通过SET obj = col（i）检索项目
0.27605对于下一个循环，与2相同，但跳过SET。
所以测试条件是col（i）.strtest1 =“ Green”然后...）
0.01275现在是字典对于dict.keys中的每个键，SET obj = dict（key）
0.02605对于dict中的每个键
不包括SET，例如3
测试条件为dict（key）.strtest1 =“ Green”，然后...）
3.68905对于带有索引的Next，对于i = 1到dict.count，设置o = dict（i）
4.16361与6相同，但没有SET
dict.items（i）.strTest1 =“绿色”然后...
0.02192还有下面的蒂姆·威廉姆斯的答案


因此，从中学到，我永远不会遍历带有索引的字典。
而且，直接使用对象（使用SET）处理VBA时，比通过引用集合或字典进行访问要快得多。
最快的方法是一个简单的FOR EACH obj IN Collection，下一个obj循环。简单地遍历字典（FOR EACH键输入dict.keys，SET obj = dict（key），NEXT键）花费的时间是原来的两倍（这是有意义的，因为每个循环都需要执行SET函数） 。尽管此负担在每个循环中是固定的，但是如果您在循环中执行多个操作（测试多个条件），则此意义将变得不那么重要。威廉先生的方法可与每种关键方法相比。

好的，假设我只是重新运行了测试，以匹配函数（模拟一种情况，我不仅要过滤，还要处理过滤的选择）。因此，如果我的标头失败，则应将其读取为“方法编号”，“方法完成1个匹配操作所花费的时间”，“每个方法比最快的方法进行1个匹配所花费的时间长”，“方法完成50个匹配所花费的时间”的系数运营，这是比基线更长的时间的因素。

方法__1x（s）因子（1x）__ 50x（s）_____ Factor（50x）
每个集合1 _______ 0.006 ____ 1 _________ 0.159 _______ 1__循环
2 _______ 0.201 ___ 35 _________ 0.336 _______ 2__用于下一个索引
3 _______ 0.276 ___ 48 ________ 19.165 _____ 120＃2跳过设置
字典中的每个键4 _______ 0.013 ____ 2 _________ 0.159 _______ 1__
5 _______ 0.026 ____ 5 _________ 5.560 ______ 35 __＃4跳过SET
6 _______ 3.689__369 ________ 3.851 ____________ 24下一个带索引的字典
7 _______ 4.164__721 _______ 211.929 ____ 1333 __＃6跳过设置
8 _______ 0.022 ____ 4 _________ 0.144 _______ 1__先生。威廉的答案

因此，这加强了上面的答案形式。随着复杂度的增加，方程式上的for-each循环或dict.keys中的每个键，设置obj = dict（key）以及William先生的答案都同样有效。使用索引的影响随着访问属性的次数而减少，但效率不及每种方法。最后，当您直接访问类对象时，与通过父级集合/词典进行引用访问相比，VBA的效率要高得多。也许这对除了我以外的所有人来说都是显而易见的，因为我没有编程背景并且正在学习中，但是对我的直觉和经验法则进行一些量化很好。

我意识到此时我正在模糊3个不同的问题。最快的过滤方法，最快的迭代方法以及访问集合或字典中对象属性的最快方法。抱歉，如果距离太远，我只是想分享我从阅读您的答案中学到的知识。

Answer 1

使用示例类对30万个对象进行了测试。

编辑：更新了一点过滤的灵活性。

Dim data As Object

Sub Tester()

    Dim colF As Collection
    Dim arr, o As Class1, n As Long, t, k, o2 As Variant

    arr = Array("Red", "Green", "Blue")
    Set data = CreateObject("scripting.dictionary")

    'load up some test data
    t = Timer
    For n = 1 To 300000#
        Set o = New Class1
        o.uniqueID = "ID" & Format(n, "000000000")
        o.strTest1 = arr(Int((2 - 0 + 1) * Rnd + 0))
        o.strTest2 = arr(Int((2 - 0 + 1) * Rnd + 0))
        o.strTest3 = arr(Int((2 - 0 + 1) * Rnd + 0))
        data.Add o.uniqueID, o
    Next n
    Debug.Print "Loaded", Timer - t

    'do some filtering
    t = Timer
    Debug.Print "filtered", Filtered("strTest1", "Red").Count, Timer - t
    t = Timer
    Debug.Print "filtered", Filtered("strTest2", "Green").Count, Timer - t
    t = Timer
    Debug.Print "filtered", Filtered("strTest3", "Blue").Count, Timer - t

End Sub

'generic filtering on named property+value
Function Filtered(propName As String, propValue As String) As Collection
    Dim rv As New Collection, o As Variant
    For Each o In data.items
        If CallByName(o, propName, VbGet) = propValue Then rv.Add o.uniqueID
    Next o
    Set Filtered = rv
End Function

输出：

Loaded                       6.601563 
filtered       100006        0.7109375 
filtered       99936         0.828125 
filtered       100144        0.9609375 

创建对象是最慢的部分：过滤非常快。

如果您的真实类只是字段的集合，那么使用自定义Type而不是类可能会获得更好的性能。无论哪种方式，如果您仍然遇到问题，最好将问题更新为包含您需要快速处理的事物类型的完整示例。