VBA циклы — For Next и For Each в Excel

Опубликовано 21.01.2014 Автор Ренат Лотфуллин
vba цикл excel for each for next

Цикл For Loop в VBA – один из самых популярных циклов в Excel. Данный цикл имеет две формы – For Next и For Each In Next. Данные операторы используются для последовательного  перемещения по списку элементов или чисел. Для завершения цикла мы можем в любой момент использовать команду выхода. Давайте подробнее рассмотрим каждый из этих циклов.

VBA цикл For Next

Цикл For Next имеет следующий синтаксис:

1
2
3
For счетчик = начало_счетчика To конец_счетчика
‘Какое-то действие
Next счетчик

То что мы делаем здесь, по существу, это создаем цикл, который использует переменную счетчик как хранитель времени. Устанавливаем его значение равным начало_счетчика, и увеличиваем (или уменьшаем) на 1 во время каждого витка. Цикл будет выполняться до тех пор, пока значение счетчик не станет равным конец_счетчика. Когда оба эти значения совпадут, цикл выполнится последний раз и остановится.

Пример цикла

1
2
3
4
5
6
Sub пример_цикла1()
For счетчик = 1 to 10
j = счетчик
Next счетчик
msgbox "Значение счетчика на последнем витке равно  " & счетчик
End Sub

Последнее значение переменной счетчик будет равным 11

VBA обратный цикл For Loop с инструкцией STEP

Если у вас появилась необходимость перемещаться от большего значения к меньшему – вы можете использовать цикл в обратном направлении. Вот пример обратного цикла:

1
2
3
4
5
6
Sub пример_цикла2()
For счетчик = 10 to 1 Step -1
j = счетчик
Next счетчик
msgbox "Значение счетчика на последнем витке равно  " & счетчик
End Sub

Последнее значение переменной счетчик будет равным 1.

Как вы могли заметить, мы можем использовать инструкцию Step n для работы цикла как  вперед, так  и в обратном направлении. По умолчанию значение Step равно 1, но оно может быть изменено, если необходимо пропускать какие-либо значения, тогда значение n будет больше одного, или перемещаться в обратном направлении, тогда n будет отрицательным.

VBA цикл For Each … Next

Цикл For Each … Next имеет следующий цикл:

1
2
3
For Each элемент_группы In группа_элементов
‘Какое-то действие
Next элемент_группы

Здесь переменная элемент_группы принадлежит к группе_элементов (железная логика!!!). Я имею в виду, что объект группа_элементов должен быть коллекцией объектов. Вы не сможете запустить цикл For Each для отдельно объекта (Microsoft сразу оповестит вас об этом 438-й ошибкой).

vba циклы excel for each for next

Данный цикл перебирает все элементы какой-либо коллекции, начиная с самого первого. Вы можете использовать данный цикл, если вам необходимо, например, обойти все листы в книге, объекты на листе, сводные таблицы и т.д.

Ниже представлен пример, как можно воспользоваться циклом For Each для просмотра всех листов книги:

1
2
3
4
5
Sub пример_цикла4()
For Each sht In ActiveWorkbook.Worksheets
MsgBox sht.Name
Next sht
End Sub

… либо всех сводных таблиц на листе

1
2
3
4
5
Sub пример_цикла()
For Each pvt In ActiveSheet.PivotTables
MsgBox pvt.Name
Next pvt
End Sub

Прерывание  цикла VBA

Если вам необходимо выйти из цикла до момента, как будет достигнуто условие завершения цикла, воспользуйтесь командой End For в связке с инструкцией IF. В примере, приведенном ниже, мы выйдем из цикла до момента достижения условия завершения, в данном цикле выход будет осуществлен при условии, когда переменная счетчик будет  равна 3.

1
2
3
4
5
6
Sub пример_цикла5()
For счетчик  = 0 To 5
MsgBox счетчик
If (счетчик = 3) Then Exit For
Next счетчик
End Sub

Пропуск части цикла в For Each

Пропускать часть цикла, а затем возвращаться назад – плохая практика. Тем не менее, давайте рассмотрим пример:

1
2
3
4
5
6
7
8
9
10
11
12
Sub пример_цикла6 ()
Dim j As Integer
For i = 0 To 5
b:
If (j = 3) Then GoTo a:
j = i
Next i
a:
j = 4
GoTo b:
MsgBox ("Значение  j = " & j)
End Sub

Здесь мы пропустили одну итерацию (когда j = 3). Как вы думаете, какой результат выдаст программа? 3? 5? Ну… на самом деле, ни один из вариантов не верный. Цикл будет выполняться бесконечно, пока память компьютера не переполнится.

Однако возможность пропустить шаг цикла без последствий существует. Вы можете увеличить значение счетчика на 1 (или другое значение), что приведет к пропуску операций, находящихся между этими значениями. Вот пример:

1
2
3
4
5
6
Sub пример_цикла7()
For i = 0 To 5
i = i + 1
MsgBox i
Next i
End Sub

Но опять же, это плохая практика написания кода, и может привести к нежелательным последствиям при написании кода в будущем. Вместо этого, при необходимости пропуска некоторых итераций, попробуйте использовать функцию If или Select Case.


12 комментариев

  1. Здравствуйте! Подскажите можно ли как нибудь сделать, чтобы цикл перемещался по столбцу и искал наименьшее значение, удалял его и снова искал и снова удалял. Остановиться должен лишь тогда, когда сумма значений в этом столбце не будет равна или меньше, какого то базового значения.

    Ответить на комментарий

  4. Доброго дня Вам!!!
    Не сможете ли мне помочь, с циклами …
    Я записал макрекордером макрос, который одну строку в таблице «Условно Форматирует».Строка по формуле по значению окрашивается в синий цвет …
    но мне нужно чтобы по этой формуле все остальные строчки таблицы так же окрашивались бы по VBA
    По-другому вопрос звучит так: как впихнуть макрос который сам записался с помощью
    макрорекордером …в цикл чтобы остальные строчки по условному форматированию тоже окрасились
    Range(«I13:AO13»).Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
    «=$A$5>=$I$13:$AO$13»
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = 16764006
    .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    ActiveWindow.ScrollColumn = 33
    ActiveWindow.ScrollColumn = 25
    ActiveWindow.ScrollColumn = 24
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Range(«O15»).Select
    End Sub

    Ответить на комментарий

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *