Как в 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, не знаю точно).
Спасибо, что прочитали до конца.
Как насчет применения этого класса? Пишите в комментариях!
А также, подписывайтесь на мой телеграмм.