How can I get a unique set of values?

Question

Given some sort of collection of values (an array or a collection of some kind), how can I generate a set of distinct values?

possible duplicate of [Extracting the collection of unique values from a filter in VBA](http://stackoverflow.com/questions/31891059/extracting-the-collection-of-unique-values-from-a-filter-in-vba) — , Sep 16 '15 at 21:05
@Jeeped This is intended to show the general technique, without mixing in Excel- or other host-specific code. — Zev Spitz, Sep 16 '15 at 21:13
Yes, I get *exactly* what you are trying to accomplish. It is just that there are so-so many Scripting.Dictionary examples already available I question whether we need a non-Community-Wiki self-answered post. Perhaps you should should sign-up for the new [Warlords of Documentation](http://meta.stackoverflow.com/questions/306213/warlords-of-documentation-your-questions-answered?cb=1) endeavour. I have. — , Sep 16 '15 at 21:18
@Jeeped There may be a lot of Dictionary examples already available, but very few showing this specific technique, and it is always buried together with host-specific code. (And yes, I have already signed up for the SO Documentation beta.) — Zev Spitz, Sep 17 '15 at 06:55

score 2 · Accepted Answer · edited May 23 '17 at 10:29

Use a Scripting.Dictionary (Tools -> References... -> Microsoft Scripting Runtime):

Function Unique(values As Variant) As Variant()
    'Put all the values as keys into a dictionary
    Dim dict As New Dictionary
    Dim val As Variant
    For Each val In values
        dict(val) = 1
    Next
    Unique = dict.Keys 'This cannot be done with a Collection, which doesn't expose its keys
End Function

In VBScript, or in VBA if you prefer using late binding (variables without explicit types):

Function Unique(values)
    Dim dict, val
    Set dict = CreateObject("Scripting.Dictionary")
    For Each val In values
    ...

If running VBA on a Mac (which doesn't have the Microsoft Scripting Runtime), there is a drop-in replacement for Dictionary available.

Some examples:

Another option (VBA only) is to use a Collection. It's a little more awkward, because there is no way to set an existing key without an error being thrown, and because the returned array has to be created manually:

Function Unique(values As Variant) As Variant()
    Dim col As New Collection, val As Variant, i As Integer
    For Each val In values
        TryAdd col, val, val
    Next
    Dim ret() As Variant
    Redim ret(col.Count - 1)
    For i = 0 To col.Count-1
        ret(i) = col(i+1)
    Next
    Unique = ret
End Function

Sub TryAdd(col As Collection, item As Variant, key As String)
    On Error Resume Next
    col.Add(item, key)
End Sub

How can I get a unique set of values?

1 Answers1

Linked