Щоб ефективно працювати у VBA, ви повинні розуміти цикли.
Цикли дозволяють повторювати кодовий блок задану кількість разів або повторювати кодовий блок для кожного об'єкта в наборі об'єктів.
Спочатку ми покажемо вам кілька прикладів, щоб показати, на що здатні цикли. Тоді ми навчимо вас усьому про петлі.
Швидкі приклади петлі VBA
Для кожної петлі
Для кожного циклу цикл проходить через кожен об’єкт у колекції, наприклад, кожен аркуш у книзі або кожну клітинку в діапазоні.
Перегляньте всі робочі аркуші у робочому зошиті
Цей код прокручуватиме всі робочі аркуші у книзі, приховуючи кожен аркуш:
| 12345678 | Sub LoopThroughSheets ()Затемнити як робочий аркушДля кожного ws у робочих аркушахws.Visible = ПравдаДаліEnd Sub |
Цикл через усі комірки в діапазоні
Цей код буде перебирати діапазон клітинок, перевіряючи, чи є значення комірки негативним, позитивним або нульовим:
| 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 = "Нуль"Закінчити ЯкщоНаступна клітинаEnd Sub |
Для наступних циклів
Інший тип циклу «для» - це цикл «Для наступного». Цикл For Next дозволяє перебирати цілі числа.
Цей код буде перебирати цілі числа від 1 до 10, відображаючи кожне з вікном повідомлення:
| 123456 | Sub ForLoop ()Dim i як ціле числоДля i = 1-10MsgBox iДалі iEnd Sub |
Виконайте цикли while
Цикли Do While циклуватимуться під час виконання умови. Цей код також буде перебирати цілі числа від 1 до 10, відображаючи кожне з вікном повідомлення.
| 12345678 | Sub DoWhileLoop ()Dim n як ціле числоn = 1Робіть, поки n <11MsgBox nn = n + 1ПетляEnd Sub |
Робіть до циклів
І навпаки, цикли «До тих пір» будуть циклічно виконуватись, поки не буде виконана умова. Цей код робить те ж саме, що і попередні два приклади.
| 12345678 | Sub DoUntilLoop ()Dim n як ціле числоn = 1Робіть до n> = 10MsgBox nn = n + 1ПетляEnd Sub |
Ми обговоримо це нижче, але вам потрібно бути гранично обережним при створенні циклів Do While або Do Until, щоб не створити безкінечний цикл.
Конструктор циклів VBA
Це знімок екрана "Конструктора циклів" з нашої надбудови Premium VBA: AutoMacro. Конструктор циклів дозволяє швидко і легко будувати цикли для перебору різних об'єктів або чисел. Ви можете виконувати дії над кожним об’єктом та/або вибирати лише об’єкти, що відповідають певним критеріям.
Надбудова також містить багато інших конструкторів коду, велику бібліотеку кодів VBA та різноманітні засоби кодування. Це обов’язково для будь -якого розробника VBA.
Тепер ми детально розглянемо різні типи петель.
VBA для наступного циклу
Для циклу синтаксису
Цикл For Next дозволяє повторити блок коду певну кількість разів. Синтаксис такий:
| 12345 | [Лічильник затемнення як ціле число]Для лічильника = від початку до кінця [Значення кроку][Робити щось]Далі [Лічильник] |
Де пункти в дужках необов’язкові.
- [Лічильник неяскравий як довго] - Оголошує змінну лічильника. Потрібно, якщо Option Explicit оголошено у верхній частині вашого модуля.
- Лічильник - Ціла змінна, що використовується для підрахунку
- Почати - Початкове значення (Приклад 1)
- Кінець - Кінцеве значення (Приклад 10)
- [Крок значення] - Дозволяє підраховувати кожне n цілих чисел замість кожного 1 цілого. Ви також можете повернутися у зворотному порядку з від'ємним значенням (наприклад, крок -1)
- [Робити щось] - Код, який повторюватиметься
- Далі [Лічильник] - Заключна заява до циклу For Next. Ви можете включити лічильник чи ні. Однак я настійно рекомендую включити лічильник, оскільки це полегшує читання вашого коду.
Якщо це заплутано, не переживайте. Ми розглянемо деякі приклади:
Порахуйте до 10
Цей код буде рахувати до 10, використовуючи цикл For-Next:
| 12345678 | Sub ForEach_CountTo10 ()Dim n як ціле числоДля n = 1-10MsgBox nДалі пEnd Sub |
Для кроку петлі
Порахуйте до 10 - лише парні числа
Цей код буде рахувати до 10 тільки з урахуванням парних чисел:
| 12345678 | Sub ForEach_CountTo10_Even ()Dim n як ціле числоДля n = 2-10 Крок 2MsgBox nДалі пEnd Sub |
Зверніть увагу, що ми додали "Крок 2". Це означає, що цикл For «переступає» через лічильник на 2. Ми також можемо використовувати від’ємне значення кроку для кроку в зворотному порядку:
Для кроку циклу - зворотний
Зворотний відлік від 10
Цей код відлічуватиме від 10:
| 123456789 | Sub ForEach_Countdown_Inverse ()Dim n як ціле числоДля n = 10 до 1 Крок -1MsgBox nДалі пПовідомлення "Підняти"End Sub |
Видалити рядки, якщо клітинка порожня
Я найчастіше використовував негативний крок For-Loop, щоб прокрутити діапазони клітинок, видаливши рядки, що відповідають певним критеріям. Якщо ви виконаєте цикл від верхніх рядків до нижніх рядків, то при видаленні рядків ви зіпсуєте лічильник.
У цьому прикладі буде видалено рядки з порожніми клітинками (починаючи з нижнього рядка):
| 12345678910 | Sub ForEach_DeleteRows_BlankCells ()Dim n як ціле числоДля n = 10 до 1 Крок -1Якщо Range ("a" & n) .Value = "" ТодіДіапазон ("a" & n) .EntireRow.DeleteЗакінчити ЯкщоДалі пEnd Sub |
Вкладено для циклу
Ви можете "вкласти" один For Loop всередину іншого For Loop. Ми будемо використовувати вкладені цикли для створення таблиці множення:
| 1234567891011 | Вкладена таблиця_встановлених файлів ()Затемнити рядок як ціле число, колонку як ціле числоДля рядка = 1 до 9Для col = 1 до 9Клітинки (рядок + 1, стовпець + 1). Значення = рядок * стовпчикНаступний колНаступний рядEnd Sub |
Вийти для
Оператор Exit For дозволяє негайно вийти з циклу For Next.
Ви зазвичай використовуєте Вихід For разом із Інструкцією If, виходячи з циклу For Next, якщо виконується певна умова.
Наприклад, для пошуку комірки можна скористатися циклом For. Як тільки ця клітинка буде знайдена, ви можете вийти з циклу, щоб прискорити код.
Цей код буде перебирати рядки з 1 по 1000, шукаючи "помилку" у стовпці A. Якщо він буде знайдений, код виділить комірку, попередить вас про знайдену помилку та вийде з циклу:
| 12345678910111213 | Sub ExitFor_Loop ()Dim i як ціле числоДля i = 1 до 1000Якщо Range ("A" & i) .Value = "error" ТодіДіапазон ("A" & i). ВиберітьMsgBox "Знайдено помилку"Вийти дляЗакінчити ЯкщоДалі iEnd Sub |
Важливо: У випадку вкладених циклів For, Вихід для виходу з поточного циклу For, а не з усіх активних циклів.
Продовжити для
У VBA немає команди «Продовжити», яка міститься у Visual Basic. Замість цього вам потрібно буде скористатися кнопкою «Вихід».
VBA для кожного циклу
VBA для кожного циклу прокручуватиме всі об’єкти в колекції:
- Усі клітини в діапазоні
- Усі аркуші в робочому зошиті
- Усі фігури на аркуші
- Усі відкриті трудові книжки
Ви також можете використовувати вкладені цикли для кожного циклу, щоб:
- Усі клітинки в діапазоні на всіх аркушах
- Усі форми на всіх аркушах
- Усі аркуші у всіх відкритих робочих зошитах
- і так далі…
Синтаксис такий:
| 123 | Для кожного об’єкта в колекції[Робити щось]Далі [об’єкт] |
Де:
- Об'єкт - Змінна, що представляє діапазон, аркуш, робочу книгу, форму тощо (наприклад, rng)
- Колекція - Колекція об'єктів (наприклад, Діапазон ("a1: a10")
- [Робити щось] - Кодовий блок для запуску на кожному об'єкті
- Далі [об’єкт] - Заключна заява. [Об’єкт] необов’язковий, проте настійно рекомендується.
Для кожної клітини в діапазоні
Цей код буде перебирати кожну клітинку в діапазоні:
| 123456789 | Sub ForEachCell_inRange ()Яскрава клітинка як діапазонДля кожної клітинки в діапазоні ("a1: a10")cell.Value = cell.Offset (0,1) .ValueНаступна клітинаEnd Sub |
Для кожного робочого аркуша у робочому зошиті
Цей код прокрутить усі робочі аркуші у книзі, знявши захист кожного аркуша:
| 123456789 | Sub ForEachSheet_inWorkbook ()Затемнити як робочий аркушДля кожного ws у робочих аркушахws.Зняти захист "пароля"Наступний wsEnd Sub |
Для кожної відкритої робочої книги
Цей код збереже та закриє всі відкриті книги:
| 123456789 | Sub ForEachWB_inWorkbooks ()Dim wb Як робочий зошитДля кожного wb у робочих зошитахwb.Закрити SaveChanges: = ПравдаНаступна вбEnd Sub |
Для кожної форми на аркуші
Цей код видалить усі фігури в активному аркуші.
| 123456789 | Sub ForEachShape ()Dim shp As ShapeДля кожного shp в ActiveSheet.Shapesshp.ВидалитиДалі шпEnd Sub |
Для кожної форми на кожному робочому аркуші у робочому зошиті
Ви також можете вкласти для кожної петлі. Тут ми прокручуємо всі фігури на всіх робочих аркушах активної книги:
| 1234567891011 | Sub ForEachShape_inAllWorksheets ()Dim shp As Shape, ws As WorksheetДля кожного ws у робочих аркушахДля кожного shp У ws.Shapesshp.ВидалитиДалі шпНаступний wsEnd Sub |
Для кожного - IF Loop
Як ми вже згадували раніше, ви можете використовувати оператор If у циклі, виконуючи дії лише за дотримання певних критеріїв.
Цей код приховає всі порожні рядки в діапазоні:
| 12345678910 | Sub ForEachCell_inRange ()Яскрава клітинка як діапазонДля кожної клітинки в діапазоні ("a1: a10")Якщо cell.Value = "" Тоді _cell.EntireRow.Hidden = ІстинаНаступна клітинаEnd Sub |
VBA Do While Loop
VBA Do While і Do Until (див. Наступний розділ) дуже схожі. Вони будуть повторювати цикл, поки (або поки) не буде виконана умова.
Цикл Do While повторює цикл, поки виконується умова.
Ось синтаксис Do While:
| 123 | Виконайте умову[Робити щось]Петля |
Де:
- Хвороба - Умова для тестування
- [Робити щось] - Повторювати блок коду
Ви також можете налаштувати цикл Do While із умовою в кінці циклу:
| 123 | Зробити[Робити щось]Цикл під час стану |
Ми демонструємо кожен з них і покажемо, чим вони відрізняються:
Роби поки
Ось приклад циклу Do While, який ми демонстрували раніше:
| 12345678 | Sub DoWhileLoop ()Dim n як ціле числоn = 1Робіть, поки n <11MsgBox nn = n + 1ПетляEnd Sub |
Цикл Поки
Тепер давайте запустимо ту саму процедуру, за винятком того, що ми перемістимо умову до кінця циклу:
| 12345678 | Sub DoLoopWhile ()Dim n як ціле числоn = 1ЗробитиMsgBox nn = n + 1Цикл при n <11End Sub |
VBA Do Until Loop
Do Do Loops повторюватиме цикл, поки не буде виконано певну умову. Синтаксис, по суті, такий самий, як цикли Do While:
| 123 | Робіть до стану[Робити щось]Петля |
і аналогічно умова може бути на початку або в кінці циклу:
| 123 | Зробити[Робити щось]Цикл до стану |
Робіть до
Цикл "До" вважатиметься до 10, як і наші попередні приклади
| 12345678 | Sub DoUntilLoop ()Dim n як ціле числоn = 1Робіть до n> 10MsgBox nn = n + 1ПетляEnd Sub |
Цикл до
Цей цикл циклу до зарахуватиме до 10:
| 12345678 | Sub DoLoopUntil ()Dim n як ціле числоn = 1ЗробитиMsgBox nn = n + 1Цикл до n> 10End Sub |
Вийдіть із циклу Do Loop
Аналогічно використанню Exit For для виходу з циклу For, ви негайно виходите з циклу Do за допомогою команди Exit Do
| 1 | Вихід До |
Ось приклад Exit Do:
| 123456789101112131415 | Sub ExitDo_Loop ()Dim i як ціле числоi = 1Робіть до i> 1000Якщо Range ("A" & i) .Value = "error" ТодіДіапазон ("A" & i). ВиберітьMsgBox "Знайдено помилку"Вихід ДоЗакінчити Якщоi = i + 1ПетляEnd Sub |
Закінчення або розрив циклу
Як ми згадували вище, ви можете використовувати Exit For або Exit Do для виходу з циклів:
| 1 | Вийти для |
| 1 | Вихід До |
Однак ці команди потрібно додати до коду, перш ніж запускати цикл.
Якщо ви намагаєтесь "розірвати" цикл, який зараз працює, ви можете спробувати натиснути ВИХІД або CTRL + Перерва на клавіатурі. Однак це може не спрацювати. Якщо це не спрацює, вам доведеться дочекатися завершення циклу або, у разі нескінченного циклу, використовувати CTRL + ALT + Видалити примусово закрити Excel.
Ось чому я намагаюся уникати циклів Do, легше випадково створити нескінченний цикл, який змусить вас перезапустити Excel, що потенційно втратить вашу роботу.
Більше прикладів циклу
Цикл через рядки
Це прокрутить усі рядки у стовпці:
| 123456789 | Загальнодоступна петля циклуThroughRows ()Яскрава клітинка як діапазонДля кожної клітинки в діапазоні ("A: A")Ff cell.value "", потім MsgBox cell.address & ":" & cell.valueНаступна клітинаEnd Sub |
Циклічні колони
Це прокрутить усі стовпці підряд:
| 123456789 | Загальнодоступна петля циклуThroughColumns ()Яскрава клітинка як діапазонДля кожної клітинки в діапазоні ("1: 1")Якщо cell.Value "" Тоді комірка MsgBox.Address & ":" & cell.ValueНаступна клітинаEnd Sub |
Перегляньте файли у папці
Цей код прокрутить усі файли в папці, створивши список:
| 12345678910111213141516171819 | Sub LoopThroughFiles ()Dim oFSO As ObjectЗатемнити папку як об’єктDim oFile As ObjectDim i як ціле числоВстановити oFSO = CreateObject ("Scripting.FileSystemObject")Встановити oFolder = oFSO.GetFolder ("C: \ Demo)i = 2Для кожного файлу oFile In oFolder.FilesДіапазон ("A" & i) .value = oFile.Namei = i + 1Наступний oFileEnd Sub |
Цикл через масив
Цей код буде циклічно переглядати масив "arrList":
| 123 | Для i = LBound (arrList) До UBound (arrList)MsgBox arrList (i)Далі i |
Функція LBound отримує "нижню межу" масиву, а UBound отримує "верхню межу".
Цикли в Access VBA
Більшість наведених вище прикладів також працюватимуть у Access VBA. Однак у Access ми перебираємо об'єкт Recordset, а не Range Object.
| 123456789101112131415161718 | Sub LoopThroughRecords ()Увімкнути Помилка Відновити ДаліDim dbs як база данихDim rst як записВстановити dbs = CurrentDbВстановити rst = dbs.OpenRecordset ("tblClients", dbOpenDynaset)З першим.Перевірити останній.Перейти спочаткуВиконувати до .EOF = TrueMsgBox (rst.Fields ("ClientName")).ПереміститиДаліПетляЗакінчити зПерший. ЗакритиВстановити rst = нічогоВстановити dbs = нічогоEnd Sub |
