Массивы и коллекции в VBA: ReDim Preserve, For Each, Worksheets

В теме 5.6 мы писали функции с одним-двумя аргументами. Но что делать, если данных не два и не три, а двадцать однотипных значений — двадцать цен, двадцать фамилий? Заводить Цена1, Цена2, … Цена20 неудобно и совсем не годится, когда заранее не известно, сколько их будет. Для этого в VBA есть массив — одна переменная, которая хранит сразу много значений под одним именем, и коллекция — похожая идея, но для готовых объектов Excel: листов, книг, диапазонов.

К концу темы ты сможешь:
— объявить массив, обратиться к его элементу по индексу и заполнить его в цикле;
— объяснить разницу между одномерным и многомерным массивом;
— сделать массив динамическим и понимать, для чего нужен ReDim Preserve;
— перебрать коллекцию объектов Excel через For Each и посчитать её через Count;
— объяснить, чем коллекция принципиально отличается от массива.

Вспомни: в теме 5.2 мы разбирали цикл For…Next. Он и заполняет, и перебирает массивы — без цикла обращаться к каждому элементу по отдельности было бы так же неудобно, как заводить Цена1, Цена2, Цена3.

Содержание

Зачем нужен массив: объявление, индексы, одномерные и многомерные

Представь: нужно посчитать сумму пяти цен, введённых пользователем. Без массива — пять отдельных переменных Цена1…Цена5 и пять строк кода на каждое действие. С массивом — одна переменная и цикл: та же логика работает для 5 значений и для 500 без единой правки кода.

Массив объявляют как обычную переменную, но с числом в скобках после имени — это верхняя граница индексов. Dim tsena(4) As Double создаёт массив не из четырёх, а из ПЯТИ элементов: по умолчанию нумерация в VBA начинается с нуля, поэтому границы — tsena(0), tsena(1), tsena(2), tsena(3), tsena(4).

Dim tsena(4) As Double
tsena(0) = 120
tsena(1) = 85
tsena(2) = 340
tsena(3) = 60
tsena(4) = 200

Dim tsena(4) As Double  ->  массив из ПЯТИ элементов (индексы с 0 по умолчанию)

 индекс:    0      1      2      3      4
           +------+------+------+------+------+
 tsena:    | 120  |  85  | 340  |  60  | 200  |
           +------+------+------+------+------+

 Обращение tsena(2) вернёт значение из ТРЕТЬЕЙ по счёту ячейки: 340.

Заполнять и перебирать массив вручную по одному элементу — та же ошибка, что заводить отдельные переменные. Обычно это делают циклом For, где счётчик цикла одновременно служит индексом массива.

Dim i As Integer
Dim summa As Double
For i = 0 To 4
    summa = summa + tsena(i)
Next i

Одномерный массив — это список: одна строка значений. Многомерный массив — это уже таблица: например, Dim tab(1, 2) As String создаёт таблицу из 2 строк (индексы 0-1) и 3 столбцов (индексы 0-2). Обращаются к ячейке такой таблицы двумя индексами: сначала строка, потом столбец — tab(0, 0), tab(1, 2) и так далее.

Dim tab(1, 2) As String   ->   таблица 2 строки x 3 столбца

              столбец 0     столбец 1     столбец 2
 строка 0    tab(0,0)      tab(0,1)      tab(0,2)
 строка 1    tab(1,0)      tab(1,1)      tab(1,2)

 Первый индекс - строка, второй - столбец. Порядок важен.

Динамический массив: ReDim и ReDim Preserve

Массивы из примеров выше — статические: размер задан один раз в Dim и не меняется. Но заранее не всегда известно, сколько будет значений — например, число заказов за день заранее не знаешь. Для этого массив объявляют динамическим — с пустыми скобками, без числа, — а размер задают позже командой ReDim, уже во время выполнения кода.

Dim zakazy() As String   ' массив без размера - динамический
ReDim zakazy(2)          ' теперь у него 3 элемента: индексы 0, 1, 2
zakazy(0) = "Заказ №1"
zakazy(1) = "Заказ №2"
zakazy(2) = "Заказ №3"

Проблема в том, что обычный ReDim заново создаёт массив и стирает всё, что в нём уже лежало. Если заказов вдруг стало больше и нужно расширить массив, не потеряв уже записанные значения, используют ReDim Preserve — она меняет размер и сохраняет прежние данные.

ReDim Preserve zakazy(3)  ' стало 4 элемента, старые 3 заказа НИКУДА не делись
zakazy(3) = "Заказ №4"

Важное ограничение. Обычный ReDim можно применять к массиву сколько угодно раз в любую сторону. А ReDim Preserve умеет менять размер только у ПОСЛЕДНЕГО измерения массива и не может поменять само количество измерений (одномерный не станет двумерным). Для одномерного массива это не проблема — там измерение всего одно.

Коллекции: готовые наборы объектов и своя коллекция

Массив — это то, что заполняешь сам: числа, строки, любые значения. Коллекция — это уже готовый набор ОБЪЕКТОВ, который предоставляет сам Excel. Например, Worksheets — коллекция всех листов книги, а Workbooks — коллекция всех открытых книг Excel. Каждый элемент такой коллекции — не число и не строка, а целый объект листа или книги со своими свойствами и методами.

Debug.Print Worksheets.Count   ' сколько листов в книге

Dim sh As Worksheet
For Each sh In Worksheets
    Debug.Print sh.Name        ' имя каждого листа по очереди
Next sh

For Each — это специальный вид цикла для перебора коллекций: он не считает индексы сам, а просто на каждом шаге даёт очередной объект коллекции, пока она не закончится. Свойство Count есть у любой коллекции и сразу отвечает на вопрос «сколько там элементов», без ручного подсчёта.

Кроме готовых коллекций Excel, можно завести и свою — для собственного набора объектов или значений, которые ты пополняешь по ходу работы кода. У любой коллекции есть всего четыре основных инструмента: Add — добавить элемент, Remove — убрать, Count — узнать количество, Item — получить элемент по номеру.

Dim goroda As New Collection
goroda.Add "Москва"
goroda.Add "Казань"
goroda.Add "Томск"

Debug.Print goroda.Count   ' 3

Dim g As Variant
For Each g In goroda
    Debug.Print g
Next g

Обратиться к элементу коллекции по номеру можно через Item: goroda.Item(1) вернёт первый добавленный город. Номер у Item начинается с 1, а не с 0, как у обычного массива — путать эти два индекса легко, если забыть про разницу.

Debug.Print goroda.Item(1)   ' первый добавленный город

МАССИВ                              КОЛЛЕКЦИЯ
───────────────────────────────────────────────────────────────
размер фиксирован в Dim/ReDim        растёт сама через .Add,
(если не динамический)               ужимается через .Remove

хранит значения (числа, строки),    хранит уже готовые ОБЪЕКТЫ
которые кладёшь сам                  (листы, книги) или свои элементы

индексация с 0 по умолчанию         нумерация Item обычно с 1

перебор: For i = 0 To ...            перебор: For Each элемент In ...

Проверь себя

Контрольный вопрос. Зачем в VBA используют массив вместо отдельных переменных Цена1, Цена2, Цена3…?

Ачтобы хранить много однотипных значений под одним именем и обрабатывать их в цикле

Бмассив работает быстрее, чем обычные переменные

Вмассив занимает меньше оперативной памяти

Готдельные переменные в VBA вообще запрещены

Подсказка: Подумай, что придётся переписывать в коде, если значений станет не 5, а 500.

Онлайн-проверка ответа появится позже

Задание. Сколько элементов будет у этого массива? Впиши число.

Исходный код для этого задания:
Dim tsena(4) As Double
Подсказка: Нумерация по умолчанию начинается с нуля, а не с единицы.

✅ Готово, если: ты ввёл(а) верный ответ, и онлайн-проверка его приняла.

Онлайн-проверка ответа появится позже

Контрольный вопрос. С какого индекса по умолчанию начинается нумерация элементов обычного массива в VBA?

Ас 0

Бс 1

Вс -1

Гнумерация не фиксирована и каждый раз случайна

Подсказка: Именно поэтому Dim tsena(4) даёт пять элементов, а не четыре.

Онлайн-проверка ответа появится позже

Задание. Что вернёт bukvy(2)? Впиши без кавычек.

Исходный код для этого задания:
Dim bukvy(3) As String
bukvy(0) = "А"
bukvy(1) = "Б"
bukvy(2) = "В"
bukvy(3) = "Г"
Подсказка: Индекс 2 — это третий по счёту элемент, если считать с нуля.

✅ Готово, если: ты ввёл(а) верный ответ, и онлайн-проверка его приняла.

Онлайн-проверка ответа появится позже

Задание. Таблицу заполнили этим двойным циклом. Чему равно значение tab(1, 2)? Впиши число.

Исходный код для этого задания:
Dim tab(1, 2) As Integer
Dim i As Integer, j As Integer
For i = 0 To 1
    For j = 0 To 2
        tab(i, j) = i * 10 + j
    Next j
Next i
Подсказка: Подставь i=1 (номер строки) и j=2 (номер столбца) в формулу i * 10 + j.

✅ Готово, если: ты ввёл(а) верный ответ, и онлайн-проверка его приняла.

Онлайн-проверка ответа появится позже

Контрольный вопрос. Как объявить массив так, чтобы его точный размер можно было задать позже, уже во время выполнения кода?

Ас пустыми скобками: Dim arr() As Тип, а размер задать позже через ReDim

Бнаписать Dim arr(?) As Тип

Вуказать заведомо большое число в скобках на всякий случай

Гв VBA так сделать нельзя, размер всегда фиксируется в Dim

Подсказка: В уроке такой массив назывался динамическим.

Онлайн-проверка ответа появится позже

Контрольный вопрос. Чем ReDim Preserve отличается от обычного ReDim при изменении размера массива?

АPreserve сохраняет уже записанные в массив значения, обычный ReDim их стирает

БPreserve работает быстрее, чем обычный ReDim

ВPreserve можно применять только к многомерным массивам

Гразницы нет, это два названия одной и той же команды

Подсказка: Подумай, что случится с уже введёнными данными в обоих случаях — это и есть разница.

Онлайн-проверка ответа появится позже

Контрольный вопрос. У массива два измерения: Dim tab(2, 3). Можно ли через ReDim Preserve изменить количество СТРОК (первое измерение), сохранив данные?

Анет — Preserve умеет менять размер только у последнего измерения

Бда, Preserve одинаково легко меняет любое измерение

Вможно, но только если массив динамический с самого начала

Гнет, ReDim Preserve вообще не работает с многомерными массивами

Подсказка: В уроке это было отдельно отмечено как ограничение — Preserve трогает только ОДНО измерение, и не первое.

Онлайн-проверка ответа появится позже

Контрольный вопрос. Чем коллекция (например, Worksheets) принципиально отличается от обычного массива?

Аэлементы коллекции — уже готовые объекты Excel, а не значения, которые кладёшь сам

Бколлекция — это просто другое название массива в новых версиях VBA

Вв коллекции, в отличие от массива, нельзя посчитать количество элементов

Гколлекцию нельзя перебрать циклом

Подсказка: Массив ты заполняешь сам числами или строками. А кто заполняет Worksheets?

Онлайн-проверка ответа появится позже

Задание. Что выведет код Debug.Print Worksheets.Count? Впиши число.

В книге Excel открыто три листа: «Январь», «Февраль», «Март».

Подсказка: Count у коллекции листов считает, сколько всего листов в книге.

✅ Готово, если: ты ввёл(а) верный ответ, и онлайн-проверка его приняла.

Онлайн-проверка ответа появится позже

Контрольный вопрос. Что делает конструкция For Each sh In Worksheets … Next sh?

Аперебирает по очереди каждый лист книги, и на каждом шаге sh указывает на очередной лист

Будаляет по очереди каждый лист книги

Всоздаёт новый лист столько раз, сколько уже есть листов

Гсчитает индекс листа так же, как обычный цикл For i = 0 To N

Подсказка: For Each создан специально для обхода коллекций, а не для подсчёта индексов.

Онлайн-проверка ответа появится позже

Контрольный вопрос. Отметь все верные утверждения о коллекциях в VBA (не менее двух вариантов).

Выберите все верные варианты.

Ау коллекции есть свойство Count — количество элементов

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

Вколлекции, как и обычные массивы, всегда индексируются с 0

Гразмер коллекции фиксирован и не может меняться после создания

Дколлекцию можно перебрать циклом For Each

Подсказка: Два утверждения приписывают коллекции свойства обычного статического массива.

Онлайн-проверка ответа появится позже

Контрольный вопрос. Какой город вернёт goroda.Item(1)?

Исходный код для этого задания:
Dim goroda As New Collection
goroda.Add "Москва"
goroda.Add "Казань"
goroda.Add "Томск"
АМосква

БКазань

ВТомск

ГItem(1) обратится к пустому элементу — города добавлены методом Add, а не Item

Подсказка: У Item нумерация начинается с 1, а не с 0 — это первый добавленный элемент.

Онлайн-проверка ответа появится позже

Задание. Что вернёт tovary.Count после выполнения этого кода? Впиши число.

Исходный код для этого задания:
Dim tovary As New Collection
tovary.Add "Ноутбук"
tovary.Add "Мышь"
tovary.Add "Клавиатура"
tovary.Add "Монитор"
tovary.Remove 2
Подсказка: Сначала добавили 4 элемента, потом один убрали методом Remove по номеру.

✅ Готово, если: ты ввёл(а) верный ответ, и онлайн-проверка его приняла.

Онлайн-проверка ответа появится позже

Итог

Итог. Массив хранит много однотипных значений под одним именем; индексация по умолчанию с нуля, Dim arr(4) даёт пять элементов. Одномерный массив — список, многомерный (Dim tab(1,2)) — таблица, где первый индекс строка, второй столбец. Динамический массив объявляют пустыми скобками и задают размер позже через ReDim; ReDim Preserve сохраняет данные при изменении размера, но меняет только последнее измерение. Коллекция (Worksheets, Workbooks или своя через New Collection) хранит готовые объекты, а не значения; Add добавляет элемент, Count считает их количество, For Each перебирает коллекцию без ручного подсчёта индексов.

Практика (необязательная)

Задание для практики. Заведи динамический массив Dim otdely() As String. Через ReDim задай ему размер на 3 элемента и заполни названиями трёх отделов твоей компании (или придуманной). Затем через ReDim Preserve увеличь размер до 4 и добавь ещё один отдел, не потеряв первые три. Выведи все значения через Debug.Print в цикле For. Отдельно заведи Collection, добавь в неё те же названия через Add и выведи их через For Each — сравни, чем этот перебор отличался от перебора массива.

← Назад · ↑ В начало урока · ⌂ В начало курса · Вперёд: Внешние файлы и данные →