Alan Beban said:
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.