Re: unique values in an array

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Alan Beban <unavaila...@xxxxxx> 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.
.

Relevant Pages

  • Re: Array size limit ?
    ... Single dimension Zero based arrays (sometimes called vectors or ... The SZArray has a lot more functionality including sort, ... Both VB6 and VB.NET do bounds ... So for VB6 has to get the lower bound add the length to ...
    (microsoft.public.vb.general.discussion)
  • Re: yet another another wish list
    ... C# and Oberon only support zero-based arrays ... By new programmers, I mean programmers whose first introduction to ... of an arbitrary lower bound hardly provides any additional expressive power. ...
    (borland.public.delphi.non-technical)
  • Re: calling .Net DLL from Excel VBA
    ... Did you manually register the .NET DLL, ... could you please let me know what you did to inform VBA about ... >> The problem with arrays is that .NET only natively supports arrays ... >> can have a variable lower bound (i.e. you can declare them like this ...
    (microsoft.public.office.developer.vba)
  • Re: calling .Net DLL
    ... >> Jonathan West wrote: ... since VB.NET does not have the Variant datatype. ... > The problem with arrays is that .NET only natively supports arrays ... > can have a variable lower bound (i.e. you can declare them like this ...
    (microsoft.public.office.developer.vba)