There may be multiple ways to get unique elements from an array;
I found using dictionary object to do the task is simple and the best. Let me
first talk about dictionary objects.
A Dictionary object is a kind of array with items and keys.
Items can be any form of data, and are stored in the array. Each item is associated
with a unique key. The key is used to retrieve an individual item and is
usually an integer or a string, but can be anything except an array.
I would be using dictionary object’s “CompareMode” property to get the unique elements from the array.
Below code explains how comparemode works and how it will be useful in removing
duplicate items.
Dim d
Set d =
CreateObject("Scripting.Dictionary")
d.CompareMode
= vbTextCompare
d.Add
"a", "Athens" ' Add some keys and items.
d.Add
"b", "Belgrade"
d.Add
"c", "Cairo"
d.Add
"B", "Baltimore" '
Add method fails on this line because the
' letter b already exists
in the Dictionary.
This means
if Comparemode is set to text compare, duplicate items cannot be added to
dictionary objects. Same logic we will be using in our function. We will keep
on adding the array items to dictionary objects. As CompareMode is set to vbTextCompare,
duplicate items will not be added. Hence at last we will get a dictionary
objects with unique/No duplicate items. We can then assign the dictionary
object items to an array.
Public
Function Remove_Duplicate_Items_From_Array(arrayName)
'Create a dictionary object
Set objDict =
CreateObject("Scripting.Dictionary")
objDict.CompareMode =
vbTextCompare
'iterate through the values
For each val in arrayName
objDict(val) =
val
Next
newArray = objDict.Items ‘Assign the itesm to array
Remove_Duplicate_Items_From_Array=newArray
Set objDict=Nothing ' Release object
End
Function
