CODEWITHSUNDEEP
vbaexcel
Erik Rasmussen
Erik Rasmussen

Reputation: 331

VBA Collection.Add Not Working

I have a collection to get the unique values from an array, but for some reason it goes through the array but the collection ultimately shows with

the code for the collection is:

Dim Materials as Variant
Dim Unique as New Collection, a

On Error Resume Next
For Each a In Materials
    Unique.Add a, a
Next

Can anyone offer insight why this wouldn't work?

Upvotes: 1

Views: 3294

Answers (1)

John Coleman
John Coleman

Reputation: 51998

If you really want a collection (since, e.g., you want to keep the items in the order in which they first appear) it is still a good idea to use a dictionary as a temp data structure while building up the collection. Something like:

Function Uniques(A As Variant) As Collection
    Dim D As Object, C As New Collection
    Set D = CreateObject("Scripting.Dictionary")
    Dim v As Variant

    For Each v In A
        If Not D.exists(v) Then
            D.Add v, 1
            C.Add v
        End If
    Next v

    Set Uniques = C
End Function

Tested like:

Sub test()
    Dim Materials As Variant, C As Collection, v As Variant
    Materials = Array(2, 3, 5, 2, 6, 5, 7, 4, 2)
    Set C = Uniques(Materials)
    For Each v In C
        Debug.Print v
    Next v
End Sub

Output:

2 
3 
5 
6 
7 
4

If all you want is a set-like object rather than a collection per-se, you could skip the collection completely and just have Uniques() (suitably modified) return the dictionary itself.

Upvotes: 2

Related Questions