Filling a ListBox with unique values

[Harry-Wishes]

Hello, I am trying to fill a Listbox of a userform with unique values from an
existing array. The array contains multiple instances of the same values. I
took a snippet of code below from an excel forum which essentially does the
trick except it pulls unique values from a range of cells of an Excel
worksheet. Of course, it works well in Excel as you'd expect but I can't seem
to adapt this example when I try to pull unique values from an array. If you
look at the subroutine immediately below, you see that it calls a function
which accepts a range of cells as a parameter. I'm trying to adapt the
subroutine and function so that I can pass in an array rather than a range of
cells. VB does not like what I'm doing so far as I'm not well versed in
passing in parameters. I can't imagine there would be much to change but I
could be wrong. Any help is welcome.

Thanks

In case you're wondering, I am working in Word, not Excel.

Private Sub UserForm_Initialize()
Dim MyUniqueList As Variant, i As Long
With Me.ListBox1
.Clear ' clear the listbox content
MyUniqueList = UniqueItemList(Range("A4:A100"), True)
For i = 1 To UBound(MyUniqueList)
.AddItem MyUniqueList(i)
Next i
.ListIndex = 0 ' select the first item
End With
End Sub


Private Function UniqueItemList(InputRange As Range, _
HorizontalList As Boolean) As Variant
Dim cl As Range, cUnique As New Collection, i As Long, uList() As Variant
Application.Volatile
On Error Resume Next
For Each cl In InputRange
If cl.Formula <> "" Then
cUnique.Add cl.Value, CStr(cl.Value)
End If
Next cl
UniqueItemList = ""
If cUnique.Count > 0 Then
ReDim uList(1 To cUnique.Count)
For i = 1 To cUnique.Count
uList(i) = cUnique(i)
Next i
UniqueItemList = uList
If Not HorizontalList Then
UniqueItemList = _
Application.WorksheetFunction.Transpose(UniqueItemList)
End If
End If
On Error GoTo 0
End Function

 

[Jean-Guy Marcil]

Harry-Wishes was telling us:
Harry-Wishes nous racontait que :

Hello, I am trying to fill a Listbox of a userform with unique values
from an existing array. The array contains multiple instances of the
same values. I took a snippet of code below from an excel forum which
essentially does the trick except it pulls unique values from a range
of cells of an Excel worksheet. Of course, it works well in Excel as
you'd expect but I can't seem to adapt this example when I try to
pull unique values from an array. If you look at the subroutine
immediately below, you see that it calls a function which accepts a
range of cells as a parameter. I'm trying to adapt the subroutine and
function so that I can pass in an array rather than a range of cells.
VB does not like what I'm doing so far as I'm not well versed in
passing in parameters. I can't imagine there would be much to change
but I could be wrong. Any help is welcome.

Thanks

In case you're wondering, I am working in Word, not Excel.

Like this perhaps, note that I included a sorting step, you can remove the
SortArray line if you do not need to do this.

Private Sub UserForm_Initialize()

Dim strArray() As String
Dim MyUniqueList As Variant
Dim i As Long

strArray = Split("four|one|two|three|three|four|two|three|four|four", "|")

With Me.ListBox1
.Clear ' clear the listbox content
MyUniqueList = UniqueItemList(strArray)
For i = 1 To UBound(MyUniqueList)
.AddItem MyUniqueList(i)
Next i
.ListIndex = 0 ' select the first item
End With

End Sub


Private Function UniqueItemList(InputStr() As String) As Variant

Dim cUnique As New Collection
Dim i As Long
Dim uList() As Variant

WordBasic.SortArray InputStr(), 0, 0, UBound(InputStr), 0, 1

For i = 0 To UBound(InputStr)
On Error Resume Next
cUnique.Add InputStr(i), CStr(InputStr(i))
On Error GoTo 0
Next

If cUnique.Count > 0 Then
ReDim uList(1 To cUnique.Count)
For i = 1 To cUnique.Count
uList(i) = cUnique(i)
Next i
UniqueItemList = uList
End If

End Function

 

[Harry-Wishes]

Hmmm. I had not thought of using the split function of a concatenated
string. I was storing the values in an actual array data structure. Anyway,
the sort in my situation was not needed so I left it out. Everything works
great!

Thanks for your help

Harry Wishes

 

[Jean-Guy Marcil]

Harry-Wishes was telling us:
Harry-Wishes nous racontait que :

Hmmm. I had not thought of using the split function of a concatenated
string. I was storing the values in an actual array data structure.
Anyway, the sort in my situation was not needed so I left it out.
Everything works great!

I only used Split as a fast and dirty way to get an array so as to show one
way of getting unique values... It was certainly not the main part of my
post!

Thanks for your help

Glad you got everything working.