如何优化VBA中System.Collections.ArrayList的过滤算法？

Your current recursive approach gets the job done, but as you’ve correctly identified, it’s inefficient—restarting the loop from the beginning every time you remove an item leads to worst-case O(X²) complexity. Let’s fix this with two straightforward O(X) alternatives that only traverse the list once.

Why Your Original Code Struggles #

Every time you remove an item and restart the scan (via recursion), you’re re-checking all the elements you’ve already verified. For example, if half the list needs removal and those items are clustered at the end, you end up scanning the first half of the list Y times (once per removal)—wasting cycles on redundant checks.

Solution 1: Reverse Index Traversal (Simplest & Most Intuitive) #

Traverse the list from the last element to the first. When you remove an item from the end or middle, the indices of the elements you haven’t checked yet (the ones before it) don’t change—so you never need to restart the loop. We’ll also use RemoveAt instead of Remove to skip the extra step of searching for the item’s position.

Public Sub FilterOptimized(ByVal testValue As Object, ByVal dataSet As ArrayList)
    Dim i As Long
    ' Iterate from the last element down to the first
    For i = dataSet.Count - 1 To 0 Step -1
        Dim item As IComparable
        Set item = dataSet(i)
        If item.CompareTo(testValue) = 0 Then
            dataSet.RemoveAt i ' Directly remove by index (no extra search)
        End If
    Next i
End Sub

Solution 2: Forward Traversal with Index Adjustment #

If you prefer iterating from start to finish, adjust the index manually when you remove an item. When you delete an element, the elements after it shift left—so you need to stay on the current index to check the new element that moved into that position.

Public Sub FilterOptimizedForward(ByVal testValue As Object, ByVal dataSet As ArrayList)
    Dim i As Long
    i = 0
    Do While i < dataSet.Count
        Dim item As IComparable
        Set item = dataSet(i)
        If item.CompareTo(testValue) = 0 Then
            dataSet.RemoveAt i
            ' Don't increment i—next loop checks the element that shifted into this index
        Else
            i = i + 1 ' Move to next element if no removal
        End If
    Loop
End Sub

Performance Impact #

Both methods run in O(X) time, no matter where the matching items are located. Every element is checked exactly once, and each removal only shifts the elements that come after it (total shift operations equal the number of items removed, which is ≤ X). This is a massive improvement over your original approach’s worst-case O(X²) performance, especially for large lists or lists with many matching items.

内容的提问来源于stack exchange，提问作者Greedo