Alan,

hmm.. nice discussion :)

What about :

not using the collection, just the dictionary.
using the dictionary comparemode for casesensitivity.

The speed gained by transferring a dictionary BACK to
the array (compared to redim/iterate for collection)
is probably compensating for the performance loss of
creating the non native dictionary.

I haven't done any performance testing.. but I plan to
test following:

Collection vs Dictionary.
Dictionary Early/Late binding.
Error Resume vs oDict.Exists


I'll let you know the findings.




Function MakeUnique(vIn, Optional CaseSensitive As Boolean, _
Optional Sorted As Integer)
'Sorted :0 No Sort,-1 Ascending, 1 Descending
Dim vOut, vItm, i&, j&, n&, oDict As Object

On Error Resume Next
vItm = UBound(vIn, 1)
If Err < 0 Then
vOut = CVErr(xlErrValue)
GoTo TheEnd
End If

Set oDict = CreateObject("scripting.dictionary")
oDict.comparemode = Abs(Not CaseSensitive)

For Each vItm In vIn
'skip empty or nullstring
If Len(vItm) Then oDict.Add CStr(vItm), vItm
Next
On Error GoTo 0
n = oDict.Count
If VarType(vIn(LBound(vIn))) = vbString Then
'String comparison
vOut = oDict.Keys
If Sorted = 0 Then GoTo TheEnd
Sorted = Sorted \ Abs(Sorted)
For i = 0 To n - 2
For j = i To n - 1
'Note on sorting..aBcAbC goes in..
'0 = binary abcABC
'1 = text aABbcC
If StrComp(vOut(i), vOut(j), vbTextCompare) = Sorted Then
vItm = vOut(i): vOut(i) = vOut(j): vOut(j) = vItm
End If
Next
Next
Else
'numeric comparison
vOut = oDict.Items
If Sorted 0 Then
For i = 0 To n - 2
For j = i To n - 1
If vOut(i) - vOut(j) < 0 Then
vItm = vOut(i): vOut(i) = vOut(j): vOut(j) = vItm
End If
Next
Next
ElseIf Sorted < 0 Then
For i = 0 To n - 2
For j = i To n - 1
If vOut(i) - vOut(j) 0 Then
vItm = vOut(i): vOut(i) = vOut(j): vOut(j) = vItm
End If
Next
Next
End If
End If

TheEnd:
MakeUnique = vOut

End Function



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


Alan Beban wrote:

The following procedure works correctly to load arr2 with red, blue,
Blue, brown if CaseSensitive is True; and to load it with red, blue,
brown if CaseSensitive is False. When I attempt to declare the
variable
x like so (which is commented out in the procedure)

If CaseSensitive Dim x As Dictionary Else Dim x As Collection

it throws a compile error--Duplicate declaration in current scope.

How can the variable x be appropriately declared?

Sub abc()
'This procedure requires project reference to
'the "Microsoft Scripting Runtime"

Dim arr, arr2, i As Long
'If CaseSensitive Dim x As Dictionary Else Dim x As Collection

CaseSensitive = True
'CaseSensitive = False

arr = Array("red", "blue", "Blue", "red", "brown")

If CaseSensitive Then Set x = New Dictionary Else Set x = New
Collection

On Error Resume Next
For Each Elem In arr
x.Add Item:=Elem, key:=CStr(Elem)
Next
On Error GoTo 0

ReDim arr2(1 To x.Count)
i = 1
For Each Elem In x
arr2(i) = Elem
i = i + 1
Next
End Sub

Thanks,
Alan Beban