Чтобы эффективно работать в VBA, вы должны понимать циклы.
Циклы позволяют повторять блок кода заданное количество раз или повторять блок кода для каждого объекта в наборе объектов.
Сначала мы покажем вам несколько примеров, чтобы показать, на что способны циклы. Затем мы научим вас всему, что касается петель.
Быстрые примеры цикла VBA
Для каждой петли
For Each Loops перебирает каждый объект в коллекции, например каждый лист в книге или каждую ячейку в диапазоне.
Прокрутите все рабочие листы в рабочей тетради
Этот код будет перебирать все листы в книге, отображая каждый лист:
| 12345678 | Sub LoopThroughSheets ()Dim ws как рабочий листДля каждого ws в листахws.Visible = TrueСледующийКонец подписки |
Перебрать все ячейки в диапазоне
Этот код будет проходить через диапазон ячеек, проверяя, является ли значение ячейки отрицательным, положительным или нулевым:
| 1234567891011121314 | Sub If_Loop ()Тусклая ячейка как диапазонДля каждой ячейки в диапазоне («A2: A6»)Если Cell.Value> 0, тоCell.Offset (0, 1) .Value = "Положительный"ElseIf Cell.Value <0 ТогдаCell.Offset (0, 1) .Value = "Отрицательное"ЕщеCell.Offset (0, 1) .Value = "Ноль"Конец, еслиСледующая ячейкаКонец подписки |
Для следующих циклов
Другой тип цикла «For» - это цикл For Next. Цикл For Next Loop позволяет вам перебирать целые числа.
Этот код будет перебирать целые числа от 1 до 10, отображая каждое с окном сообщения:
| 123456 | Sub ForLoop ()Dim i как целое числоДля i = от 1 до 10MsgBox iДалее яКонец подписки |
Циклы "Пока"
Циклы выполнения цикла «Пока» будут повторяться, пока выполняется условие. Этот код также будет перебирать целые числа от 1 до 10, отображая каждое с окном сообщения.
| 12345678 | Sub DoWhileLoop ()Dim n как целое числоп = 1Делай, пока n <11MsgBox nп = п + 1ПетляКонец подписки |
До циклов
И наоборот, Do until Loops будет повторяться до тех пор, пока не будет выполнено условие. Этот код делает то же самое, что и в предыдущих двух примерах.
| 12345678 | Подложка DoUntilLoop ()Dim n как целое числоп = 1До n> = 10MsgBox nп = п + 1ПетляКонец подписки |
Мы обсудим это ниже, но вам нужно быть предельно осторожным при создании циклов Do While или Do While, чтобы не создать бесконечный цикл.
Конструктор циклов VBA
Это снимок экрана «Loop Builder» из нашей надстройки Premium VBA: AutoMacro. Конструктор циклов позволяет быстро и легко создавать циклы для обхода различных объектов или чисел. Вы можете выполнять действия с каждым объектом и / или выбирать только те объекты, которые соответствуют определенным критериям.
Надстройка также содержит множество других построителей кода, обширную библиотеку кода VBA и набор инструментов кодирования. Это необходимо для любого разработчика VBA.
Теперь мы подробно рассмотрим различные типы петель.
VBA для следующего цикла
Для синтаксиса цикла
Цикл For Next Loop позволяет вам повторять блок кода определенное количество раз. Синтаксис:
| 12345 | [Тусклый счетчик как целое число]Для счетчика = от начала до конца [значение шага][Сделай что-нибудь]Далее [Счетчик] |
Где элементы в скобках необязательны.
- [Тусклый счетчик по длине] - Объявляет переменную счетчика. Требуется, если Option Explicit объявлен в верхней части вашего модуля.
- Прилавок - Целочисленная переменная, используемая для подсчета
- Начинать - Начальное значение (Пример 1)
- Конец - Конечное значение (Пример 10)
- [Значение шага] - Позволяет считать каждые n целых чисел вместо каждого целого числа. Вы также можете сделать обратное с отрицательным значением (например, шаг -1).
- [Сделай что-нибудь] - Код, который будет повторяться
- Далее [Счетчик] - Заключительное слово для цикла For Next. Вы можете включить счетчик или нет. Однако я настоятельно рекомендую включить счетчик, так как он облегчает чтение кода.
Если это сбивает с толку, не волнуйтесь. Мы рассмотрим несколько примеров:
Считай до 10
Этот код будет считать до 10 с использованием цикла For-Next:
| 12345678 | Sub ForEach_CountTo10 ()Dim n как целое числоДля n = от 1 до 10MsgBox nСледующий nКонец подписки |
Для шага цикла
Считайте до 10 - только четные числа
Этот код будет считать до 10, считая только четные числа:
| 12345678 | Sub ForEach_CountTo10_Even ()Dim n как целое числоДля n = от 2 до 10 Шаг 2MsgBox nСледующий nКонец подписки |
Обратите внимание, что мы добавили «Шаг 2». Это указывает циклу For «пройти» через счетчик на 2. Мы также можем использовать отрицательное значение шага для перехода в обратном направлении:
Шаг цикла - обратный
Обратный отсчет с 10
Этот код будет отсчитывать от 10:
| 123456789 | Sub ForEach_Countdown_Inverse ()Dim n как целое числоДля n = от 10 до 1 Шаг -1MsgBox nСледующий nMsgBox "Взлет"Конец подписки |
Удалить строки, если ячейка пуста
Чаще всего я использовал цикл For-Loop с отрицательным шагом, чтобы перебирать диапазоны ячеек, удаляя строки, соответствующие определенным критериям. Если вы перейдете от верхних строк к нижним строкам, при удалении строк вы испортите свой счетчик.
В этом примере будут удалены строки с пустыми ячейками (начиная с нижней строки):
| 12345678910 | Sub ForEach_DeleteRows_BlankCells ()Dim n как целое числоДля n = от 10 до 1 Шаг -1Если Range ("a" & n) .Value = "" ТогдаДиапазон ("a" и n) .EntireRow.DeleteКонец, еслиСледующий nКонец подписки |
Вложенный цикл For
Вы можете «вложить» один цикл For внутри другого цикла For Loop. Мы будем использовать Nested For Loops для создания таблицы умножения:
| 1234567891011 | Sub Nested_ForEach_MultiplicationTable ()Тусклый ряд как целое число, столбец как целое числоДля ряда = от 1 до 9Для col = от 1 до 9Ячейки (строка + 1, столбец + 1). Значение = строка * столбецСледующий столбецСледующая строкаКонец подписки |
Выход для
Оператор Exit For позволяет немедленно выйти из цикла For Next.
Обычно вы используете Exit For вместе с оператором If, выходя из цикла For Next Loop, если выполняется определенное условие.
Например, вы можете использовать цикл For Loop, чтобы найти ячейку. Как только эта ячейка будет найдена, вы можете выйти из цикла, чтобы ускорить код.
Этот код будет перебирать строки с 1 по 1000 в поисках «ошибки» в столбце A. Если она найдена, код выберет ячейку, предупредит вас о найденной ошибке и выйдет из цикла:
| 12345678910111213 | Sub ExitFor_Loop ()Dim i как целое числоДля i = от 1 до 1000Если Range ("A" & i) .Value = "error" ТогдаДиапазон ("A" и i). ВыбратьMsgBox "Обнаружена ошибка"Выход дляКонец, еслиДалее яКонец подписки |
Важно: В случае вложенных циклов For, Exit For завершает только текущий цикл For, а не все активные циклы.
Продолжить для
В VBA нет команды «Продолжить», которая есть в Visual Basic. Вместо этого вам нужно будет использовать «Выход».
VBA для каждого цикла
VBA For Each Loop будет перебирать все объекты в коллекции:
- Все ячейки в диапазоне
- Все листы в книге
- Все фигуры на листе
- Все открытые книги
Вы также можете использовать Nested For Each Loops, чтобы:
- Все ячейки в диапазоне на всех листах
- Все формы на всех листах
- Все листы во всех открытых книгах
- и так далее…
Синтаксис:
| 123 | Для каждого объекта в коллекции[Сделай что-нибудь]Следующий [Объект] |
Где:
- Объект - Переменная, представляющая диапазон, рабочий лист, рабочую книгу, форму и т. Д. (Например, rng)
- Коллекция - Коллекция объектов (например, Range («a1: a10»)
- [Сделай что-нибудь] - Блок кода для запуска на каждом объекте
- Следующий [Объект] - Заключительное заявление. [Объект] не является обязательным, но настоятельно рекомендуется.
Для каждой ячейки в диапазоне
Этот код будет перебирать каждую ячейку в диапазоне:
| 123456789 | Sub ForEachCell_inRange ()Тусклая ячейка как диапазонДля каждой ячейки в диапазоне ("a1: a10")cell.Value = cell.Offset (0,1) .ValueСледующая ячейкаКонец подписки |
Для каждого рабочего листа в книге
Этот код будет перебирать все листы в книге, снимая защиту с каждого листа:
| 123456789 | Sub ForEachSheet_inWorkbook ()Dim ws как рабочий листДля каждого ws в листахws.Unprotect "пароль"Следующий wsКонец подписки |
Для каждой открытой книги
Этот код сохранит и закроет все открытые книги:
| 123456789 | Sub ForEachWB_inWorkbooks ()Dim wb As WorkbookДля каждого ББ в книгахwb.Close SaveChanges: = TrueСледующий wbКонец подписки |
Для каждой формы на листе
Этот код удалит все фигуры на активном листе.
| 123456789 | Sub ForEachShape ()Dim shp как формаДля каждого файла в ActiveSheet.Shapesshp.УдалитьСледующая shpКонец подписки |
Для каждой формы на каждом листе в книге
Вы также можете вкладывать For Each Loops. Здесь мы пройдемся по всем фигурам на всех листах в активной книге:
| 1234567891011 | Sub ForEachShape_inAllWorksheets ()Dim shp as shape, ws as WorksheetДля каждого ws в листахДля каждой шп в ws.Shapesshp.УдалитьСледующая shpСледующий wsКонец подписки |
Для каждого - IF Loop
Как мы упоминали ранее, вы можете использовать оператор If внутри цикла, выполняя действия только при соблюдении определенных критериев.
Этот код скроет все пустые строки в диапазоне:
| 12345678910 | Sub ForEachCell_inRange ()Тусклая ячейка как диапазонДля каждой ячейки в диапазоне ("a1: a10")Если cell.Value = "" Тогда _cell.EntireRow.Hidden = ИстинаСледующая ячейкаКонец подписки |
Цикл выполнения цикла в VBA
VBA Do While и Do While (см. Следующий раздел) очень похожи. Они будут повторять цикл, пока (или до тех пор, пока) не будет выполнено условие.
Цикл Do While Loop будет повторять цикл, пока выполняется условие.
Вот синтаксис Do while:
| 123 | Сделать пока условие[Сделай что-нибудь]Петля |
Где:
- Состояние - Состояние для проверки
- [Сделай что-нибудь] - Кодовый блок для повторения
Вы также можете настроить цикл Do While с условием в конце цикла:
| 123 | Делать[Сделай что-нибудь]Цикл, пока условие |
Мы продемонстрируем каждый из них и покажем, чем они отличаются:
Делать пока
Вот пример цикла Do While, который мы продемонстрировали ранее:
| 12345678 | Sub DoWhileLoop ()Dim n как целое числоп = 1Делай, пока n <11MsgBox nп = п + 1ПетляКонец подписки |
Цикл пока
Теперь давайте запустим ту же процедуру, за исключением того, что мы переместим условие в конец цикла:
| 12345678 | Sub DoLoopWhile ()Dim n как целое числоп = 1ДелатьMsgBox nп = п + 1Цикл пока n <11Конец подписки |
VBA: делать до цикла
Do until Loops будет повторять цикл до тех пор, пока не будет выполнено определенное условие. Синтаксис по существу такой же, как у циклов Do While:
| 123 | Сделать до состояния[Сделай что-нибудь]Петля |
и аналогично условие может идти в начале или в конце цикла:
| 123 | Делать[Сделай что-нибудь]Цикл до состояния |
До тех пор, пока
Этот цикл do until будет считать до 10, как и в наших предыдущих примерах.
| 12345678 | Подложка DoUntilLoop ()Dim n как целое числоп = 1До n> 10MsgBox nп = п + 1ПетляКонец подписки |
Цикл до
Этот цикл «Цикл до» будет считаться до 10:
| 12345678 | Подложка DoLoopUntil ()Dim n как целое числоп = 1ДелатьMsgBox nп = п + 1Цикл до n> 10Конец подписки |
Выход из цикла
Подобно использованию Exit For для выхода из цикла For, вы используете команду Exit Do для немедленного выхода из цикла Do
| 1 | Выход Сделать |
Вот пример Exit Do:
| 123456789101112131415 | Sub ExitDo_Loop ()Dim i как целое числоя = 1До i> 1000Если Range ("A" & i) .Value = "error" ТогдаДиапазон ("A" и i). ВыбратьMsgBox "Обнаружена ошибка"Выход СделатьКонец, еслия = я + 1ПетляКонец подписки |
Завершить или прервать цикл
Как мы упоминали выше, вы можете использовать Exit For или Exit Do для выхода из циклов:
| 1 | Выход для |
| 1 | Выход Сделать |
Однако эти команды необходимо добавить в ваш код перед запуском цикла.
Если вы пытаетесь «разорвать» цикл, который в данный момент выполняется, вы можете попробовать нажать ESC или CTRL + Пауза перерыв на клавиатуре. Однако это может не сработать. Если это не сработает, вам нужно дождаться завершения цикла или, в случае бесконечного цикла, использовать CTRL + ALT + Удалить чтобы принудительно закрыть Excel.
Вот почему я стараюсь избегать циклов Do: проще случайно создать бесконечный цикл, который заставит вас перезапустить Excel, что может привести к потере вашей работы.
Больше примеров петель
Цикл по строкам
Это будет проходить через все строки в столбце:
| 123456789 | Публичный Sub LoopThroughRows ()Тусклая ячейка как диапазонДля каждой ячейки в диапазоне ("A: A")Ff cell.value "" затем MsgBox cell.address & ":" & cell.valueСледующая ячейкаКонец подписки |
Цикл по столбцам
Это будет проходить через все столбцы в строке:
| 123456789 | Общедоступная подпрограмма LoopThroughColumns ()Тусклая ячейка как диапазонДля каждой ячейки в диапазоне ("1: 1")Если cell.Value "" Тогда MsgBox cell.Address & ":" & cell.ValueСледующая ячейкаКонец подписки |
Перебирать файлы в папке
Этот код будет перебирать все файлы в папке, создавая список:
| 12345678910111213141516171819 | Sub LoopThroughFiles ()Dim oFSO как объектDim oFolder As ObjectDim oFile As ObjectDim i как целое числоУстановите oFSO = CreateObject ("Scripting.FileSystemObject")Установите oFolder = oFSO.GetFolder ("C: \ Demo)я = 2Для каждого oFile в oFolder.FilesДиапазон ("A" & i) .value = oFile.Nameя = я + 1Следующий oFileКонец подписки |
Цикл через массив
Этот код будет циклически перебирать массив arrList:
| 123 | Для i = LBound (arrList) To UBound (arrList)MsgBox arrList (i)Далее я |
Функция LBound получает «нижнюю границу» массива, а UBound - «верхнюю границу».
Циклы в Access VBA
Большинство приведенных выше примеров также будут работать в Access VBA. Однако в Access мы перебираем объект Recordset, а не объект Range.
| 123456789101112131415161718 | Sub LoopThroughRecords ()При ошибке Возобновить ДалееDim dbs как база данныхDim rst As RecordsetУстановите dbs = CurrentDbУстановите rst = dbs.OpenRecordset ("tblClients", dbOpenDynaset)С первой.MoveLast.MoveFirstДо тех пор, пока .EOF = TrueMsgBox (rst.Fields ("ClientName")).MoveNextПетляКонец ссначала закрытьУстановить rst = ничегоУстановить dbs = NothingКонец подписки |
