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

Содержание

2.5.2 VBA. Организация циклов | Пакеты прикладных программ

Учебные программы » Пакеты прикладных программ » Конспект лекций » 2.5.2 VBA. Организация циклов

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

VBA поддерживает циклические конструкции двух видов:

Циклы с фиксированным числом повторений (циклы со счетчиком).
Циклы с неопределенными числом повторений (циклы с условием).

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

Фиксированные циклы

VBA предоставляет две управляющие структуры для организации фиксированного цикла: For … Next (цикл со счетчиком) и For Each … Next (цикл с перечислением).

Оператор For … Next это типовой цикл со счетчиком, выполняющий заданное число итераций. Синтаксис оператора For … Next:

For <счетчик> = <начЗначение> То <конЗначение> [Step <приращение>] <блок операторов> Next [<счетчик>]

Пример использования оператора For … Next.

Листинг 9. Оператор For … Next

' ЗАДАЧА: Составить программу, которая получает два числа от пользователя. ' Складывает все числа в диапазоне, заданном этими двумя числами, а затем ' отображает результирующую сумму. Sub sample7() Dim i As Integer ‘счетчик цикла Dim sStart ‘начальное значение счетчика Dim sEnd ‘конечное значение счетчика Dim sSum As Long ‘результирующая сумма sStart = InputBox("Введите первое число:") sEnd = InputBox("Введите второе число:") sSum = 0 For i = CInt(sStart) To CInt(sEnd) sSum = sSum + i Next i MsgBox "Сумма чисел от " & sStart & " до " & sEnd & " равна: " & sSum End Sub

Оператор цикла For Each … Next относится к категории операторов объектного типа, т. е. применяется в первую очередь к коллекциям объектов, а также к массивам. Тело цикла выполняется фиксированное число раз, соответствующее числу элементов массива или коллекции. Формат оператора For Each … Next:

For Each <элемент> In <группа> <блок операторов> Next [<элемент>]

Циклы с условием (неопределенные циклы)

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

Четыре вида циклов Do..Loop, которые различаются типом проверяемого условия и временем выполнения этой проверки.
Непрерываемый цикл While … Wend.

Цикл Do While … Loop — типичный цикл с предусловием. Условие проверяется до того, как выполняется тело цикла. Цикл продолжает свою работу, пока это <условие> выполняется (т.е. имеет значение True). Так как проверка выполняется в начале, то тело цикла может ни разу не выполниться. Формат цикла Do While … Loop:

Do While <условие> <блок операторов> Loop

Листинг 10. Цикл Do While … Loop

' ЗАДАЧА: Составить программу, которая предусматривает ввод пользователем ' произвольной последовательности чисел. Ввод должен быть прекращен ' только после того, как сумма введенных нечетных чисел превысит 100. Sub sample8() Dim OddSum As Integer ‘сумма нечетных чисел Dim OddStr As String ‘строка с нечетными числами Dim Num ‘для приема вводимых чисел OddStr = "" ‘инициализация выходной строки OddSum = 0 ‘инициализация суммы OddSum Do While OddSum < 100 ‘начало цикла Num = InputBox("Введите число: ") If (Num Mod 2) <> 0 Then ‘проверка на четность OddSum = OddSum + Num ‘накопление суммы нечетных чисел OddStr = OddStr & Num & " " End If Loop 'вывод строки с нечетными числами MsgBox prompt:="Нечетные числа: " & OddStr End Sub

Оператор Do … Loop While предназначен для организации цикла с постусловием. Условие проверяется после того, как тело цикла, будет выполнено хотя бы один раз. Цикл продолжает свою работу, пока <условие> остается истинным. Формат цикла Do … Loop While:

Do <блок операторов> Loop While<условие>

Листинг 11. Цикл с постусловием

' ЗАДАЧА: Составить программу игры "Угадай число". Программа должна случайным ' образом генерировать число в диапазоне от 1 до 1000, пользователь должен ' угадать это число. Программа на каждое вводимое число выводит подсказку ' "больше" или "меньше". Sub sample8() Randomize Timer ' инициализация генератора случайных чисел Dim msg As String ' строка сообщения Dim SecretNumber As Long, UserNumber As Variant Begin:	SecretNumber = Round(Rnd * 1000) ' число, сгенерированное компьютером UserNumber = Empty ' число, вводимое пользователем Do ' игровой процесс Select Case True Case IsEmpty(UserNumber): msg = "Введите число" Case UserNumber > SecretNumber: msg = "Слишком много!" Case UserNumber < SecretNumber: msg = "Слишком мало!" End Select UserNumber = InputBox(prompt:=msg, Title:="Угадай число") Loop While UserNumber <> SecretNumber ' проверка If MsgBox("Играть еще? ", vbYesNo + vbQuestion, "Вы угадали!") = vbYes Then GoTo Begin End If End Sub

Циклы Do Until … Loop и Do … Loop Until являются инверсиями ранее рассмотренных циклов с условием. В общем случае они работают аналогично, за исключением того, что тело цикла выполняется при ложном условии (т.е. <условие>=False). Формат цикла Do Until … Loop:

Do Until <условие> <блок операторов> Loop

Формат цикла Do … Loop Until:

Do <блок операторов> Loop Until<условие>

Практическое задание: Перепишите программы из листингов 10 и 11 с использованием инвертированных операторов цикла.

Цикл While … Wend также относится к циклам с условием. Данный оператор полностью соответствует структуре Do While … Loop. Формат цикла While … Wend:

While <условие> <блок операторов> Wend

Отличительной особенностью этого оператора является невозможность принудительного завершения (прерывания) тела цикла (оператор Exit Do не работает в цикле While … Wend).

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

Для досрочного завершения итерации и выхода из цикла применяется оператор Exit. Этот оператор применим в любой циклической структуре, кроме While . .. Wend. Общий синтаксис использования Exit для прерывания цикла таков:

<начало_цикла> [<блок операторов1>] Exit (For | Do) [<блок операторов2>] [Exit (For | Do)] ... <конец_цикла>

При выполнении оператора Exit цикл прерывается, и управление передается оператору, следующему за оператором <конец_цикла>. В теле цикла может присутствовать несколько операторов Exit.

Листинг 12. Принудительный выход из цикла

Sub sample9() For i = 1 To 10000000 If i = 10 Then Exit For ' выход из цикла, когда счетчик достигнет 10 Next End Sub

CC-BY-CA Анатольев А.Г., 31.01.2012

Разделы дисциплины

Методические материалы

Конспект лекций

Лабораторный практикум

Дополнительные материалы

Материалы раздела

Полный конспект лекций (.pdf, 2.3МБ)

Тема 1.1 Введение в предмет. Понятие ППП

Тема 1.2 Структура и основные компоненты ППП

Тема 1.3 Эволюция ППП

1.3.1 Примеры современных прикладных пакетов

Тема 2. 1 Структура и состав MS Office. Основные приложения

Тема 2.2 Введение в офисное программирование

Тема 2.3 Макросы. Использование макрорекордера

Тема 2.4 Среда разработки VBE

Тема 2.5 Синтаксис VBA

2.5.1 VBA. Ветвления

2.5.2 VBA. Организация циклов

2.5.3 VBA. Процедуры и функции

2.5.4 VBA. Модули

2.5.5 Структурные типы данных

Тема 2.6 Объектно-ориентированное программирование в VBA

Тема 2.7 Объектная модель компонентов MS Office. Библиотеки типов

Тема 2.8 Разработка приложений для MS Office

Тема 2.9 Формы и компоненты управления. Обработка событий

Тема 2.10 Интеграция с внешними приложениями

Тема 3.1 Структура и состав ППП 1С:Предприятие. Режимы работы

Тема 3.2 Основные компоненты ППП 1С:Предприятие. Конфигурации и информационные базы

Тема 3.3 Объекты метаданных

Тема 3.4 Конфигуратор. Назначение и возможности

Тема 3.5 Разработка приложений в 1С

Тема 3. 6 Входной язык 1С. Общий синтаксис

Тема 3.7 Библиотечные процедуры и функции

Тема 3.8 Взаимодействие с внешними приложениями

Тема 3.9 Отладка и профилирование

Тема 3.10 Управление пользователями в 1С

Тема 3.11 Сервисное обслуживание информационных баз

4.1 Основные тенденции в развитии ППП

Связанные темы

Рабочая программа, методика рейтингового контроля, экзаменационные вопросы (скачать 3.6МБ)

Полный конспект лекций (.pdf, 2.3МБ)

Тема 1.1 Введение в предмет. Понятие ППП

Тема 1.2 Структура и основные компоненты ППП

Тема 1.3 Эволюция ППП

1.3.1 Примеры современных прикладных пакетов

Тема 2.1 Структура и состав MS Office. Основные приложения

Тема 2.2 Введение в офисное программирование

Тема 2.3 Макросы. Использование макрорекордера

Тема 2.4 Среда разработки VBE

Тема 2.5 Синтаксис VBA

2.5.1 VBA. Ветвления

2.5.4 VBA. Модули

2.5.5 Структурные типы данных

Тема 2. 6 Объектно-ориентированное программирование в VBA

Тема 2.7 Объектная модель компонентов MS Office. Библиотеки типов

Тема 2.8 Разработка приложений для MS Office

Тема 2.9 Формы и компоненты управления. Обработка событий

Тема 2.10 Интеграция с внешними приложениями

Тема 3.1 Структура и состав ППП 1С:Предприятие. Режимы работы

Тема 3.3 Объекты метаданных

Тема 3.4 Конфигуратор. Назначение и возможности

Тема 3.5 Разработка приложений в 1С

Тема 3.6 Входной язык 1С. Общий синтаксис

Тема 3.9 Отладка и профилирование

Тема 3.10 Управление пользователями в 1С

Тема 3.11 Сервисное обслуживание информационных баз

4.1 Основные тенденции в развитии ППП

Программирование циклов в VBA

Использование интерактивной доски в учебном процессе

История и возможности текстового редактора Microsoft Word

Антивирусное ПО: что выбрать?

Как преобразовать файл .docx в .doc.

Возможности Movavi Screen Capture Studio

Аренда программного обеспечения — SaaS

Переход с версии 1С:Бухгалтерия 7. 7 на 8

Переносимые приложения

Фрилансеры — «интеллектуальные наемники»

Самостоятельный выбор компьютера

Дипломы на заказ – стоит ли покупать?

Репетиторы готовы помочь всегда

Основные этапы написания контрольной работы

VBA Операторы цикла. Вложенные циклы

7.1 Операторы цикла
7.2 Вложенные циклы

7.1 Операторы цикла.

Циклы позволяют выполнить одну или несколько строк кода несколько раз. VBA поддерживает следующие циклы:


For...Next
For Each...Next
Do... Loop

Конструкция For . . . Next. Когда число повторений известно заранее, используют цикл For . . . Next. В цикле For используется переменная, называемая переменной цикла или счетчиком цикла, которая увеличивается или уменьшается на заданную величину при каждом повторении цикла. Синтаксис этой конструкции следующий:


For counter = start To end [Step increment]
 операторы 
Next [counter]

Параметры counter (счетчик), start (начало цикла), end (конец цикла) и increment (приращение) являются числовыми.

Примечание. Параметр increment может быть как положительным, так и отрицательным. Если он положителен, параметр start должен быть меньше или равен параметру end, иначе цикл не будет выполняться. Если параметр increment отрицателен, то параметр start должен быть больше или равен значению параметра end, чтобы выполнялось тело цикла. Если параметр Step не задан, то значение параметра increment по умолчанию равно 1.

VBA выполняет цикл For в следующей последовательности:

1. Устанавливает значение переменной цикла counter в значение start.

2. Сравнивает значение переменной цикла counter и значение параметра end. Если переменная counter больше, VBA завершает выполнение цикла. (Если значение параметра increment отрицательно, то VBA прекращает выполнение цикла при условии, что значение переменной цикла counter меньше значения параметра end.)

3. Выполняет операторы тела цикла statements.

4. Увеличивает значение переменной цикла counter на 1 или на величину значения параметра increment, если он задан.

5. Повторяет шаги со 2 по 4.

Рассмотрим пример: Вычислить значение функции f(t)

при заданных a, b, n, если t изменяется от a до b с шагом Dt=(b-a)/(n-1).


Sub пример3()
Dim f() As Single
Dim a As Single, b As Single, t As Single, dt As Single
Dim i As Integer, n As Integer
Call read("a1", a) : Call read("b1", b) : Call read("c1", n)
ReDim f(1 To n - 1)
dt = (b - a) / (n - 1) : t = a
Call out("a2", "i") : Call out("b2", "t") : Call out("c2", "f(t)")
For i = 1 To n - 1
t = t + dt
If t <= -1 Then
f(i) = -1
ElseIf t > 1 Then
f(i) = 1
Else
f(i) = t
End If
Call out("a" & (2 + i), i) : Call out("b" & (2 + i), t) : Call out("c" & (2 + i), f(i))
Next i
End Sub

Конструкция For Each . . . Next

Цикл For Each . . . Next похож на цикл For . . . Next, но он повторяет группу операторов для каждого элемента из набора объектов или из массива, вместо повторения операторов заданное число раз. Он особенно полезен, когда неизвестно, сколько элементов содержится в наборе.

Синтаксис конструкции цикла For Each . . . Next таков:


For Each element In group
  операторы 
Next element

Следует помнить следующие ограничения при использовании цикла For Each . . . Next:

Для наборов параметр element может быть только переменной типа variant, общей переменной типа object или объектом, перечисленным в Object Browser

Для массивов параметр element может быть только переменной типа Variant

Нельзя использовать цикл For Each . . . Next с массивом, имеющим определенный пользователем тип, так как переменная типа variant не может содержать значение определенного пользователем типа

Конструкция Do…Loop

Цикл Do применяется для выполнения блока операторов неограниченное число раз. Существует несколько разновидностей конструкции Do . . . Loop, но каждая из них вычисляет выражение-условие, чтобы определить момент выхода из цикла. Как и в случае конструкции If . . . Then условие должно быть величиной или выражением, принимающими значение False (нуль) или True (не нуль).

В следующей конструкции Do . . . Loop операторы выполняются до тех пор, пока значением условия является True (Истина):


Do While условие
  операторы 
Loop

Выполняя этот цикл, VBA сначала проверяет условие. Если условие ложно (False), он пропускает все операторы цикла. Если оно истинно (True), VBA выполняет операторы цикла, снова возвращается к оператору Do While и снова проверяет условие.

Следовательно, цикл, представленный данной конструкцией, может выполняться любое число раз, пока значением условия является не нуль или True (Истина). Отметим, что операторы тела цикла не выполняются ни разу, если при первой проверке условия оно оказывается ложным (False).

Рассмотрим пример: Вычислить сумму ряда

с заданной точностью.


Sub пример4()
Dim e As Single, x As Single, s As Single
Dim m As Single, p As Single, i As Single
Call read("a1", x) : Call read("b1", e)
s = 0: i = 1: m = 1: p = -1
Call out("a2", "i") : Call out("b2", "m") : Call out("c2", "s")
Do While Abs(m) >= e
p = -p * x
m = p / i
s = s + m
Call out("a" & (2 + i), i) : Call out("b" & (2 + i), Abs(m)) : Call out("c" & (2 + i), s)
i = i + 1
Loop
End Sub

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


Do
  операторы 
Loop 
While условие

Две другие разновидности конструкции цикла аналогичны предыдущим, за исключением того, что цикл выполняется, пока условие ложно (False):

Цикл не выполняется вообще или выполняется много раз:

Do Until условие

операторы Loop

Цикл выполняется по крайней мере один раз:

операторы

Loop Until условие

7.2 Вложенные циклы.

Можно помещать структуры управления внутрь других структур управления (например, блок If . . . Then внутрь цикла For . . . Next). Говорят, что структура управления, помещенная внутрь другой структуры управления, является вложенной.

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

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

Рассмотрим пример суммирования элементов Aij матрицы A(n,m) построчно.


Sub пример5()
Dim a() As Single, s() As Single
Dim n As Integer, m As Integer
Dim i As Integer, j As Integer
Call read("a1", n): Call read("b1", m)
ReDim a(1 To n, 1 To m), s(1 To n)
'Чтение матрицы
For i = 1 To n
For j = 1 To m
Call readcell(i + 1, j, a(i, j))
Next j
Next i
'Вычисление
For i = 1 To n
s(i) = 0
For j = 1 To m
s(i) = s(i) + a(i, j)
Next j
Call outcell(i + 1, m + 1, s(i))
Next i
End Sub

Заметим, что первый оператор Next закрывает внутренний цикл For, а последний оператор Next закрывает внешний цикл For. Точно так же и для вложенных операторов If, операторы End If автоматически применяются для закрытия ближайшего к нему оператора If. Вложенные структуры Do . . . Loop работают подобным же образом: самый дальний оператор Loop соответствует самому дальнему оператору Do.

При вводе/выводе элементов двумерного массива на рабочий лист Microsoft Excel удобно применять пользовательские процедуры ввода/вывода:


Sub readcell(i As Integer, j As Integer, val As Variant)
val = Лист1.Cells(i, j).Value
End Sub
Sub outcell(i As Integer, j As Integer, val As Variant)
Лист1.Cells(i, j).Value = val
End Sub

где I — номер строки, j — номер столбца рабочего листа.

Выход из структур управления

Оператор Exit позволяет выходить непосредственно из цикла For, цикла Do, процедуры Sub или процедуры Function. Синтаксис оператора Exit прост:


For counter = start To end [Step -increment]
[блок операторов]
[Exit For]
[блок операторов] 
Next [counter]
Do [(While | Until} условие]
[блок операторов]
[Exit Do]
[блок операторов] 
Loop

Exit For внутри цикла For и Exit Do внутри цикла Do могут появиться сколько угодно раз.

Оператор Exit Do работает со всеми разновидностями синтаксиса цикла Do.

Операторы Exit For и Exit Do применяются, если необходимо завершить цикл немедленно, не продолжая дальнейших итераций или не ожидая выполнения блока операторов в теле цикла.

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

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

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

При завершении цикла по концу набора переменная цикла имеет значение Nothing (Ничего), если она является переменной типа object (Объект), или значение Empty (Пусто), если она является переменной типа Variant

Циклы Excel VBA — For Each, For Next, Do While, Вложенные и другие

В этой статье

Краткие примеры циклов VBA
- Циклы For Each
- Циклы For Next
- Циклы Do While 900 08
- Делать до циклов
VBA Loop Builder
VBA For Next Loop
- For Loop Syntax
- For Loop Step
- For Loop Step — Inverse
- Вложенный For Loop
- Выход на
- Продолжить на
VBA Для каждого цикла
- Для каждой ячейки в диапазоне
- Для каждого рабочего листа в рабочей книге
- Для каждой открытой рабочей книги
- Для каждой формы на рабочем листе
- Для каждой фигуры на каждом рабочем листе в рабочей книге
- Для каждого – IF Loop
VBA Do While Loop
- Do While
- Do While
VBA Do While Loop
- Do Until
- Цикл до
Выход из цикла
Завершить или разорвать цикл
Дополнительные примеры циклов
- Цикл по строкам
- Цикл по столбцам
- Цикл по файлам в папке
- Цикл по массиву
90 008
Циклы в Access VBA

Для эффективной работы в VBA , вы должны понимать циклы.

Циклы позволяют повторять блок кода заданное количество раз или повторять блок кода для каждого объекта в наборе объектов.

Сначала мы покажем вам несколько примеров, чтобы показать вам, на что способны циклы. Затем мы научим вас всему, что касается циклов.

Краткие примеры циклов VBA

Циклы For Each

Циклы For Each Циклы перебирают каждый объект в коллекции, например, каждый рабочий лист в рабочей книге или каждую ячейку в диапазоне.

Цикл по всем рабочим листам в рабочей книге

Этот код будет проходить по всем рабочим листам в рабочей книге, отображая каждый лист:

 Sub LoopThroughSheets()
Dim ws As рабочий лист
 
    Для каждого ws в рабочих листах
        ws.Visible = Истина
    Следующий
 
Конец суб

Цикл по всем ячейкам в диапазоне

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

 Sub If_Loop()
Затемнить ячейку как диапазон
 
  Для каждой ячейки в диапазоне («A2: A6»)
    Если Ячейка. Значение > 0 Тогда
      Cell.Offset(0, 1).Value = "Положительное"
    ИначеЕсли Cell.Value < 0 Тогда
      Cell.Offset(0, 1).Value = "Отрицательное"
    Еще
      Ячейка.Смещение(0, 1).Значение = "Ноль"
     Конец, если
  Следующая ячейка
 
Конец суб

Циклы For Next

Другой тип цикла For Next — цикл For Next. Цикл For Next позволяет перебирать целые числа.

Этот код будет перебирать целые числа от 1 до 10, отображая каждое с окном сообщения:

 Sub ForLoop()
    Dim i как целое число
    Для i = от 1 до 10
        MsgBox я
    Далее я
End Sub

Циклы Do While

Циклы Do While будут выполняться до тех пор, пока выполняется условие. Этот код также будет перебирать целые числа от 1 до 10, отображая каждое с окном сообщения.

 Sub DoWhileLoop()
    Dim n как целое число
    п = 1
    Делать, пока n < 11
        MsgBox n
        п = п + 1
    Петля
End Sub

Циклы "До"

И наоборот, циклы "До" будут выполняться до тех пор, пока не будет выполнено условие. Этот код делает то же самое, что и два предыдущих примера.

 Sub DoUntilLoop()
    Dim n как целое число
    п = 1
    Делать до n >= 10
        MsgBox n
        п = п + 1
    Петля
Конец суб

Мы обсудим это ниже, но вы должны быть предельно осторожны при создании циклов Do While или Do Until, чтобы не создать бесконечный цикл.

VBA Loop Builder

Это снимок экрана «Loop Builder» из нашей надстройки Premium VBA: AutoMacro. Loop Builder позволяет быстро и легко создавать циклы для циклического перебора различных объектов или чисел. Вы можете выполнять действия над каждым объектом и/или выбирать только те объекты, которые соответствуют определенным критериям.

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

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

Кодирование VBA стало проще

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

Узнать больше

VBA For Next Loop

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

For Next Loop позволяет повторять блок кода заданное количество раз. Синтаксис:

 [Тусклый счетчик как целое]

Для счетчика = от начала до конца [значение шага]
    [Сделай что-нибудь]
Далее [Счетчик]

Где элементы в скобках являются необязательными.

[Dim Counter as Long] — объявляет переменную счетчика. Требуется, если Option Explicit объявлен в верхней части вашего модуля.
Счетчик — целочисленная переменная, используемая для подсчета

Старт – Начальное значение (Пример 1)
Конец – Конечное значение (Пример 10)
[Значение шага] — позволяет подсчитывать каждые n целых чисел вместо каждого 1 целого числа. Вы также можете пойти в обратном направлении с отрицательным значением (например, Шаг -1)
[Сделать что-нибудь] — Код, который будет повторяться
Next [Счетчик] — Оператор закрытия цикла For Next. Вы можете включить счетчик или нет. Тем не менее, я настоятельно рекомендую включить счетчик, так как он облегчает чтение кода.

Если это сбивает с толку, не волнуйтесь. Мы рассмотрим несколько примеров:

Считаем до 10

Этот код будет считать до 10 с помощью цикла For-Next:

 Sub ForEach_CountTo10()

Dim n как целое число
Для n = от 1 до 10
    MsgBox n
Следующий n

End Sub

For Loop Step

Счет до 10 — только четные числа

Этот код будет считать до 10 только с учетом четных чисел:

 Sub ForEach_CountTo10_Even()

Dim n как целое число
Для n = 2 до 10 Шаг 2
    MsgBox n
Следующий n

Конец суб

Обратите внимание, что мы добавили «Шаг 2». Это указывает циклу For «перешагнуть» счетчик на 2. Мы также можем использовать отрицательное значение шага для перехода в обратном направлении:

Программирование VBA | Генератор кода работает на вас!

Для шага цикла — обратный

Обратный отсчет с 10

Этот код будет выполнять обратный отсчет с 10:

 Sub ForEach_Countdown_Inverse()

Dim n как целое число
Для n = 10 до 1 Шаг -1
    MsgBox n
Следующий n
MsgBox "Поднять"

Конец суб

Удалить строки, если ячейка пуста

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

В этом примере будут удалены строки с пустыми ячейками (начиная с нижней строки):

 Sub ForEach_DeleteRows_BlankCells()

Dim n как целое число
Для n = 10 до 1 Шаг -1
    Если Диапазон("a" & n). Value = "" Тогда
        Диапазон ("a" & n).EntireRow.Delete
    Конец, если
Следующий n

Конец суб

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

Один цикл For можно «вложить» в другой цикл For. Мы будем использовать Nested For Loops для создания таблицы умножения:

 Sub Nested_ForEach_MultiplicationTable()

Затемнить строку как целое число, столбец как целое число

Для строки = от 1 до 9
    Для столбца = от 1 до 9
        Ячейки (строка + 1, столбец + 1). Значение = строка * столбец
    Следующий столбец
Следующий ряд

End Sub

Exit For

Оператор Exit For позволяет немедленно выйти из цикла For Next.

Обычно вы используете Exit For вместе с оператором If, выходя из цикла For Next при выполнении определенного условия.

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

Этот код будет перебирать строки с 1 по 1000 в поисках «ошибки» в столбце A. Если она будет найдена, код выберет ячейку, предупредит вас о найденной ошибке и выйдет из цикла:

 Sub ExitFor_Loop ()

Dim i как целое число
 
Для я = 1 до 1000
    Если Диапазон("A" & i).Value = "ошибка" Тогда
        Диапазон("A" & i).Выбрать
        MsgBox "Обнаружена ошибка"
        Выход для
    Конец, если
Далее я

Конец суб

Важно! В случае вложенных циклов For команда Exit For закрывает только текущий цикл For, а не все активные циклы.

Продолжить Для

VBA не имеет команды «Продолжить», которая есть в Visual Basic. Вместо этого вам нужно будет использовать «Выход».

Автомакрос | Окончательная надстройка VBA | Нажмите для бесплатной пробной версии!

VBA For Each Loop

VBA For Each Loop будет перебирать все объекты в коллекции:

Все ячейки в диапазоне
Все рабочие листы в рабочей книге
Все фигуры на листе
Все открытые книги

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

Все ячейки в диапазоне на всех листах
Все фигуры на всех листах
Все листы во всех открытых книгах
и так далее…

Синтаксис:

 Для каждого объекта в коллекции
[Сделай что-нибудь]
Далее [Объект]

Где:

Объект — переменная, представляющая диапазон, рабочий лист, рабочую книгу, фигуру и т. д. (например, rng)
Коллекция – Коллекция объектов (например, Range("a1:a10")
[Сделать что-нибудь] — Блок кода для запуска на каждом объекте
Следующий [Объект] — Заключительное заявление. [Объект] является необязательным, однако настоятельно рекомендуется.

Для каждой ячейки в диапазоне

Этот код будет перебирать каждую ячейку в диапазоне:

 Sub ForEachCell_inRange()

Затемнить ячейку как диапазон

Для каждой ячейки в диапазоне («a1: a10»)
    ячейка.Значение = ячейка.Смещение(0,1).Значение
Следующая ячейка

Конец суб

Для каждого рабочего листа в рабочей книге

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

 Sub ForEachSheet_inWorkbook()

Dim ws As рабочий лист

Для каждого ws в рабочих листах
    ws.Unprotect "пароль"
Следующий мс

End Sub

Для каждой открытой книги

Этот код сохранит и закроет все открытые книги:

 Sub ForEachWB_inWorkbooks()

Dim wb как рабочая книга

Для каждого ББ в рабочих книгах
    wb. Close SaveChanges: = True
Следующий бб

Конец суб

Автомакрос | Окончательная надстройка VBA | Нажмите для бесплатной пробной версии!

Для каждой фигуры на рабочем листе

Этот код удалит все фигуры на активном листе.

 Подпрограмма ForEachShape()

Дим шп как форма

Для каждой shp в ActiveSheet.Shapes
    шп.Удалить
Следующая шп

Конец сабвуфера

Для каждой формы на каждом рабочем листе в рабочей книге

Вы также можете вложить циклы For Each. Здесь мы пройдемся по всем фигурам на всех листах в активной книге:

 Sub ForEachShape_inAllWorksheets()

Dim shp As Shape, ws As Рабочий лист

Для каждого ws в рабочих листах
    Для каждой шп в ws.Shapes
        шп.Удалить
    Следующая шп
Следующий мс

End Sub

Для каждого — цикл IF

Как мы упоминали ранее, вы можете использовать оператор If внутри цикла, выполняя действия только при соблюдении определенных условий.

Этот код скроет все пустые строки в диапазоне:

 Sub ForEachCell_inRange()

Затемнить ячейку как диапазон

Для каждой ячейки в диапазоне («a1: a10»)
    Если ячейка.Значение = "" Тогда _
       ячейка.EntireRow.Hidden = Истина
Следующая ячейка

Конец суб

VBA Do While Loop

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

Цикл Do While повторяет цикл, пока выполняется условие.

Вот синтаксис Do While:

 Do While Condition
[Сделай что-нибудь]
Цикл

Где:

Условие – Условие для проверки
[Сделать что-нибудь] — Кодовый блок для повторения

Вы также можете настроить цикл Do While с условием в конце цикла:

 Do
[Сделай что-нибудь]
Loop While Condition

Мы продемонстрируем каждое из них и покажем, чем они отличаются:

AutoMacro | Окончательная надстройка VBA | Нажмите для бесплатной пробной версии!

Do While

Вот пример цикла Do While, который мы продемонстрировали ранее:

 Sub DoWhileLoop()
    Dim n как целое число
    п = 1
    Делать, пока n < 11
        MsgBox n
        п = п + 1
    Петля
Конец суб

Loop While

Теперь давайте запустим ту же процедуру, за исключением того, что мы переместим условие в конец цикла:

 Sub DoLoopWhile()
    Dim n как целое число
    п = 1
    Делать
        MsgBox n
        п = п + 1
    Цикл Пока n < 11
End Sub

VBA Do Loop

Do Until Loops будет повторять цикл до тех пор, пока не будет выполнено определенное условие. Синтаксис, по существу, такой же, как и у циклов Do While:

 Do until Condition
[Сделай что-нибудь]
Петля

и аналогично условие может идти в начале или в конце цикла:

 Do
[Сделай что-нибудь]
Цикл до условия

До

Этот цикл до будет считать до 10, как и в наших предыдущих примерах

 Sub DoUntilLoop()
    Dim n как целое число
    п = 1
    Делать до n > 10
        MsgBox n
        п = п + 1
    Петля
End Sub

Автомакрос | Окончательная надстройка VBA | Нажмите для бесплатной пробной версии!

Цикл до

Этот цикл до цикла будет считаться до 10:

 Sub DoLoopUntil()
    Dim n как целое число
    п = 1
    Делать
        MsgBox n
        п = п + 1
    Цикл до n > 10
End Sub

Exit Do Loop

Подобно команде Exit For для выхода из цикла For, вы используете команду Exit Do для немедленного выхода из цикла Do

 Exit Do

Вот пример Exit Do:

 Sub ExitDo_Loop ()

Dim i как целое число
я = 1

Делать до i > 1000
    Если Диапазон("A" & i). Value = "ошибка" Тогда
        Диапазон("A" & i).Выбрать
        MsgBox "Обнаружена ошибка"
        Выход Сделать
    Конец, если
    я = я + 1
Петля

Конец суб

End or Break Loop

Как мы упоминали выше, вы можете использовать Exit For или Exit Do для выхода из цикла:

 Exit For

 Exit Do

петля.

Если вы пытаетесь «разорвать» текущий цикл, попробуйте нажать ESC или CTRL + Pause Break на клавиатуре. Однако это может не сработать. Если это не сработает, вам нужно дождаться окончания цикла или, в случае бесконечного цикла, использовать CTRL + ALT + Удалить , чтобы принудительно закрыть Excel.

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

Дополнительные примеры циклов

AutoMacro | Окончательная надстройка VBA | Нажмите для бесплатной пробной версии!

Цикл по строкам

Это цикл по всем строкам в столбце:

 Public Sub LoopThroughRows()
 
Затемнить ячейку как диапазон
 
Для каждой ячейки в диапазоне («A: A»)
    Если cell. value <> "" Then MsgBox cell.address & ": " & cell.Value
Следующая ячейка
 
Конец суб

Зациклить столбцы

Это зациклит все столбцы подряд:

 Public Sub LoopThroughColumns()

Затемнить ячейку как диапазон

Для каждой ячейки в диапазоне («1: 1»)
    Если cell.Value <> "" Then MsgBox cell.Address & ": " & cell.Value
Следующая ячейка

Конец сабвуфера

Циклический просмотр файлов в папке

Этот код будет циклически просматривать все файлы в папке, создавая список:

 Sub LoopThroughFiles ()

Dim oFSO как объект
Dim oFolder как объект
Dim oFile как объект
Dim i как целое число

Установите oFSO = CreateObject("Scripting.FileSystemObject")

Установите oFolder = oFSO.GetFolder("C:\Demo)

я = 2

Для каждого oFile в oFolder.Files
    Диапазон ("A" & i).value = oFile.Name
    я = я + 1
Следующий файл

Конец суб

Цикл по массиву

Этот код будет проходить по массиву ‘arrList’:

 For i = LBound(arrList) To UBound(arrList)
    MsgBox arrList(i)
Next i

Функция LBound получает «нижнюю границу» массива, а UBound — «верхнюю границу».

Циклы в Access VBA

Большинство приведенных выше примеров также будут работать в Access VBA. Однако в Access мы перебираем объект набора записей, а не объект диапазона.

 Sub LoopThroughRecords()
   При ошибке Возобновить Далее
   Dim dbs как база данных
   Dim сначала как набор записей
   Установите dbs = CurrentDb
   Установите сначала = dbs.OpenRecordset("tblClients", dbOpenDynaset)
   С первым
      .MoveLast
      .MoveFirst
      Делать до тех пор, пока .EOF = True
         MsgBox(rst.Fields("ИмяКлиента"))
        .MoveNext
     Петля
   Конец с
   рст.Закрыть
   Установить сначала = ничего
   Установите dbs = Ничего
Конец суб

Как использовать циклы VBA в Excel (для следующего, для каждого, вложенные)

Вы являетесь опытным пользователем Excel и хотите улучшить свои навыки и ускорить свою карьеру?

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

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

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

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

Сила повторения

Так что же такое циклы?

Циклы — это просто способ повторения действий с компьютерным кодом, но с компьютерной скоростью и компьютерным масштабом.

Например,

Представьте, что вам нужно выполнить аналогичную операцию с миллионом ячеек электронной таблицы или 100 книгами. Это займет у вас практически целую вечность, но Excel VBA может выполнять такие задачи за считанные секунды.

Здесь действительно начинает проявляться сила программирования.

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

В сегодняшней статье мы рассмотрим:

Для следующих циклов
Вложенные циклы и
Для каждого цикла

Итак, давайте разберем, как работает каждый из них.

Циклы For Next

Циклы For Next Циклы — это программная структура в VBA, которая позволяет нам повторять некоторые действия, определенные фрагментом кода, фиксированное количество раз .

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

Например,

Чтобы помочь вам понять синтаксис, вот очень простой пример макроса, который просто отправляет сообщение пользователю с первыми пятью целыми числами прямо в Excel.

 Sub ForNext ()
Dim Counter как целое число
Для счетчика = от 1 до 5
MsgBox (счетчик)
Следующий счетчик
End Sub

Вот как это работает:

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

 Счетчик тусклости как целое число

Сам цикл For Next начинается с ключевого слова For , после чего нам нужно фактически установить значение только что объявленной переменной счетчика. Это значит, что у нашего цикла будет какая-то отправная точка.

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

 For Counter = 1

После установки Counter равным 1, теперь нам нужно сообщить циклу, сколько итераций мы хотим, чтобы он выполнял цикл, начиная со значения, которое мы установили для переменной counter. В данном случае это 5, так что полная первая строка цикла будет такой:

 Для счетчика = от 1 до 5

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

Затем вы можете закрыть тело цикла с ключевым словом Next , за которым следует имя переменной-счетчика, которую вы увеличиваете на протяжении каждого цикла цикла:

 Next Counter

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

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

В нашем примере операция очень проста. Мы просто хотим ввести в поле сообщения это значение, чтобы результат появился в Excel.

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

 MsgBox (счетчик)

Например,

Если мы запустим наш макрос так...

Мы начнем с того, что увидим число 1, напечатанное в окне сообщения.

За ними следует 2, затем 3, 4 и 5, так что наша программа делает именно то, для чего мы ее разработали.

Ура!

`Итерация по диапазонам ячеек`

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

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

Например,

Давайте рассмотрим более практический пример.

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

 Sub RemoveNegatives()
Dim Counter как целое число
Dim RCount как целое число
RCount = Выбор.Строки.Количество
Для счетчика = 1 для RCount
Если Selection.Cells(Counter, 1).Value < 0 Тогда
Selection.Cells(Счетчик, 1).ClearContents
Конец, если
Следующий счетчик
End Sub

И вот набор данных, который мы будем использовать в качестве нашего примера:

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

Итак, давайте разберем, что означает этот код:

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

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

Достаточно просто, не так ли?

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

Итак, предполагая, что мы уже выбрали желаемый диапазон, как мы можем определить количество строк в этом диапазоне, чтобы мы знали, сколько раз наш цикл For Next должен повторяться?

Оказывается, объект Selection в Excel имеет свойство Rows (и, кстати, свойство Columns). И, в свою очередь, эти свойства Rows и Columns имеют свойство Count . Таким образом, мы можем использовать следующую строку кода, чтобы получить количество строк в текущем выделении:

 RCount = Selection.Rows.Count

Обратите внимание, что сначала нужно объявить переменную RCount . Затем мы можем настроить первую строку нашего цикла For Next для итерации от 1 до количества строк в выбранных ячейках:

 For Counter = 1 To RCount

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

Отличным способом сделать это в цикле For Next является свойство Cells , которое позволяет нам нацеливаться на отдельные ячейки только с номером строки и номером столбца.

Теперь, хотя свойство «Ячейки» по умолчанию работает с сеткой Excel в целом, оно также может работать в пределах выбранного диапазона, как если бы этот диапазон был всей сеткой.

Например,

Если я выберу диапазон, а затем нацелю Ячейки (1,1) в этом диапазоне, он просто вернет верхнюю левую ячейку диапазона независимо от моего выбора.

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

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

Поэтому необходимо также настроить оператор If внутри цикла для проверки этого условия. Но чтобы применить логический тест, нам нужно получить доступ к отдельным ячейкам, что мы можем сделать с помощью свойства Cells:

 If Selection.Cells(Counter, 1).Value < 0 Then

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

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

Затем в теле оператора If нам просто нужно очистить содержимое этой конкретной ячейки, что эффективно удалит отрицательное число. Таким образом, мы можем позаимствовать код, который мы использовали в логическом тесте оператора If для нацеливания на ячейку, а затем просто связать 9 символов VBA.0171 ClearContents метод к результату:

 Selection.Cells(Counter, 1).ClearContents

И это все, что нам нужно, чтобы очистить содержимое целевой ячейки для определенного цикла цикла, ЕСЛИ значение этой ячейки равно отрицательный.

Итак, теперь, чтобы подтвердить и убедиться, что это работает, я выделю этот массив чисел (обратите внимание, что четыре из них отрицательные)…

А затем, если мы запустим наш макрос следующим образом:

отсутствуют четыре отрицательных числа, поэтому наш код сработал!

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

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

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

Итак, давайте посмотрим на следующий цикл, который мы можем использовать.

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

До сих пор мы запускали наш цикл через один столбец данных, но что, если мы хотим выполнить такую же операцию с двумерными данными, как показано ниже?

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

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

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

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

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

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

 Sub RemoveNegatives()
Dim Counter как целое число
Dim RCount как целое число
RCount = Выбор.Строки.Количество
Для счетчика = 1 для RCount
Если Selection.Cells(Counter, 1).Value < 0 Тогда
Selection.Cells(Счетчик, 1).ClearContents
Конец, если
Следующий счетчик
Конец суб

Вот как это будет работать:

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

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

Проще говоря:

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

Теперь, когда мы добавляем второй цикл For Next , нам также понадобится вторая переменная-счетчик. Когда вы имеете дело с вложенными циклами, подобными этому, у вас обычно будет две переменные-счетчики, которые часто являются последовательными буквами алфавита. Иногда вы увидите «i» и «j», но я обычно использую только «A» и «B».

Итак, на первом этапе обновления нашего макроса мы предпримем следующие шаги:

Изменим имя переменной счетчика на «A»
Изменить все ссылки на эту переменную счетчика соответствующим образом
Добавьте вторую переменную счетчика с именем «B»

Просто!

Вы можете увидеть эти изменения, отраженные в коде ниже:

 Sub RemoveNegatives()
Dim A как целое число
Dim B как целое число
RCount = Выбор. Строки.Количество
Для A = 1 To RCount
Если Selection.Cells(Counter, 1).Value < 0 Тогда
Selection.Cells(A, 1).ClearContents
Конец, если
Следующий счетчик
Конец суб

Далее, прямо внутри нашего текущего цикла For Next , мы начнем второй цикл For Next следующим образом:

 For B = 1 To

переменная счетчика «B» до?

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

Но для нашего внутреннего цикла мы хотим выполнить итерацию до количества ячеек в определенной строке… так как же нам добраться до этого числа?

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

Итак, давайте создадим еще одну переменную с именем CCount , похожую на нашу существующую переменную RCount , и установим ее значение равным количеству столбцов в нашем выборе.

( CCount для столбцов, RCount для строк)

Это будет выглядеть так:

 Dim CCount As Integer
CCount = Selection.Columns.Count

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

Обратите внимание, что тело нашего исходного цикла For Next — по сути, оператор If — теперь находится внутри внутреннего цикла. Как вы увидите, это ключ к повторению как строк, так и столбцов выбранного диапазона:

 Sub RemoveNegatives()
Dim A как целое число
Dim B как целое число
Dim RCount как целое число
Dim CCount как целое число
RCount = Выбор.Строки.Количество
CCount = Выбор.Колонки.Количество
Для A = 1 To RCount
Для B = 1 To CCount
Если Selection.Cells(A, 1).Value < 0 Тогда
Selection.Cells(A, 1).ClearContents
Конец, если
Следующий Б
Следующий А
Конец суб

Теперь, когда мы заложили основу для нашего решения, мы можем перейти к реальному значению Nested For Next циклов.

Итак, внутри внутреннего цикла у нас есть доступ как к переменной A, которая отслеживает данную строку в выбранном диапазоне, так и к нашей переменной B, которая отслеживает данный столбец.

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

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

 If Selection.Cells(A, 1).Value < 0 Тогда
Selection.Cells(A, 1).ClearContents
End If

Но имея в своем распоряжении вторую переменную-счетчик, мы можем динамически указывать как строку, так и столбец внутри внутреннего цикла. Это дает окончательную версию нашего цикла Nested :

 Sub RemoveNegatives()
Dim A как целое число
Dim B как целое число
Dim RCount как целое число
Dim CCount как целое число
RCount = Выбор. Строки.Количество
CCount = Выбор.Колонки.Количество
Для A = 1 To RCount
Для B = 1 To CCount
Если Selection.Cells(A, B).Value < 0 Тогда
Selection.Cells(A, B).ClearContents
Конец, если
Следующий Б
Следующий А
Конец суб

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

Затем запустим макрос…

(барабанная дробь, пожалуйста) 🥁

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

И, как и в случае с нашим одномерным циклом For Next , нет абсолютно никаких причин, кроме ограничений вычислительной мощности вашего компьютера, по которым это решение не может масштабироваться до 2D-диапазонов гораздо больших размеров!

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

Циклы For Each

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

Итак, что именно я подразумеваю под «такой задачей»?

Короче говоря, перебирая коллекцию объектов в Excel — например, коллекцию всех рабочих листов в книге или (более уместно в нашем недавнем примере) коллекцию всех ячеек в диапазоне ячеек.

Циклы For Each оптимальны для перебора элементов коллекции, без необходимости беспокоиться о подсчете количества необходимых итераций и переходе .

Для этих конкретных сценариев — перебор коллекций объектов Excel — цикл For Each часто является более быстрой, простой и интуитивно понятной альтернативой циклу For Next .

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

Вот как это работает

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

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

 Sub RemoveNegatives()
Для A = 1 To RCount
Для B = 1 To CCount
Если Selection.Cells(A, B).Value < 0 Тогда
Selection.Cells(A, B).ClearContents
Конец, если
Следующий Б
Следующий А
Конец суб

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

Важно:

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

Мы можем объявить эту переменную — которую для ясности я назову «Ячейка», поскольку объекты, которые мы итерируем, являются ячейками — с типом данных Range, поскольку ячейки сами по себе являются просто диапазонами:

 Dim Cell As Range

Имея эту переменную, мы можем удалить циклы For Next из нашего кода и начать настройку оболочки нашего цикла For Each (обратите внимание на единственный «цикл»: мы только Вложенных циклов не требуется!)

Первая строка цикла выглядит так:

 Для каждой ячейки в выделении

Давайте немного распаковываем это.

Во-первых, у нас есть ключевые слова « Для каждого ”
Затем мы указываем переменную, которая будет содержать каждый отдельный член коллекции, которую мы итерируем, что, конечно же, будет переменной Cell, которую мы только что объявили
И затем ключевое слово В
Наконец, мы указываем набор объектов, которые мы итерируем. В нашем случае это просто объект Selection , который представляет собой набор ячеек, которые пользователь выбрал в данный момент

Затем мы можем закрыть цикл, так сказать, почти так же, как вы закрываете цикл For Next . Мы делаем это, используя ключевое слово «Далее», за которым следует имя переменной, с помощью которой мы отслеживаем итерации цикла.

Итак, вот как выглядит наш цикл:

 Для каждой выбранной ячейки
Если Selection.Cells(A, B).Value < 0 Тогда
Selection.Cells(A, B).ClearContents
Конец, если
Следующая ячейка

Итак, теперь, когда у нас есть оболочка нашего цикла For Each , мы можем обратить внимание на рефакторинг кода внутри цикла.

Но как?

Итак, в каждом цикле цикла For Each у нас есть доступ ровно к одному объекту в общей коллекции. Это означает, что для каждого цикла нашего цикла For Each у нас есть доступ ровно к одной ячейке в выбранном диапазоне. И эта ячейка фиксируется в нашей переменной Cell.

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

 Sub RemoveNegatives()
Затемнить ячейку как диапазон
Для каждой выбранной ячейки
Если Ячейка.Значение < 0 Тогда
Cell.ClearContents
Конец, если
Следующая ячейка
End Sub

Это немного более читабельно и просто, чем цикл Nested For Next , а!?

Даже на первый взгляд довольно ясно, что делает код:

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

Полегче!

Чтобы убедиться, что это работает, я выделю копию своего 2D-диапазона ячеек…

Затем запустим обновленный макрос:

Ура!

Похоже, он успешно выполнил ту же работу, что и наши циклы Nested For Next , но примерно с половиной строк кода.

И хотя циклы For Each обычно более удобны для пользователя, чем циклы For Next (особенно Nested Для циклов Next ) важно отметить, что они ограничены случаями использования, в которых вы перебираете набор объектов Excel (например, ячейки в диапазоне).

Однако циклы For Next можно использовать практически в любой ситуации, когда вам нужно выполнить определенное количество итераций, что делает их более универсальными.

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

Например,

Допустим, мы хотим создать простую программу, которая записывает список чисел от 1 до N, начиная с активной ячейки, и предлагает пользователю указать длину этого списка. должен быть in.

Другими словами, значение N.

Поскольку циклы For Each ограничены перебором наборов объектов, они не могут нам здесь помочь. Тем не менее, для цикла For Next такая проблема не представляет труда:

После запроса пользователя ввести число через поле ввода...

Мы просто используем цикл For Next для итерации от 1 до указанного числа, при этом каждый цикл цикла записывает текущее значение итератора переменная i , в ячейку электронной таблицы, которая смещена на одну дополнительную строку от активной ячейки, например:

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