vba: get unique values from array

Question

is there built in functionality in vba to get unique values from a one-dimensional array? what about just getting rid of duplicates?

if not, then how would i get the unique values from an array?

Answer 1

I don't know of any built-in functionality in VBA. The best would be to use a collection using the value as key and only add to it if a value doesn't exist.

Answer 2

There's no built-in functionality to remove duplicates from arrays. Raj's answer seems elegant, but I prefer to use dictionaries.

Dim d As Object
Set d = CreateObject("Scripting.Dictionary")
'Set d = New Scripting.Dictionary

Dim i As Long
For i = LBound(myArray) To UBound(myArray)
    d(myArray(i)) = 1
Next i

Dim v As Variant
For Each v In d.Keys()
    'd.Keys() is a Variant array of the unique values in myArray.
    'v will iterate through each of them.
Next v

EDIT: I changed the loop to use LBound and UBound as per Tomalak's suggested answer. EDIT: d.Keys() is a Variant array, not a Collection.

Answer 3

No, VBA does not have this functionality. You can use the technique of adding each item to a collection using the item as the key. Since a collection does not allow duplicate keys, the result is distinct values that you can copy to an array, if needed.

You may also want something more robust. See Distinct Values Function at http://www.cpearson.com/excel/distinctvalues.aspx

Distinct Values Function

A VBA Function that will return an array of the distinct values in a range or array of input values.

Excel has some manual methods, such as Advanced Filter, for getting a list of distinct items from an input range. The drawback of using such methods is that you must manually refresh the results when the input data changes. Moreover, these methods work only with ranges, not arrays of values, and, not being functions, cannot be called from worksheet cells or incorporated into array formulas. This page describes a VBA function called DistinctValues that accepts as input either a range or an array of data and returns as its result an array containing the distinct items from the input list. That is, the elements with all duplicates removed. The order of the input elements is preserved. The order of the elements in the output array is the same as the order in the input values. The function can be called from an array entered range on a worksheet (see this page for information about array formulas), or from in an array formula in a single worksheet cell, or from another VB function.

Answer 4

No, nothing built-in. Do it yourself:

• Instantiate a Scripting.Dictionary object
• Write a For loop over your array (be sure to use LBound() and UBound() instead of looping from 0 to x!)
• On each iteration, check Exists() on the dictionary. Add every array value (that doesn't already exist) as a key to the dictionary (use CStr() since keys must be strings as I've just learned, keys can be of any type in a Scripting.Dictionary), also store the array value itself into the dictionary.
• When done, use Keys() (or Items()) to return all values of the dictionary as a new, now unique array.
• In my tests, the Dictionary keeps original order of all added values, so the output will be ordered like the input was. I'm not sure if this is documented and reliable behavior, though.

Answer 5

This post contains 2 examples. I like the 2nd one:

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