Dear All,

I'm trying to write some code to do a fancy series of searches, but before I can do that, I need to create a list of unique elements (ie get rid of duplicates). I used an example I found somewhere to write my own function that does the job, and works fine for arrays defined item by item in excel vba.

Function funUniqueList(vaArrayIn() As Variant) As Variant

Dim vaUniqueList() As Variant 'Stores unique values
Dim strElement As String 'Stores the element that is being investigated
Dim bIsUnique As Boolean 'Stores whether the current item is still unique after comparing to each other element

ReDim vaUniqueList(0 To 0) As Variant 'Resize to allow for storage

vaUniqueList(0) = vaArrayIn(0) 'First item must be unique

For i = 0 To UBound(vaArrayIn)
    strElement = vaArrayIn(i) 'Stores each element one at a time
    
    For j = 0 To UBound(vaUniqueList) 'Checks the element against the growing list of unique elements.
        If strElement = vaUniqueList(j) Then 'If it matches any, the element is not unique and the subloop is exited
            bIsUnique = 0
            Exit For
        End If
    Next j
    
    If bIsUnique = True Then 'If the element is unique, it is added to the list of unique items
    ReDim Preserve vaUniqueList(0 To UBound(vaUniqueList) + 1)
    vaUniqueList(UBound(vaUniqueList)) = strElement
    End If
    
    bIsUnique = 1 'The value is reset
Next i

funUniqueList = vaUniqueList 'Creates output for function

End Function
This works fine on arrays that are defined via excel vba enumerating each element. However, if I try to pass an array I created by reading in from a range of values it gives me a "subscript out of range" error. If I try to redim the range it also gets angry, although for different reasons. What is the right way to do this? My attempt is below.

Sub ArrayPassTest()

Dim vaRawList() As Variant 'To Be Read in from Excel spreadsheet
Dim vaUniqueValues() As Variant 'To store result from uniquelist function

vaRawList = Range("A2:A317").Value 'Read in value from range

vaUniqueValues = funUniqueList(vaRawList) 'Run the function and store the resulting array

Range("B1").Resize(UBound(vaUniqueValues), 1).Value = vaUniqueValues 'Write the values to the spreadsheet

End Sub
The data of interest is just a bunch of numbers (that I treat as strings really) that are all 9 digits long. Thanks much for your help.