Коллекции в макросах Excel

Коллекции (Collection) элементов Excel – это объект, представляющий из себя упорядоченный набор данных, на который можно ссылаться как на одно целое.
Отдельные элементы коллекции являются также объектами. Элементы коллекции никак между собой не связаны и могут иметь разные типы данных.
Примеры готовых коллекций в VBA: Workbooks, Worksheets, Range и Cells.

Коллекции похожи на массивы, поэтому важно понимать, что они из себя представляют и чем они отличаются от массивов.

Коллекции и массивы элементов в Excel

Коллекции и массивы используются для группировки переменных в Excel. Они оба хранят набор похожих данных, например список названий фруктов или фамилий сотрудников. Использование коллекции или массива позволяет работать с большим количеством данных в Excel , а также быстро и легко получать нужный результат.

Например, есть 100 учеников, которые получили оценки по предметам в году. Нужно задать переменные по ученикам, причем все они должны быть использованы по отдельности. Для того, чтобы не объявлять каждый раз переменную, можно использовать коллекцию или массив. Использование цикла с коллекцией или массивами означает, что вам нужна только одна строка для добавления или чтения значений.

 ' Создать массив правильного размера 
    Dim coll() As Long 
    ReDim coll(1 To StudentCount)
' Создать коллекцию
    Dim coll As New Collection   

Рассмотрим отличия коллекций и массивов, чтобы определиться с их использованием.

• Количество данных, используемых в массиве или коллекции

Основное отличие состоит в том, что в массиве размер устанавливается один раз до того, как начнете добавлять элементы. Массивы используются, когда размер фиксирован.
Например, есть лист оценок учеников, на котором каждый ученик располагается на новой строке. В этом случае можно  посчитать количество строк, чтобы получить количество студентов. 

Рассмотрим другой пример, где мы не знаем количество заранее. Например, нужно сделать выборку по определенным предметам, а также в процессе обучения ученики могут быть добавлены или удалены из списка. Таким образом, данные по ученикам изменяются, поэтому вы не знаете, какой размер массива создать. 
Вы можете создать массив максимально возможного размера. Проблема в том, что у вас будет много пустых переменных, с которыми придется что-то делать. Это очень неэффективно. Так что для этого примера лучше использовать коллекцию. Коллекция используется, когда размер часто изменяется.

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

 ' Объявить
    Dim coll As New Collection

    ' Добавить элемент - VBA следит за изменением размера
    coll.Add "Яблоко"
    coll.Add "Слива"

    ' удалить элемент - VBA следит за изменением размера
    coll.Remove 1

• Использование данных в массивах и коллекциях

Основные типы данных (переменные, такие как string, date, long, currency и т.д.) в коллекциях доступны только для чтения. Вы можете добавить или удалить элемент, но не можете изменить его значение. Если вы собираетесь изменять значения в группе элементов, Вам нужно будет использовать массив. Коллекция только для чтения.

Создание коллекции

Вы можете объявить и создать коллекцию в одной строке:

    Dim coll As New Collection   

Вы также можете объявить и затем создать коллекцию, если и когда вам это нужно.

    Dim coll As Collection 'Объявить
    Set coll = New Collection 'Создать

Разница между этими методами заключается в следующем:
— в первом случае коллекция создается всегда;
— во втором случае коллекция создается только при соблюдении определенного условия (достижении строки Set). Например, If filefound = True Then Set coll = New Collection

Удаление всех элементов из коллекции

Чтобы удалить все элементы из коллекции, вы можете просто установить ее в новую коллекцию: 
Set Coll = New Collection

Важно: если у нас есть две или более переменных, которые ссылаются на одну и ту же коллекцию, она не будет удалена (см. Очистка памяти в VBA).

Добавление элементов в коллекцию

С помощью свойства add, за которым следует добавляемое значение:

   coll.Add "Яблоко" 
   coll.Add "Слива"

Каждый следующий элемент добавляется в следующий доступный индекс. В примере с фруктами яблоко добавляется в положение 1, а слива — в положение 2.

Вы можете использовать параметры «Before» или «After», чтобы указать, где вы хотите разместить элемент в коллекции:

• coll.Add «Лимон», Before:=1 ‘Добавить лимон перед первым пунктом.
  После этого кода порядок коллекции выглядит так: 1.Лимон 2.Яблоко 3.Слива
• coll.Add «Лимон», After:=1 ‘Добавьте лимон после первого пункта
  После этого кода порядок коллекции выглядит так: 1.Яблоко 2. Лимон 3.Слива

Использование ключей для коллекции элементов Excel

Вы также можете добавить элементы, используя ключ:
collMark.Add Item:=45, Key:=»Петр»
Использование ключа позволяет получить доступ к элементу не зная его индекса, а просто задав его ключ. Даже если изменится индекс элемента (его порядковый номер), то по ключу мы все равно получим нужный элемент.
Важно: каждый ключ должен быть уникальным.

Преимущество использование ключей в том, что можно получить значение элемента коллекции напрямую и не искать его среди других элементов.
Недостатком использования ключей является то, что нельзя проверить, существует ли ключ.

Синтаксис добавления элементов в коллекцию

Collection.Add Элемент, [Ключ], [До], [После], где

* Аргументы «До» и «После» не могут применяться одновременно. Если аргументу «До» или «После» присвоено числовое значение, оно должно быть в пределах диапазона от 1 до значения свойства Collection.Count. Если это строка, она должна соответствовать одному из ключей существующих в коллекции элементов.

Доступ к элементам коллекции

Для доступа к элементам коллекции используется индекс  — позиция элемента в коллекции на основе порядка, в котором они были добавлены. 
Порядок также можно установить с помощью параметра «Before» или «After».

Чтобы напечатать элемент используется команда Debug.Print:
Debug.Print coll(1) или Debug.Print coll.Item(1)
Debug.Print coll(«Петр») ‘при использовании ключей

Для доступа ко всем элементам в коллекции вы можете использовать цикл For или цикл For Each. 

Использование цикла For

    For i = 1 To coll.Count
        Debug.Print coll(i)
    Next i

Использование цикла For Each

    Dim fruit As Variant
    For Each fruit In coll
        Debug.Print fruit
    Next fruit