CreateObject("System.Collections.ArrayList")

**pike** · Jul 7th 2012

doctored examples

Code

Option Explicit
Sub ptest()
    'http://www.robvanderwoude.com/vbstech.php#Data
    'http://www.thecodecage.com/forumz/showthread.php?p=1055000938#post1055000938
    'this code creates and populates an ArrayList
    
    Dim myArrayList As Object, myArrayList2 As Object
    Dim xItem, myrange
    
    Set myArrayList = CreateObject("System.Collections.ArrayList")
    myArrayList.Add "Kyle"
    myArrayList.Add "123"
    myArrayList.Add "C"
    myArrayList.Add "snb"
    myArrayList.Add "pike"


    'result to immediate window
    Debug.Print Join(myArrayList.toarray(), Chr(10))
    
    ' copy / clone ArrayList
    Set myArrayList2 = myArrayList.Clone
    
    'Now, to add an element and sort the ArrayList, all we need to do is:
    'add the new element to the ArrayList
    myArrayList.Add "Z"
    
    'remove the new element to the ArrayList
    myArrayList.Remove "C"
    
    'result to immediate window
    Debug.Print Join(myArrayList.toarray(), Chr(10))
    
    'result to immediate window
    Debug.Print "List Has C " & myArrayList.Contains("C")
    Debug.Print "List Has Z " & myArrayList.Contains("Z")


    'result to immediate window - count elements and capacity
    Debug.Print "Size     : " & myArrayList.Count
    Debug.Print "Capacity : " & myArrayList.Capacity
    
    'trim blank elements from the ArrayList
    myArrayList.TrimToSize
    
    'result to immediate window - count elements and capacity
    Debug.Print "Size     : " & myArrayList.Count
    Debug.Print "Capacity : " & myArrayList.Capacity
    
    'sort the ArrayList
    myArrayList.Sort
    
    'result to immediate window
    Debug.Print Join(myArrayList.toarray(), Chr(10))
    
    '  reverse the ArrayList
    myArrayList.Reverse
    
    'result to immediate window
    Debug.Print Join(myArrayList.toarray(), Chr(10))
    
    ' copy / clone ArrayList2 result to immediate window
    Debug.Print Join(myArrayList2.toarray(), Chr(10))
    
    'toarray transfer ArrayList to  results to worksheet
    Range("B1").Resize(myArrayList.Count) = Application.Transpose(myArrayList.toarray())
    Range("C1").Resize(1, myArrayList.Count) = myArrayList.toarray()


    Set myArrayList = Nothing
    Set myArrayList2 = Nothing
End Sub

Display More

thats cool no loops

**Krishnakumar** · Aug 1st 2012

Re: CreateObject("System.Collections.ArrayList")

Hi pike,

:cool:

I see jindon use this object very often.

**Ger Plante** · Aug 1st 2012

Re: CreateObject("System.Collections.ArrayList")

Thats just totally awesome... thx Pike. I'll have to have a play with that later today. I would feel almost bad using it though... seems like taking a short cut and forgetting about a lot of what I learned with REDIMing

**pike** · Aug 1st 2012

Re: CreateObject("System.Collections.ArrayList")

Big fan of jindon and follow his posts and that’s where i first observed its benefits
kyle123 enlighten to toarray(), now if i can just figure out how to add a range, IList or ICollection with out looping!

**pike** · Aug 9th 2012

Re: CreateObject("System.Collections.ArrayList")

these may also be helpful

Code

Sub Queuelist()
    With CreateObject("System.Collections.Queue")
        .Enqueue "Kyle"
        .Enqueue "123"
        .Enqueue "snb"
        .Enqueue "pike"
        .Enqueue "Simon"


        Debug.Print Join(.Toarray(), Chr(10))


        .Dequeue


        Debug.Print .Peek


        Debug.Print Join(.Toarray(), Chr(10))


        Range("A1").Resize(.Count) = Application.Transpose(.Toarray())
        Range("C1").Resize(1, .Count) = .Toarray()


    End With


End Sub

Display More

**pike** · Aug 9th 2012

Re: CreateObject("System.Collections.ArrayList")

and of course

sortedlist

Code

'http://www.robvanderwoude.com/vbstech.php#Data


'http://www.thecodecage.com/forumz/showthread.php?p=1055000938#post1055000938


    Dim objSortedList As Object
    Dim objList2 As Object
    Dim i      As Integer


    Set objSortedList = CreateObject("System.Collections.Sortedlist")


    objSortedList.Add "First", "AAAA"
    objSortedList.Add "Second", "!"
    objSortedList.Add "Third", "CCCC"
    objSortedList.Add "Fourth", ","
  
    Debug.Print objSortedList.IndexOfKey("First")
    Debug.Print objSortedList.IndexOfValue("AAAA")
    Debug.Print objSortedList.IndexOfKey("Second")
    Debug.Print objSortedList.IndexOfValue("!")
    Debug.Print objSortedList.IndexOfKey("Third")
    Debug.Print objSortedList.IndexOfValue("CCCC")
    Debug.Print objSortedList.IndexOfKey("Fourth")
    Debug.Print objSortedList.IndexOfValue(",")




    For i = 0 To objSortedList.Count - 1
        Debug.Print objSortedList.GetKey(i) & vbTab & objSortedList.GetByIndex(i)


    Next
    'This is the resulting output:


    'First Hello
    'Fourth  !
    'Second  ,
    'Third world
    'Note how the list is automatically sorted by keys; it is not possible to sort the list by values.


    'Like ArrayLists, SortedLists have Count and Capacity properties, and a TrimToSize method, demonstrated in the following code:


    Debug.Print "Size     : " & objSortedList.Count
    Debug.Print "Capacity : " & objSortedList.Capacity




    objSortedList.TrimToSize


    Debug.Print "Size     : " & objSortedList.Count
    Debug.Print "Capacity : " & objSortedList.Capacity
    'This will result in the following output:


    'Size     : 4
    'Capacity : 16


    'Size     : 4
    'Capacity : 4
    'Cloning a SortedList is a piece of cake:
    Dim xxxx
    Set objList2 = objSortedList.Clone
    Debug.Print "Sorted List Key(1) = " & objSortedList.GetKey(1)
    Debug.Print "Cloned List Key(1) = " & objList2.GetKey(1)
    'The result:




    'Sorted List Key(1) = Fourth
    'Cloned List Key(1) = Fourth
    Set objList2 = Nothing
    Set objSortedList = Nothing
End Sub

Display More

**pike** · Aug 18th 2012

Re: CreateObject("System.Collections.ArrayList")

few more methods

Code

Option Explicit
Sub System_Collections_ArrayList()


    ' Reference Library    mscorlib


    Dim ArrayListOne As Object, ArrayListTwo As ArrayList, ArrayListThree As Object, ArrayListFour As ArrayList


    Set ArrayListOne = CreateObject("System.Collections.ArrayList")    ' method with out Reference library
    Set ArrayListTwo = New ArrayList                  'Can use this method after loading  Reference Library    mscorlib
    Set ArrayListThree = CreateObject("System.Collections.ArrayList")    ' method with out Reference library
    Set ArrayListFour = New ArrayList                 'Can use this method after loading  Reference Library    mscorlib


    ArrayListOne.Add "A"
    ArrayListOne.Add "B"
    ArrayListOne.Add "D"
    ArrayListOne.Insert 2, "C"
    ArrayListOne.Insert 2, "C"
    ArrayListOne.Add "E"
    ArrayListOne.Add "C"
    ArrayListOne.Add "F"
    ArrayListOne.Add "G"


    Debug.Print ArrayListOne.Item(3)
    Debug.Print ArrayListOne.Contains("C")
    Debug.Print ArrayListOne.IndexOf_2("C", 0, 4)
    Debug.Print ArrayListOne.Contains("H")
    Debug.Print ArrayListOne.LastIndexOf("C")


    ArrayListOne.Remove "C"
    ArrayListOne.RemoveAt 5


    With ArrayListTwo
        .Add "1"
        .Add "2"
        .Add "3"
        .Add "4"
        .Add "5"
    End With


    ArrayListThree.Add "EE"
    ArrayListThree.Add "CC"
    ArrayListThree.Add "BB"
    ArrayListThree.Insert 2, "DD"
    ArrayListThree.Add "AA"
    
    ArrayListOne.Addrange ArrayListThree
    ArrayListOne.insertRange 6, ArrayListTwo
    ArrayListOne.Addrange ArrayListTwo
    ArrayListOne.RemoveRange 6, 5
    ArrayListOne.Reverse_2 7, 5


    ArrayListOne.TrimToSize


    Debug.Print Join(ArrayListOne.toarray(), Chr(10))
    Debug.Print ArrayListOne.ToString


    Range("A10").Resize(ArrayListOne.Capacity) = Application.Transpose(ArrayListOne.toarray())
    Range("C10").Resize(, ArrayListOne.Count) = ArrayListOne.toarray()


    With ArrayListOne


        Debug.Print .binarysearch_2("CC")


        .Sort


        Debug.Print .binarysearch_2("CC")


        .Reverse


        Debug.Print Join(.toarray(), Chr(10))
        Debug.Print .GetHashCode


    End With


    Debug.Print Join(ArrayListFour.toarray(), Chr(10))


    ArrayListFour.Add "pike"
    ArrayListFour.Add "Japan Dave"
    ArrayListFour.Add "Kyle123"
    ArrayListFour.Add "snb"


    ArrayListOne.SetRange 5, ArrayListFour


    Debug.Print Join(ArrayListOne.toarray(), Chr(10))


    Set ArrayListOne = Nothing
    Set ArrayListTwo = Nothing
    Set ArrayListThree = Nothing
    Set ArrayListFour = Nothing


End Sub

Display More

Kyle123 · Sep 14th 2012

Re: CreateObject("System.Collections.ArrayList")

Did you ever manage to get an array in without looping?

I think I've made my mind up that you can't since the array list is just a collection, though I'd be very happy for you to show me the error of my ways

**pike** · Sep 14th 2012

Re: CreateObject("System.Collections.ArrayList")

hello
Not yet .. I'll keep trying as i have a few ideas with specialcells()

**pike** · Nov 28th 2013

Re: CreateObject("System.Collections.ArrayList")

passing ArrayList directly to array no loop

Code

Option Explicit


Sub Your_Kidding_Me()
   
   Dim InputArray
   InputArray = Array("a", "b", "c", "1", "2", "3", "D", "1", "i", "ii", "iii", "2", "i", "ii")


    Dim ArrayTwo()
    Dim myArrayList As Object
    Dim ArrayItem As Long
    Dim LevelOne As String, LevelTwo As String, LevelThree As String
 
    Set myArrayList = CreateObject("System.Collections.ArrayList")
    For ArrayItem = LBound(InputArray) To UBound(InputArray)
        If Not IsNumeric(InputArray(ArrayItem)) And Left(InputArray(ArrayItem), 1) <> "i" Then
            LevelOne = InputArray(ArrayItem)
            LevelTwo = vbNullString
            LevelThree = vbNullString
        ElseIf IsNumeric(InputArray(ArrayItem)) Then
            myArrayList.Remove InputArray(ArrayItem - 1)
            LevelTwo = InputArray(ArrayItem)
            LevelThree = vbNullString
        Else
            LevelThree = InputArray(ArrayItem)
        End If
        myArrayList.Add LevelOne & LevelTwo & LevelThree
    Next ArrayItem
  ReDim ArrayTwo(1 To myArrayList.Count)


  ArrayTwo() = myArrayList.toarray()
  
  
  For ArrayItem = LBound(ArrayTwo) To UBound(ArrayTwo)
 ' For ArrayItem = 0 To myArrayList.Count - 1
      '  ArrayTwo(ArrayItem + 1) = myArrayList(ArrayItem)
       Debug.Print ArrayTwo(ArrayItem)
    Next
    Set myArrayList = Nothing
End Sub

Display More

**pike** · Nov 30th 2013

Re: CreateObject("System.Collections.ArrayList")

replace item in array with .array() () and workable GetRange

Code

VBA CODE:
Sub ArrayTo_test() 
    Dim x As Long, eList, eItems 
    Dim mySourceList As Object 
    Set mySourceList = CreateObject("System.Collections.ArrayList") 
    mySourceList.Add "three" 
    mySourceList.Add "napping" 
    mySourceList.Add "cats" 
    mySourceList.Add "in" 
    mySourceList.Add "the" 
    mySourceList.Add "barn" 
     
    Dim myTargetArray(10) As String 
    myTargetArray(0) = "The" 
    myTargetArray(1) = "quick" 
    myTargetArray(2) = "brown" 
    myTargetArray(3) = "fox" 
    myTargetArray(4) = "jumped" 
    myTargetArray(5) = "over" 
    myTargetArray(6) = "the" 
    myTargetArray(7) = "lazy" 
    myTargetArray(8) = "dog" 
     
    For x = LBound(myTargetArray) To UBound(myTargetArray) 
        Debug.Print myTargetArray(x) 
    Next x 
     
    myTargetArray(2) = mySourceList.toarray()(2) 
     
    For x = LBound(myTargetArray) To UBound(myTargetArray) 
        Debug.Print myTargetArray(x) 
    Next x 
     
    Set eList = mySourceList.GetRange(2, 4) 
    For Each eItems In eList 
        Debug.Print "List Items= " & eItems 
    Next 
End Sub

Display More

Copyto() ...eludes ??

apo · Feb 13th 2014

Re: CreateObject("System.Collections.ArrayList")

Thanks pike.. bookmarked for reference..

**pike** · Aug 25th 2014

Re: CreateObject("System.Collections.ArrayList")

here is a few more CreateObject("System.Collections.ArrayList") methods that you can possibly use

Code

.IndexOf_3 ("A")' Last  occurrence index in arraylist
.Sort_2 (CreateObject("System.Collections.CaseInsensitiveComparer"))
.Sort_3(2,3,nothing) 'Index, Count, Nothing
.Sort_3(2,3,CreateObject("System.Collections.CaseInsensitiveComparer")) 'Index, Count, Comparer

Code

With CreateObject("System.Collections.ArrayList")
        For Each eItem In Array("a", "b", "c", "d", "e" , "A", "B", "C", "D", "E")
            .Add eItem
        Next
        .Sort
          strmsg = "Sort   : " & Join(.ToArray)
        .Sort_3 2, 5, CreateObject("System.Collections.CaseInsensitiveComparer")
        strmsg = strmsg & vbLf & "Sort_3: " & Join(.ToArray)
        .Sort_2 CreateObject("System.Collections.CaseInsensitiveComparer")
        strmsg = strmsg & vbLf & "Sort_2: " & Join(.ToArray)
  
    End With

Display More

and CreateObject("System.Random")

Code

Sub DRandoms()


    Dim fixedRands As Object 'Random
    Dim strMsg As String
    Dim i      As Integer
    Set fixedRands = CreateObject("System.Random")
    '0 ??????????? strMsg = "Next(0?????):"
    For i = 0 To 5
        strMsg = strMsg & " " & fixedRands.Next
    Next
    '????????????????
    strMsg = strMsg & vbCrLf & "Next_2(5??10?????)"
    For i = 0 To 5
        strMsg = strMsg & " " & fixedRands.Next_2(5, 10)
    Next                                              '???????????? 0 ???????????
    strMsg = strMsg & vbCrLf & "Next_3(20?????0?????):"
    For i = 0 To 5
        strMsg = strMsg & " " & fixedRands.Next_3(20)
    Next                                              '0.0 ? 1.0 ???????????(0.0 ?? 1.0 ?????????????)
    strMsg = strMsg & vbCrLf & "Nextdouble(0.0?1.0?????):"
    For i = 0 To 5
        strMsg = strMsg & " " & fixedRands.Nextdouble
    Next
    MsgBox strMsg
    Set fixedRands = Nothing
End Sub

Display More

**S O** · Aug 26th 2014

Re: CreateObject("System.Collections.ArrayList")

Nice post! This seems like one of those things where I'll probably start going out of my way to use it. :lol:

**pike** · Aug 26th 2014

Re: CreateObject("System.Collections.ArrayList")

few more

Code

.BinarySearch_2(Value)
.BinarySearch_3(value, CreateObject("System.Collections.CaseInsensitiveComparer")
.BinarySearch(index, count, value, Nothing)
.BinarySearch(index, count, value, CreateObject("System.Collections.CaseInsensitiveComparer"))

interesting that they can be set up store two billion items!

**pike** · Aug 27th 2014

Re: CreateObject("System.Collections.ArrayList")

example of an ArrayList of ArrayList

Code

Option Explicit
Sub test()
Dim MasterDataList As Object
Dim DataListOne As Object
Dim DatalistTwo As Object
Dim xItem
    Set MasterDataList = CreateObject("System.Collections.ArrayList")
    Set DataListOne = CreateObject("System.Collections.ArrayList")
    Set DatalistTwo = CreateObject("System.Collections.ArrayList")


    DataListOne.Add Split("a, b, c", ",")
    DataListOne.Add Split("D E F")
    DataListOne.Add Split("I, J, K", ",")


    DatalistTwo.Add Array("1", "2", "3")
    DatalistTwo.Add Array("4", "5", "6")
    DatalistTwo.Add Array("7", "8", "9")


    For Each xItem In DataListOne
        MsgBox xItem(2)
        MsgBox DataListOne(0)(0)
    Next xItem


    For Each xItem In DataListOne
        MsgBox Join(xItem)
    Next
    
    For Each xItem In DatalistTwo
        MsgBox Join(xItem)
    Next


    MasterDataList.Add DataListOne
    MasterDataList.Add DatalistTwo


    MsgBox MasterDataList(0)(1)(1)
    MsgBox MasterDataList(1)(1)(1)


End Sub

Display More

**pike** · Aug 31st 2014

Re: CreateObject("System.Collections.ArrayList")

Little more information on using CreateObject("System.Random") class methods and discriptions. Translated by Google Translate from japanese to english
http://tokidokidokin.com/2011/…%82%92%E4%BD%BF%E3%81%86/

Next method - Return a random number greater than or equal to 0
Next_2 method - will return a random number within a specified range
Next_3 method - Returns a random number greater than or equal to 0 less than the maximum value that you specify
NextDouble method - will return a random number between 1.0 and 0.0

**pike** · Aug 15th 2018

another system collectons

Code

Sub Stacklist()
    With CreateObject("System.Collections.Stack")
        .Push "Kyle"
        .Push "123"
        .Push "roy"
        .Push "pike"
        .Push "Simon"


        Debug.Print Join(.Toarray(), Chr(10))


        .pop


        Debug.Print .Peek


        Debug.Print Join(.Toarray(), Chr(10))


        Range("A1").Resize(.Count) = Application.Transpose(.Toarray())
        Range("C1").Resize(1, .Count) = .Toarray()


    End With


End Sub

Display More

CreateObject("System.Collections.ArrayList")

Participate now!

Share