Чтобы получить максимальную отдачу от 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:
Для Counter = Start To End [Step Value] [Code Block to Execute] Next [counter]
В цикле For Next вы можете использовать счетчик (или любую переменную), который будет использоваться для запуска цикла. Этот счетчик позволяет запускать этот цикл необходимое количество раз.
Например, если я хочу сложить первые 10 положительных целых чисел, то значение моего счетчика будет от 1 до 10.
Давайте рассмотрим несколько примеров, чтобы лучше понять, как работает цикл For Next.
Пример 1 - Добавление первых 10 натуральных чисел
Ниже приведен код, который добавит первые 10 положительных целых чисел с помощью цикла For Next.
Затем отобразится окно сообщения, показывающее сумму этих чисел.
Sub AddNumbers () Dim Total as Integer Dim Count as Integer Total = 0 For Count = 1 to 10 Total = Total + Count Next Count MsgBox Total End Sub
В этом коде значение Total устанавливается в 0 перед переходом в цикл For Next.
Как только он попадает в цикл, он сохраняет общее значение после каждого цикла. Таким образом, после первого цикла, когда счетчик равен 1, значение «Total» становится равным 1, а после второго цикла оно становится равным 3 (1 + 2), и так далее.
И, наконец, по окончании цикла переменная Total содержит сумму первых 10 положительных целых чисел.
Затем MsgBox просто отображает результат в окне сообщения.
Пример 2 - Добавление первых 5 четных положительных целых чисел
Чтобы суммировать первые пять четных положительных целых чисел (например, 2,4,6,8 и 10), вам понадобится аналогичный код с условием, чтобы рассматривать только четные числа и игнорировать нечетные числа.
Вот код, который это сделает:
Sub AddEvenNumbers () Dim Total as Integer Dim Count as Integer Total = 0 For Count = 2 to 10 Step 2 Total = Total + Count Next Count MsgBox Total End Sub
Обратите внимание, что мы начали значение Count с 2, а также использовали "Шаг 2‘.
Когда вы используете 'Шаг 2', он сообщает коду увеличивать значение «Count» на 2 при каждом запуске цикла.
Таким образом, значение Count начинается с 2, а затем становится 4, 6, 8 и 10, когда происходит цикл.
ПРИМЕЧАНИЕ. Другой способ сделать это - запустить цикл от 1 до 10 и внутри цикла проверить, является ли число четным или нечетным. Однако использование Step в этом случае является более эффективным способом, так как не требует, чтобы цикл выполнялся 10 раз, а только 5 раз.
Значение Step также может быть отрицательным. В таком случае счетчик запускается с более высокого значения и продолжает уменьшаться на указанное значение шага.
Пример 3 - Ввод серийного номера в выбранные ячейки
Вы также можете использовать цикл For Next для просмотра коллекции объектов (таких как ячейки, рабочие листы или книги),
Вот пример, который быстро вводит серийные номера во все выбранные ячейки.
Sub EnterSerialNumber () Dim Rng As Range Dim Counter As Integer Dim RowCount As Integer Set Rng = Selection RowCount = Rng.Rows.Count For Counter = 1 To RowCount ActiveCell.Offset (Counter - 1, 0). Value = Counter Next Counter End Sub
Приведенный выше код сначала подсчитывает количество выбранных строк, а затем присваивает это значение переменной RowCount. Затем мы запускаем цикл от «1 до RowCount».
Также обратите внимание, что, поскольку выделение может быть любым количеством строк, мы установили для переменной Rng значение Selection (со строкой «Set Rng = Selection»). Теперь мы можем использовать переменную «Rng» для ссылки на выделение в нашем коде.
Пример 4 - Защита всех листов в активной книге
Вы можете использовать цикл «Для следующего», чтобы просмотреть все рабочие листы в активной книге и защитить (или снять защиту) каждый из рабочих листов.
Ниже приведен код, который это сделает:
Sub ProtectWorksheets () Dim i As Integer For i = 1 В ActiveWorkbook.Worksheets.Count Worksheets (i) .Protect Next i End Sub
Приведенный выше код подсчитывает количество листов с помощью ActiveWorkbook.Worksheets.Count. Это сообщает VBA, сколько раз цикл нужно запускать.
В каждом случае он обращается к I-й книге (с использованием рабочих листов (i)) и защищает ее.
Вы можете использовать этот же код для снятия защиты с листов. Просто измените строку Рабочие листы (i) .Protect к Рабочие листы (i) .UnProtect.
Вложенные циклы «для следующих»
Вы можете использовать вложенные циклы «Для следующего», чтобы выполнить более сложную автоматизацию в Excel. Вложенный цикл «For Next» будет означать, что в цикле «For Next» есть цикл «For Next».
Позвольте мне показать вам, как это использовать, на примере.
Предположим, в моей системе открыто 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
Оператор Exit For позволяет полностью выйти из цикла For Next.
Вы можете использовать его в тех случаях, когда вы хотите, чтобы цикл For Next завершался при выполнении определенного условия.
Рассмотрим пример, когда у вас есть набор чисел в столбце A, и вы хотите выделить все отрицательные числа красным шрифтом. В этом случае нам нужно проанализировать каждую ячейку на предмет ее значения, а затем соответствующим образом изменить цвет шрифта.
Но чтобы сделать код более эффективным, мы можем сначала проверить, есть ли в списке отрицательные значения. Если нет отрицательных значений, мы можем использовать оператор Exit For, чтобы просто выйти из кода.
Ниже приведен код, который это делает:
Sub HghlightNegative () Dim Rng As Range Set Rng = Range ("A1", Range ("A1"). End (xlDown)) Counter = Rng.Count For i = 1 To Counter If WorksheetFunction.Min (Rng)> = 0 Затем выйти для If Rng (i) .Value <0 Then Rng (i) .Font.Color = vbRed Next i End Sub
Когда вы используете оператор «Exit For» во вложенном цикле «For Next», он выйдет из цикла, в котором он выполняется, и продолжит выполнение следующей строки в коде после цикла For Next.
Например, в приведенном ниже коде оператор «Exit For» выведет вас из внутреннего цикла, но внешний цикл продолжит работать.
Sub SampleCode () Для i = от 1 до 10 Для j = от 1 до 10 Выход для следующего J Далее i End Sub
Сделать цикл
Цикл «Выполнить пока» позволяет вам проверить наличие условия и запустить цикл, пока это условие выполняется (или имеет значение ИСТИНА).
В цикле Do While Loop есть два типа синтаксиса.
Выполните [Условие пока] [Блок кода для выполнения] Цикл
и
Выполните [блок кода для выполнения] Цикл [условие пока]
Разница между этими двумя вариантами заключается в том, что в первом случае сначала проверяется условие «Пока» перед выполнением любого блока кода, а во втором случае сначала выполняется блок кода, а затем проверяется условие «Пока».
Это означает, что если условие «Пока» имеет значение «Ложь» в обоих случаях, код все равно будет выполняться хотя бы один раз во втором случае (поскольку условие «Пока» проверяется после того, как код был выполнен один раз).
Теперь давайте посмотрим на несколько примеров использования циклов Do While в VBA.
Пример 1. Добавление первых 10 положительных целых чисел с помощью VBA
Предположим, вы хотите сложить первые десять положительных целых чисел с помощью цикла Do While в VBA.
Для этого вы можете использовать цикл Do While, пока следующее число не станет меньше или равно 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 Result End Sub
Вышеупомянутый цикл продолжает работать до тех пор, пока значение «i» не станет 11. Как только оно станет равным 11, цикл завершится (так как условие While станет ложным).
Внутри цикла мы использовали переменную Result, которая содержит окончательное значение. После завершения цикла в окне сообщения отображается значение переменной «Result».
Пример 2 - Введите даты текущего месяца
Допустим, вы хотите ввести все даты текущего месяца в столбец таблицы.
Вы можете сделать это, используя следующий код цикла Do While:
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 подпрограмма конца цикла
Приведенный выше код вводит все даты в первый столбец рабочего листа (начиная с A1). Циклы продолжаются до тех пор, пока значение месяца в переменной CMDate не совпадет со значением текущего месяца.
Заявление о выходе
Вы можете использовать оператор Exit Do, чтобы выйти из цикла. Как только код выполняет строку «Exit Do», он выходит из цикла Do While и передает управление следующей строке сразу после цикла.
Например, если вы хотите ввести только первые 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
В приведенном выше коде оператор IF используется для проверки того, больше ли значение i 10 или нет. Как только значение «i» становится равным 10, выполняется инструкция Exit Do и цикл завершается.
До цикла
Циклы Do While очень похожи на циклы Do While.
В режиме «Do While» цикл выполняется до тех пор, пока не будет выполнено заданное условие, а в «Do While» - до тех пор, пока не будет выполнено заданное условие.
В цикле Do until Loop есть два типа синтаксиса.
Выполнить [Пока условие] [Блок кода для выполнения] Цикл
и
Выполните [Блок кода для выполнения] Цикл [До условия]
Разница между этими двумя вариантами заключается в том, что в первом случае сначала проверяется условие «Пока» до выполнения любого блока кода, а во втором случае сначала выполняется блок кода, а затем проверяется условие «Пока».
Это означает, что если условие «Пока» имеет значение ИСТИНА в обоих случаях, код все равно будет выполняться хотя бы один раз во втором случае (поскольку условие «До» проверяется после того, как код был выполнен один раз).
Теперь давайте посмотрим на несколько примеров использования циклов Do until в VBA.
Примечание. Все примеры для Do While такие же, как и для Do While. Они были изменены, чтобы показать вам, как работает цикл Do until.
Пример 1. Добавление первых 10 положительных целых чисел с помощью VBA
Предположим, вы хотите сложить первые десять положительных целых чисел с помощью цикла Do until в VBA.
Для этого вам нужно запускать цикл до тех пор, пока следующее число не станет меньше или равно 10. Как только число станет больше 1o, ваш цикл остановится.
Вот код VBA, который запустит этот цикл и покажет результат в окне сообщения.
Sub AddFirst10PositiveIntegers () Dim i As Integer i = 1 До тех пор, пока i> 10 Result = Result + i i = i + 1 Loop MsgBox Result End Sub
Вышеупомянутый цикл продолжает работать до тех пор, пока значение «i» не станет 11. Как только оно станет равным 11, цикл завершится (когда условие «До» станет истинным).
Пример 2 - Введите даты текущего месяца
Допустим, вы хотите ввести все даты текущего месяца в столбец таблицы.
Вы можете сделать это, используя следующий код цикла Do until:
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 подпрограмма конца цикла
Приведенный выше код вводит все даты в первый столбец рабочего листа (начиная с A1). Цикл продолжается до тех пор, пока Месяц переменной CMDate не станет равным Месяцу текущего месяца.
Заявление о выходе
Вы можете использовать оператор Exit Do, чтобы выйти из цикла.
Как только код выполняет строку «Exit Do», он выходит из цикла Do until и передает управление следующей строке сразу после цикла.
Например, если вы хотите ввести только первые 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, выполняется условие «Exit Do» и цикл завершается.
Для каждого
В VBA вы можете перемещаться по набору коллекций, используя цикл «For Each».
Вот несколько примеров коллекций в Excel VBA:
- Коллекция всех открытых рабочих книг.
- Коллекция всех листов в книге.
- Коллекция всех ячеек в диапазоне выбранных ячеек.
- Коллекция всех диаграмм или фигур в книге.
Используя цикл «For Each», вы можете просмотреть каждый из объектов в коллекции и выполнить с ним какое-либо действие.
Например, вы можете просмотреть все листы в книге и защитить их, или вы можете просмотреть все выделенные ячейки и изменить форматирование.
С циклом «For Each» (также называемым циклом «For Each-Next») вам не нужно знать, сколько объектов содержится в коллекции.
Цикл «For Each» будет автоматически проходить по каждому объекту и выполнять указанное действие. Например, если вы хотите защитить все листы в книге, код будет одинаковым, независимо от того, есть ли у вас книга с 3 листами или 30 листами.
Вот синтаксис цикла For Each-Next в Excel VBA.
Для каждого элемента в коллекции [Блок кода для выполнения] Далее [элемент]
Теперь давайте посмотрим на пару примеров использования цикла «Для каждого цикла» в Excel.
Пример 1. Просмотрите все рабочие листы в книге (и защитите их)
Предположим, у вас есть рабочая книга, в которой вы хотите защитить все рабочие листы.
Ниже для цикла For Each-Next это легко сделать:
Sub ProtectSheets () Dim ws As Worksheet для каждого ws в ActiveWorkbook.Worksheets ws.Protect Next ws End Sub
В приведенном выше коде мы определили переменную «ws» как объект рабочего листа. Это сообщает VBA, что «ws» следует интерпретировать как объект рабочего листа в коде.
Теперь мы используем оператор «For Each» для прохождения каждого «ws» (который является объектом рабочего листа) в коллекции всех рабочих листов в активной книге (предоставленной ActiveWorkbook.Worksheets).
Обратите внимание, что в отличие от других циклов, в которых мы пытались защитить все рабочие листы в книге, здесь нам не нужно беспокоиться о том, сколько листов в книге.
Нам не нужно их подсчитывать, чтобы запустить цикл. Цикл For Each гарантирует, что все объекты анализируются один за другим.
Пример 2. Просмотрите все открытые книги (и сохраните все)
Если вы работаете с несколькими книгами одновременно, может пригодиться возможность сохранить все эти книги сразу.
Ниже код VBA может сделать это за нас:
Sub SaveAllWorkbooks () Dim wb As Workbook for each wb In Workbooks wb.Save Next wb End Sub
Обратите внимание, что в этом коде вы не получите запрос на сохранение книги в определенном месте (если вы сохраняете ее впервые).
Он сохраняет его в папке по умолчанию (в моем случае это была папка «Документы»). Этот код лучше всего работает, когда эти файлы уже сохранены, вы вносите изменения и хотите быстро сохранить все книги.
Пример 3. Просмотрите все ячейки в выделенном фрагменте (выделите отрицательные значения)
Используя цикл «Для каждого», вы можете перебирать все ячейки в определенном диапазоне или в выбранном диапазоне.
Это может быть полезно, если вы хотите проанализировать каждую ячейку и выполнить действие на ее основе.
Например, ниже приведен код, который пройдет по всем ячейкам в выделенном фрагменте и изменит цвет ячеек с отрицательными значениями на красный.
Sub HighlightNegativeCells () Dim Cll As Range для каждого Cll в выбранном, если Cll.Value <0, то Cll.Interior.Color = vbRed End, если следующий Cll End Sub
(Обратите внимание, что я использовал Cll в качестве короткого имени переменной для Cell. Не рекомендуется использовать имена объектов, такие как Sheets или Range, в качестве имен переменных)
В приведенном выше коде цикл For Each-Next проходит через набор ячеек в выделенном фрагменте. Оператор IF используется для определения отрицательного значения ячейки. В этом случае ячейке будет присвоен красный цвет, иначе она перейдет к следующей ячейке.
Если у вас нет выделения и вместо этого вы хотите, чтобы VBA выделял все заполненные ячейки в столбце, начиная с определенной ячейки (точно так же, как мы используем Control + Shift + стрелку вниз для выбора всех заполненных ячеек), вы можете используйте приведенный ниже код:
Sub HighlightNegativeCells () Dim Cll As Range Dim Rng As Range Set Rng = Range ("A1", Range ("A1"). End (xlDown)) Для каждого Cll In Rng Если Cll.Value <0, то Cll.Interior.Color = vbRed End, если следующий Cll End Sub
В приведенном выше примере не имеет значения, сколько там заполненных ячеек. Он начнется с ячейки A1 и проанализирует все смежные заполненные ячейки в столбце.
Вам также не нужно выделять ячейку A1. Вы можете выбрать любую удаленную ячейку, и при запуске кода он все равно будет рассматривать все ячейки в столбце A (начиная с A1) и раскрашивать отрицательные ячейки.
Статус "Выход для"
Вы можете использовать оператор Exit For в цикле For Each-Next, чтобы выйти из цикла. Обычно это делается при соблюдении определенного условия.
Например, в примере 3, когда мы просматриваем набор ячеек, может быть более эффективным проверить, есть ли какие-либо отрицательные значения или нет. Если нет отрицательных значений, мы можем просто выйти из цикла и сэкономить время обработки VBA.
Ниже приведен код VBA, который сделает это:
Sub HighlightNegativeCells () Dim Cll As Range для каждого Cll в выделении, если WorksheetFunction.Min (Selection)> = 0, затем выйти, если Cll.Value <0, то Cll.Interior.Color = vbRed End, если следующий Cll End Sub
Куда поместить код VBA
Хотите знать, где находится код VBA в вашей книге Excel?
В Excel есть серверная часть VBA, называемая редактором VBA. Вам необходимо скопировать и вставить код в окно кода модуля VB Editor.
Вот как это сделать:
- Перейдите на вкладку Разработчик.
- Выберите вариант Visual Basic. Это откроет редактор VB в бэкэнде.
- На панели Project Explorer в редакторе VB щелкните правой кнопкой мыши любой объект книги, в которую вы хотите вставить код. Если вы не видите Project Explorer, перейдите на вкладку View и нажмите Project Explorer.
- Перейдите во вкладку "Вставить" и нажмите "Модуль". Это вставит объект модуля для вашей книги.
- Скопируйте и вставьте код в окно модуля.
