VBA ArrayList filled with Arrays- find array element

Question

I'm new to VBA and am trying to figure out categorize an excel sheet based on the contents of a column. My first problem is -I have an arraylist which I have populated with arrays, and-i'm having trouble accessing the elements of the arrays contained in the arraylist.(can use switch statement as word might be buried in text) The second problem is I have to intialise a new array rather than using the Object names- I have tried casting. Any help greatly appreciated.

        'declare variable for the active cell for comparison
        Dim ActiveTxt As String
        Dim StringTxt As String
        Dim Pop_Cell As Range
        Dim msg As String    

        'intialize the Array list
        Dim category_List As Object
        Set category_List = CreateObject("System.Collections.ArrayList")


        'Parent Array- has to match sequence of the intialised arrays
        Dim Parent()
        Parent = Array("Flights", "Accomodation", "Other_Subsistence")


        'Array for Search terms
        '**********************
        'search terms are case sensitive
        '**********************

        Dim Flights()
        Flights = Array("aerlin", "aerling", "ryanair", "ryan", "cityjet", "luft", "lufthansa", "aer", "transavia", "easyjet", "air", "swiss", "aero", "wow air"           
        Dim Accomodation()
        Accomodation = Array("hotel")
        Dim Other_Subsistence()
        Other_Subsistence = Array("subsistance", "overnight")





        'add Arrays to the arraylist
        category_List.Add (Flights)
        category_List.Add (Accomodation)
        category_List.Add (Other_Subsistance)



       'select first line of data
       Range("A4").Select



        'Set do loop to stop when an empty cell reached
        Do Until IsEmpty(ActiveCell)

        'to loop through the ArrayList (category_List)

        For i = 0 To UBound(category_List(i))



        'Loop through the Array
        'For i = 0 To UBound(Flights)



            'declaring variables for Search
            ActiveTxt = ActiveCell.Text

       '************************this is where the problem lies -used 1 as a test would use i and j and interate through them once it works
            StringTxt = category_List.Item(1).get(1)




            'Search by comparison- "if the cell contains this word"
            If InStr(1, ActiveTxt, StringTxt) Then
            'below makes a pop up box and populates it
            'MsgBox ("found" & ActiveTxt)

            'this populates the cell where the searched for value has been      found with "flights" value
                '*****this then needs to be Array
                ActiveCell.Offset(0, 3).Value = StringTxt

               'if found then exit the loop to stop searching though it
                Exit For


            Else

                End If




            Next i



        Loop

Why use an `ArrayList`? If they are keyed by strings (`"Flights"`, etc.) then using a dictionary or collection seems more natural. — John Coleman, Dec 11 '16 at 23:29
[ArrayList](https://msdn.microsoft.com/en-us/library/system.collections.arraylist(v=vs.110).aspx#Anchor_6) shouldn'tbe used in [.net](http://stackoverflow.com/questions/5063156/why-isnt-arraylist-marked-obsolete) so it probably shouldn't be used in VBA either. — Daniel Dušek, Dec 12 '16 at 09:52
@dee By that logic VBA shouldn't be used at all, since .Net replaced VB6. Sorry, but I disagree. It works fine and is still in bazillions of applications. — Excel Hero, Feb 19 '19 at 07:21

score 1 · Answer 1 · answered Dec 12 '16 at 01:39

These examples demonstrate how to reference and iterate over arrays stored in an System.Collections.ArrayList and a Scripting Dictionary.

Note: You can only read values from arrays stored in collections. If you need to modify the values you will need to write them to a temp array, update the temp array and then reassign them to the collection.

Sub ArrayListExample()
    Dim Flights As Variant, Parent As Variant, Other_Subsistence As Variant
    Dim list As Object
    Set list = CreateObject("System.Collections.ArrayList")
    list.Add Array("Flights", "Accomodation", "Other_Subsistence")
    list.Add Array("aerlin", "aerling", "ryanair", "ryan", "cityjet", "luft", "lufthansa", "aer", "transavia", "easyjet", "air", "swiss", "aero", "wow air")
    list.Add Array("subsistance", "overnight")

    Parent = list.Item(0)
    Flights = list.Item(1)
    'This works because Item is the default property of an ArrayList
    Other_Subsistence = list(2)

End Sub

Sub DictionaryExample()
    Dim Flights As Variant, Parent As Variant, Other_Subsistence As Variant
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    dict.Add "Parent", Array("Flights", "Accomodation", "Other_Subsistence")
    dict.Add "Flights", Array("aerlin", "aerling", "ryanair", "ryan", "cityjet", "luft", "lufthansa", "aer", "transavia", "easyjet", "air", "swiss", "aero", "wow air")
    'This works because Item is the default property of an Scripting.Dictionary
    dict("Other_Subsistence") = Array("subsistance", "overnight")

    Parent = dict.Item("Parent")
    Flights = dict.Item("Flights")
    'This works because Item is the default property of an ArrayList
    Other_Subsistence = dict("Other_Subsistence")

End Sub

VBA ArrayList filled with Arrays- find array element

1 Answers1