[SOLVED] Create empty lists and then append elements to that list

Create empty lists and then append elements to that list

I have code in Python that I'm trying to convert to VBA.

List = [] 

For x in range:
    if x not in list:
    list.append(x)

I would create an empty list, the Python code would loop through my desired data (defined here as "range") then check if the element was in the list, and if it wasn't, add it.

I am trying to do the same in VBA. It needs to go down one column, and add unique elements in that column to a VBA list.

Based on searching I have this:

Dim list() As Variant

For n = 1 To end
   If list.Contains(Cells(n,1).value) Then 
       list(n) = Cells(n,1).value 
       n= n+1

When I run this code, I get an error where it highlights the "list" in

If list.Contains(Cells(n,1).value) Then

and says

"Invalid qualifier".

I tried changing it to

if list.Contains(Cells(n,1).value) = True

to add a qualifier.

All I need to do is create a list of strings. Is there a better way to do this in VBA?

Solution

You can use a dictionary to process for unique items. In this case an array would be equivalent to a list. You populate the distinct list from the dictionary keys.

Public Sub test()
    Dim r As Range   ' this is what you would iterate over bit like your existing range
    Dim distinctList() 'empty list
    Dim dict As Object, inputValues(), i As Long
    Set r = ActiveSheet.Range("A1:A10")          'Alter as required
    Set dict = CreateObject("Scripting.Dictionary")
    inputValues = Application.Transpose(r.Value) 'List of all values. Faster to process as array.
    For i = LBound(inputValues) To UBound(inputValues)
        dict(inputValues(i)) = vbNullString 'add distinct list values to dictionary with overwrite syntax
    Next
    If dict.Count > 0 Then
        distinctList = dict.keys ' generate distinct list
    End If
End Sub