The UniquifyStringArray() function takes an array as input and returns an array that contains unique elements from that array. The number of unique items in the array is the return value of the function. A routine that demonstrates how to use the UniquifyStringArray() function is also listed.

Function UniquifyStringArray(ByRef InputArray() As String, _
    ByRef UniqueArray() As String) As Long

    Dim C As New Collection
    Dim I As Long

    On Error Resume Next

    For I = LBound(InputArray) To UBound(InputArray)
        C.Add InputArray(I), InputArray(I)

    ReDim UniqueArray(1 To C.Count)

    For I = 1 To C.Count
        UniqueArray(I) = C(I)

    UniquifyStringArray = C.Count

    Set C = Nothing
End Function

Sub Demo()
    Dim A(1 To 5) As String
    Dim B() As String
    Dim I As Long
    Dim S As String

    A(1) = "1"
    A(2) = "4"
    A(3) = "2"
    A(4) = "4"
    A(5) = "1"
    I = UniquifyStringArray(A, B)
    S = Trim(Str(I)) + " unique elements: "
    For I = LBound(B) To UBound(B)
        S = S + B(I) + ", "
    S = Left(S, Len(S) - 2)
    MsgBox S
End Sub

Contact OfficeOne on email at officeone@officeoneonline.com. Copyright © 2001-2023 OfficeOne. All rights reserved.