Цикл vba: VBA Excel. Циклы

Как создать и использовать цикл FOR-NEXT в VBA Excel — быстро и легко

Приложения

0 1.515 3 минут на чтение

Visual Basic характеризуется использованием структурного программирования в коде с целью повышения качества и читаемости кода. Структуры вроде for — next позволяет вам определить количество выполнений инструкции . Цикл For-next можно использовать в VBA Excel для повторить действие в течение указанного времени .

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

Что такое структурное программирование?

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

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

Как создать и использовать цикл for-next в VBA?

Цикл for-next — один из наиболее часто используемых типов циклов в VBA из-за его частого использования. Его синтаксис легко понять:

для start_variable до limit_value
     Инструкции внутри пряжки следующий start_variable
‘увеличивает переменную и конец инструкции

начальная_переменная: Это переменная, которая содержит начальное значение. В общем, перед запуском цикла переменной присваивается значение, и ее синтаксис будет следующим:

начальная_переменная = 1

Это значение увеличивается по мере выполнения условий цикла, пока не достигнет окончательного значения.

лимит_значение: Эта переменная запускает последовательность и подсчитывает количество повторов. Если начальная переменная достигает предельного значения, цикл останавливается. Это может быть переменная со значением, присвоенным перед инструкцией, или прямое значение. Например, если мы хотим, чтобы начальная переменная считала только до 10, синтаксис будет следующим:

для start_variable до 10

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

Пример цикла for-next в VBA

Цикл for-next очень прост в использовании в Visual Basic и может использоваться со многими объектами Excel . Была создана форма с двумя текстовыми полями, в которых пользователь может разместить начальное и конечное значение цикла. В форме есть кнопка, которая при нажатии заполняет поле со списком. Код, присвоенный форме и кнопке, будет следующим:

Dim i As Integer
Dim lim как целое число

Частная подписка btnContar_Click ()
     предел = предел. Текст
     Для i = start.Text Ограничить результат.AddItem
          i
          Затем я
End Sub

• Первые две строки кода создать переменные i и lim как целое числовое значение.
• Затем, нажав на кнопку, значение должен быть присвоено переменной lim в в зависимости от установленного пользователем предельного значения.
• Начните цикл for-next, где значение i равно начальному значению что разместил пользователь. Это значение будет увеличиваться, пока не станет равным значению lim.
• Каждый раз, когда цикл выполняется, в поле со списком добавляется новое значение. Enfin, переменная i увеличивает свое значение + 1 когда инструкция закончится.

Do-Loop в VBA

Есть два типа структур Do-Loop: Do-While и Do-Until, похож на цикл While . Делай-пока повторяйте инструкции, пока выполняется условие , то есть его значение истинно. В отличие от Do-until, который выполняется до тех пор, пока условие не изменит свое значение на true, то есть на истинное.

Синтаксис обеих структур одинаковый, который меняется в зависимости от слова while for up.

Выполнить, пока условие ‘while может быть изменено на до
    Инструкции в петле
Exit do Exit on 
    инструкции цикла
букле

Подобные предметы

Как сделать цикл в excel vba?

Содержание

• 0.1 Цикл «For … Next»
• 0.2 Цикл «For Each»
• 0.3 Оператор прерывания цикла «Exit For»
• 1 VBA цикл For Next
• 2 VBA обратный цикл For Loop с инструкцией STEP
• 3 VBA цикл For Each … Next
  • 3.1 Прерывание  цикла VBA
  • 3.2 Пропуск части цикла в For Each
  • 3.3 Цикл For в VBA
  • 3.4 Цикл For Each в VBA
  • 3.5 Цикл While в VBA
  • 3.6 Цикл Until в VBA
• 4 Синтаксис цикла For…Next
• 5 Компоненты цикла For…Next
• 6 Примеры циклов For…Next
  • 6. 1 Простейший цикл
  • 6.2 Простейший цикл с шагом
  • 6.3 Цикл с отрицательными аргументами
  • 6.4 Вложенный цикл
  • 6.5 Выход из цикла
  • 6.6 Цикл с дробными аргументами

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

К циклам VBA относятся:

• Цикл For
• Цикл Do While
• Цикл Do Until

Далее мы подробно рассмотрим каждый из этих циклов.

Оператор цикла «For» в Visual Basic

Структура оператора цикла For в Visual Basic может быть организована в одной из двух форм: как цикл For … Next или как цикл For Each.

Цикл «For … Next»

Цикл For … Next использует переменную, которая последовательно принимает значения из заданного диапазона. С каждой сменой значения переменной выполняются действия, заключённые в теле цикла. Это легко понять из простого примера:

For i = 1 To 10     Total = Total + iArray(i)  Next i

В этом простом цикле For … Next используется переменная i, которая последовательно принимает значения 1, 2, 3, … 10, и для каждого из этих значений выполняется код VBA, находящийся внутри цикла. Таким образом, данный цикл суммирует элементы массива iArray в переменной Total.

В приведённом выше примере шаг приращения цикла не указан, поэтому для пошагового увеличения переменной i от 1 до 10 по умолчанию используется приращение . Однако, в некоторых случаях требуется использовать другие значения приращения для цикла. Это можно сделать при помощи ключевого слова Step, как показано в следующем простом примере.

For d = 0 To 10 Step 0.1     dTotal = dTotal + d  Next d

Так как в приведённом выше примере задан шаг приращения равный 0.1, то переменная dTotal для каждого повторения цикла принимает значения 0. 0, 0.1, 0.2, 0.3, … 9.9, 10.0.

Для определения шага цикла в VBA можно использовать отрицательную величину, например, вот так:

For i = 10 To 1 Step -1     iArray(i) = i  Next i

Здесь шаг приращения равен -1, поэтому переменная i с каждым повторением цикла принимает значения 10, 9, 8, … 1.

Цикл «For Each»

Цикл For Each похож на цикл For … Next, но вместо того, чтобы перебирать последовательность значений для переменной-счётчика, цикл For Each выполняет набор действий для каждого объекта из указанной группы объектов. В следующем примере при помощи цикла For Each выполняется перечисление всех листов в текущей рабочей книге Excel:

Dim wSheet As Worksheet    For Each wSheet in Worksheets     MsgBox "Найден лист: " & wSheet.Name  Next wSheet

Оператор прерывания цикла «Exit For»

Оператор Exit For применяется для прерывания цикла. Как только в коде встречается этот оператор, программа завершает выполнение цикла и переходит к выполнению операторов, находящихся в коде сразу после данного цикла. Это можно использовать, например, для поиска определённого значения в массиве. Для этого при помощи цикла просматривается каждый элемент массива. Как только искомый элемент найден, просматривать остальные нет необходимости – цикл прерывается.

Применение оператора Exit For продемонстрировано в следующем примере. Здесь цикл перебирает 100 записей массива и сравнивает каждую со значением переменной dVal. Если совпадение найдено, то цикл прерывается:

For i = 1 To 100     If dValues(i) = dVal Then        IndexVal = i        Exit For     End If  Next i

Цикл «Do While» в Visual Basic

Цикл Do While выполняет блок кода до тех пор, пока выполняется заданное условие. Далее приведён пример процедуры Sub, в которой при помощи цикла Do While выводятся последовательно числа Фибоначчи не превышающие 1000:

'Процедура Sub выводит числа Фибоначчи, не превышающие 1000  Sub Fibonacci()     Dim i As Integer 'счётчик для обозначения позиции элемента в последовательности     Dim iFib As Integer 'хранит текущее значение последовательности     Dim iFib_Next As Integer 'хранит следующее значение последовательности     Dim iStep As Integer 'хранит размер следующего приращения       'инициализируем переменные i и iFib_Next     i = 1     iFib_Next = 0     'цикл Do While будет выполняться до тех пор, пока значение     'текущего числа Фибоначчи не превысит 1000       Do While iFib_Next < 1000        If i = 1 Then           'особый случай для первого элемента последовательности           iStep = 1           iFib = 0        Else           'сохраняем размер следующего приращения перед тем, как перезаписать           'текущее значение последовательности           iStep = iFib           iFib = iFib_Next        End If          'выводим текущее число Фибоначчи в столбце A активного рабочего листа        'в строке с индексом i        Cells(i, 1). Value = iFib        'вычисляем следующее число Фибоначчи и увеличиваем индекс позиции элемента на 1        iFib_Next = iFib + iStep        i = i + 1     Loop    End Sub

В приведённом примере условие iFib_Next < 1000 проверяется в начале цикла. Поэтому если бы первое значение iFib_Next было бы больше 1000, то цикл бы не выполнялся ни разу.

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

Схематично такой цикл Do While с проверяемым условием в конце будет выглядеть вот так:

Do  ...  Loop While iFib_Next < 1000

Цикл «Do Until» в Visual Basic

Цикл Do Until очень похож на цикл Do While: блок кода в теле цикла выполняется раз за разом до тех пор, пока заданное условие выполняется (результат условного выражения равен True). В следующей процедуре Sub при помощи цикла Do Until извлекаются значения из всех ячеек столбца A рабочего листа до тех пор, пока в столбце не встретится пустая ячейка:

iRow = 1  Do Until IsEmpty(Cells(iRow, 1))     'Значение текущей ячейки сохраняется в массиве dCellValues     dCellValues(iRow) = Cells(iRow, 1).Value     iRow = iRow + 1  Loop

В приведённом выше примере условие IsEmpty(Cells(iRow, 1)) находится в начале конструкции Do Until, следовательно цикл будет выполнен хотя бы один раз, если первая взятая ячейка не пуста.

Однако, как было показано в примерах цикла Do While, в некоторых ситуациях нужно, чтобы цикл был выполнен хотя бы один раз, не зависимо от первоначального результата условного выражения. В таком случае условное выражение нужно поместить в конце цикла, вот так:

Do  ...  Loop Until IsEmpty(Cells(iRow, 1))

Урок подготовлен для Вас командой сайта office-guru. ru
Источник: /> Перевел: Антон Андронов

Правила перепечаткиЕще больше уроков по Microsoft Excel

Оцените качество статьи. Нам важно ваше мнение:

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

VBA цикл For Next

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

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

Пример цикла

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

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

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

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

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

VBA цикл For Each … Next

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

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

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

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

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

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

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

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

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

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

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

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

Excel VBA. Цикл в макросе

Создадим самый простой цикл используя VBA в Excel.

В этот раз напишем всё своими руками:

  Sub Заполнение()  'Запускаем цикл от 1 до 10  'Начало цикла  For i = 1 To 10  'В первый столбец записываем порядковый номер  Cells(i + 1, 1) = i  'Во второй столбец записываем произведение порядкового номера и значения указанного в ячейке "B1"  Cells(i + 1, 2) = i * Range("b1"). Value  'Конец цикла  Next  End Sub

Расшифрую, приведённый выше код VBA:

• Весь код макроса заключается между Sub и End Sub, после Sub пишется название макроса.
• Текст после ‘ означает комментарий
• Цикл заключается между For … to и Next
• i — в моём случае переменная, Вы можете выбрать любую другую, в том числе поддерживаются и русские обозначения (например: For переменная=1 To 10 …)
• Cells(строка, столбец) – ячейка в которую хотим что-то записать или из которой хотим извлечь информацию, в нашем случае столбец мы указали константу (1 и 2), а строку сделали переменной (i+1).
• Range(“b1”) возвращает значение ячейки “B1”

А ещё, можно сразу дописать минимакрос по очистке диапазона от значений

  Sub Очистка()  Range("A2:B11").ClearContents  End Sub

Доброго времени суток! Данную статью я решил посвятить рубрике по основам программирования в Visual Basic for Application. И сегодня мы поговорим о циклах в VBA, разберём их синтаксис и рассмотрим несколько примеров, которые часто встречаются программисту.

Сначала напомню, что цикл — процесс повторения части кода, выполняемый, пока не будет выполнено или нарушено заданное нами условие.

В данной статье мы разберём синтаксис и примеры следующих циклов в VBA:

• For
• For each
• While
• Until

Цикл For в VBA

Цикл for в VBA обычно используется при зацикливании фрагмента кода, если нам известно конечное значение counter — счетчика, при котором мы выйдем из цикла.
Возьмём для примера самый распространённый пример:

Сгенерировать массив из 5 целых значений

Dim mas(5) As Integer For i% = 0 To 4     mas(i) = Int((10 * Rnd) + 1) Next i 

Обратите ваше внимание, что в этом примере используется неявное объявление при работе с циклами в VBA. i% — означает неявное объявление переменной i в формате integer. Такая конструкция по сути заменяет следующую: dim i as integer. Это используется для сокращения кода и для удобства написания и чтения. В старых версиях VBA необходимо указывать знак формата после каждого использования неявной переменной. В более поздних версиях достаточно всего один раз.

VBA для цикла for даёт возможность использовать функцию Step. Как ясно из перевода, это шаг, с которым мы будем проходить наш интервал. По умолчанию, он равен 1. Популярный вариант использования встречается в случаях, когда counter связан с переменной, используемой внутри цикла. Например, при написании программ, связанных с функциями.

Найти пересечение графика функции y = 5*x + 5 с осью ординат

Function expr(x As Integer) As Integer     expr = 5 * x + 5 End Function Sub CodeTown()     Dim i As Integer     For i = -10 To 10 Step 1         If expr(i) = 0 Then MsgBox "При Y = 0, Х = "+ CStr(i)     Next i End Sub 

Теперь представим, что у нас достаточно большой диапазон и мы не хотим заставлять компьютер считать лишние итерации. На этот случай существует команда Exit For. Перепишем наш последний пример с новой командой.

Function expr(x As Integer) As Integer     expr = 5 * x + 5 End Function Sub CodeTown()     Dim i As Integer     For i = -10 To 10 Step 1         If expr(i) = 0 Then             MsgBox "При Y = 0, Х = "+ CStr(i)             Exit For         End If     Next i End Sub 

C помощью команды Exit можно закончить выполнение любого цикла в VBA. Достаточно указать после Exit название используемого цикла. Также им возможно завершить работу любой процедуры или функции.

Цикл For Each в VBA

For Each в VBA основан на переборе всех элементов, указанного типа в массиве, объекте или группе.
Самый популярный вариант его использования — перебор страниц в рабочей книге.

Вывести названия всех листов в рабочей книге

For Each ws In ThisWorkbook.Worksheets     MsgBox ws.Name Next ws 

И ещё один интересный пример:

Изменить размер шрифта и выравнить по центру текст в label

For Each element In UserForm1. Controls     If InStr(1, UserForm1.Controls.Item(i%).Name, "Label") > 0 Then         UserForm1.Controls.Item(i%).TextAlign = fmTextAlignCenter         UserForm1.Controls.Item(i%).Font.Size = 20         i% = i% + 1     End If Next element 

Тут следует понимать, что через Controls можно обратиться к любому элементу формы. Если отфильтровать по имени, например, как мы сделали выше, то можно выделить группы элементов и изменить их свойства. В данном случае, размер шрифта и выравнивание.

Цикл While в VBA

Циклы в VBA, которые используют структуру Do..Loop (это while и until циклы) можно записывать с разным расположением фрагмента условия. Как видите на картинке выше, условие может проверяться после выполнения одной итерации, а может перед запуском цикла.
Самый популярный пример:

Отсортируйте по возрастанию сгенерированный массив методом пузырька

Dim mas(5) As Integer For i% = 0 To 4     mas(i%) = Int((10 * Rnd) + 1) Next i Dim count As Integer, temp As Integer count = 1 Do While count > 0     count = 0     For i% = 0 To 3         If mas(i) > mas(i + 1) Then             temp = mas(i)             mas(i) = mas(i + 1)             mas(i + 1) = temp             count = count + 1         End If     Next i% Loop 

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

Цикл Until в VBA

Как видите, отличия от while крайне несущественные. Цикл Until в VBA можно реализовать с помощью конструкции while NOT (condition). Тем не менее, приведу пример:

Заставить пользователя ввести число

Dim temp As Variant Do     temp = InputBox("Введите число") Loop Until IsNumeric(temp) 

Почему заставить? Потому, что если пользователь закроет окно ввода, это его не спасёт, оно будет появляться вновь и вновь пока он не введёт любое число.

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

Цикл For…Next в VBA Excel, его синтаксис и описание отдельных компонентов. Примеры использования цикла For…Next.

Цикл For…Next в VBA Excel предназначен для выполнения группы операторов необходимое количество раз, заданное управляющей переменной цикла — счетчиком. При выполнении цикла значение счетчика после каждой итерации увеличивается или уменьшается на число, указанное выражением оператора Step, или, по умолчанию, на единицу. Когда необходимо применить цикл к элементам, количество которых и индексация в группе (диапазон, массив, коллекция) неизвестны, следует использовать цикл For Each… Next.

1. Синтаксис цикла For…Next
2. Компоненты цикла For…Next
3. Примеры циклов For…Next
   • Простейший цикл
   • Простейший цикл с шагом
   • Цикл с отрицательными аргументами
   • Вложенный цикл
   • Выход из цикла
   • Цикл с дробными аргументами

Синтаксис цикла For…Next

  For counter = start To end                     Next   

  For счетчик = начало To конец                     Next   

В квадратных скобках указаны необязательные атрибуты цикла For…Next.

Компоненты цикла For…Next

Компонент	Описание
counter	Обязательный атрибут. Числовая переменная, выполняющая роль счетчика, которую еще называют управляющей переменной цикла.
start	Обязательный атрибут. Числовое выражение, задающее начальное значение счетчика.
end	Обязательный атрибут. Числовое выражение, задающее конечное значение счетчика.
Step*	Необязательный атрибут. Оператор, указывающий, что будет задан шаг цикла.
step	Необязательный атрибут. Числовое выражение, задающее шаг цикла. Может быть как положительным, так и отрицательным.
statements	Необязательный** атрибут. Операторы вашего кода.
Exit For	Необязательный атрибут. Оператор выхода из цикла до его окончания.
Next	Здесь counter — необязательный атрибут. Это то же самое имя управляющей переменной цикла, которое можно здесь не указывать.

*Если атрибут Step отсутствует, цикл For…Next выполняется с шагом по умолчанию, равному .

**Если не использовать в цикле свой код, смысл применения цикла теряется.

Примеры циклов For…Next

Вы можете скопировать примеры циклов в свой модуль VBA, последовательно запускать их на выполнение и смотреть результаты.

Простейший цикл

Заполняем десять первых ячеек первого столбца активного листа цифрами от 1 до 10:

  Sub test1()  Dim i As Long    For i = 1 To 10      Cells(i, 1) = i    Next  End Sub  

Простейший цикл с шагом

В предыдущий цикл добавлен оператор Step со значением 3, а результаты записываем во второй столбец:

  Sub test2()  Dim i As Long    For i = 1 To 10 Step 3      Cells(i, 2) = i    Next  End Sub  

Цикл с отрицательными аргументами

Этот цикл заполняет десять первых ячеек третьего столбца в обратной последовательности:

  Sub test3()  Dim i As Long    For i = 0 To -9 Step -1      Cells(i + 10, 3) = i + 10    Next  End Sub  

Увеличиваем размер шага до -3 и записываем результаты в четвертый столбец активного листа:

  Sub test4()  Dim i As Long    For i = 0 To -9 Step -3      Cells(i + 10, 4) = i + 10    Next  End Sub  

Вложенный цикл

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

  Sub test5()  Dim i1 As Long, i2 As Long    For i1 = 1 To 10  'Пятой ячейке в строке i1 присваиваем 0      Cells(i1, 5) = 0        For i2 = 1 To 4          Cells(i1, 5) = Cells(i1, 5) + Cells(i1, i2)        Next    Next  End Sub  

Выход из цикла

В шестой столбец активного листа запишем названия десяти животных, конечно же, с помощью цикла For…Next:

  Sub test6()  Dim i As Long    For i = 1 To 10      Cells(i, 6) = Choose(i, "Медведь", "Слон", "Жираф", "Антилопа", _      "Крокодил", "Зебра", "Тигр", "Ящерица", "Лев", "Бегемот")    Next  End Sub  

Следующий цикл будет искать в шестом столбце крокодила, который съел галоши. В ячейку седьмого столбца цикл, пока не встретит крокодила, будет записывать строку «Здесь был цикл», а когда обнаружит крокодила, запишет «Он съел галоши» и прекратит работу, выполнив команду Exit For. Это будет видно по ячейкам рядом с названиями животных ниже крокодила, в которых не будет текста «Здесь был цикл».

  Sub test7()  Dim i As Long    For i = 1 To 10      If Cells(i, 6) = "Крокодил" Then        Cells(i, 7) = "Он съел галоши"        Exit For          Else        Cells(i, 7) = "Здесь был цикл"      End If    Next  End Sub  

Результат работы циклов For…Next из примеров:

Результат работы циклов For…Next

Такие данные на активном листе Excel вы получите, если последовательно запустите на выполнение в редакторе VBA все семь подпрограмм из примеров, демонстрирующих работу циклов For…Next.

Цикл с дробными аргументами

Атрибуты start, end и step могут быть представлены числом, переменной или числовым выражением:

  For i = 1 To 20 Step 2  For i = a To b Step c  For i = a - 3 To 2b + 1 Step c/2     

В результате вычисления значения переменной вне цикла или выражения внутри его может получиться дробный результат. VBA Excel округлит его до целого числа, используя бухгалтерское округление:

  'Значения атрибутов до округления  For i = 1.5 To 10.5 Step 2.51  'Округленные значения атрибутов  For i = 2 To 10 Step 3     

Старайтесь не допускать попадания в тело цикла For…Next неокругленных значений аргументов, чтобы не получить непредсказуемые результаты его выполнения. Если без дробных чисел не обойтись, а необходимо использовать обычное округление, применяйте функцию рабочего листа WorksheetFunction.Round для округления числа перед использованием его в цикле For…Next.

циклов VBA | Изучите различные типы циклов VBA в Excel

Обновлено 8 апреля 2023 г.

Циклы можно считать наилучшим функционирующим компонентом VBA, а также сердцевиной макросов. Обычно они помогают сократить количество строк в коде и выполнить работу за считанные минуты, на что у человека могут уйти часы.

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

Если вы раньше не использовали скрипт VBA, вы можете просто открыть книгу Excel и нажать Alt + F11 . Он откроет проект VBA.

Давайте углубимся внутрь.

Предположим, мы хотим напечатать от 1 до 10 чисел за один раз. Мы можем сделать это с помощью следующего кода.

Код:

 Подцикл1()
Отладка. Печать 1
Отладка. Печать 2
Отладка. Печать 3
Отладка. Печать 4
Отладка. Печать 5
Отладка. Печать 6
Отладка. Печать 7
Отладка. Печать 8
Отладка. Печать 9Отладка. Печать 10
End Sub 

Если вы можете быстро пройти код, я добавил Debug.Print один за другим для печати чисел от 1 до 10. После запуска кода мы можем увидеть вывод в Immediate Window. Чтобы открыть окно немедленного просмотра, нажмите Ctrl+G  в любом месте Excel.

Запустите код с помощью клавиши F5 или вручную, как показано на скриншоте ниже. Итак, давайте проверим вывод этого кода.

Типы циклов VBA в Excel

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

Тип № 1 — VBA

Для следующего цикла

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

код:

 Sub loop1()
Для i = от 1 до 10
    Ячейки (я, 1). Значение = я
Далее я
End Sub 

Этот код дает точно такой же результат, как и предыдущий. Однако самое примечательное в этом коде — уменьшение количества строк. Мы сократили код из 12 строк до кода из 5 строк.

В этом коде loop1 — это имя макроса. Цикл for начинается с i = 1, он печатает значение i (т.е. 1) в ячейке excel, а в Next снова переходит к началу цикла for и делает i = 2 печатает значение i (т.е. 2) в следующей ячейке и так далее, пока код не достигнет своего последнего значения, т. е. 10. Как только код достигнет последнего значения i (10), код завершится сам из-за отсутствия любого целочисленного значения выше 10.

Запустите код с помощью клавиши F5 или вручную. Таким образом, вы можете увидеть вывод, как показано ниже.

Тип № 2 — Цикл VBA For с прямым шагом

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

Как подсказывает общая математика, числа, находящиеся на расстоянии 2 друг от друга (начиная с первого четного числа 2), считаются четными числами, верно?

Применим ту же логику. См. приведенный ниже код для справки.

Код:

 Sub ForwardStep()
Для i = 2 до 20 Шаг 2
    Ячейки(i,  1).Значение = i
Далее я
End Sub 

Для значений, начинающихся с 2, этот цикл позволяет печатать 2, 4, 6, 8…20 в последующих ячейках Excel. Шаг 2 явно задан так, что, начиная с номера 2, код будет переходить к следующему номеру с шагом 2 (т.е. будет напечатано 4) и так далее. Этот процесс будет продолжаться до тех пор, пока в ячейке не появится число 20. Как только в ячейке появится 20, цикл автоматически прервется.

Запустите код с помощью клавиши F5 или вручную. Таким образом, вы можете увидеть вывод, как показано ниже.

Тип № 3 — Цикл VBA с шагом назад

 В так же, как и для цикла с шагом вперед, мы можем использовать цикл for с шагом назад. Например, что, если мы установим наш шаг назад как -2 и запустим цикл for со значениями 20 до 2?

Посмотрим, как это работает.

код:

 Sub BackwardStep()
Для i = 20 до 2 Шаг -2
Отладка.Печать i
Далее я
Конец суб 

После запуска кода мы можем увидеть вывод в Immediate Window. Чтобы открыть окно немедленного просмотра, нажмите Ctrl+G  в любом месте Excel.

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

Тип № 4 — VBA Nested For Loop

Мы также можем вложить цикл For. Однако в коде не рекомендуется делать вложенность одного и того же цикла более трех раз (это может быть расценено как ошибка в логике).

См. приведенный ниже код вложенного цикла for.

Код:

 Sub NestedFor()
Для i = от 1 до 10
Для j = от 1 до 2
           Ячейки(i, j).Значение = i * j
Следующий j
Далее я
End Sub 

В этом примере при i = 1 и j = 1 макрос помещает значение i * j (1 * 1) на пересечение строки i = 1 и столбца j = 1 (т.е. ячейка A1) и переходит к следующему значению j (2), сохраняя i = 1. На пересечении строки i = 1 и столбца j = 2 (т.е. ячейки B1) макрос вводит значение I * j (1 * 2). Цикл игнорирует следующее значение j, так как j изменяется только от 1 до 2 и переходит к следующему значению i.

Для i = 2 и j = 1 макрос вводит значение i * j (2 * 1) в пересечение строки 2 ^nd и столбца 1 ^st (т. е. ячейку A2) и продолжает. До диапазона i и j.

Запустите код с помощью клавиши F5 или вручную, как показано на скриншоте ниже. Таким образом, вы можете увидеть вывод, как показано ниже.

Тип № 5 — Цикл VBA Do While

Цикл Do While — один из самых интересных циклов в VBA. Это позволяет вам проверять условие и записывать вывод (DO), когда определенное условие или набор условий выполняются (или условие/я является/являются ИСТИННЫМИ).

См. код ниже:

Код:

 Sub do_whileloop()
Dim i как целое число
я = 1
Делать, пока я <= 10
Ячейки (я, 1). Значение = я * я
я = я + 1
Петля
End Sub 

Этот цикл работает следующим образом:

Для значений i от 1 (первоначально i = 1) до 10 он вычисляет i * i (т.е. квадрат значения для каждого целочисленного значения i) и вводит его в ячейку с 1 по 10 соответственно. Вывод — это не что иное, как квадраты целых чисел от 1 до 10. Пожалуйста, внимательно посмотрите на Цикл под этим кодом. Добавлять обязательно, иначе система выдаст ошибку.

Запустить код с помощью клавиши F5 или вручную. Таким образом, вы можете увидеть вывод, как показано ниже.

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

Что следует помнить

• При использовании цикла for с шагом назад убедитесь, что начальный номер должен быть больше конечного, и наоборот.
• В цикле Do while убедитесь, что вы используете цикл, чтобы закрыть цикл do while перед завершением всего кода VBA. В противном случае система выдает ошибку компилятора с сообщением об ошибке «Do Without Loop».

Вы можете скачать этот шаблон Excel для циклов VBA здесь – Шаблон Excel для циклов VBA

Рекомендуемые статьи

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

1. Функция VBA
2. ВПР VBA
3. XML-код VBA
4. Изошибка VBA

Как использовать циклы For в Excel VBA: пошаговое руководство (2023)

Перейти к содержимому

Как использовать «циклы for» в Excel VBA: шаг за шагом (2023)

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

Это просто, но это одна из самых мощных вещей, которые вы можете сделать с помощью VBA. (И это избавляет вас от огромного количества ввода.)

В этом руководстве я покажу вам, как использовать определенный тип цикла под названием «циклы For», а также приведу несколько примеров.

Загрузите образец файла Excel здесь и попробуйте сами на практических примерах.

Содержание

Как написать цикл for в VBA

Это цикл For в Excel VBA.

 Пример подцикла()
Для i = от 1 до 5
Ячейки (я, я). Значение = я
Следующий
End Sub 

Приведенный выше код состоит из двух основных частей:

• Счетчик
• Команды

Первая переменная счетчик . Это подсчитывает, сколько раз цикл был выполнен (он также сообщает компьютеру, сколько раз выполнять команды).

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

После того, как цикл будет выполнен указанное количество раз, он завершается, и ваш код VBA продолжает выполняться в строке ниже выхода из цикла VBA (слово «Следующее», которое вы видите в приведенном выше коде).

Чтобы запустить цикл For

Теперь давайте вернемся к предыдущему примеру цикла VBA.

 Пример подцикла()
Для i = от 1 до 5
Ячейки (я, я). Значение = я
Следующий
End Sub 

Чтобы запустить цикл for, как в приведенном выше примере, вам понадобится переменная-счетчик для числового значения.

Вот что делает «Для i = от 1 до 5».

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

Вам не нужно нужно , чтобы использовать i в качестве имени вашей переменной; это может быть что угодно.

Но i обычно используется в программировании, поэтому мы будем придерживаться этого соглашения.

Каспер Лангманн , специалист по Microsoft Office

Сколько раз должен выполняться код

Указанный нами диапазон «от 1 до 5» сообщает Excel, сколько раз выполняется цикл. В этом случае он запустит цикл пять раз.

Крайнее левое число — это начальное значение переменной-счетчика (i).

Итак, при первом выполнении цикла i будет равно 1.

В следующий раз оно будет установлено в 2.

В третий раз, 3.

И так далее…

Код выполняется в цикле

Далее мы пишем строк кода, которые будет выполнять Excel.

В нашем примере это код:

В нашем случае код Cells(i,i) идентифицирует ячейку с координатами ( i, i ).

Да, это i раньше. Таким образом, мы повторно используем значение счетчика переменных, чтобы делать что-то внутри цикла. Это не обязательно, но довольно весело, а иногда и очень удобно 😊

Каспер Лангманн , специалист Microsoft Office

И .Value = я установил значение, равное i.

Да, мы снова используем значение переменной счетчика! 🤯

Как выйти из цикла

Наконец, мы используем Следующее слово , чтобы сообщить Excel, что достиг конца цикла. Затем он начнется заново, если счетчик не исчерпан.

Поскольку For всегда указывает на начало цикла For, а Next всегда указывает на его конец, цикл For часто называют циклом For Next.

Когда счетчик достигает желаемого значения счетчика (в данном случае 5), цикл останавливается.

Вот результат:

Понимание результата

При первом запуске цикла i равно 1. Таким образом, в ячейке A1 установлено значение 1.

Теперь, как (1,1) или (i,i) записывать адреса ячеек? 🤔

Это еще один метод, который вы можете использовать для ссылки на ячейки с кодом VBA. (1,1) означает строку 1, столбец 1.

Итак, (1,1) — это A1. И (2,3) будет C2.

В следующий раз, когда цикл запустится, i будет 2, поэтому ячейка в (2,2) будет установлена ​​на 2.

И так далее.

И окончательное значение (5,5) устанавливается равным 5.

Совет для профессионалов: Загружаемый шаблон Excel

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

Другое Пример цикла for

Вот еще один пример цикла for:

 Sub AverageSales()
 Для я = 2 до 21
  новыйДиапазон = Диапазон (Ячейки (i, 2), Ячейки (i, 5))
  Ячейки (i, 6). Значение = Приложение. Среднее (новый диапазон)
  Следующий
End Sub 

Откройте пример электронной таблицы и перейдите к Visual Basic во вкладке Developer . Или используйте приведенный ниже ярлык:

Windows: Alt + F11
Mac: Fn + ⌥ + F11

Скопируйте и вставьте приведенный выше сценарий в новый модуль и выполняйте его шаг за шагом, используя F8. Посмотрите, как Excel выполняет сценарий, и подумайте о том, как значение i меняет происходящее.

Как использовать вложенные циклы for в VBA

Чтобы получить еще больше от цикла for, вы можете поместить внутрь него еще один цикл for. Это называется вложенными циклами, и хотя поначалу это может быть трудно понять, это очень мощный инструмент.

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

 Sub QuartlyAverage()
Dim breakStatus как целое число
состояние разрыва = 0
Для я = 2 до 21
 новыйДиапазон = Диапазон (Ячейки (i, 2), Ячейки (i, 5))
 Для j = от 2 до 5
  Ячейки(i, j).Выбрать
  Если Ячейки(i, j) = 0 Тогда
   состояние разрыва = 1
   Выход для
   Еще
     состояние разрыва = 0
   Конец, если
  Следующий
Диапазон ("J1"). Значение = состояние разрыва
Если брейкСтатус = 1 Тогда
  Ячейки(i, 6).Value = "Отсутствующие данные"
  Еще
   Ячейки (i, 6). Значение = Приложение. Среднее (новый диапазон)
  Конец, если
 Следующий
Конец суб 

Давайте разберем его по частям. Сначала мы рассмотрим внешний цикл. Вот как это выглядит:

 Для i = от 2 до 21
 новыйДиапазон = Диапазон (Ячейки (i, 2), Ячейки (i, 5))
  Если брейкСтатус = 1 Тогда
   Ячейки(i, 6).Value = "Отсутствующие данные"
   Еще
    Ячейки (i, 6). Значение = Приложение.  Среднее (новый диапазон)
   Конец, если
Далее 

Excel считает от 2 до 21 и просматривает ячейки в диапазоне от ( i , 2) до ( i , 5). В первый раз это будет B1:E1. В следующий раз B2:E2 и так далее.

Затем он проверяет переменную с именем «breakStatus». Если для этой переменной установлено значение 1, Excel записывает «отсутствующие данные» в столбце F.

(Это говорит нам о том, что отсутствует один из показателей продаж. )

Если breakStatus не установлен в 1, он принимает среднее значение ячеек в диапазоне, который мы установили ранее.

Вот и все. Это довольно просто.

Теперь давайте посмотрим на внутренний цикл:

 Для j = от 2 до 5
Если Ячейки(i, j) = 0 Тогда
состояние разрыва = 1
Выход для
Еще
состояние разрыва = 0
Конец, если
Следующие 

Создает новый счетчик j и просматривает отдельные ячейки в каждом цикле. При первом прогоне внешнего цикла i устанавливается равным 1. Таким образом, первый прогон внутреннего цикла будет смотреть на ( i, j) , что равно (1, 2) или A2.

Excel проверяет, установлено ли в этой ячейке значение 0, и если это так, устанавливает breakStatus в 1 и выходит из цикла (это то, что делает «Exit For»; см. ниже).

Если эта ячейка не равна 0, цикл продолжается. При следующем проходе через внутреннюю петлю j будет установлено значение 3, поэтому Excel просматривает A3, чтобы увидеть, установлен ли он на 0.

Прерывание цикла Excel VBA: как выйти из циклов

код: «Выход для».

Отлично подходит для остановки кода, если какое-то условие было выполнено.

Вы почти всегда захотите использовать его как часть оператора if, как в приведенном выше коде.

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

Запускайте сценарий в примере электронной таблицы пошагово, нажимая F8.

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

Изменение значения шага (шаг 2 и т. д.)

Взгляните на приведенный ниже код:

 Sub stepvalueexample()
Для i = от 1 до 20
Ячейки (я, 1). Значение = 5 * я
Следующий
Конец суб 

Он помещает 5 раз i (и начинается с 1) в ячейку A1 и переходит к остальной части столбца A, умножая i на 5.

Так для всех ячеек, пока значение счетчика шагов не достигнет 20.

Итак, 20 раз.

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

 Для i = от 1 до 20 Шаг 2
Ячейки (я, 1). Значение = 5 * я
Далее 

Код «Шаг 2» добавляется после части «от 1 до 20».

Это означает, что любое другое значение i будет просто пропущено циклом.

В данном случае это означает, что если i = 2, 4, 6, 8 и т. д., он не будет включен.