The VBA ArrayList is a much better alternative to the built-in VBA Collection. It contains much richer functionality such as sorting, converting to an array, removing all items etc.
Check out the quick guide for an overview of what the ArrayList does. The rest of this post provides examples of how to use the ArrayList.
Contents
- 1 Quick Guide to the VBA ArrayList
- 2 Description
- 3 Download the Source Code
- 4 Declare and Create the VBA ArrayList
- 5 Adding Items to the VBA ArrayList
- 6 Reading through an ArrayList
- 7 Sorting
- 8 Cloning the VBA ArrayList
- 9 Copying from an VBA ArrayList to an Array
- 10 Writing Directly to a Range
- 11 Array to a VBA ArrayList(1D)
- 12 Remove All Items from the ArrayList
- 13 What’s Next?
Quick Guide to the VBA ArrayList
| Task | Method | Parameters | Examples |
|---|---|---|---|
| Access item | Item | index - long integer | value = list.Item(0) value = list.Item(3) |
| Access item added last | Item | index - long integer | value = list.Item(list.Count - 1) |
| Access item added first | Item | index - long integer | value = list.Item(0) |
| Access all items(For Each) | N/A | N/A | Dim element As Variant For Each element In fruit Debug.Print element Next element |
| Access all items(For) | Item | index - long integer | Dim i As Long For i = 0 To list.Count - 1 Debug.Print list.item(i) Next i |
| Add item | Add | object or value | list.Add "Apple" list.Add "Pear" |
| Copy ArrayList to another ArrayList | Clone | None | Dim list2 As Object Set list2 = list.Clone |
| Copy to Array | ToArray | None | Dim arr As Variant arr = list.ToArray |
| Copy to a range(row) | ToArray | None | Sheet1.Range("A1").Resize(1, list.Count).Value = list.ToArray |
| Copy to a range(column) | ToArray | None | Sheet1.Range("A3").Resize(list.Count, 1).Value = WorksheetFunction.Transpose(list.ToArray) |
| Create | CreateObject | "System.Collections.ArrayList" | Dim list As Object Set list = CreateObject("System.Collections.ArrayList") |
| Declare | N/A | N/A | Dim list As Object |
| Find - check if item exists | Contains | item to find | list.Contains("Apple") |
| Find the position of an item in the ArrayList | IndexOf | 1. Item to find. 2. Position to start searching from. | Dim index As Long ' Search from 0 position index = fruit.IndexOf("Pear", 0) |
| Get number of items | Count | None | totalElements = list.Count |
| Insert Item | Insert | 1. Index - position to insert at. 2 Value - object or value to insert. | list.Insert 0, "Peach" ' First list.Insert 1, "Banana" ' Second list.Insert list.Count, "Orange" ' Last |
| Remove all Items | Clear | None | list.Clear |
| Remove item at position | RemoveAt | Index - position where the item is | list.RemoveAt 0 |
| Remove item by name | Remove | Item - the item to remove from the ArrayList | list.Remove "Apple" |
| Remove a range of Items | RemoveRange | 1. Index - starting postion. 2. Count - the number of items to remove. | list.RemoveRange 1,3 |
| Reverse the list | Reverse | None | list.Reverse |
| Sort in ascending | Sort | None | list.Sort |
Description
The ArrayList is similar to the VBA built-in Collection. It is not part of VBA, but it is in an external library which we can access easily. The ArrayList is the same one that is used in the language C#. As you would expect, the ArrayList has a built-in sort, array conversion and other functionality that you would expect in a modern programming language. For the purpose of this article, I will refer to it as the VBA ArrayList.
Download the Source Code
Declare and Create the VBA ArrayList
Like all external libraries we can create the ArrayList using early and late binding.
Late Binding
We use CreateObject to create the ArrayList using late binding:
' https://excelmacromastery.com/ Sub UsingArrayList() Dim coll As Object Set coll = CreateObject("System.Collections.ArrayList") End Sub
The disadvantage of late binding is that we don’t have access to the Intellisense. The advantage is that it is better to use when distributing a VBA application to a user.
Early Binding
Update 12-Nov-2019: Intellisense doesn’t currently work for the ArrayList.
Early binding allows use to use the Intellisense to see what is available to use. We must first add the type library as a reference and then select it from the reference list. We can use the following steps to do this:
- Select Tools and then References from the menu.
- Click on the Browse.
- Find the file mscorlib.tlb and click Open. It should be in a folder like this C:\Windows\Microsoft.NET\Framework\v4.0.30319.
- Scroll down the list and check mscorlib.dll.
- Click Ok.
You can now use the following code to declare the ArrayList using early binding:
Dim coll As New ArrayList
VBA ArrayList Automation Error
You may encounter the VB Run-time Error ‘-2146232576 Automation Error’ when trying to get the ArrayList to work. Or sometimes your code has been working for a long time and then suddenly this error appears.
This is caused by not having the correct .Net Framework version installed. The correct version is 3.5. It doesn’t matter if you have a later version like 4.7, you must have 3.5 installed.
Adding Items to the VBA ArrayList
Adding items to the ArrayList is very similar to how we add them to the Collection. We use the Add method:
' https://excelmacromastery.com/ Sub AddingToList() Dim coll As Object Set coll = CreateObject("System.Collections.ArrayList") ' Add items coll.Add "Apple" coll.Add "Watermelon" coll.Add "Pear" coll.Add "Banana" ' Insert to first position coll.Insert 0, "Plum" End Sub
Reading through an ArrayList
We read through the ArrayList similar to the VBA Collection except that we read from zero to Count-1 rather than from one to Count.
Note: We will use this PrintToImmediateWindow sub in the follow examples to show the contents of the array after the various operations.
' Print all items to the Immediate Window(Ctrl + G) ' Items must be basic data type e.g. Long, String, Double ' https://excelmacromastery.com/ Sub PrintToImmediateWindow(coll As Object) Dim i As Long For i = 0 To coll.Count - 1 Debug.Print coll(i) Next i End Sub
We can use the For Each loop with the VBA ArrayList just like we use it with a Collection:
' Print all items to the Immediate Window(Ctrl + G) ' Items much be basic data type e.g. Long, String, Double ' https://excelmacromastery.com/ Sub PrintToImmediateWindowEach(coll As Object) Dim item As Variant For Each item In coll Debug.Print item Next item End Sub
You can download all the code examples at the top of this post.
Sorting
Sort will sort the VBA ArrayList in ascending order.
To sort in descending order simply use Reverse after Sort.
The following code shows an example of sorting in both ascending and descending order:
' https://excelmacromastery.com/ Sub Sorting() Dim coll As Object Set coll = CreateObject("System.Collections.ArrayList") ' Add items coll.Add "Apple" coll.Add "Watermelon" coll.Add "Pear" coll.Add "Banana" coll.Add "Plum" ' Sort coll.Sort Debug.Print vbCrLf & "Sorted Ascending" ' Add this sub from "Reading through the items" section PrintToImmediateWindow coll ' Reverse sort coll.Reverse Debug.Print vbCrLf & "Sorted Descending" PrintToImmediateWindow coll End Sub
' https://excelmacromastery.com/ Sub PrintToImmediateWindow(coll As Object) Dim i As Long For i = 0 To coll.Count - 1 Debug.Print coll(i) Next i End Sub
Cloning the VBA ArrayList
We can create a copy of the ArrayList by using the Clone method. This creates a brand new copy of the ArrayList.
It’s not the same as assigning the variable which means both variables point to the same ArrayList e.g.
' Both variables point to the same ArrayList Set coll2 = coll
We use Clone like this:
' https://excelmacromastery.com/ Sub Cloning() ' Create the ArrayList Dim coll1 As Object Set coll1 = CreateObject("System.Collections.ArrayList") ' Add items coll1.Add "Apple" coll1.Add "Watermelon" coll1.Add "Pear" coll1.Add "Banana" coll1.Add "Plum" ' Creates a copy of the original ArrayList Dim coll2 As Object Set coll2 = coll1.Clone ' Remove all items from coll1 coll1.Clear ' Add PrintToImmediateWindow sub from "Reading through the items" section Debug.Print vbCrLf & "coll1 Contents are:" PrintToImmediateWindow coll1 Debug.Print vbCrLf & "coll2 Contents are:" PrintToImmediateWindow coll2 End Sub
' https://excelmacromastery.com/ Sub PrintToImmediateWindow(coll As Object) Dim i As Long For i = 0 To coll.Count - 1 Debug.Print coll(i) Next i End Sub
Copying from an VBA ArrayList to an Array
We can copy from the ArrayList to an array in one line using the ToArray method:
' https://excelmacromastery.com/ Sub CopyToArray() ' Declare and Create ArrayList Dim coll As Object Set coll = CreateObject("System.Collections.ArrayList") ' Add items coll.Add "Apple" coll.Add "Watermelon" coll.Add "Pear" coll.Add "Banana" coll.Add "Plum" ' Copy to array Dim arr As Variant arr = coll.ToArray ' Print the array Debug.Print vbCrLf & "Printing the array contents:" PrintArrayToImmediate arr End Sub
' Prints the contents of a one dimensional array ' to the Immediate Window(Ctrl + G) ' https://excelmacromastery.com/ Sub PrintArrayToImmediate(arr As Variant) Dim i As Long For i = LBound(arr) To UBound(arr) Debug.Print arr(i) Next i End Sub
You can download all the code examples at the top of this post.
Writing Directly to a Range
One of the biggest advantages of the ArrayList is that we can write the contents directly to a range.
The code below writes the contents to both a row and a column:
' Writes the contents of an ArrayList to a worksheet range ' https://excelmacromastery.com/ Sub ClearArrayList() ' Declare and Create ArrayList Dim fruit As Object Set fruit = CreateObject("System.Collections.ArrayList") ' Add items fruit.Add "Apple" fruit.Add "Watermelon" fruit.Add "Pear" fruit.Add "Banana" fruit.Add "Plum" fruit.Add "Peach" ' ' Clean existing data Sheet1.Cells.ClearContents ' Write to a row Sheet1.Range("C1").Resize(1, fruit.Count).Value = fruit.toArray ' Write to a column Sheet1.Range("A1").Resize(fruit.Count, 1).Value = WorksheetFunction.Transpose(fruit.toArray) End Sub
Array to a VBA ArrayList(1D)
As we have seen, there is an in-built function ToArray which will copy from an ArrayList to an Array.
If we want to copy from an Array to an ArrayList we need to create our own function which I have done below. Because we read through the items one at a time, it may be a bit slow if we have a lot of data:
' https://excelmacromastery.com/ Function ArrayToArrayList(arr As Variant) As Object ' Check that array is One Dimensional On Error Resume Next Dim ret As Long ret = -1 ret = UBound(arr, 2) On Error Goto 0 If ret <> -1 Then Err.Raise vbObjectError + 513, "ArrayToArrayList" _ , "The array can only have one 1 dimension" End If ' Create the ArrayList Dim coll As Object Set coll = CreateObject("System.Collections.ArrayList") ' Add items to the ArrayList Dim i As Long For i = LBound(arr, 1) To UBound(arr, 1) coll.Add arr(i) Next i ' Return the new ArrayList Set ArrayToArrayList = coll End Function
You can use it like this:
' https://excelmacromastery.com/ Sub ReadFromArray1D() Dim arr(1 To 3) As Variant arr(1) = "PeterJ" arr(2) = "Jack" arr(3) = "Jill" ' Create the ArrayList Dim coll As Object Set coll = ArrayToArrayList(arr) PrintToImmediateWindow coll End Sub
Remove All Items from the ArrayList
We can remove all the items from an ArrayList by using the Clear function:
' https://excelmacromastery.com/ Sub ClearArrayList() ' Declare and Create ArrayList Dim coll As Object Set coll = CreateObject("System.Collections.ArrayList") ' Add items coll.Add "Apple" coll.Add "Watermelon" coll.Add "Pear" coll.Add "Banana" coll.Add "Plum" Debug.Print vbCrLf & "The number of items is: " & coll.Count ' Remove all item coll.Clear Debug.Print "The number of items is: " & coll.Count End Sub
You can download all the code examples at the top of this post.
What’s Next?
Free VBA Tutorial If you are new to VBA or you want to sharpen your existing VBA skills then why not try out the The Ultimate VBA Tutorial.
Related Training: Get full access to the Excel VBA training webinars and all the tutorials.
(NOTE: Planning to build or manage a VBA Application? Learn how to build 10 Excel VBA applications from scratch.)
Paul—-This is amazimg. Thank you!
Thanks, Spencer. Glad you like it.
Thank you Paul, learn a lot from your blog.
Wonderful.
Is the ArrayList Mac compatible? I was looking for an alternative to the Collection as discovered Collections are extremely slow at cleaning up objects placed in the Collection Item and exploring if using an Array would help. Note Will be adding, deleting, iterating on large datasets with Key, Item pairs. A dictionary would be great but isn’t Mac compatible.
It’s not compatible with the Mac as it’s part of the .Net library.
Check out the dictionary class written for Mac and, of course, works on.a PC.
https://sysmod.wordpress.com/2011/11/24/dictionary-vba-class-update/
I’ve scrapped using the ArrayList idea as realized in VBA, after testing various data structures, as too all were slow at cleaning up large datasets that contain objects. You start to notice the effects once got 100,000 custom objects and appears to exponentially take longer to dereference all the objects. It must be something to do with VBA and it’s garbage collection.
Hi Mark, I think cleaning up that number of objects would be slow in many languages.
Hi Mark, if the memory capacity is not the concern, why not to create a new collection instead of cleaning it up item by item and let garbage collector take care about the rest?
Instead of:
Do Until Col.Count = 0
Col.Remove(1)
Loop
Do This:
Set Col = New Collection
it is the fastest way how to “Clean Up” then collection.
Paul,
You mention that Early Binding allows Intellisense, but I have selected mscorlib.dll from my ms Access vba references and –no intellisense. I’m trying to get some vba based demos of the various data structures in Systems.Collections ( Stack, Queue, ArrayList…).
I enjoy your site videos and your more recent youtube (class module, collections, dictionary)
Thanks in advance.
jack
Hi Jack,
The Intellisense doesn’t appear to work with this library anymore. I have tried with all the different .Net libraries and the result is the same.
It may have been caused by a Windows update. I will post more information when I know more about the cause.
-Paul
Hello Paul
I am currently using an array of structures with each array member representing an object. I would like to use something more object oriented. I have need to sort, determine existence of a object, create references to another similar array of structures, etc.
Will an array list support an array of structures? And will you cover that in Wednesday night’s tutorial?
Thanks in advance
Mike
My objects have multiple
You can add objects to any data structures such as Arrays, Collections, Dictionaries, ArrayList and so on.
Hello Paul
Is there a way to return an ArrayList from a function, like a bellow?
This is the code I have implemented
Public Function ReturnArrayList() As ArrayList // or return type As Variant
Dim alist As ArrayList
Set alist = New ArrayList // Early Binding
alist.Add “a”
alist.Add “b”
alist.Add “c”
alist.Add “240”
ReturnArrayList = alist
End Sub
Sub GetArrayList()
Dim alist As ArrayList
alist = ReturnArrayList()
MsgBox alist(0)
End Sub
Thank you,
Nishan
Hi Nishan,
You need to use Set when returning an object.
I cannot reference mscorlib.dll in any way. I invariable receive the message “Can’t add a reference to the specified file”. I installed ‘NET Framework 3.5 SP1’, restarted laptop, but the same result… I work on Excel 2016 64 bit…
I have the same problem. I too use 64 bit office.
I am using Office 365 x64.
In the specified folder (C:\Windows\Microsoft.NET\Framework\v4.0.30319) there are both mscorlib.dll and mscolib.tbl. Trying to open mscorlib.dll gave me an error (“Can’t add a reference to the specified file.”). Opening mscorlib.tbl worked, and then I found mscorlib.dll in the reference list window and added it. But as Paul said, Intelisense is not available for this object, so the only minor advantages to early binding is that it’s easier to declare (dim al as ArrayList VS dim al as object: CreateObject(“System.Collections.ArrayList”) and a minor boost to speed.
Thanks for the feedback Alexandru.
In my system is not named “mscorlib.tbl” but “mscorlib.tlb”, but as yous said, it provides almost no functionality and has a few disadvantages so better use late binding as suggested.
Hello Paul, great tutorial.
Can you go through multidimensional arrays?
I am trying to add a List as New Array List to MultiList which is also an Array List
MultiList.add (List)
Does not work, thoughts?
What do you mean “does not work”? What is the error?
Awsome explination as always … One question though, how can I get distinct values from an array or range ignoring the case sensitive repetitive words ? In dictionaries, we can use “dict.CompareMode = vbTextCompare”. Any such thing for ArrayList ?
In the quickguide,
Access item added last Item index – long integer value = list.Item(coll.Count – 1)
Did you mean to say list.count instead of Coll.Count?
Yes. I’ve updated that now.
Paul – thank you very much for your wonderful article on ArrayLists. I was able to learn how to successfully use them and I’ve implemented them in a number of my VBA programs.
I have one question for you: does your method of writing an ArrayList to an Excel range work if the values stored in the array are strings? I was getting a type mismatch error when I tried to write string values using the code with the Transpose worksheet function (error didn’t occur with Doubles). I was able to get it to work by using a loop based on the size of the ArrayList and using Range().Value = ArrayList(i).Value.
Thanks again!
Paul Kelly, muito obrigado pelo conteúdo abordado, é mais uma ferramenta que vale utilizar para o nosso portfólio. Um ponto importante e que vi alguns usuários mencionar foi a respeito de performance!
Seria interessante algo abordando sobre a Biblioteca QUEUE.
I might do something with the Queue at some point.
Hi Paul,
I have a arraylist called OrdersList, containing 39 data rows by 8 columns.
When I write ?Orderslist.Count in the Debug Window, it returns 39
This is right.
Then I sort the OrdersList and would like to export the sorted arraylist.
This is not working, get an Run-time error 13 – Type mismatch
Is there a way to retreive the multi dimensional arraylist ‘records’ so that they can be exported to a new worksheet?
It cannot be written out in one go like an array. You will have to read through the list and write out each part.
Hello Paul,
Could you explain more about the need for .NET Framework 3.5. I encounter the automation bug on a collegue laptop and it looks like there is only the 4.0 Framework installed. As we do not have admin autorisation to install software, does it mean I simply cannot use Arrays?
Thanks
Thanks for this wonderful explanation.
One tip: when writing to a worksheet, WorksheetFunction.Transpose will only work for strings <= 255 characters.
Awesome guide! I keep looking for your articles if I want to search something for VBA.
BTW, is it really only possible to have a Key-value pair list with Dictionary? are there other alternatives than a Dictionary? I want to use arraylist because of its multiple functions. But I want to save it with a Key, so it will be like a mutidimensional arraylist of arraylist/dictionary inside. with a key to find the right arraylist
Thanks Pampi. The dictionary is the only data structure with a key-value pair. You can store arraylists as the elements in the dictionary.
I have always used collections/dictionaries because they are 100x faster than arraylists when adding items.
ArrayLists have some nice features, but I had to stop using them because of this automation error that you mentioned. It was easier and quicker for me to switch to collections / dictionaries / arrays than negotiating to provide us the proper .Net framework version (IT: “Your supervisor must make a request”, Supervisors: “What? You said you can do it with Excel” “We can’t buy another software”, “But all of you have got Excel, you must have made it wrong”, “How do you spell it?”)
Very true. I’m always conscious that it’s not part of excel and may cause problems if the application will be distributed to a lot of users.
Hi paul, do you know which .net method can be used by createobject?
because i faild create System.Net.WebClient, there must be some limitation when creating object, so i googled but barely found some tips.
Hi, I don’t know an easy way of figuring out which will work and which won’t. I usually just try it with CreateObject to see if it works or not.
Hi, Paul.
Thanks for your articles, they are very usefull for VBA-fans.
Two questions: Is it possible to put into ArrayList an object (e.g, some user class instance) – like in a dictionary?
And how could I if the above possible to transpose such ArrayList to Worksheet?
Hi Paul,
Thanks a lot, this helped me in my current project.
However, does VBA support the use of a comparator in the Sort method? I would like to sort a list of Dictionary’s with a custom sorting criteria.
Cheers,
John
No. Unfortunately VBA doesn’t support Comparators.
The “Contains” method doesn’t seem to work when passing a string. It does however work if I set it to the object. For instance:
y = myArrayList(2)
Debug.Print myArrayList.Contains(y) ‘Returns False
Set x = myArrayList(2)
Debug.Print myArrayList.Contains(x) ‘Returns True
Any ideas on how to get the .Contains method to work when checking a sting?
Found my issue. I was loading the arraylist from the worksheet using the “Cells” function. Apparently, the ArrayList was taking it as an object instead of a string. I put Cstr around the cells function and everything works now.
Thanks for the feedback Gary.
When using Cells() or Range() you should use the Value property when adding to an Array, Arraylist, Collection etc.
e.g. list.Add Cells(1,1).Value
-Paul
Hi Paul,
Excellent site – always recommend it .
If you want to add the unlisted arraylist methods!!
Link below
https://www.ozgrid.com/forum/index.php?thread/110834-createobject-system-collections-arraylist/
Regards Peter
In the section re: Early Binding, I believe “mscorlib.tlb” is incorrect. I think it should be “mscorlib.dll”.
You can use type libraries(tlb files) to connect to COM libraries.
It would be nice if we see articles about the other data structures from the same library: SortedList, Queue and Stack.
I am having an issue with creating an array list as noted in this post. It works perfectly fine on my computer (the computer used to write the macro which uses this approach of creating an array); however, once I have others on my team test the macro on their computer, it errors on the line the variable is set (i.e., Set ColumnsList = CreateObject(“System.Collections.ArrayList”)). I can’t seem to figure out why it works on my computer, but not theirs. Any help would be greatly appreciated.
Thank you for your helpful guide!
Since today I am getting the mentioned Automation Error, even though I have .NET 3.5 installed and enabled.
Any thoughts what could cause it?
This is good… similar to lists in other languages, but what would be even better and far more useful is a 2D array list. Something like a dataframe, where one could sort and filter based on a column. Is this possible in VBA? I know you can create a 2D array, but then you don’t have any built in methods.
Unfortunately there is nothing like this in VBA.