Коллекции Excel — это удобный способ хранения набора данных в макросах VBA. Представьте себе коробку, в которую вы можете складывать любые предметы: числа, текст, даты, имена. Коллекции Excel позволяют обращаться к этим предметам как к единому целому. В этой статье мы простыми словами разберём, что такое коллекции Excel, чем они отличаются от массивов, как их создавать и использовать.
Что такое коллекция в Excel макросах?
Коллекция — это специальный объект в VBA (языке макросов), который хранит упорядоченный набор данных. Вы можете добавить в коллекцию любой элемент: строку, число, дату или даже другую коллекцию. Каждый элемент в коллекции имеет свой номер (индекс): первый элемент — номер 1, второй — номер 2 и так далее.
Примеры готовых коллекций, которые уже есть в Excel VBA:
- Workbooks — коллекция всех открытых книг Excel.
- Worksheets — коллекция всех листов в книге.
- Sheets — коллекция всех листов (включая диаграммы).
- Range — коллекция ячеек.
📌 Коллекции Excel очень похожи на массивы, но у них есть важные отличия. Давайте разберёмся, что лучше использовать в какой ситуации.
Коллекции и массивы: в чём разница (и что выбрать новичку)
И коллекции Excel, и массивы помогают хранить много данных под одним именем. Например, у вас есть 100 учеников. Вместо того чтобы создавать 100 отдельных переменных, вы создаёте одну коллекцию или один массив, куда помещаете всех учеников.
Главные отличия (простым языком):
- Размер массива фиксированный. Вы должны заранее знать, сколько элементов будет в массиве. Если вы ошиблись, массив придётся пересоздавать.
Пример: вы знаете, что в классе ровно 30 учеников. Можно использовать массив. - Размер коллекции можно менять. Вы можете добавлять и удалять элементы в любой момент. Excel сам позаботится о том, чтобы коллекция стала больше или меньше.
Пример: вы делаете выборку учеников, которые получили пятёрку. Вы не знаете, сколько их будет — 5 или 20. Лучше использовать коллекцию. - В массиве можно изменять элементы. Если вы хотите поменять значение «Яблоко» на «Груша», в массиве это легко сделать.
В коллекции элементы доступны только для чтения. Вы можете добавить или удалить элемент, но не можете изменить его значение. Придётся удалить старый и добавить новый.
Простой пример с фруктами:
' Создаём коллекцию фруктов Dim fruits As New Collection fruits.Add "Яблоко" fruits.Add "Слива" ' Добавляем новый фрукт — коллекция сама становится больше fruits.Add "Лимон" ' Удаляем элемент — коллекция сама становится меньше fruits.Remove 1
💡 Вывод: используйте массивы, когда размер данных известен заранее и не меняется. Используйте коллекции Excel, когда размер данных может меняться (добавляются или удаляются элементы).
Как создать коллекцию в макросе
Создать коллекцию Excel очень просто. Есть два способа.
Способ 1. Объявить и создать в одной строке (коллекция создаётся сразу):
Dim students As New Collection
Способ 2. Сначала объявить, потом создать (коллекция создаётся только при нужном условии):
Dim students As Collection ' объявили ' ... какой-то код ... Set students = New Collection ' создали, когда понадобилось
Второй способ полезен, если вы не уверены, понадобится ли коллекция. Например, вы проверяете, есть ли данные, и только тогда создаёте коллекцию.
Как добавить элементы в коллекцию
Добавлять элементы очень просто — используйте команду Add.
Dim fruits As New Collection ' Добавляем элементы fruits.Add "Яблоко" ' элемент с номером (индексом) 1 fruits.Add "Слива" ' элемент с индексом 2 fruits.Add "Лимон" ' элемент с индексом 3
Как добавить элемент в определённое место:
Вы можете указать, куда именно вставить новый элемент — перед каким-то существующим (Before) или после него (After).
' Добавим лимон перед первым элементом fruits.Add "Лимон", Before:=1 ' Теперь порядок: Лимон, Яблоко, Слива ' Добавим грушу после второго элемента fruits.Add "Груша", After:=2 ' Теперь порядок: Лимон, Яблоко, Груша, Слива
Как использовать КЛЮЧИ — быстрый доступ к элементам
Когда вы добавляете элемент в коллекцию Excel, вы можете дать ему уникальное имя — ключ. Тогда вы сможете обращаться к элементу не по номеру (индексу), а по имени. Это очень удобно!
Dim marks As New Collection
' Добавляем оценки учеников с ключами-именами
marks.Add Item:=5, Key:="Иванов"
marks.Add Item:=4, Key:="Петров"
marks.Add Item:=5, Key:="Сидорова"
' Теперь можно получить оценку напрямую по ключу
Debug.Print marks("Иванов") ' выведет 5
Debug.Print marks("Петров") ' выведет 4
⚠️ Важно: ключи должны быть уникальными. Нельзя добавить два элемента с одинаковым ключом.
Преимущества ключей: вы можете найти элемент даже если его номер (индекс) изменился (например, вы что-то удалили из коллекции).
Недостаток: вы не можете проверить, существует ли ключ в коллекции. Если вы обратитесь к несуществующему ключу, макрос выдаст ошибку.
Как получить доступ к элементам коллекции
Есть два способа получить элемент из коллекции:
- По номеру (индексу):
fruits(1)— первый элемент,fruits(2)— второй. - По ключу (если вы его задали):
marks("Иванов").
' Получаем элементы по индексу
Debug.Print fruits(1) ' выведет Лимон
Debug.Print fruits(2) ' выведет Яблоко
' Получаем элемент по ключу (если ключ был задан)
Debug.Print marks("Иванов") ' выведет 5
Как перебрать все элементы коллекции (циклы)
Чтобы пройтись по всем элементам коллекции Excel и, например, вывести их на экран, используйте простые циклы.
Способ 1. Цикл For (по индексам):
Dim i As Long
For i = 1 To fruits.Count
Debug.Print fruits(i)
Next i
Способ 2. Цикл For Each (проще и быстрее):
Dim fruit As Variant
For Each fruit In fruits
Debug.Print fruit
Next fruit
Оба способа выведут все фрукты по очереди. For Each чаще используется с коллекциями, потому что он короче и понятнее.
Как удалить элементы из коллекции
Удалить элемент можно по индексу:
fruits.Remove 1 ' удаляем первый элемент
Как удалить все элементы сразу: просто создайте коллекцию заново.
Set fruits = New Collection ' старая коллекция исчезает, создаётся новая пустая
Краткая шпаргалка по коллекциям Excel (таблица)
| Что нужно сделать | Пример команды |
|---|---|
| Объявить | Dim coll As Collection |
| Создать (в коде) | Set coll = New Collection |
| Объявить и создать сразу | Dim coll As New Collection |
| Добавить элемент | coll.Add "Яблоко" |
| Добавить с ключом | coll.Add Item:=5, Key:="Вася" |
| Получить элемент по индексу | coll(1) или coll.Item(1) |
| Получить элемент по ключу | coll("Вася") |
| Узнать количество элементов | coll.Count |
| Удалить элемент | coll.Remove 1 |
| Удалить все элементы | Set coll = New Collection |
| Перебрать все элементы (For Each) | For Each item In coll: Debug.Print item: Next |
Пример из жизни: коллекция учеников и их оценок
Представьте, что вы учитель и вам нужно сохранить оценки учеников. Вы не знаете, сколько учеников придёт. Идеально подойдут коллекции Excel.
Sub ПримерКоллекции()
' Создаём коллекцию
Dim оценки As New Collection
' Добавляем оценки (ключ — фамилия ученика)
оценки.Add Item:=5, Key:="Иванов"
оценки.Add Item:=4, Key:="Петров"
оценки.Add Item:=3, Key:="Сидорова"
' Выводим все оценки
Dim ученик As Variant
For Each ученик In оценки
Debug.Print ученик
Next ученик
' Выводим оценку конкретного ученика
Debug.Print "Оценка Петрова: " & оценки("Петров")
' Добавляем нового ученика
оценки.Add Item:=5, Key:="Кузнецов"
' Узнаём, сколько всего учеников
Debug.Print "Всего учеников: " & оценки.Count
End Sub
Заключение
Коллекции Excel — это мощный и гибкий инструмент для хранения данных в макросах VBA. Они отлично подходят, когда вы не знаете заранее, сколько элементов понадобится. Коллекции Excel легко создавать, добавлять элементы, удалять их и перебирать. Главное отличие от массивов — коллекции могут менять свой размер, а массивы — нет. Для новичков коллекции часто удобнее, потому что не нужно думать о фиксированном размере и перераспределении памяти. Начните использовать коллекции Excel в своих макросах — и вы заметите, как код становится проще и понятнее.