How to create a one-dimensional array in VBA. ArrayList

When I first met this class, I thought “Why? After all, there are simple arrays”. And then I tried it and I don’t know how I lived without it before.

Let’s start with an example

Let’s assume that on the active sheet in column 1 there is a list of employees’ full names.

Our task is to collect only unique full names in an array and sort it in descending order (well, such a slightly strange task). Let’s solve it first without using ArrayListand at the end we compare the result.
To get unique values, create a function GetDistinctItems and pass the column with the full name to it. In the function itself, we run through the loop For Each for all full names and add unique ones to the object Buffer (Dictionary). Next method Keys extracting elements into an additional function DescendingSort (we use bubble sort) and get the sorted values ​​into a variable Sortedwhich we return as the result of the function.

Public Sub Main()
    Dim FullNameColumn As Range
    Set FullNameColumn = ActiveSheet.UsedRange.Columns(1) ' Получаем первый столбец.

    Dim DistinctList As Variant
    DistinctList = GetDistinctItems(FullNameColumn) ' Передаем диапазон в функцию.
    Debug.Print Join(DistinctList, vbCrLf) ' Выводим результат.
End Sub

Public Function GetDistinctItems(ByRef Range As Range) As Variant
    Dim Data As Variant: Data = Range.Value ' Преобразуем диапазон в массив.
    Dim Buffer As Object: Set Buffer = CreateObject("Scripting.Dictionary") ' Создаем объект Dictionary.

    Dim Item
    For Each Item In Data
        If Not Buffer.Exists(Item) Then Buffer.Add Item, Empty ' Проверяем наличие элемента и добавляем если отсутствует.
    Next
    
    Dim Sorted As Variant
    Sorted = DescendingSort(Buffer.Keys()) ' Сортируем функцией DescendingSort.
    GetDistinctItems = Sorted ' Возвращаем результат.
End Function

Public Function DescendingSort(ByRef Data As Variant) As Variant
    Dim i As Long
    For i = LBound(Data) To UBound(Data) - 1
        Dim j As Long
        For j = i + 1 To UBound(Data)
            If Data(i) < Data(j) Then
                Dim Temp As Variant
                Temp = Data(j)
                Data(j) = Data(i)
                Data(i) = Temp
            End If
        Next
    Next

    DescendingSort = Data
End Function

Trivially? Quite. Compact? Well, in general, yes, but in the end we will write even more compactly, and at the same time we will solve the problem of writing a new function, if suddenly the result needs to be sorted in ascending order.

What is it

Let’s begin with that ArrayList this is a namespace class System.Collections libraries mscorlibwhich implements the interface IList. Naturally, in VBA it is somewhat cut in terms of methods, otherwise it could not be (for example, there are no methods AddRange or BinarySearch). However, you can (and should) work with it.
Basically, it’s a dynamic array. It does not need to be redefined by itself to change the dimension, it is enough to add elements using the method Add. Somewhere I read that at a low level (may experts forgive me, I don’t know if I’m using this phrase correctly here) there are some nuances in terms of performance, but, frankly, for the entire time I used this object, I did not notice any problems and the time the macro ran because of it if and grows in general, it is not critical at all.

What is strength brother convenience?
At least in that it is a dynamic array. You just add elements through the method and you don’t need to bother with the topic ReDim (and even more so Preserve) and calculating the size of the future array.

And then the goodies begin.

First, we can unload all elements with one method ToArray. As the name suggests, it converts all elements of an object into a regular array of type Variant.
Secondly, we can make a list of unique values ​​by checking their presence with the method Contains.
Thirdly, you can forget about the function UBoundbecause this class has a property Countwhich, as you might guess, returns the number of elements placed in the object.
Fourth, it is possible to quickly sort the elements both in ascending order (method Sort), and in descending order (first we use the method Sortand after the method Reverse).

Well, quickly run through the remaining properties:
Item(Index)
Provides access to an element by its index.

and methods:
IndexOf(Item, StartFrom)
Returns the index of the element. Required argument StartFrom will help to find each subsequent index of the same elements.

RemoveAt(Index)
Removes an element by index.

Remove(Item)
Removes the given element.

RemoveRange(StartPosition, Count)
Removes a range of elements. StartPosition points to the index of the first element, Count by the number of elements in the range to be removed.

Clear()
Removes all elements.

Insert(Position, Item)
Adds an element at the given position.

Clone()
Creates a copy of the object (essentially creates a new object rather than returning a reference to the current one).

How to create this miracle

Create class object ArrayList can be done using the function CreateObject:

Dim List As Object
Set List = CreateObject("System.Collections.ArrayList")

or through Tools -> Reference connect the library mscorlib.dlland then create as a regular object:

Dim List As New ArrayList

The disadvantage of both bindings is that you will not get the interface of the object. I personally don’t know the reason, but for some reason VBA in Excel (I didn’t check anywhere else) does not see the properties and methods of this class (in late binding, no object has them anyway, since the variable type Objectbut in the early one it usually is).
You can, of course, get part of the interface by declaring a variable with the type IList and after that assign it an instance ArrayListbut by doing so, we will lose most of the functionality, for example, methods Sort, ToArray, Reverse.

Let’s go back to the beginning

Remember our example? I offer a solution with new knowledge.
Now we add unique values ​​to the object Buffer (ArrayList), before checking the method Contains the presence of the full name in the list of elements. At the end of the cycle, apply the method Sort and Reverse to get a descending list. We unload the result by the method ToArray. Agree, this time everything is much more compact.

Public Sub Main()
    Dim FullNameColumn As Range
    Set FullNameColumn = ActiveSheet.UsedRange.Columns(1) ' Получаем первый столбец.

    Dim DistinctList As Variant
    DistinctList = GetDistinctItems(FullNameColumn) ' Передаем диапазон в функцию.
    Debug.Print Join(DistinctList, vbCrLf) ' Выводим результат.
End Sub

Public Function GetDistinctItems(ByRef Range As Range) As Variant
    Dim Data As Variant: Data = Range.Value ' Преобразуем диапазон в массив.
    Dim Buffer As Object: Set Buffer = CreateObject("System.Collections.ArrayList") ' Создаем объект ArrayList.

    Dim Item
    For Each Item In Data
        If Not Buffer.Contains(Item) Then Buffer.Add Item ' Проверяем наличие элемента и добавляем если отсутствует.
    Next

    Buffer.Sort: Buffer.Reverse ' Сортируем по возрастанию, а потом переворачиваем (по убыванию).
    GetDistinctItems = Buffer.ToArray() ' Выгружаем в виде массива.
End Function

What is the result

As a result, we have an advantage over the class Collection in that there is a check for the presence of an element in the list (without dancing with a tambourine) and fast unloading as an array (without writing a loop).
in front of the class Dictionaryperhaps, the advantage is that there is no need to prescribe keys (if they are not initially needed).
Well, both of the above lose in terms of sorting, adding elements by index, etc.

In general, a fairly easy-to-use class for working with one-dimensional arrays. Of course, get data from an object Range much easier in a regular array, but if you need to create a new one (for example, in a loop), then, as for me, ArrayList excellent option.

PS (problems, problems)

After writing the article, I noticed that my example on a clean PC does not work, it appears automation error -2146232576 when creating an ArrayList object.
Judging by this answer, mscorlib requires .NET Framework 3.5 to be enabled.
You can do this through Control Panel -> Programs -> Enable or disable Windows components -> check the box next to .NET Framework 3.5 (includes .NET 2.0 and 3.0) after which the necessary files for the operation of the component will be downloaded to the PC.
Necessarily After the done actions, restart Excel. I got an error during installation. Fixed by turning off Excel and re-enabling the component.

By the way, there were no such problems on my working PC; this component was already connected by the organization (or by default in early Windows, I don’t know for sure).

Thank you for reading to the end.

How about using this class? Write in the comments!
Also, subscribe to my telegrams.