In this Article
- Using a VBA ArrayList
- Distributing Your Excel Application Containing an Array List
- Scope of an Array List Object
- Populating and Reading from Your Array List
- Editing and Changing Items in an Array List
- Adding an Array of Values to an Array List
- Reading / Retrieving a Range of Items from an Array List
- Searching for Items Within an Array List
- Insert and Remove Items
- Sorting an Array List
- Cloning an Array List
- Copying a List Array into a Conventional VBA Array Object
- Copying a List Array into a Worksheet Range
- Empty All Items from an Array List
- Array List Methods Summary for Excel VBA
Using a VBA ArrayList
An ArrayList is a VBA object that can be used to store values. It is similar to a Collection object, but it has far greater flexibility from a programming point of view. Let’s discuss some difference between ArrayLists and Collections and Arrays.
- The Collection object only has two methods (Add, Remove) and two properties (Count, Item) whereas an Array List has many more.
- The Collection object is read only. Once values have been added, the indexed value cannot be changed, whereas on an Array List, editing is possible.
- The ArrayList object expands and contracts in size according to how many items that it contains. It does not need to be dimensioned before use like an Array.
- The ArrayList is one dimensional (same as the Collection object) and the default data type is Variant, which means that it will accept any type of data, whether it be numeric, text, or date.
In many ways the Array List addresses a number of shortcomings of the Collection object. It is certainly far more flexible in what it can do.
The Array List object is not part of the standard VBA library. You can use it in your Excel VBA code by using late or early binding.
Sub LateBindingExample() Dim MyList As Object Set MyList = CreateObject("System.Collections.ArrayList") End Sub
Sub EarlyBindingExample() Dim MyList As New ArrayList End Sub
In order to use the early binding example, you must first enter a reference in VBA to the file ‘mscorlib.tlb’
You do this by selecting ‘Tools | References ‘ from the Visual Basic Editor (VBE) window. A pop-up window will appear with all available references. Scroll down to ‘mscorlib.dll’ and tick the box next to it. Click OK and that library is now part of your project:
One of the big drawbacks of an Array List object is that it does not have ‘Intellisense’. Normally, where you are using an object in VBA such as a range, you will see a pop-up list of all the available properties and methods. You do not get this with an Array List object, and it sometimes needs careful checking to make sure that you have spelt the method or property correctly.
Also, if you press F2 in the VBE window, and search on ‘arraylist’, nothing will be displayed, which is not very helpful to a developer.
Your code will run considerably faster with early binding, because it is all compiled up front. With late binding, the object has to be compiled as the code runs
Distributing Your Excel Application Containing an Array List
As already pointed out, the ArrayList object is not part of Excel VBA. This means that any of your colleagues that you distribute the application to must have access to the file ‘mscorlib.tlb’
This file is normally located in:
It could be worth writing some code (using the Dir method) to check that this file exists when a user loads the application so that they experience a ‘soft landing’ if not found. If it is not present, and the code runs then errors will occur.
Also, the user must have the correct .Net Framework version installed. Even if the user has a later version, V3.5 must be installed otherwise your application will not work
Scope of an Array List Object
In terms of scope, the Array List object is only available whilst the workbook is open. It does not get saved when the workbook is saved. If the workbook is re-opened then the Array List object needs to be re-created using VBA code.
If you want your Array List to be available to all the code in your code module, then you need to declare the Array List object in the Declare section at the very top of the module window
This will ensure that all your code within that module can access the Array List. If you want any module within your workbook to access the Array List object, then define it as a global object.
Global MyCollection As New ArrayList
Populating and Reading from Your Array List
The most basic action that you want to take is to create an array list, put some data into it and then prove that the data can be read. All the code examples in this article assume that you are using early binding, and have added ‘mscorlib.tlb’ to the VBA references, as described above.
Sub ArrayListExample() 'Create new array list object Dim MyList As New ArrayList 'Add items to list MyList.Add "Item1" MyList.Add "Item2" MyList.Add "Item3" 'Iterate through array list to prove values For N = 0 To MyList.Count - 1 MsgBox MyList(N) Next N End Sub
This example creates a new ArrayList object, populates it with 3 items, and the iterates through the list displaying each item.
Note that the ArrayList index starts at 0, not 1, so you need to subtract 1 from the Count value
You can also use a ‘For…Each’ loop to read the values:
Sub ArrayListExample() 'Create new array list object Dim MyList As New ArrayList 'Add items to list MyList.Add "Item1" MyList.Add "Item2" MyList.Add "Item3" 'Iterate through array list to prove values For Each I In MyList MsgBox I Next I End Sub
Editing and Changing Items in an Array List
A major advantage of an Array List over a Collection is that the items in the list can be edited and changed within your code. The Collection object is read only whereas the Array List object is read / write.
Sub ArrayListExample() 'Create new array list object Dim MyList As New ArrayList 'Add items to list MyList.Add "Item1" MyList.Add "Item2" MyList.Add "Item3" 'Change item 1 from ‘Item2’ to ‘Changed’ MyList(1) = "Changed" 'Iterate through array list to prove change worked For Each I In MyList 'Display item name MsgBox I Next I End Sub
In this example, the second item, ‘Item2’ is altered to the value ‘Changed’ (remember that the index starts at 0). When the iteration is run at the end of the code, the new value will be displayed.
Adding an Array of Values to an Array List
You can enter values into your Array List by using an array containing a list of these values or references to cell values on a worksheet
Sub AddArrayExample() 'Create Array list object Dim MyList As New ArrayList 'iterate through array values adding them to the array list For Each v In Array("A1", "A2", "A3") 'Add each array value to list MyList.Add v Next 'iterate through array values with worksheet references adding them to the array list For Each v In Array(Range("A5").Value, Range("A6").Value) MyList.Add v Next 'Iterate through array list to prove values For N = 0 To MyList.Count – 1 'Display list item MsgBox MyList.Item(N) Next N End Sub
Reading / Retrieving a Range of Items from an Array List
By using the GetRange method on an Array List, you can specify a rage of consecutive items to be retrieved. The two parameters required are the starting index position and the number of items to be retrieved. The code populates a second Array List object with the sub set of items which can then be read separately.
Sub ReadRangeExample() 'Define objects Dim MyList As New ArrayList, MyList1 As Object 'Add items to ‘MyList’ object MyList.Add "Item1" MyList.Add "Item2" MyList.Add "Item3" MyList.Add "Item6" MyList.Add "Item4" MyList.Add "Item7" 'Capture 4 items in ‘MyList’ starting at index position 2 Set MyList1 = MyList.GetRange(2, 4) 'Iterate through the object ‘MyList1’ to display the sub set of items For Each I In MyList1 'Display item name MsgBox I Next I End Sub
Searching for Items Within an Array List
You can test whether a named item is in your list by using the ‘Contains’ method. This will return True or False
You can also find the actual index position by using the ‘IndexOf’ method. You need to specify the start index for the search (usually 0). The return value is the index of the first instance of the found item. You can then use a loop to change the starting point to the next index value to find further instances if there are several duplicate values.
If the value is not found then a value of -1 is returned
This example demonstrates using ‘Contains’, item not found, and looping through the array list to find the position of all duplicate items:
Sub SearchListExample() 'Define array list and variables Dim MyList As New ArrayList, Sp As Integer, Pos As Integer 'Add new items including a duplicate MyList.Add "Item1" MyList.Add "Item2" MyList.Add "Item3" MyList.Add "Item1" 'Test for “Item2” being in list - returns True MsgBox MyList.Contains("Item2") 'Get index of non-existent value – returns -1 MsgBox MyList.IndexOf("Item", 0) 'Set the start position for the search to zero Sp = 0 'Iterate through list to get all positions of ‘Item1” Do 'Get the index position of the next ‘Item1’ based on the position in the variable ‘Sp’ Pos = MyList.IndexOf("Item1", Sp) 'If no further instances of ‘Item1’ are found then exit the loop If Pos = -1 Then Exit Do 'Display the next instance found and the index position MsgBox MyList(Pos) & " at index " & Pos 'Add 1 to the last found index value – this now becomes the new start position for the next search Sp = Pos + 1 Loop End Sub
Note that the search text used is case sensitive and wild cards are not accepted.
Insert and Remove Items
If you do not wish to add your items onto the end of the list, you can insert them at a particular index position so that the new item is in the middle of the list. The index numbers will be automatically adjusted for the subsequent items.
Sub InsertExample() 'Define array list object Dim MyList As New ArrayList 'Add items to array list MyList.Add "Item1" MyList.Add "Item2" MyList.Add "Item3" MyList.Add "Item1" 'Insert ‘Item6’ at index position 2 MyList.Insert 2, "Item6" 'Iterate through items in the array list to show new order and index position For N = 0 To MyList.Count - 1 MsgBox MyList(N) & " Index " & N Next N End Sub
In this example, ‘Item6’ is added into the list at index position 2, so the ‘item3’ which was at index position 2 now moves to index position 3.
An individual item can be removed by using the ‘Remove’ method.
Note that there is no error produced if the item name is not found. All the subsequent index numbers will be changed to suit the removal.
If you know the index position of the item you can use the ‘RemoveAt’ method e.g.
Note that if the index position given is greater than the number of items in the array list, then an error will be returned.
You can remove a range of values from the list by using the ‘RemoveRange’ method. The parameters are the starting index and then the number of items to remove e.g.
MyList.RemoveRange 3, 2
Note that you will get an error in your code if the number of items offset from the start value is greater than the number of items in the array list.
In both the ‘RemoveAt’ and ‘RemoveRange’ methods, some code would be advisable to check whether the index numbers specified are greater than the total number of items in the array list in order to trap any possible errors. The ‘Count’ property will give the total number of items in the array list.
Sub RemoveExample() 'Define array list object Dim MyList As New ArrayList 'Add items to array list MyList.Add "Item1" MyList.Add "Item2" MyList.Add "Item3" MyList.Add "Item1" MyList.Add "Item4" MyList.Add "Item5" 'Insert ‘Item6’ at index position 2 MyList.Insert 2, "Item6" 'Remove ‘Item2’ MyList.Remove "Item2" 'Remove ‘Item’ – this does not exist in the array list but does not error MyList.Remove "Item" 'Remove the item at index position 2 MyList.RemoveAt 2 'Remove 2 consecutive items starting at index position 2 MyList.RemoveRange 3, 2 'Iterate through the array list to show what is left and what index position it is now in For N = 0 To MyList.Count - 1 MsgBox MyList(N) & " Index " & N Next N End Sub
Note that if you are using the ‘RemoveAt’ to remove an item at a specific position then as soon as that item is removed, all the subsequent index positions are altered. If you have multiple removals using the index position, then a good idea is to start with the highest index number and step backwards down to position zero so that you will always be removing the correct item. In this way you will not have the problem
Sorting an Array List
Another big advantage over a collection is that you can sort the items into ascending or descending order.
The Array List object is the only object in Excel VBA with a sorting method. The sorting method is very fast and this can be an important consideration for using an Array List.
In the collection object, some ‘out of the box’ thinking was required to sort all the items, but with an array list, it is very simple.
The ‘Sort’ method sorts in ascending order, and the ‘Reverse’ method sorts in descending order.
Sub ArrayListExample() 'Create Array List object Dim MyList As New ArrayList 'Add items in a non-sorted order MyList.Add "Item1" MyList.Add "Item3" MyList.Add "Item2" 'Sort the items into ascending order MyList.Sort 'Iterate through the items to show ascending order For Each I In MyList 'Display item name MsgBox I Next I 'Sort the items into descending order MyList.Reverse 'Iterate through the items to show descending order For Each I In MyList 'Display item name MsgBox I Next I End Sub
Cloning an Array List
An array list has the facility to create a clone or copy of itself. This is useful if a user makes changes to the items using a front end and your VBA code, but you need to keep a copy of the items in their original state as a backup.
This could provide the user with an ‘Undo’ feature. They may have made the changes, and wish to revert back to the original list.
Sub CloneExample() 'Define two objects – array list and an object Dim MyList As New ArrayList, MyList1 As Object 'Populate first object with items MyList.Add "Item1" MyList.Add "Item2" MyList.Add "Item3" 'Copy Mylist to MyList1 Set MyList1 = MyList.Clone 'Iterate through MyList1 to prove cloning For Each I In MyList1 'Display item name MsgBox I Next I End Sub
‘MyList1’ now contains all the items from ‘MyList’ in the same order
Copying a List Array into a Conventional VBA Array Object
You can use a simple method to copy the array list into a normal VBA array:
Sub ArrayExample() 'Create array list object and a standard array object Dim MyList As New ArrayList, NewArray As Variant 'Populate array list with items MyList.Add "Item1" MyList.Add "Item2" MyList.Add "Item3" 'Copy the array list to the new array NewArray = MyList.ToArray 'Iterate through the new array – note that the array list count provides the maximum index For N = 0 To MyList.Count – 1 'Display item name MsgBox NewArray(N) Next N End Sub
Copying a List Array into a Worksheet Range
You can copy your array list to a specific worksheet and cell reference without the need to iterate through the array list. You need only specify the first cell reference
Sub RangeExample() 'Create new array list object Dim MyList As New ArrayList 'Add items to list MyList.Add "Item1" MyList.Add "Item2" MyList.Add "Item3" 'Clear the target sheet Sheets("Sheet1").UsedRange.Clear 'Copy items across a row Sheets("Sheet1").Range("A1").Resize(1, MyList.Count).Value = MyList.toArray 'Copy items down a column Sheets("Sheet1").Range("A5").Resize(MyList.Count, 1).Value = _ WorksheetFunction.Transpose(MyList.toArray) End Sub
Empty All Items from an Array List
There is a simple function (Clear) to clear the array list completely
Sub ClearListExample() 'Create array list object Dim MyList As New ArrayList 'Add new items MyList.Add "Item1" MyList.Add "Item2" MyList.Add "Item3" 'Show count of items MsgBox MyList.Count 'Clear all items MyList.Clear 'Show count of items to prove that clear has worked MsgBox MyList.Count End Sub
This example creates items in an array list and then clears the array list. Message boxes prove before and after the number of items in the array list.
Array List Methods Summary for Excel VBA
|Add / Edit item||Value||MyList.Add “Item1”|
|Clone an Array List||None||Dim MyList As Object|
|Set MyList2 = MyList.Clone|
|Copy to Array||None||Dim MyArray As Variant|
|MyArray = MyList.ToArray|
|Copy to a worksheet range(row)||None||Sheets(“Sheet1”).Range(“A1”).Resize(1, MyList.Count).Value = MyList.ToArray|
|Copy to a worksheet range(column)||None||Sheets(“Sheet1”).Range(“A3”).Resize(MyList.Count, 1).Value = WorksheetFunction.Transpose(MyList.ToArray)|
|Create||“System.Collections.ArrayList”||Dim MyList As Object|
|Set MyList = CreateObject(“System.Collections.ArrayList”)|
|Declare||N/A||Dim MyList As Object|
|Find / check if item exists||Item to find||MyList.Contains(“Item2”)|
|Find the position of an item in the ArrayList||1. Item to find.||Dim IndexNo As Long|
|2. Position to start searching from.||IndexNo = MyList.IndexOf(“Item3”, 0)|
|IndexNo = MyList.IndexOf(“Item5”, 3)|
|Get number of items||None||MsgBox MyList.Count|
|Insert Item||1. Index – position to insert at.||MyList.Insert 0, “Item5”|
|2 Value – object or value to insert.||MyList.Insert 4, “Item7”|
|Read item||Index – long integer||MsgBox MyList.Item(0)|
|Read item added last||Index – long integer||MsgBox MyList.Item(list.Count – 1)|
|Read item added first||Index – long integer||MsgBox MyList.Item(0)|
|Read all items(For Each)||N/A||Dim element As Variant|
|For Each element In MyList|
|Read all items(For)||Index – long integer||Dim i As Long|
|For i = 0 To MyList.Count – 1|
|Remove all Items||None||MyList.Clear|
|Remove item at position||Index position where the item is||MyList.RemoveAt 5|
|Remove item by name||The item to remove from the ArrayList||MyList.Remove “Item3”|
|Remove a range of Items||1. Index – starting postion.||MyList.RemoveRange 4,3|
|2. Count – the number of items to remove.|
|Sort in Descending Order||None||MyList.Reverse|
|Sort in ascending order||Non||MyList.Sort|