Read Range To Array, Then Pass Array To Function

**ProbablyNotARealName** · 09-17-2010, 06:12 PM

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.

**DonkeyOte** · 09-18-2010, 04:33 AM

You can generate unique listings in a number of ways (Collections, Dictionary Objects, iterating Arrays etc)

I would suggest having a look at Colo's code: http://puremis.net/excel/code/021.shtml

Be aware also of Array dimensions - a Range will always default to a 2d Array (with Base 1)
(for vectors you can use Transpose to convert to 1d Array - once for vertical, twice for horizontal)

**snb** · 09-18-2010, 06:16 AM

Sub snb()
  sn = unique_snb(Split("aa|bb|cc|dd|ee|dd|cc", "|"))
End Sub

Function unique_snb(sq)
  For j = 0 To UBound(sq)
    If InStr(c01 & "|" , "|" & sq(j) & "|") = 0 Then c01 = c01 & "|" & sq(j)
  Next
  unique_snb = Split(Mid(c01, 2), "|")
End Function

applied to a range:

Sub snb_range()
  sn = uniquerange_snb(Range("A1:B5"))
End Sub

Function uniquerange_snb(sq As Range)
  For Each cl In sq
    If InStr(c01 & "|", "|" & cl & "|") = 0 Then c01 = c01 & "|" & cl
  Next
  uniquerange_snb = Split(Mid(c01, 2), "|")
End Function

**ProbablyNotARealName** · 09-20-2010, 12:29 PM

Thanks all. It turns out DonkeyOte was right, it was a problem with dimensions. I used transpose to make it a 1D array, made sure the indicies were alright and I was good to go. It works just like I want it to now.