Щоб отримати максимальну віддачу від Excel та VBA, вам потрібно знати, як ефективно використовувати цикли.
У VBA цикли дозволяють проходити через набір об'єктів/значень та аналізувати їх по одному. Ви також можете виконувати певні завдання для кожного циклу.
Ось простий приклад використання циклів VBA в Excel.
Припустимо, у вас є набір даних, і ви хочете виділити всі клітинки в парних рядках. Ви можете використовувати цикл VBA, щоб пройти діапазон та проаналізувати номер рядка кожної комірки. Якщо він виходить рівним, ви надаєте йому колір, інакше залишаєте як є.
Звичайно, це дуже просто зробити цикл в Excel VBA (і ви також можете це зробити за допомогою умовного форматування).
У реальному житті ви можете зробити набагато більше з циклами VBA в Excel, які допоможуть вам автоматизувати завдання.
Ось ще кілька практичних прикладів, коли цикли VBA можуть бути корисними:
- Перебирання діапазону клітинок та аналіз кожної клітинки (виділіть клітинки з певним текстом у ній).
- Перегляньте всі аркуші та зробіть з ними що -небудь (наприклад, захистіть/зніміть захист).
- Перегляньте всі відкриті книги (і збережіть кожну книгу або закрийте всі, крім активної книги).
- Перегляньте всі символи клітинки (і витягніть числову частину з рядка).
- Перегляньте всі значення масиву.
- Перегляньте всі діаграми/об’єкти (і надайте рамку або змініть колір фону).
Тепер, щоб найкраще використовувати цикли в Excel VBA, вам потрібно знати про різні види, які існують, і правильний синтаксис кожного з них.
У цьому посібнику я продемонструю різні типи циклів Excel VBA і розгляну кілька прикладів для кожного циклу
Примітка: Це буде величезний підручник, де я спробую детально розкрити кожен цикл VBA. Я рекомендую вам додати цю сторінку в закладки для подальшого використання.Якщо ви зацікавлені у вивченні VBA простим способом, ознайомтеся з моїм Онлайн навчання Excel VBA.
Для наступного циклу
Цикл «For Next» дозволяє вам проходити блок коду протягом зазначеної кількості разів.
Наприклад, якщо я прошу вас додати цілі числа від 1 до 10 вручну, ви б додали перші два числа, потім до результату додали третє число, а потім до результату додали четверте число тощо.
Чи не так?
Така ж логіка використовується в циклі For Next у VBA.
Ви вказуєте, скільки разів ви хочете, щоб цикл запускався, а також вказуєте, що ви хочете, щоб код робив щоразу під час запуску циклу.
Нижче наведено синтаксис циклу For Next:
Для лічильника = Почати до кінця [Значення кроку] [Блок коду для виконання] Далі [лічильник]
У циклі For Next ви можете використовувати лічильник (або будь -яку змінну), яка буде використовуватися для запуску циклу. Цей лічильник дозволяє запускати цей цикл необхідну кількість разів.
Наприклад, якщо я хочу додати перші 10 натуральних чисел, то моє значення Counter буде від 1 до 10.
Давайте розглянемо кілька прикладів, щоб краще зрозуміти, як працює цикл For Next.
Приклад 1 - Додавання перших 10 натуральних чисел
Нижче наведено код, який додасть перші 10 натуральних чисел за допомогою циклу For Next.
Після цього відобразиться вікно повідомлення із сумою цих чисел.
Sub AddNumbers () Dim Total Total As Integer Dim Count As Integer Total = 0 For Count = 1 до 10 Total = Total + Count Next Count MsgBox Total End Sub
У цьому коді значення Total встановлюється на 0, перш ніж потрапити в цикл For Next.
Як тільки він потрапляє в цикл, він зберігає загальне значення після кожного циклу. Отже, після першого циклу, коли лічильник дорівнює 1, значення «Загальна» стає 1, а після другого циклу воно стає 3 (1+2) тощо.
І, нарешті, коли цикл закінчується, змінна "Total" має суму перших 10 натуральних чисел.
Потім MsgBox просто відображає результат у вікні повідомлення.
Приклад 2 - Додавання перших 5 парних цілих чисел
Щоб підсумувати перші п’ять парних цілих чисел (тобто 2,4,6,8 і 10), вам потрібен подібний код із умовою враховувати лише парні числа та ігнорувати непарні числа.
Ось код, який це зробить:
Sub AddEvenNumbers () Загальне затемнення як ціле число Підрахунок затемнення як ціле число Всього = 0 для підрахунку = 2 до 10 Крок 2 Всього = Всього + підрахунок Наступний рахунок MsgBox Загальний кінцевий підряд
Зверніть увагу, що ми почали значення Count з 2, а також використали "Крок 2‘.
Коли ви використовуєте "Крок 2", він повідомляє коду збільшувати значення "Count" на 2 при кожному запуску циклу.
Отже, значення Count починається з 2, а потім стає 4, 6, 8 і 10 у міру зациклення.
ПРИМІТКА. Інший спосіб зробити це - запустити цикл від 1 до 10 і перевірити, чи є число парним чи непарним. Однак використання Step у цьому випадку є більш ефективним способом, оскільки не вимагає циклу працювати 10 разів, а лише 5 разів.
Значення кроку також може бути від'ємним. У такому випадку лічильник починається з більш високого значення і продовжує зменшуватися на вказане значення кроку.
Приклад 3 - Введення серійного номера у вибрані клітинки
Ви також можете використовувати цикл For Next для перегляду колекції об’єктів (наприклад, клітинок, аркушів або книг),
Ось приклад, який швидко вводить серійні номери у всі вибрані комірки.
Sub EnterSerialNumber () Dim Rng як діапазон Dim Counter As Integer Dim RowCount As Integer Set Rng = Selection RowCount = Rng.Rows.Count For Counter = 1 To RowCount ActiveCell.Offset (Лічильник - 1, 0). Значення = Лічильник Наступний кінець лічильника Підп
Наведений вище код спочатку підраховує кількість вибраних рядків, а потім призначає це значення змінній RowCount. Потім ми запускаємо цикл від "1 до RowCount".
Також зверніть увагу, що оскільки виділення може бути будь -якою кількістю рядків, ми встановили змінну Rng на Вибір (з рядком "Встановити Rng = Виділення"). Тепер ми можемо використовувати змінну "Rng" для посилання на вибір у нашому коді.
Приклад 4 - Захистіть усі робочі аркуші в активній книзі
Ви можете використовувати цикл «Для наступного», щоб переглянути всі аркуші активної книги та захистити (або зняти захист) кожен із аркушів.
Нижче наведено код, який буде це робити:
Sub ProtectWorksheets () Dim i As Integer For i = 1 До ActiveWorkbook.Worksheets.Count Worksheets (i) .Захист Next i End Sub
Наведений вище код підраховує кількість аркушів за допомогою ActiveWorkbook.Worksheets.Count. Це повідомляє VBA, скільки разів цикл потрібно запустити.
У кожному випадку він посилається на I книгу (за допомогою робочих аркушів (i)) і захищає її.
Цей же код можна використовувати і для зняття захисту аркушів. Просто змініть рядок Робочі аркуші (i). Захист до Робочі листи (i) .UnProtect.
Вкладені цикли "Для наступного"
Ви можете використовувати вкладені цикли "Для наступного", щоб зробити більш складну автоматизацію в Excel. Вкладена цикл "Для наступного" означатиме, що в циклі "Для наступного" є цикл "Для наступного".
Дозвольте мені показати вам, як це використовувати на прикладі.
Припустимо, у мене в системі відкрито 5 робочих книг, і я хочу захистити всі аркуші у всіх цих книгах.
Нижче наведено код, який буде це робити:
Sub ProtectWorksheets () Dim i As Integer Dim j As Integer For i = 1 To Workbooks.Count For j = 1 To Workbooks (i) .Worksheets.Count Workbooks (i) .Worksheets (j). Protect Next j Next i End Sub
Наведене вище є вкладеним циклом For Next, оскільки ми використовували один цикл For Next в іншому.
Оператори "EXIT For" у "For Next Loops"
Оператор "Exit For" дозволяє повністю вийти з циклу "For Next".
Ви можете використовувати його у випадках, коли ви хочете, щоб цикл For Next закінчувався, коли виконується певна умова.
Давайте візьмемо приклад, коли у вас є набір чисел у стовпці А, і ви хочете виділити всі негативні числа червоним шрифтом. У цьому випадку нам потрібно проаналізувати кожну клітинку на її значення, а потім відповідно змінити колір шрифту.
Але, щоб зробити код більш ефективним, ми можемо спочатку перевірити, чи є у списку від’ємні значення чи ні. Якщо немає від'ємних значень, ми можемо використовувати оператор Exit For, щоб просто вийти з коду.
Нижче наведено код, який робить це:
Sub HghlightNegative () Dim Rng As Range Set Rng = Діапазон ("A1", Діапазон ("A1"). Кінець (xlDown)) Лічильник = Rng.Count For i = 1 Лічильник, якщо WorksheetFunction.Min (Rng)> = 0 Тоді Вийдіть для Якщо Rng (i). Значення <0 Тоді Rng (i). Font.Color = vb Червоний Далі i Кінець Sub
Коли ви використовуєте оператор "Exit For" у вкладеному циклі "For Next", він виходить із циклу, в якому він виконується, і продовжує виконувати наступний рядок у коді після циклу For Next.
Наприклад, у наведеному нижче коді оператор "Exit For" виведе вас із внутрішнього циклу, але зовнішній цикл продовжить працювати.
Sub SampleCode () For i = 1 To 10 For j = 1 to 10 Exit For Next J Next i End Sub
Робіть, поки цикл
Цикл "Do While" дозволяє перевірити наявність умови та запустити цикл, поки ця умова виконується (або має значення TRUE).
У циклі Do While є два типи синтаксису.
Виконайте цикл [Умова поки] [Блок коду для виконання]
та
Виконувати [Код блоку для виконання] Цикл [Умова поки]
Різниця між цими двома полягає в тому, що в першому, перш ніж виконується будь -який блок коду, перевіряється умова While, а у другому - спочатку кодовий блок, а потім перевіряється умова While.
Це означає, що якщо умова While є False - це обидва випадки, код все одно запускатиметься принаймні один раз у другому випадку (оскільки умова "While" перевіряється після того, як код був виконаний один раз).
Тепер давайте розглянемо деякі приклади використання циклів Do While у VBA.
Приклад 1 - Додайте перші 10 позитивних цілих чисел за допомогою VBA
Припустимо, ви хочете додати перші десять натуральних чисел за допомогою циклу Do While у VBA.
Для цього ви можете використовувати цикл «До», поки наступне число не буде меншим або рівним 10. Як тільки число стане більше 1o, ваш цикл зупиниться.
Ось код VBA, який запускатиме цей цикл Do While і показуватиме результат у вікні повідомлення.
Sub AddFirst10PositiveIntegers () Dim i As Integer i = 1 Do While i <= 10 Result = Result + i i = i + 1 Loop MsgBox Результат Кінець Sub
Вищевказаний цикл продовжує працювати, поки значення "i" не стане 11. Як тільки воно стане 11, цикл закінчується (коли умова While стає False).
У циклі ми використовували змінну Result, яка містить кінцеве значення Після завершення циклу у вікні повідомлення відображається значення змінної "Result".
Приклад 2 - Введіть дати поточного місяця
Припустимо, ви хочете ввести всі дати поточного місяця у стовпець робочого аркуша.
Ви можете зробити це за допомогою такого коду циклу Do While:
Sub EnterCurrentMonthDates () Dim CMDate As Date Dim i As Integer i = 0 CMDate = DateSerial (Рік (Дата), Місяць (Дата), 1) Do While Month (CMDate) = Місяць (Дата) Діапазон ("A1"). Зсув (i, 0) = CMDate i = i + 1 CMDate = CMDate + 1 Loop End Sub
Наведений вище код міститиме всі дати у першому стовпці робочого аркуша (починаючи з A1). Цикли тривають доти, доки значення змінної "CMDate" місяця не збігається зі значенням поточного місяця.
Вихід із заяви
Ви можете використовувати оператор Exit Do, щоб вийти з циклу. Щойно код виконує рядок «Вийти з Do», він виходить із циклу Do While і передає управління наступному рядку одразу після циклу.
Наприклад, якщо ви хочете ввести лише перші 10 дат, то можна вийти з циклу, як тільки будуть введені перші 10 дат.
Нижче наведений код зробить це:
Sub EnterCurrentMonthDates () Dim CMDate As Date Dim i As Integer i = 0 CMDate = DateSerial (Рік (Date), Month (Date), 1) Do While Month (CMDate) = Month (Date) Range ("A1"). Offset (i, 0) = CMDate i = i + 1 Якщо i> = 10 Тоді Вийти Do CMDate = CMDate + 1 Loop End Sub
У наведеному вище коді оператор IF використовується для перевірки того, чи є значення i більше 10 чи ні. Як тільки значення "i" стає 10, оператор Exit Do виконується і цикл закінчується.
Робіть до циклу
Цикли "Do Until" дуже схожі на цикли "Do While".
У "Do While" цикл працює доти, поки не буде виконана дана умова, а в "Do Until" - цикл, поки не буде виконано зазначену умову.
У циклі Do Until Loop існує два типи синтаксису.
Виконайте цикл [До умови] [Блок коду для виконання]
та
Виконувати [Код блоку для виконання] Цикл [До умови]
Різниця між цими двома параметрами полягає в тому, що в першому, перш ніж виконується будь -який блок коду, перевіряється умова До, а у другому - спочатку виконується блок коду, а потім перевіряється умова До.
Це означає, що якщо умова "До" - "ІСТИНА" - це обидва випадки, код все одно запускатиметься принаймні один раз у другому випадку (оскільки умова "До" перевіряється після того, як код був виконаний один раз).
Тепер давайте подивимося на деякі приклади використання циклів Do Until у VBA.
Примітка: Усі приклади для Do While такі ж, як і для Do While. Вони були змінені, щоб показати, як працює цикл "До тих пір".
Приклад 1 - Додайте перші 10 позитивних цілих чисел за допомогою VBA
Припустимо, ви хочете додати перші десять натуральних чисел за допомогою циклу Do Until у VBA.
Для цього вам потрібно запустити цикл, доки наступне число не буде меншим або рівним 10. Як тільки число стане більше 1o, ваш цикл зупиниться.
Ось код VBA, який запускатиме цей цикл і покаже результат у вікні повідомлення.
Sub AddFirst10PositiveIntegers () Dim i As Integer i = 1 Do While i> 10 Result = Result + i i = i + 1 Loop MsgBox Результат Кінець Sub
Вищевказаний цикл продовжує працювати, поки значення "i" не стане 11. Як тільки воно стане 11, цикл закінчується (оскільки умова "Поки" стає True).
Приклад 2 - Введіть дати поточного місяця
Припустимо, ви хочете ввести всі дати поточного місяця у стовпець робочого аркуша.
Ви можете зробити це за допомогою такого коду циклу «До до»:
Sub EnterCurrentMonthDates () Dim CMDate As Date Dim i As Integer i = 0 CMDate = DateSerial (рік (дата), місяць (дата), 1) Дія до місяця (CMDate) Місяць (дата) Діапазон ("A1"). Зміщення ( i, 0) = CMDate i = i + 1 CMDate = CMDate + 1 Loop End Sub
Наведений вище код міститиме всі дати у першому стовпці робочого аркуша (починаючи з A1). Цикл триває, поки місяць змінної CMDate не дорівнює місяцю поточного місяця.
Вихід із заяви
Ви можете використати оператор "Exit Do", щоб вийти з циклу.
Як тільки код виконує рядок «Вийти з Do», він виходить із циклу «До до» і передає управління наступному рядку відразу після циклу.
Наприклад, якщо ви хочете ввести лише перші 10 дат, то можна вийти з циклу, як тільки будуть введені перші 10 дат.
Нижче наведений код зробить це:
Sub EnterCurrentMonthDates () Dim CMDate As Date Dim i As Integer i = 0 CMDate = DateSerial (рік (дата), місяць (дата), 1) Дія до місяця (CMDate) Місяць (дата) Діапазон ("A1"). Зміщення ( i, 0) = CMDate i = i + 1 Якщо i> = 10 Тоді Вийти Do CMDate = CMDate + 1 Loop End Sub
У наведеному вище коді, як тільки значення "i" стає 10, виконується вивід "До" і цикл закінчується.
Для кожного
У VBA ви можете перебирати набір колекцій за допомогою циклу "Для кожного".
Ось кілька прикладів колекцій у Excel VBA:
- Збірник усіх відкритих робочих зошитів.
- Збірник усіх робочих листів у робочому зошиті.
- Сукупність усіх клітинок у діапазоні вибраних клітинок.
- Сукупність усіх діаграм або фігур у робочій книзі.
Використовуючи цикл «Для кожного», ви можете переглядати кожен із об’єктів у колекції та виконувати над ним певні дії.
Наприклад, ви можете переглянути всі робочі аркуші у книзі та захистити їх, а можете переглянути всі клітинки у виділенні та змінити форматування.
За допомогою циклу "Для кожного" (також відомого як цикл "Для кожного-наступного") вам не потрібно знати, скільки об'єктів є у колекції.
Цикл "Для кожного" буде автоматично проходити через кожен об'єкт і виконувати зазначену дію. Наприклад, якщо ви хочете захистити всі аркуші в книзі, код буде таким самим, незалежно від того, чи є у вас книга з 3 -ма аркушами або 30 аркушами.
Ось синтаксис циклу For Every-Next у Excel VBA.
Для кожного елемента у колекції [Блок коду для виконання] Далі [елемент]
Тепер давайте розглянемо пару прикладів використання циклу «Для кожного» в Excel.
Приклад 1 - Перегляньте всі робочі аркуші у робочій книзі (і захистіть її)
Припустимо, у вас є робоча книга, де ви хочете захистити всі аркуші.
Нижче для циклу Every-Next це можна легко зробити:
Sub ProtectSheets () Затемнення ws як робочого аркуша для кожного ws в ActiveWorkbook.Worksheets ws.Protect Next ws End Sub
У наведеному вище коді ми визначили змінну "ws" як об'єкт робочого аркуша. Це говорить VBA, що "ws" слід інтерпретувати як об'єкт робочого аркуша в коді.
Тепер ми використовуємо оператор "Для кожного", щоб пройти кожне "ws" (що є об'єктом аркуша) у колекції всіх аркушів активної книги (надано ActiveWorkbook.Worksheets).
Зауважте, що на відміну від інших циклів, де ми намагалися захистити всі аркуші у книзі, тут нам не потрібно турбуватися про те, скільки аркушів є у книзі.
Нам не потрібно рахувати їх для запуску циклу. Для кожного циклу гарантує, що всі об’єкти аналізуються один за одним.
Приклад 2 - Перегляньте всі відкриті книги (і збережіть усі)
Якщо ви одночасно працюєте з кількома книгами, вам може стати в нагоді зберегти всі ці книги одночасно.
Нижче код VBA може зробити це для нас:
Sub SaveAllWorkbooks () Dim wb як робоча книга для кожної wb у робочих книгах wb. Зберегти наступну wb End Sub
Зауважте, що в цьому коді ви не отримуєте підказки з проханням зберегти книгу в певному місці (якщо зберігати її вперше).
Він зберігає його у папці за замовчуванням (у моєму випадку це була папка "Документи"). Цей код найкраще працює, коли ці файли вже збережено, і ви вносите зміни, і хочете швидко зберегти всі книги.
Приклад 3 - Пройдіть усі клітинки у виділеному місці (виділіть негативні значення)
За допомогою циклу "Для кожного" можна прокрутити всі клітинки в певному діапазоні або у вибраному діапазоні.
Це може бути корисним, якщо ви хочете проаналізувати кожну клітинку та виконати на її основі дію.
Наприклад, нижче наведено код, який пройде через усі клітинки у виділенні та змінить колір осередків з негативними значеннями на червоний.
Sub HighlightNegativeCells () Dim Cll як діапазон для кожного Cll у виділенні Якщо Cll.Value <0 Тоді Cll.Interior.Color = vbRed End Якщо наступний Cll End Sub
(Зверніть увагу, що я використовував Cll як коротку назву змінної для Cell. Бажано не використовувати назви об’єктів, таких як Таблиці чи Діапазон, як імена змінних)
У наведеному вище коді цикл For Every-Next проходить через збір клітинок у виділенні. Оператор IF використовується для визначення того, чи є значення комірки від'ємним чи ні. Якщо це так, клітинка набуває червоного кольору всередині, інакше вона переходить до наступної комірки.
Якщо у вас немає виділення, а натомість потрібно, щоб VBA вибрав усі заповнені клітинки у стовпці, починаючи з певної комірки (так само, як ми використовуємо Control + Shift + клавіша зі стрілкою вниз для вибору всіх заповнених клітинок), ви можете використовуйте код нижче:
Sub HighlightNegativeCells () Dim Cll As Range Dim Rng As Range Set Rng = Діапазон ("A1", Діапазон ("A1"). Кінець (xlDown)) Для кожного Cll У Rng Якщо Cll.Value <0 Тоді Cll.Interior.Color = vb Червоний кінець, якщо наступний Cll Кінець Під
У наведеному вище прикладі не має значення, скільки заповнених клітин. Він почнеться з комірки A1 і проаналізує всі суміжні заповнені клітинки у стовпці.
Вам також не потрібно вибирати комірку А1. Ви можете вибрати будь-яку віддалену клітинку, і під час запуску коду вона все одно розглядатиме всі клітинки у стовпці А (починаючи з А1) та забарвлюватиме негативні клітинки.
Статистика "Вихід для"
Ви можете використати оператор "Вихід для" у циклі "За кожен наступний", щоб вийти з циклу. Зазвичай це робиться у разі виконання певної умови.
Наприклад, у Прикладі 3, коли ми проходимо через набір клітинок, може бути ефективніше перевірити, чи є негативні значення чи ні. Якщо негативних значень немає, ми можемо просто вийти з циклу та заощадити час обробки VBA.
Нижче наведено код VBA, який буде це робити:
Sub HighlightNegativeCells () Dim Cll як діапазон для кожного Cll у виділенні If WorksheetFunction.Min (Selection)> = 0 Тоді вийти For If Cll.Value <0 Тоді Cll.Interior.Color = vbRed End Якщо наступний Cll End Sub
Де розмістити код VBA
Цікаво, де код VBA міститься у вашій книзі Excel?
Excel має бекенд VBA, який називається редактором VBA. Вам потрібно скопіювати та вставити код у вікно коду модуля VB Editor.
Нижче наведено кроки для цього.
- Перейдіть на вкладку Розробник.
- Натисніть на опцію Visual Basic. Це відкриє редактор VB у бекенді.
- На панелі «Провідник проектів» у редакторі VB клацніть правою кнопкою миші будь-який об’єкт книги, до якого потрібно вставити код. Якщо ви не бачите Провідник проектів, перейдіть на вкладку Вигляд і натисніть Провідник проектів.
- Перейдіть до пункту Вставити та натисніть Модуль. Це додасть об’єкт модуля до вашої книги.
- Скопіюйте та вставте код у вікно модуля.
