Sorting of values is a quite common programming task. VBA has no native sorting method. You can use algorithms like QuickSort to sort the array in place. However, there is another, maybe easier way how to sort your data without using algorithms.
ArrayList is a dynamic array type whose size is dynamically increased as required. It is similar to VBA Collection type but offers more methods. One of them is a Sort method. Indexes in this collection are zero-based.
The ArrayList is not part of the standard VBA-library, but a part of System.Collections namespace of .NET Framework.
ArrayList object in VBA
There are basically two ways how to create an ArrayList instance in VBA.
- Early Binding which requires mscorlib.tlb reference. In your VBA editor go to Tools -> References and choose mscorlib.dll library.
- Late Binding where you do not need to reference any library.
I prefer to use Late Binding, because it seems to be more robust, portable and you avoid possible compatibility issues when moving between computers with a different version of the same library. Keep in mind that using Late Binding you lose IntelliSense features like code completion.
Dim firstList As ArrayList Set firstList = New ArrayList
Dim secondList As Object Set secondList = CreateObject("System.Collections.ArrayList")
Option Explicit Sub ArrayListExample() Const TOP_BOUND = 100 Dim i As Long ' early binding Dim firstList As ArrayList Set firstList = New ArrayList For i = 1 To TOP_BOUND ' populate with random numbers between 0 - 100 firstList.Add Int((100 - 0 + 1) * Rnd + 0) Next ' late binding Dim secondList As Object Set secondList = CreateObject("System.Collections.ArrayList") 'Create the ArrayList For i = 1 To TOP_BOUND ' populate with random [A-Z] characters secondList.Add Chr(Int((90 - 65 + 1) * Rnd + 65)) Next ' sort lists firstList.Sort secondList.Sort ' you can call also other methods of ArrayList class firstList.AddRange secondList firstList.InsertRange 0, secondList For i = 0 To firstList.Count - 1 Debug.Print firstList(i) Next Dim arr() ' populate array variable with ArrayList content arr = firstList.ToArray() End Sub
In the procedure ArrayListExample, we create two ArrayList objects. The first one is an example of early binding and it is filled with random numbers between 0 and 100. The second is late bound and it contains random characters between [A-Z]. We call Sort method on both of them. Later, we test other methods of ArrayList object, method AddRange (adds the elements to the end of the list) and method InsertRange (inserts the elements of a collection into the ArrayList at the specified index). In the end, we print the sorted content of the firstList into VBE Immediate window.
There are other useful ArrayList methods you can use to build your application like:
- Contains method: Determines whether an element is in the ArrayList.
- RemoveAt method: Removes the element at the specified index of the ArrayList.
- RemoveRange method: Removes a range of elements from the ArrayList.
- Reverse method: Reverses the order of the elements in the ArrayList or a portion of it.
Please check official Microsoft reference here.