Как в VBA правильно создавать одномерный массив. ArrayList

Когда я впервые встретил этот класс, подумал «Зачем? Ведь есть простые массивы». А потом попробовал и не представляю как жил без него раньше.

Начну сразу с примера

Предположим, на активном листе в столбце 1 находится список ФИО сотрудников.

Наша задача собрать в массив только уникальные ФИО и отсортировать его по убыванию (ну такая вот немного странная задача). Сначала решим ее без использования ArrayList, а в конце сравним результат.
Для получения уникальных значений, создаем функцию GetDistinctItems и в нее передаем столбец с ФИО. В самой функции пробегаем циклом For Each по всем ФИО и добавляем уникальные в объект Buffer (Dictionary). Далее методом Keys извлекаем элементы в дополнительную функцию DescendingSort (используем сортировку пузырьком) и получаем отсортированные значения в переменную Sorted, которую и возвращаем как результат функции.

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

Тривиально? Вполне. Компактно? Ну в целом да, но в конце мы напишем еще более компактней, а заодно решим проблему написания новой функции, если вдруг результат нужно будет сортировать по возрастанию.

Что есть такое

Начнем с того, что ArrayList это класс из пространства имен System.Collections библиотеки mscorlib, который реализует интерфейс IList. Естественно, в VBA он несколько порезан в плане методов, иначе и быть не могло (например нет методов AddRange или BinarySearch). Но и тем не менее с ним можно (и нужно) работать.
По сути, это динамический массив. Его не нужно самостоятельно переопределять, чтобы изменить размерность, достаточно добавлять элементы с помощью метода Add. Где-то я читал, что на низком уровне (да простят меня знатоки, я не знаю правильно ли я применяю это словосочетание здесь) есть свои нюансы в плане производительности, но, откровенно говоря, за все время использования этого объекта каких-либо проблем я не замечал и время работы макроса из-за него если и растет вообще, то совсем не критично.

В чем же сила брат удобство?
Как минимум в том, что это динамический массив. Вы просто добавляете элементы через метод и не нужно заморачиваться на тему ReDim (и уж тем более Preserve) и вычислений размеров будущего массива.

А дальше начинаются вкусняхи

Во-первых, мы можем выгрузить все элементы одним методом ToArray. Как следует из названия, он преобразует все элементы объекта в обычный массив типа Variant.
Во-вторых, мы можем составлять список уникальных значений, проверяя их наличие методом Contains.
В-третьих, можно забыть про функцию UBound, ведь у этого класса есть свойство Count, которое, как не сложно догадаться, возвращает количество элементов помещенных в объект.
В-четвертых, есть возможность быстро отсортировать элементы как по возрастанию (метод Sort), так и по убыванию (сначала используем метод Sort, а после метод Reverse).

Ну и быстро пробегаем по оставшимся свойствам:
Item(Index)
Предоставляет доступ к элементу по его индексу.

и методам:
IndexOf(Item, StartFrom)
Возвращает индекс элемента. Обязательный аргумент StartFrom поможет найти каждый последующий индекс одинаковых элементов.

RemoveAt(Index)
Удаляет элемент по индексу.

Remove(Item)
Удаляет переданный элемент.

RemoveRange(StartPosition, Count)
Удаляет диапазон элементов. StartPosition указывает на индекс первого элемента, Count на количество элементов в удаляемом диапазоне.

Clear()
Удаляет все элементы.

Insert(Position, Item)
Добавляет элемент по заданной позиции.

Clone()
Создает копию объекта (по сути создает новый объект, а не возвращает ссылку на текущий).

Как создать это чудо

Создать объект класса ArrayList можно с помощью функции CreateObject:

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

или через Tools -> Reference подключить библиотеку mscorlib.dll, а дальше создавать как обычный объект:

Dim List As New ArrayList

Минус и той и другой привязки в том, что интерфейс объекта вы не получите. Причину лично я не знаю, но почему-то VBA в Excel (больше нигде не проверял) не видит свойства и методы этого класса (в поздней привязке их и так нет ни у какого объекта, так как тип переменной Object, а вот в ранней обычно есть).
Можно, конечно, получить часть интерфейса, объявив переменную с типом IList и уже после этого присвоить ей инстанс ArrayList, но тем самым мы потеряем бОльшую часть функционала, например методы Sort, ToArray, Reverse.

Вернемся в начало

Помните наш пример? Предлагаю решение с новыми знаниями.
Теперь мы добавляем уникальные значения в объект Buffer (ArrayList), перед этим проверяя методом Contains наличие ФИО в списке элементов. По окончанию цикла применяем метод Sort и Reverse для получения списка по убыванию. Выгружаем результат методом ToArray. Согласитесь на этот раз все гораздо компактней.

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

Что в итоге

В итоге мы имеем преимущество перед классом Collection в том, что есть проверка на наличие элемента в списке (без танцев с бубном) и быстрая выгрузка в виде массива (без написания цикла).
Перед классом Dictionary, пожалуй, преимущество в отсутствии необходимости прописывать ключи (если они изначально не нужны).
Ну и оба вышеперечисленных проигрывают в плане сортировки, добавления элементов по индексу и т.д.

В общем и целом, достаточно удобный в применении класс для работы с одномерными массивами. Конечно, получать данные из объекта Range гораздо проще в обычный массив, но если нужно создавать новый (например в цикле), то, как по мне, ArrayList превосходный вариант.

P.S. (проблемки, проблемушки)

Уже после написания статьи обратил внимание, что мой пример на чистом ПК не работает, появляется automation error -2146232576 при создании объекта ArrayList.
Судя по этому ответу, для работы mscorlib необходимо включить .NET Framework 3.5.
Сделать это можно через Панель управления -> Программы -> Включение или отключение компонентов Windows -> поставить галочку напротив .NET Framework 3.5 (включает .NET 2.0 и 3.0) после чего на ПК скачаются необходимые файлы для работы компонента.
Обязательно после проделанных действий перезагрузить Excel. У меня при установке выдал ошибку. Исправилось выключением Excel и повторным включением компонента.

К слову на моем рабочем ПК таких проблем не было, т.е. данный компонент уже был подключен организацией (или по умолчанию в ранних Windows, не знаю точно).

Спасибо, что прочитали до конца.

Как насчет применения этого класса? Пишите в комментариях!
А также, подписывайтесь на мой телеграмм.