I have set up an array that holds string values from excel, (option
base 1).

Is there a function that will delete all the duplicate values in the
array and just leave the unique ones, i have many such string array
that i need to do the same thing for.

My code is like this

term() as string (declare array)

Redim term(noItems) (re dim array with the number of items)

Import data to the term array

Call Unique(term) (pass the term array to the unique function and get
back the term array with just the unique items only)

Can any body help

Cheers

Tony

Private Sub Unique(ByRef term As Variant)
Dim tmp As Variant
Dim i As Long
Dim j As Long

ReDim tmp(LBound(term) To UBound(term))
j = LBound(term)
For i = LBound(term) To UBound(term)
If IsError(Application.Match(term(i), tmp, 0)) Then
tmp(j) = term(i)
j = j + 1
End If
Next i
ReDim Preserve tmp(LBound(tmp) To j - 1)
term = tmp

End Sub


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



wrote in message
...
I have set up an array that holds string values from excel, (option
base 1).

Is there a function that will delete all the duplicate values in the
array and just leave the unique ones, i have many such string array
that i need to do the same thing for.

My code is like this

term() as string (declare array)

Redim term(noItems) (re dim array with the number of items)

Import data to the term array

Call Unique(term) (pass the term array to the unique function and get
back the term array with just the unique items only)

Can any body help

Cheers

Tony

Bob Phillips wrote:
Private Sub Unique(ByRef term As Variant)
Dim tmp As Variant
Dim i As Long
Dim j As Long

ReDim tmp(LBound(term) To UBound(term))
j = LBound(term)
For i = LBound(term) To UBound(term)
If IsError(Application.Match(term(i), tmp, 0)) Then
tmp(j) = term(i)
j = j + 1
End If
Next i
ReDim Preserve tmp(LBound(tmp) To j - 1)
term = tmp '<----error message:Variable uses an Automation type not supported in Visual Basic

End Sub


The Op said his array is of type String(). The following doesn't work:

Sub test12()
Dim term() As String
ReDim term(0 To 3)
term(0) = "a"
term(1) = "b"
term(2) = "c"
term(3) = "c"
Unique term
End Sub

Would something like this work?

Sub TestIt()
Dim V
V = Array("a", "b", "c", "b", "d", "c", "e")
V = Unique(V)
End Sub

Function Unique(V) As Variant
Dim d As Object
Dim Obj

Set d = CreateObject("Scripting.Dictionary")
On Error Resume Next

For Each Obj In V
d.Add Obj, 1
Next Obj
Unique = d.Keys
End Function

--
HTH
Dana DeLouis



"Alan Beban" wrote in message
...
Bob Phillips wrote:
Private Sub Unique(ByRef term As Variant)
Dim tmp As Variant
Dim i As Long
Dim j As Long

ReDim tmp(LBound(term) To UBound(term))
j = LBound(term)
For i = LBound(term) To UBound(term)
If IsError(Application.Match(term(i), tmp, 0)) Then
tmp(j) = term(i)
j = j + 1
End If
Next i
ReDim Preserve tmp(LBound(tmp) To j - 1)
term = tmp '<----error message:Variable uses an Automation type not
supported in Visual Basic

End Sub

The Op said his array is of type String(). The following doesn't work:

Sub test12()
Dim term() As String
ReDim term(0 To 3)
term(0) = "a"
term(1) = "b"
term(2) = "c"
term(3) = "c"
Unique term
End Sub

Dana DeLouis wrote:
Would something like this work?

Sub TestIt()
Dim V
V = Array("a", "b", "c", "b", "d", "c", "e")
V = Unique(V)
End Sub

Function Unique(V) As Variant
Dim d As Object
Dim Obj

Set d = CreateObject("Scripting.Dictionary")
On Error Resume Next

For Each Obj In V
d.Add Obj, 1
Next Obj
Unique = d.Keys
End Function

Not if V is of type String() as stated by the OP. E.g., the following
doesn't work:

Sub TestIt()
Dim V() As String
ReDim V(1 To 7)
V(1) = "a"
V(2) = "b"
V(3) = "c"
V(4) = "b"
V(5) = "d"
V(6) = "c"
V(7) = "e"
V = Unique(V) '<---Type mismatch error message
End Sub

Not if V is of type String() as stated by the OP.

Ok. How about plan B ?

Function Unique(V) As Variant
Dim d As Object
Dim Obj

Set d = CreateObject("Scripting.Dictionary")
On Error Resume Next

For Each Obj In V
d.Add Obj, 1
Next Obj

If TypeName(V) = "String()" Then
Dim s() As String
Dim J As Long

ReDim s(1 To d.Count)
J = 1

For Each Obj In d.keys
s(J) = Obj
J = J + 1
Next Obj
Unique = s
Else
Unique = d.keys
End If
End Function

- - - - - -
HTH
Dana DeLouis


Not if V is of type String() as stated by the OP. E.g., the following
doesn't work:

Sub TestIt()
Dim V() As String
ReDim V(1 To 7)
V(1) = "a"
V(2) = "b"
V(3) = "c"
V(4) = "b"
V(5) = "d"
V(6) = "c"
V(7) = "e"
V = Unique(V) '<---Type mismatch error message
End Sub

If your array will **always** be a String array, this subroutine should do
what you want...

Sub RemoveDuplicates(ArrayIn() As String)
Dim X As Long
Dim Index As Long
Dim Combined As String
Dim TempArray() As String
TempArray = ArrayIn
Index = LBound(ArrayIn)
For X = LBound(ArrayIn) To UBound(ArrayIn)
If InStr(Chr$(1) & Combined & Chr$(1), Chr$(1) & _
ArrayIn(X) & Chr$(1)) = 0 Then
TempArray(Index) = ArrayIn(X)
Combined = Combined & Chr$(1) & ArrayIn(X)
Index = Index + 1
End If
Next
ReDim Preserve TempArray(LBound(ArrayIn) To Index - 1)
ArrayIn = TempArray
End Sub

Rick
wrote in message
...
I have set up an array that holds string values from excel, (option
base 1).

Is there a function that will delete all the duplicate values in the
array and just leave the unique ones, i have many such string array
that i need to do the same thing for.

My code is like this

term() as string (declare array)

Redim term(noItems) (re dim array with the number of items)

Import data to the term array

Call Unique(term) (pass the term array to the unique function and get
back the term array with just the unique items only)

Can any body help

Cheers

Tony

This works.

Function Unique(V As Variant) As Variant
Dim myItem As Variant
Dim myArray() As String
Dim i As Integer
Dim myCol As New Collection

On Error Resume Next
For i = LBound(V) To UBound(V)
myCol.Add Item:=V(i), Key:=V(i)
Next i
On Error GoTo 0

ReDim myArray(myCol.Count)
i = LBound(myArray)

For Each myItem In myCol
myArray(i) = CStr(myItem)
i = i + 1
Next myItem

Unique = myArray
End Function


On Jan 12, 8:00 pm, Alan Beban wrote:
Dana DeLouis wrote:
Would something like this work?

Sub TestIt()
Dim V
V = Array("a", "b", "c", "b", "d", "c", "e")
V = Unique(V)
End Sub

Function Unique(V) As Variant
Dim d As Object
Dim Obj

Set d = CreateObject("Scripting.Dictionary")
On Error Resume Next

For Each Obj In V
d.Add Obj, 1
Next Obj
Unique = d.Keys
End Function

Not if V is of type String() as stated by the OP. E.g., the following
doesn't work:

Sub TestIt()
Dim V() As String
ReDim V(1 To 7)
V(1) = "a"
V(2) = "b"
V(3) = "c"
V(4) = "b"
V(5) = "d"
V(6) = "c"
V(7) = "e"
V = Unique(V) '<---Type mismatch error message
End Sub

Harlan Grove wrote:
Do I take it that converting 1D VBA arrays into 2D VBA arrays is
intended functionality for ArrayUniques? If so, you might want to
consider mentioning it to the poor unfortunates who try to use your
function library.

The functionality is chosen by the user. You and the other poor
unfortunates need only read the description of ArrayUniques in the
description portion of the function library or at the beginning of the
ArrayUniques procedure code. It states quite clearly that the default
output is a single column 1-based vertical array, therefore necessarily
two-dimensional.

The user selects among:

1-based vertical 2-D (of necessity) V = ArrayUniques(V) default case

0-based vertical 2-D (of necessity) V = ArrayUniques(V, , "0vert")

1-based horizontal 1-D V = ArrayUniques(V, , "1horiz")

0-based horizontal 1-D V = ArrayUniques(V, , "0horiz")

The second parameter (Boolean) provides for case matching (the default)
or not

The fourth parameter (Boolean) provides for omitting blanks (the
default) or not.

I'm changing the description to make it explicit that the horizontal
outputs are one-dimensional.

I don't mind you ****ing on the library; you have to do that,
particularly when you're not on your meds. But do the group the courtesy
of understanding the specific function you're going to discuss before
you do.

Alan Beban

Alan Beban wrote...
....
The functionality is chosen by the user. You and the other poor
unfortunates need only read the description of ArrayUniques in the
description portion of the function library or at the beginning of
the ArrayUniques procedure code. It states quite clearly . . .

If so, *YOU* screwed up your overly simple examples, which I quote:

Sub TestIt()
Dim V() As String
ReDim V(0 To 6)
V(0) = "a"
V(1) = "b"
V(2) = "c"
V(3) = "b"
V(4) = "d"
V(5) = "c"
V(6) = "e"
V = ArrayUniques(V)
End Sub

Sub TestIt2()
Dim V() As String
ReDim V(0 To 0)
Assign Array("a", "b", "c", "b", "d", "c", "e"), V
V = ArrayUniques(V)
End Sub

In both cases you start off with 0-based 1D arrays and convert them
into 1-based 2D arrays. In theory, you understand your own array
functions library better than anyone else, so when you provide
examples of its use, shouldn't you try to make examples of using it as
straightforward as possible?

In this case, that would have meant ArrayUniques calls like so:

V = ArrayUniques(V, , "0horiz")

But if generality is desired, ranges, 1D and 2D arrays accepted, and
returned arrays having the same lower dimension bounds as the passed
arrays, consider


Function adistinct(ByVal a As Variant) As Variant
'returns result array if successful, #REF! if passed multiple
'area range, #NUM! if passed scalar or 3D or higher array,
'#VALUE! would indicate runtime error if called as a udf
'------------------------------------------------------------
'requires reference to Microsoft Scripting Runtime
'if running under Excel 97 or Mac versions of Excel,
'comment next line and uncomment the line after
Dim d As Dictionary
'Dim d As Object
Dim i As Long, j As Long, k As Long, n As Long, x As Variant

'convert single area ranges to arrays; die on multiple area ranges
adistinct = CVErr(xlErrRef)
If TypeOf a Is Range Then _
If a.Areas.Count = 1 Then a = a.Value Else Exit Function

'check for 3rd dim'n bounds
On Error Resume Next
i = -1
i = UBound(a, 3) - LBound(a, 3) 'if 3D or higher, i now = 0
j = -1
j = UBound(a, 2) - LBound(a, 2) 'if 2D or higher, j now = 0
On Error GoTo 0

'die on non-Range objects, scalars and 3D or higher arrays
adistinct = CVErr(xlErrNum)
If IsObject(a) Or (Not IsArray(a)) Or i = 0 Then Exit Function

'if running under Excel 97 or Mac versions of Excel,
'comment next line and uncomment the line after
Set d = New Dictionary
'Set d = CreateObject("Scripting.Dictionary")

'load distinct values into Dictionary object
For Each x In a
If Not d.Exists(x) Then d.Add Key:=x, Item:=0
Next x

'keep original 1st dim'n lower bound (k), but change
'upper bound based on number of distinct values (n)
k = LBound(a, 1)
n = d.Count + k - 1

'reduce a to its distinct values
If j < 0 Then '1D
ReDim a(k To n)

For j = k To n
a(j) = d.Keys(j - k)
Next j

Else '2D
'keep original 2nd dim'n lower bound (i), and make it
'the upper bound as well, so a degenerate 2nd dim'n
i = LBound(a, 2)
ReDim a(k To n, i To i)

For j = k To n
a(j, i) = d.Keys(j - k)
Next j

End If

Set d = Nothing

adistinct = a

End Function


This doesn't do exactly the same thing your ArrayUniques does, but it
does return specific type arrays with the same number of dimensions
and lower dimension bounds as the passed arrays, which I consider more
convenient. And it took just 69 lines including comments and blank
lines with no compound statements compared to ArrayUniques, which
weighs in at 140 lines.

As I said, this function doesn't do exactly the same thing as yours.
With regard to case insensitivity, if an array contained "AA", "Aa",
"aA" and "aa", which should be kept as the distinct value? The first
found? The last found? The most frequently occurring? The one with the
most upper or lower case chars? Determined by collation sequence? If
first or last, should the function iterate through the array row-major
or column-major? To me, it takes more than just one 2-state optional
parameter for this.

As for omitting blanks, there'd be at most one instance of "" in the
result array, and that'd be easy enough to eliminate using a separate
filtering function. The advantage of a separate filtering function is
that it could accept an array of values to remove, possibly including
Empty, error values, etc.

That leaves changing the number of dimensions and array lower bounds,
and for those things I'd prefer to use different functions, and have a
broader choice than just 0 or 1 as lower bounds.

Harlan Grove wrote:

As I said, this function doesn't do exactly the same thing as yours.
With regard to case insensitivity, if an array contained "AA", "Aa",
"aA" and "aa", which should be kept as the distinct value? The first
found? The last found? The most frequently occurring? The one with the
most upper or lower case chars? Determined by collation sequence? If
first or last, should the function iterate through the array row-major
or column-major? To me, it takes more than just one 2-state optional
parameter for this.

Seems that by choosing case insensitivity, the user will have indicated
that it doesn't make any difference which is kept as the distinct value;
that he/she is indicating that for his/her purposes they are all
equivalent.

As for omitting blanks, there'd be at most one instance of "" in the
result array, and that'd be easy enough to eliminate using a separate
filtering function. The advantage of a separate filtering function is
that it could accept an array of values to remove, possibly including
Empty, error values, etc.

Not sure what the thrust of this comment is. The one instance of ""
(which will often result from unwanted blanks in the range/array from
which duplicates are to be eliminated) is eliminated with a single
simple line of code -- If OmitBlanks Then x.Remove ("")

There is no need for the ArrayUniques function to contain the separate
filtering function for any additional filtering that a user might
design. The filtering can readily be accomplished, if desired, with
something like

FilteringFunction(ArrayUniques([ArrayUniques parameters]),
[FilteringFunction parameters])

Alan Beban

Alan Beban wrote...
....
Seems that by choosing case insensitivity, the user will have
indicated that it doesn't make any difference which is kept as the
distinct value; that he/she is indicating that for his/her purposes
they are all equivalent.

Fair enough, so first match. An alternative way to handle this would
be to use a different function to change all text to upper, lower or
Proper case before calling the function that would remove duplicates.

. . . The one instance of "" (which will often result from unwanted
blanks in the range/array from which duplicates are to be
eliminated) is eliminated with a single simple line of code -- If
OmitBlanks Then x.Remove ("")

Now that you mention it, this lies in an inefficient block of code.

On Error Resume Next
For Each Elem In arr
x.Add Item:=Elem, Key:=CStr(Elem)
Next
If OmitBlanks Then x.Remove ("")
On Error GoTo 0

There's no need for the enclosing error trapping, which slows things
down. Just test whether each new array value is already loaded into
the dictionary, and only add those that aren't. Similarly, only remove
"" if it exists.

For Each Elem In arr
If not x.Exists(Elem) Then x.Add Item:=0, Key:=Elem
Next
If OmitBlanks And x.Exists("") Then x.Remove ""

There is no need for the ArrayUniques function to contain the
separate filtering function for any additional filtering that a
user might design. The filtering can readily be accomplished, if
desired, with something like

FilteringFunction(ArrayUniques([ArrayUniques parameters]),
[FilteringFunction parameters])

Indeed, but note that your parameter to omit blanks only eliminates
zero-length strings. If the input array were a range, and that range
contained blank cells (in the ISBLANK sense), those cells would be
recorded as Empty rather than "", so using your optional parameter
wouldn't eliminate them. My point is that IF a user would want to
eliminate both "" and Empty, it's more efficient to reduce the array
to the distinct values POSSIBLY INCLUDING "" and Empty, then eliminate
the at most single remaining "" and Empty items.

If all a user would ever want to do would be eliminating "" values,
your approach would be fine. However, if a user would want to
eliminate "" AND Empty values (and/or possibly other values, e.g.,
whatever Excel would receive representing missing values from database
records, which are not always "" or Empty), then the likely need to
call another function would negate the possible usefullness of
eliminating "" in ArrayUniques.

Harlan Grove wrote:. . . . .but note that your parameter to omit
blanks only eliminates
zero-length strings. If the input array were a range, and that range
contained blank cells (in the ISBLANK sense), those cells would be
recorded as Empty rather than "", so using your optional parameter
wouldn't eliminate them. My point is that IF a user would want to
eliminate both "" and Empty, it's more efficient to reduce the array
to the distinct values POSSIBLY INCLUDING "" and Empty, then eliminate
the at most single remaining "" and Empty items.

No. Despite the fact that empty cells are recorded as Empty, the
Dictionary Add Method treats zero-length strings and empty cells both
the same; it loads "" into the dictionary, and once it's loaded either,
it won't load the other. So the optional ArrayUniques parameter does
indeed eliminate both "" and Empty.

Alan Beban

Alan Beban wrote...
....
No. Despite the fact that empty cells are recorded as Empty, the
Dictionary Add Method treats zero-length strings and empty cells
both the same; it loads "" into the dictionary, and once it's loaded
either, it won't load the other. So the optional ArrayUniques
parameter does indeed eliminate both "" and Empty.

My diagnosis was wrong. ArrayUniques does something different wrong.

You're assigning CStr(Elem) to Dictionary keys, and that does convert
blank cells to "", but it also eliminates the distinction between,
say, 0 the number and "0" the text string, which means your
ArrayUniques may be eliminating too many items from its passed array.
If you assign Elem itself, whatever type it may happen to be, to
Dictionary keys, "" and Empty will be treated as distinct values.

Harlan Grove wrote:
. . .
Now that you mention it, this lies in an inefficient block of code.

On Error Resume Next
For Each Elem In arr
x.Add Item:=Elem, Key:=CStr(Elem)
Next
If OmitBlanks Then x.Remove ("")
On Error GoTo 0

There's no need for the enclosing error trapping, which slows things
down. Just test whether each new array value is already loaded into
the dictionary, and only add those that aren't. Similarly, only remove
"" if it exists.

For Each Elem In arr
If not x.Exists(Elem) Then x.Add Item:=0, Key:=Elem
Next
If OmitBlanks And x.Exists("") Then x.Remove ""

You're correct. A quick and dirty test suggests that it's on the order
of a tenth of a second faster for an array of 10,000 elements.

Alan Beban

Harlan Grove wrote:
. . .
Now that you mention it, this lies in an inefficient block of code.

On Error Resume Next
For Each Elem In arr
x.Add Item:=Elem, Key:=CStr(Elem)
Next
If OmitBlanks Then x.Remove ("")
On Error GoTo 0

There's no need for the enclosing error trapping, which slows things
down. Just test whether each new array value is already loaded into
the dictionary, and only add those that aren't. Similarly, only remove
"" if it exists.

For Each Elem In arr
If not x.Exists(Elem) Then x.Add Item:=0, Key:=Elem
Next
If OmitBlanks And x.Exists("") Then x.Remove ""

Item:=0 needs to be Item:=Elem (or the later y = x.Items needs to be
changed to y = x.Keys)

Alan Beban

Alan Beban wrote...
Harlan Grove wrote:
....
For Each Elem In arr
If not x.Exists(Elem) Then x.Add Item:=0, Key:=Elem
Next
If OmitBlanks And x.Exists("") Then x.Remove ""

Item:=0 needs to be Item:=Elem (or the later y = x.Items needs to be
changed to y = x.Keys)

Yup, make the latter assignment y = x.Keys.

Harlan Grove wrote:
Alan Beban wrote...
...

No. Despite the fact that empty cells are recorded as Empty, the
Dictionary Add Method treats zero-length strings and empty cells
both the same; it loads "" into the dictionary, and once it's loaded
either, it won't load the other. So the optional ArrayUniques
parameter does indeed eliminate both "" and Empty.


My diagnosis was wrong. ArrayUniques does something different wrong.

You're assigning CStr(Elem) to Dictionary keys, and that does convert
blank cells to "" . . .
If you assign Elem itself, whatever type it may happen to be, to
Dictionary keys, "" and Empty will be treated as distinct values.

Nope (you should really test this stuff before you state conclusions).
After your comment about error trapping being inefficient, I substituted
your suggested snippet, except that because I later had y = x.Items to
load the Dictionary items into an array, I changed Item:=0 in your
snippet to Item:=Elem. As you suggest, "" and Empty were treated as
distinct values to the extent that if a blank cell precedes the first
"", then the Add Method appears to load Empty rather than "", but it
still fails (appropriately) to load the subsequent zero-length string,
and the loaded Empty is treated as "" in the later If x.Exists("")
statement; i.e., again assuming an empty cell precedes the zero-length
string in the range/array being tested for duplicates, Empty is loaded
rather than "" and the zero-length string is treated as a duplicate, but
If x.Exists("") nevertheless returns True and the Empty is removed with
x.Remove ""

You're assigning CStr(Elem) to Dictionary keys, and that . . .
eliminates the distinction between,
say, 0 the number and "0" the text string, which means your
ArrayUniques may be eliminating too many items from its passed array.

Nope; again an erroneous apparently untested conclusion. Assigning Elem
itself to the Dictionary keys eliminates the distinction between 0 and
"0"; assigning CStr(Elem) preserves it.

These results may not be the way you think VBA works, or would like or
expect it to work, but them's the facts.

Alan Beban

Harlan Grove wrote:
. . .
Now that you mention it, this lies in an inefficient block of code.

On Error Resume Next
For Each Elem In arr
x.Add Item:=Elem, Key:=CStr(Elem)
Next
If OmitBlanks Then x.Remove ("")
On Error GoTo 0

There's no need for the enclosing error trapping, which slows things
down. Just test whether each new array value is already loaded into
the dictionary, and only add those that aren't. Similarly, only remove
"" if it exists.

For Each Elem In arr
If not x.Exists(Elem) Then x.Add Item:=0, Key:=Elem
Next
If OmitBlanks And x.Exists("") Then x.Remove ""

The snippet I'm using is

For Each Elem In arr
If not x.Exists(Elem) Then x.Add Item:=Elem, Key:=CStr(Elem)
Next
If OmitBlanks Then x.Remove ""

Item:=Elem because the later code uses y = x.Items to load the array

Key:=Cstr(Elem) to preserve the distinction between, e.g., 0 and "0"
(and it continues to allow blanks and zero-length strings to be treated
as duplicates)

Delete And x.Exists("") as superfluous

Thanks for the constructive treatment; even the erroneous conclusions
were instructive.

Alan Beban

Alan Beban wrote...
Harlan Grove wrote...
....
Nope (you should really test this stuff before you state
conclusions). . . . the Add Method appears to load Empty rather than
"", but it still fails (appropriately) to load the subsequent
zero-length string, and the loaded Empty is treated as "" in the
later If x.Exists("") statement; . . .
....

You're right, but I consider this a bug in the Dictionary object to be
worked around, requiring more code when filling the Dictionary.

For Each x In a
If Not d.Exists(x) Then d.Add Key:=x, Item:=0

'workaround for "" and Empty keys treated as the same
If IsEmpty(x) Then
d.Item("") = d.Item("") Or 2 'set 2nd bit
If i < 0 Then i = 2 'Empty appears first
ElseIf x = "" Then
d.Item("") = d.Item("") Or 1 'set 1st bit
If i < 0 Then i = 1 '"" appears first
End If
Next x

x = IIf(d.Exists(""), d.Item(""), 0)

More code when calculating the result array's 1st dimension bounds.

k = LBound(a, 1)
'workaround for "" and Empty keys treated as the same
If x < 3 Then 'either Empty or "" only appears or neither did
n = d.Count + k - 1
x = Null
Else 'both Empty and "" appeared
n = d.Count + k
x = IIf(i = 2, "", Empty) 'the one that appeared LAST
End If

And different code to fill the result array.

'workaround for "" and Empty keys treated as the same
If Not IsNull(x) Then a(n) = x

For j = 0 To d.Count - 1
a(j + k) = d.Keys(j)
Next j

or

'workaround for "" and Empty keys treated as the same
If Not IsNull(x) Then a(n, i) = x

For j = 0 To d.Count - 1
a(j + k, i) = d.Keys(j)
Next j

You're assigning CStr(Elem) to Dictionary keys, and that . . .
eliminates the distinction between, say, 0 the number and "0" the
text string, which means your ArrayUniques may be eliminating too
many items from its passed array.

Nope; again an erroneous apparently untested conclusion. Assigning
Elem itself to the Dictionary keys eliminates the distinction
between 0 and "0"; assigning CStr(Elem) preserves it.

These results may not be the way you think VBA works, or would like
or expect it to work, but them's the facts.

This one I did test. Guess you either didn't or botched it.

Let's test them facts, shall we?

In the module where ArrayUniques resides, run this macro

Sub hgtest()
Dim v As Variant, x As Variant
v = ArrayUniques(Array(1, "1", "foo"))
Debug.Print LBound(v, 1); UBound(v, 1)
For Each x In v
Debug.Print TypeName(x), x
Next x
End Sub

The resulting Debug output is

1 2
Double 1
String foo

which shows ArrayUniques eliminates "1". Swap the order of 1 and "1",
and the Debug output becomes

1 2
String 1
String foo

this time eliminating 1.

Now run similar tests using my revised adistinct function.

Sub hgtest()
Dim v As Variant, x As Variant
v = adistinct(Array(1, "1", "foo", Empty, ""))
Debug.Print LBound(v, 1); UBound(v, 1)
For Each x In v
Debug.Print TypeName(x), x
Next x
End Sub

which produces the following Debug output

0 4
Integer 1
String 1
String foo
Empty
String

BTW, note that ArrayUniques stores numeric 1 as a Double while
adistinct stores it as an Integer. FTHOI, reverse the order of 1 and
"1" in the latter test procedure, and the Debug output becomes

0 4
String 1
Integer 1
String foo
Empty
String

So what exactly was the point you were trying to make?

Harlan Grove wrote:
. . .
You're assigning CStr(Elem) to Dictionary keys, and that . . .
eliminates the distinction between, say, 0 the number and "0" the
text string, which means your ArrayUniques may be eliminating too
many items from its passed array.

Nope; again an erroneous apparently untested conclusion. Assigning
Elem itself to the Dictionary keys eliminates the distinction
between 0 and "0"; assigning CStr(Elem) preserves it.

These results may not be the way you think VBA works, or would like
or expect it to work, but them's the facts.


This one I did test. Guess you either didn't or botched it.

Botched it. I was running the equivalent of

Set rng = Range("b1:b3")
v = ArrayUniques(rng)

where b1:b3 contained

1
"1"
foo

which produces the output

1 3
Double 1
String "1"
String foo

Back to the drawing boards.

Alan Beban