Урок 5 Как заставить Excel повторять действия: все о циклах VBA

Если вы здесь, то это очень хороший знак! Вы движетесь вперёд, несмотря на препятствия, и вы уже готовы освоить очень важную часть языка программирования. И это значит, что вы научитесь с помощью небольшого числа строчек кода обрабатывать тысячи, десятки или сотни тысяч ячеек Excel. Если вы пришли сюда не после Урока 4, то настоятельно рекомендуем вам отправиться и изучить все уроки, начиная с первого, так как весь материал этого урока даётся в предположении, что вы уже хорошо усвоили все предыдущие уроки:

Урок 1 Введение в макросы Excel: что это и зачем нужно

Вам следует не просто читать материал, а обязательно выполнять на практике все предложенные задания!

Перед тем, как приступить к теме этого урока, разберем домашнее задание предыдущего урока. Текст задания смотрите на уроке 4.

Домашнее задание предыдущего урока

Решение у задач очень простое. Ниже приводится код. Если в этом коде есть что-то непонятное, то смело задавайте вопросы в комментариях.

Решение задачи 1

Sub FormatCellByValue()
Dim cellValue As Double
cellValue = Range("A1").Value
If cellValue > 100 Then
   Range("A1").Interior.Color = vbGreen ' Зеленый
ElseIf cellValue >= 50 Then
   Range("A1").Interior.Color = vbYellow ' Жёлтый
Else
   Range("A1").Interior.Color = vbRed ' Красный
End If
End Sub

Решение задачи 2

Function GetDiscount(status As String) As Integer
Select Case status
   Case "VIP"
      GetDiscount = 15
   Case "Постоянный"
      GetDiscount = 5
   Case "Акция"
      GetDiscount = 2
   Case Else
      GetDiscount = 0
End Select
End Function

Sub Test()
   MsgBox GetDiscount("Постоянный")
End Sub

Массивы

Перед тем как двинуться дальше и перейти к циклам, нам необходимо узнать кое-что важное. Это массивы и коллекции VBA. Давайте приступим к изучению массивов, а потом перейдем к коллекциям.

Массив — это структура данных, которая хранит набор значений одного типа (числа, строки, объекты) под одним именем с доступом по индексу.

Типы массивов

Статические — размер задается заранее и не меняется.

Dim flowers(10) As String ' Массив из 10 элементов
Dim numbers(3 To 7) As Integer ' Массив из 5 элементов

Указанные записи создадут массивы:

1. С именем flowers, в состав которого входит 10 элементов с индексами от 1 до 10 и все эти элементы имеют тип String. Обращаться к каждому элементу этого массива мы можем по индексу. Например, чтобы обратиться к элементу номер 3 нам нужно использовать запись flowers(3).
2. Массив с именем numbers, в состав которого входит 5 элементов с индексами 3, 4, 5, 6, 7 и все эти элементы имеют тип Integer. Обращаться к каждому элементу этого массива мы можем также по индексу. Например, чтобы обратиться к элементу с порядковым номером 3 нам нужно использовать запись numbers(5).

Также есть динамические массивы — размер можно изменять во время выполнения.

Dim names() As String
ReDim names(1 To 10) ' Задаем размер позже

ReDim — это оператор, который изменяет размер массива во время выполнения программы. Он особенно полезен для динамических массивов, когда их размер заранее неизвестен.

Размерность

Выше приведены примеры создания одномерных массивов. Доступ к каждому их элементу осуществляется заданием одного индекса (по сути это нумерованный список элементов). Также могут существовать массивы с количеством индексов 2, 3, 4 и так далее. Это многомерные массивы.

Многомерные (таблицы, матрицы):

Dim matrix(1 To 3, 1 To 3) As Double ' 3x3 (например, для расчетов)

Что обратиться к такому массиву необходимо указать 2 индекса: matrix(1, 2). Лист Excel — это двумерный массив, так как каждый элемент имеет два индекса: номер строки и номер столбца.

Как работать с массивами

Заполнение массива

Указав номер элемента массива мы определяем его значение, далее мы работаем с этим элементом как с обычной переменной.

Dim fruits(1 To 3) As String
fruits(1) = "Яблоко"
fruits(2) = "Груша"
fruits(3) = "Банан"

Перебор элементов

Существуют две полезные функции: LBound и UBound, которые позволяют определить нижний и верхний индекс массива. Таким образом, если размер массива изменится, то перебор будет только существующих элементов и не вызовет появления ошибки. Пример ниже содержит цикл. Вернитесь к нему после изучения раздела про цикл For, а пока можете пропустить.

For i = LBound(fruits) To UBound(fruits)
   Debug.Print fruits(i) ' Выведет все фрукты
Next i

LBound() — нижняя граница индекса.
UBound() — верхняя граница.

Возможные ошибки

Если обратиться к несуществующему элементу, то это приведёт к появлению ошибки:

Dim arr(3) As Integer ' Индексы от 0 до 3 (4 элемента)
arr(4) = 10 ' Ошибка: индекс вне границ!

Как избежать подобных ошибок: всегда проверяйте границы с LBound и UBound.

Коллекции

Коллекция — это «умный контейнер» для хранения группы данных (чисел, строк, объектов) с удобными встроенными методами управления. Главное отличие от массивов — коллекции динамические (не нужно задавать размер заранее) и поддерживают именованные элементы.

Именованные элементы — это способ присвоить уникальный текстовый ключ (имя) элементу коллекции для доступа к нему без использования числового индекса.

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

Основные возможности

Создание и добавление элементов в коллекцию производится нижеуказанным способом, в данном случае создается коллекция fruits:

Dim fruits As New Collection

' Добавление элементов
fruits.Add "Яблоко" ' Без ключа
fruits.Add "Банан", "banana" ' С ключом "banana"
fruits.Add 42 ' Можно смешивать типы данных!

Add — это метод коллекции, который выполняет действие добавления нового элемента коллекции.

Метод в VBA — это действие или операция, которую можно выполнить с объектом. Это «команда», которая делает что-то конкретное: сохраняет файл, сортирует данные, вычисляет значение и так далее. Методы всегда вызываются через точку после объекта.

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

Для доступа к элементам коллекции можно использовать 2 способа: по индексу и по ключу.

' По индексу (как в массиве)
MsgBox fruits.Item(1) ' Выведет "Яблоко"

' По ключу (если он задан)
MsgBox fruits.Item("banana") ' Выведет "Банан"

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

Для удаления элементов применяется метод Remove. 

fruits.Remove 1 ' Удалит элемент "Яблоко" (первый элемент)
fruits.Remove("banana") ' Удалит элемент "Банан" по ключу

Чтобы узнать количество элементов можно воспользоваться методом Count:
 

MsgBox "Всего фруктов: " & fruits.Count ' Выведет 1 (после удаления осталось только число 42)

Плюсы коллекций

• Автоматическое управление памятью (не нужно ReDim как в массивах).
• Поддержка ключей (удобно для словарей).
• Можно хранить разные типы данных (строки, числа, объекты).

Когда использовать

1. Для группировки связанных объектов (листы, формы, файлы);
2. Если нужен быстрый доступ по имени (ключу);
3. Для динамических данных, где размер заранее неизвестен.

Range в VBA

Ранее мы обращались к ячейкам с помощью такого объекта как Range. Теперь, когда мы знаем что такое коллекции, давайте узнаем про Range немного больше.

Range — это базовый объект в Excel VBA, который представляет собой ячейку, строку, столбец или произвольный диапазон на листе.

Range("A1")           ' Одна ячейка
Range("A1:B10")       ' Прямоугольный диапазон A1-A10,B1-B10
Range("A1, C5, D10")  ' Несмежные ячейки

Таким образом можно объединить различные ячейки в коллекцию. На этом и будущих уроках мы ещё вернемся к Range, чтобы узнать о нём ещё больше.

Циклы в VBA

Итак, мы вплотную подошли к теме сегодняшнего урока! Да, этот урок насыщен информацией, но пройдя его и полностью и изучив весь материал, вы выйдете на совершенно новый уровень программирования. Что ж, продолжаем.

Циклы — это конструкции, которые позволяют повторять выполнение кода многократно, пока соблюдается заданное условие. Они работают как «автоматический повтор» для рутинных операций: программа выполняет одни и те же действия для каждого элемента массива данных или пока не достигнет нужного результата. В VBA используются три основных типа циклов:

1. For — когда известно точное количество повторов;
2. For Each — для перебора всех элементов в группе (ячейки, листы, файлы);
3. Do While/Until — когда число итераций заранее неизвестно.

Зачем они нужны? Циклы устраняют рутину. Без них программисту пришлось бы вручную писать сотни одинаковых команд.

Например, как можно использовать циклы:

• Обработать все строки таблицы (от 1 до 10 000);
• Переименовать каждый файл в папке;
• Повторять расчеты, пока ошибка не станет меньше 0.1%.

Проще говоря, циклы — это «роботы-помощники», которые выполняют монотонную работу за вас. Чем сложнее задача, тем больше они экономят времени.

Цикл For — когда известно число повторов

Базовый синтаксис:

For счётчик = начало To конец [Step шаг]
' Тело цикла
Next [счётчик]

Итак, как работает этот цикл. Счётчик — это переменная, которая изменяет своё значение каждый раз, когда цикл в очередной раз завершается (в момент Next). Начальное значение этой переменной мы задаём как начало, конечное значение — как конец, а на сколько меняется каждый раз значение переменной счётчик определяется как шаг.

В качестве row указываем номер строки, начиная с 1. А column — это номер столбца. Это очень удобно! Мы обращаемся к значениям листа как к обычному массиву.

Практический пример 1 (заполнение столбца A):

Sub Test1()
For i = 1 To 10
   Cells(i, 1).Value = i * 2
Next i
End Sub

Пришло время попрактиковаться. Создайте макрос Test1 и запустите его. Вот что должно получиться в результате:

Как видите, код макроса заполнил ячейки А1–А10 числами, значения которых равны удвоенному значению номера строки. В качестве счётчика у нас выступала переменная i, которая изменялась от 1 до 10 с шагом 1 (этот шаг применяется по умолчанию, если шаг явно не указан).

Итого, строчка Cells(i, 1).Value = i*2 заполнения ячеек выполнилась 10 раз:

Cells(1, 1).Value = 1 * 2
Cells(2, 1).Value = 2 * 2
Cells(3, 1).Value = 3 * 2
Cells(4, 1).Value = 4 * 2
Cells(5, 1).Value = 5 * 2
Cells(6, 1).Value = 6 * 2
Cells(7, 1).Value = 7 * 2
Cells(8, 1).Value = 8 * 2
Cells(9, 1).Value = 9 * 2
Cells(10, 1).Value = 10 * 2

А в самом коде эта строчка записана только 1 раз. Вот какими полезными являются циклы!

Иногда бывает необходимо перебирать переменную в обратном направлении. В таком случае шаг указывается явно. Создайте макрос, приведённый ниже:

Пример 2 с обратным отсчётом (шаг –1):

Sub Test2()
For i = 10 To 1 Step -1
   Cells(i, 2).Value = i
Next
End Sub

Результат работы этого макроса появится на том же листе, на который вывел данные и предыдущий макрос, но уже в столбце номер 2 (ячейки B1–B10), потому что при записи значения второй индекс был указан как «2»: Cells(i, 2)

Цикл For Each — для коллекций объектов

Такой цикл идеален для:

• Обработки всех ячеек в диапазоне;
• Перебора листов книги;
• Работы с файлами в папке и так далее.

Синтаксис:

For Each элемент In коллекция
' Действия с элементом
Next

Давайте выполним практический пример 3. Как закрасить все непустые ячейки:

Sub Test3()
For Each cell In Range("A1:B100")
   If Not IsEmpty(cell) Then
      cell.Interior.Color = RGB(200, 240, 200)
   End If
Next
End Sub

Создадим такой макрос и проверим его на данных, которые остались от работы предыдущих макросов, но удалив значения из некоторых ячеек: A3, A4, A7, B1, B5, B6, B10. Результат работы такого макроса:

Как видите, всё ячейки, в которых были числа раскрасились в приятный светло-зелёный цвет. Этот цвет был задан новым для вас способом, а именно, как RGB(200, 240, 200). Таким образом может быть задан любой цвет. Вы просто указываете значение каждого из трёх его компонентов: красного, зелёного и синего. Каждый компонент может меняться в диапазоне от 0 до 255.

Также разберем условие «If Not IsEmpty(cell) Then». Функция IsEmpty проверяет пустая ли данная ячейка, а Not —логически отрицает результат. Таким образом условие звучит как «ЕСЛИ ячейка НЕ пустая, ТОГДА».

Таким циклом очень просто пользоваться, потому что мы напрямую работаем с одним элементом из множества элементов и нам не нужно заботиться о счётчиках индексов массивов, а также изменении значений этих счётчиков. Range("A1:B100") представляет из себя множество всех ячеек с номерами A1–A100, B1–B100, а переменная cell — конкретную ячейку, с которой цикл работает в данный момент (в данной итерации) и можно быть полностью уверенным, что цикл поработает со всеми ячейками заданного множества.

Пример 4. Как переименовать все листы

А что если мы хотим использовать очень удобный вариант цикла For Each, но нам необходимо знать индекс? Очень просто. Давайте посмотрим на примере переименования листов. Сделайте код макроса и запустите его.

Sub Test4()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
   ws.Name = "Отчёт_" & ws.Index
Next
End Sub

Изначально листы Excel имели имена Лист1, Лист2, Лист3. Но макрос их переименовал и теперь они такие:

Разберём макрос:

ThisWorkbook.Worksheets — возвращает коллекцию всех листов текущей книги Excel;
ws — переменная, представляющая собой отдельный лист (при каждой итерации цикла в эту переменную попадает новый лист из коллекции ThisWorkbook.Worksheets);
ws.Name — имя листа;
ws.Index — индекс листа.

Структура коллекции более сложная, чем изображено на картинке, но именно с этой её частью работает макрос Test4 в нашем примере.

Циклы Do While/Until — когда число повторов неизвестно

Проще говоря, While работает пока условие верно, а Until — пока не верно.

Варианты синтаксиса цикла While:

' Проверка ПЕРЕД выполнением
Do While условие
   ' Тело цикла (выполняется, если условие = True)
Loop

' Проверка ПОСЛЕ выполнения (минимум 1 проход)
Do
   ' Тело цикла
Loop While условие

Практический пример 5. Поиск первой пустой ячейки:

Напишите нижеследующий код:

Sub Test5()
Dim rowNum As Integer
rowNum = 1

Do While Not IsEmpty(Cells(rowNum, 1))
   rowNum = rowNum + 1
Loop

MsgBox "Первая пустая ячейка: A" & rowNum
End Sub

Вот как этот код будет выглядеть в редакторе Visual Basic:

После запуска должно появиться такое сообщение:

Теперь рассмотрим варианты с Until. Этот вариант, конечно же, может быть заменен вариантом с While, но был введён в VBA для большего удобства.

Варианты синтаксисам цикла Until:

' Проверка ПЕРЕД выполнением
Do Until условие
    ' Тело цикла (выполняется, если условие = False)
Loop

Do
   ' Тело цикла (выполнится хотя бы 1 раз)
Loop Until условие

Пример с Until:

Dim i As Integer
i = 1

Do Until i > 5
    MsgBox "Итерация: " & i
    i = i + 1
Loop

Этот пример выведет сообщения для i = 1, 2, 3, 4, 5 и остановится, когда i станет 6. То есть, как только условие стало верным — работа Until прекратилась.

Ускорение работы циклов

Ускорение циклов критически важно для эффективной работы программ. Иногда циклы, работающие с тысячами строк/файлов, и без оптимизации могут выполняться минутами вместо секунд. Например: обработка 50 000 строк без оптимизации может занять 20 секунд, с оптимизацией — 0.5 секунды. Особенно это критично для пользователей со слабыми ПК.

Отключаем обновление экрана

Application.ScreenUpdating = False ' В начале макроса
' ... код ...
Application.ScreenUpdating = True ' В конце

Отключаем пересчёт формул

Application.Calculation = xlCalculationManual
' ... код ...
Application.Calculation = xlCalculationAutomatic

Работаем с массивами вместо ячеек (для больших данных)

Так как непосредственная работа с листом Excel происходит довольно-таки медленно (потому что происходит постоянное обновление экрана и, возможно, пересчёт значений), при работе с большими объемами данных имеет смысл использовать хитрый приём:

1. Создаётся копия листа Excel в специально созданном массиве.
2. Все данные массива обрабатываются необходимым способом.
3. Данные из массива копируются на лист Excel обратно в то же место.

Вот практический пример, который демонстрирует такой способ ускорения работы:

Sub Test6()
Dim dataArr As Variant ' Создаем массив
dataArr = Range("A1:B1000").Value ' Копируем ячейки листа в массив

For i = 1 To UBound(dataArr)
   dataArr(i, 2) = dataArr(i, 1) * 2
Next

Range("A1:B1000").Value = dataArr ' Копируем данные массива обратно на лист
End Sub

Прямое присваивание

Иногда можно ускорить работу, наоборот, отказавшись от цикла. Например, был написан такой код, который делает шрифт в ячейках А1–А1000 жирным:

For i = 1 To 1000
    Cells(i, 1).Font.Bold = True ' 1000 обращений к ячейке
Next i

Мы можем заметно оптимизировать этот код, сделав его короче, а также заметно сократив время его выполнения:

Range("A1:A1000").Font.Bold = True ' 1 обращение

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

' Цвет текста и заливки
Range("A1:A1000").Font.Color = RGB(255, 0, 0)  ' Красный текст
Range("A1:A1000").Interior.Color = RGB(0, 255, 0)  ' Зелёная заливка

' Внешние границы
Range("A1:D100").Borders.LineStyle = xlContinuous

' Заполнение одним значением
Range("A1:A100").Value = "Текст"

' Очистка значений (без форматирования)
Range("A1:A1000").ClearContents

' Полная очистка (значения + форматирование)
Range("A1:A1000").Clear

' Включение автофильтра
Range("A1:F1").AutoFilter

' Формула для всего столбца
Range("B1:B100").Formula = "=A1*2"

' Замена во всем листе
Cells.Replace What:="Старое", Replacement:="Новое"

' Замена только в столбце A
Columns("A:A").Replace "Ошибка", "Исправлено"

Опасности при работе с циклами

Самая большая опасность при работе с циклом — создать «бесконечный» цикл, в результате выполнения которого программа зависнет. В некоторых случаях Excel сам отключит такой цикл, а в некоторых — может понадобиться воспользоваться знаменитой комбинацией Ctrl+Alt+Del. Чтобы предотвратить такой исход можно предусмотреть аварийный выход из цикла. Такой выход осуществляется командой Exit Do. Конечно, такой выход может пригодиться не только для аварийного случая, но и в других ситуациях, когда это выгодно и удобно для создания оптимального и красивого кода макроса.

Бесконечный цикл (если условие никогда не выполняется)

' Добавляем "предохранитель"
Dim counter As Integer
Do While условие
   counter = counter + 1
   If counter > 1000 Then Exit Do ' Аварийный выход
Loop

Изменение коллекции во время перебора (приводит к ошибкам)

Использование циклов с возрастающим шагом, цикла For Each для перебора элементов коллекции и при этом удаление этих элементов приведёт к ошибкам, потому что мы меняем индексацию.

Например, в коллекции три элемента с индексами 1,2,3. Когда в первой итерации произойдет удаление элемента с индексом 1 коллекция изменится. Теперь это будет коллекция, состоящая из двух элементов с индексами 1 и 2. Во второй итерации будет удалён элемент с индексом 2. Теперь это будет коллекция, состоящая из одного элемента с индексом 1. Третей итерации не будет, так как элемента с индексом 3 уже нет. Итого: мы хотели удалить все элементы, но удалили только два, а один остался.

' НЕЛЬЗЯ:
For Each ws In Worksheets
   If ws.Name = "Удалить" Then ws.Delete ' Ошибка!
Next

' Правильно:
Dim i As Integer
For i = Worksheets.Count To 1 Step -1
   If Worksheets(i).Name = "Удалить" Then Worksheets(i).Delete
Next

Практический пример: удаление всех листов кроме первого. Создайте макрос, код приведён ниже.

Sub Test7()
Dim i As Integer
For i = Worksheets.Count To 1 Step -1
   If Worksheets(i).Name <> "Отчёт_1" Then Worksheets(i).Delete
Next
End Sub

Запустите макрос и подтвердив удаление листов, убедитесь, что остался только 1 лист:

Чтобы перед каждым удаляемым листом не появлялось окно с предупреждением, можно поставить выключатель предупреждений. После цикла обратно подключите предупреждения.

Sub Test7()
Application.DisplayAlerts = False ' Отключаем предупреждения
Dim i As Integer
For i = Worksheets.Count To 1 Step -1
   If Worksheets(i).Name <> "Отчёт_1" Then Worksheets(i).Delete
Next
Application.DisplayAlerts = True ' Включаем предупреждения
End Sub

Практическое задание

Задача 1

Напишите макрос, который:

1. Проходит по столбцу A до первой пустой ячейки
2. Для всех ячеек со значениями >50 добавляет комментарий «Выше среднего»

Подсказка: комментарий добавляется, например, с помощью такого кода:

' Добавить комментарий к ячейке A1
Range("A1").AddComment "Это пример комментария"

Задача 2 (со звездочкой)

Создайте функцию CountColoredCells(range, color), которая подсчитывает количество ячеек с указанным цветом заливки color в диапазоне range.

Запустите эту функцию для диапазона A1:F10, предварительно закрасив некоторые ячейки этого диапазона, проверьте правильно ли работает функция.

На следующем уроке

Освоим работу с листами и книгами — массовое создание, копирование и анализ данных!

• 4 способа создания и удаления листов;
• как копировать данные между книгами без открытия файлов;
• работу с десятками файлов через VBA;
• секреты ускорения операций с книгами;
• защиту и скрытие листов программно.

Теперь вы можете обрабатывать тысячи строк без ручного труда!

Возникли трудности, или вы успешно сделали домашнее задание? В любом случае — оставляйте вопросы и отзывы в комментариях!

Вступайте в наше сообщество ВК ustanovkaos_ru, чтобы не пропустить выход следующих уроков!