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

Share