NickHK wrote:
You can do a similar thing with a Collection and then you don't need the
Scripting reference.
Could you post the comparable code for a Collection approach?
Or sort your array first, then step through the elements copy distinct
elements to a new array.
Why is it necessary to sort the array first?
Alan
NickHK
"Lazzaroni" wrote in message
...
I was looking for a simple way to remove duplicates from an array;
something
that was equivalent to running a SELECT DISTINCT. I didn't find exactly
what
I was looking for but I found something close and modified it. Hopefully
someone else will find this useful, or perhaps even improve upon it.
Function DistinctArray(oInputArray, _
Optional MatchCase As Boolean = True, _
Optional OmitBlanks As Boolean = True)
'declare the variables
Dim oOutputArray As Variant
Dim oElement As Variant
Dim oDictionary As Dictionary
'create new dictionary object
'requires Microsoft Scripting Runtime reference
Set oDictionary = New Dictionary
'set case sensitivity
oDictionary.CompareMode = Abs(Not MatchCase)
'load elements from array into dictionary replacing duplicates
For Each oElement In oInputArray
oDictionary.Item(CStr(oElement)) = oElement
Next
'delete any blanks
If OmitBlanks Then
If oDictionary.Exists("") Then oDictionary.Remove ("")
End If
'load dictionary items into new array
oOutputArray = oDictionary.Items
'return the new array with distinct values only
DistinctArray = oOutputArray
End Function
The original function can be found he
http://home.pacbell.net/beban/