VBA get unique values from array explanation

Question

I have found this code but I have hard times understanding it. Could you please explain it in details?

https://stackoverflow.com/questions/3017852/vba-get-unique-values-from-array?answertab=active#=

    Sub unique() 
  Dim arr As New Collection, a 
  Dim aFirstArray() As Variant 
  Dim i As Long 

  aFirstArray() = Array("Banana", "Apple", "Orange", "Tomato", "Apple", _ 
  "Lemon", "Lime", "Lime", "Apple") 

  On Error Resume Next 
  For Each a In aFirstArray 
     arr.Add a, a 
  Next 

  For i = 1 To arr.Count 
     Cells(i, 1) = arr(i) 
  Next 

End Sub

JNevill · Accepted Answer · 2019-04-26T14:23:56.237

Adding comments to each line:

Sub unique()

    'Declare a collection object called `arr`. Despite the name, it's not an array. Also declare the variable `a` as type variant (default)
    Dim arr As New Collection, a

    'Declare an array of type variant, being used here in this example and it will be loaded with fruit names.
    Dim aFirstArray() As Variant

    'Declare a variable called `i` as a long integer type
    Dim i As Long


    'Here's are our example single-dimensional array for which we want to find unique values.
    aFirstArray() = Array("Banana", "Apple", "Orange", "Tomato", "Apple", _
    "Lemon", "Lime", "Lime", "Apple")

    'If we encounter an error adding `aFirstArray` array elements/items into our `arr` collection object then ignore them
    On Error Resume Next

    'Loop through each element(fruit name) in the array `aFirstArray`
    For Each a In aFirstArray

        'Add the item to the collection. The key and the value are both being set to the fruitname which is now in variable `a`
        'If the key (fruit name) already exists, that `On Error Resume Next` will ignore the error that pops.
        arr.Add a, a
    Next

    'Now we have a collection object `arr` that contains unique values from the array held in both the key and value of each item.
    'Iterate from 1 to the however many unique items are in the collection object `arr`
    For i = 1 To arr.Count

        'Print the value (fruitname) out to the workbook
        Cells(i, 1) = arr(i)
    Next

End Sub

The reason that a collection object is being used here is that it works much like an array, but instead of an index holding a value, we can set a key. Keys must be unique, so when we try to add that same key AFTER it's already been set with a value it errors out. What you end up with is a collection object with unique keys (and in this case a matching value) from the array.

You'll see similar versions of this subroutine/function using Dictionary objects as well. I prefer those over collections since a dictionary object has a method exists, so instead of On Error Resume Next which is more of a crutch, you can check if the key already exists in your dictionary before adding If Not myDictionary.Exists(keyvalue) Then myDictionary.Add keyValue, val.

Thank you very much. It was hard to understand those variables and their functions since they were not fully explained before! — Alessio_110, Apr 26 '19 at 15:03

score 0 · Answer 2 · answered Apr 26 '19 at 14:19

aFirstArray() = ... creates an array with value that aren't (necessarily) unique.

The next block of code attempts to add each of these items to a Collection, and uses the On Error Resume Next to ignore the error that would otherwise raise if you attempt to add an already-existing-item to the collection, thus ensuring the arr (Collection) contains only the unique values from the array.

From the dox on the Collection.Add method:

An error also occurs if a specified key duplicates the key for an existing member of the collection.

VBA get unique values from array explanation

2 Answers2