Excel циклы

Проход по диапазону ячеек Looping Through a Range of Cells

При использовании Visual Basic часто требуется выполнить один и тот же блок операторов в каждой ячейке диапазона. When using Visual Basic, you often need to run the same block of statements on each cell in a range of cells. Для этого необходимо объединить оператор цикла и один или несколько методов для идентификации каждой ячейки по отдельности и выполнить операцию. To do this, you combine a looping statement and one or more methods to identify each cell, one at a time, and run the operation.

Один из способов пройти по диапазону — использовать цикл For. Next со свойством Cells. One way to loop through a range is to use the For. Next loop with the Cells property. С помощью свойства Cells можно заменить номера индексов ячеек счетчиком циклов (или другими переменными или выражениями). Using the Cells property, you can substitute the loop counter (or other variables or expressions) for the cell index numbers. В следующем примере индекс строки заменяется переменной counter . In the following example, the variable counter is substituted for the row index. Процедура проходит по диапазону ячеек C1:C20, присваивая значение 0 (ноль) каждому числу, абсолютное значение которого меньше 0,01. The procedure loops through the range C1:C20, setting to 0 (zero) any number whose absolute value is less than 0.01.

Еще один простой способ пройти по диапазону — использовать цикл For Each. Next с коллекцией ячеек, указанной в свойстве Range. Another easy way to loop through a range is to use a For Each. Next loop with the collection of cells specified in the Range property. Visual Basic автоматически присваивает объектную переменную для следующей ячейки при каждом выполнении цикла. Visual Basic automatically sets an object variable for the next cell each time the loop runs. Следующая процедура проходит по диапазону ячеек A1:D10, присваивая значение 0 (ноль) каждому числу, абсолютное значение которого меньше 0,01. The following procedure loops through the range A1:D10, setting to 0 (zero) any number whose absolute value is less than 0.01.

Если вы не знаете границы диапазона, по которому нужно пройти, можно использовать свойство CurrentRegion, чтобы возвратить диапазон, окружающий активную ячейку. If you do not know the boundaries of the range you want to loop through, you can use the CurrentRegion property to return the range that surrounds the active cell. Например, при запуске на листе следующая процедура выполняет проход по диапазону, окружающему активную ячейку, присваивая значение 0 (ноль) каждому числу, абсолютное значение которого меньше 0,01. For example, the following procedure, when run from a worksheet, loops through the range that surrounds the active cell, setting to 0 (zero) any number whose absolute value is less than 0.01.

Поддержка и обратная связь Support and feedback

Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Have questions or feedback about Office VBA or this documentation? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь. Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.

Источник: docs.microsoft.com

Excel циклы

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

Циклы

Циклы – повторяющиеся действия. Бывает нужно сделать одно и то же действие определенное количество раз (например, на пяти листах вставить столбец с одинаковыми данными или заголовком), а бывает, что нужно делать какое-то действие “до тех пор, пока . ” (например, пока не найдется строка с нужными данными). В VBA для двух этих случаев есть два цикла: “for” – для повторения действий заданное число раз, и “while” – для повторения действий “до тех пор, пока . “. Разница между двумя циклами только одна: в первом случае вы знаете, сколько раз надо повторить цикл, а во втором – не знаете, но знаете, когда нужно остановиться. Есть и другие циклы, но их можно легко заменить этими двумя.

Цикл for

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

F or i=1 to 5 ‘ присваивает значения переменной i в первый раз 1,

‘ затем прибавляя по 1 с каждым новым заходом в

‘ цикл. Значение 5 будет последним.

Sheets(i).Activate ‘ активируем очередной лист

Columns(1).Insert ‘ вставляем первый столбец

Next i ‘ конец цикла, то есть конец списка действий,

‘ которые нужно сделать 5 раз подряд

Цикл While

Цикл While используется чаще, чем For, потому что часто мы не знаем, сколько раз нужно сделать какое-то действие. Он используется, например, при поиске строчки с заданными критериями. Рассмотрим такой пример: в столбцах есть данные – фамилия (1 столбец), имя (2 столбец), отчество (3), год рождения (4). Нам нужно найти Иванова и, например, удалить его из базы. Допустим, мы знаем, что такой человек один. Тогда программа может быть такой:

i=2 ‘ определяем, с какой строки начинается база (в первой строке расположены заголовки)

Do While Cells(i,1)<>“Иванов” ‘ делать до тех пор, пока

В итоге цикл закончится на искомой строчке. После этого можно делать с этой строчкой всё, что угодно.

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

Оператор If

Условный оператор If (если) называется так, потому что проверяет выполнение условия. Принцип его работы понять очень просто: если условие выполняется, то делаем так, иначе делаем так. Рассмотрим наш пример: нам нужно пробежать по всей базе, то есть до тех пор, пока строчки не пусты, т.е:

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

Do While С ells(i,1)<>“” and С ells(i+1,1)<>“” and С ells(i+2,1)<>“”

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

Do While С ells(i,1)<>“” and С ells(i,2)<>“” and С ells(i,3)<>“”

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

Воспользуемся этим условием

Do While С ells(i,1)<>“” and С ells(i,2)<>“” and С ells(i,3)<>“”

If Cells ( i ,1)=”Иванов” then ‘ Если в строке Иванов

Rows(i).Delete ‘ тогда удаляем эту строку

i=i-1 ‘ возвращаемся на строку выше

End If ‘ закрываем условие

i=i+1 ‘ переходим к следующей строке

Обрати внимание на необходимость возвращаться на строку выше при удалении строки. В программе мы указываем номер строки, а переходим к следующей, прибавляя единичку. Если удалить строку номер 5, то на её место “въедут” данные шестой строки, став снова пятой. Но так как эти данные мы не просмотрели, придется зайти в строку №5 снова. Чтобы это сделать, после удаления отнимаем единичку, потом при переходе прибавляем единичку и попадаем в строку с тем же номером.

Источник: analitica.jimdofree.com

Циклы VBA

Доброго времени суток! Данную статью я решил посвятить рубрике по основам программирования в Visual Basic for Application. И сегодня мы поговорим о циклах в VBA, разберём их синтаксис и рассмотрим несколько примеров, которые часто встречаются программисту.

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

В данной статье мы разберём синтаксис и примеры следующих циклов в VBA:

• For
• For each
• While
• Until

Цикл For в VBA

Цикл for в VBA обычно используется при зацикливании фрагмента кода, если нам известно конечное значение counter — счетчика, при котором мы выйдем из цикла.
Возьмём для примера самый распространённый пример:

Сгенерировать массив из 5 целых значений

Обратите ваше внимание, что в этом примере используется неявное объявление при работе с циклами в VBA. i% — означает неявное объявление переменной i в формате integer. Такая конструкция по сути заменяет следующую: dim i as integer . Это используется для сокращения кода и для удобства написания и чтения. В старых версиях VBA необходимо указывать знак формата после каждого использования неявной переменной. В более поздних версиях достаточно всего один раз.

VBA для цикла for даёт возможность использовать функцию Step. Как ясно из перевода, это шаг, с которым мы будем проходить наш интервал. По умолчанию, он равен 1. Популярный вариант использования встречается в случаях, когда counter связан с переменной, используемой внутри цикла. Например, при написании программ, связанных с функциями.

Найти пересечение графика функции y = 5*x + 5 с осью ординат

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

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

Цикл For Each в VBA

For Each в VBA основан на переборе всех элементов, указанного типа в массиве, объекте или группе.
Самый популярный вариант его использования — перебор страниц в рабочей книге.

Вывести названия всех листов в рабочей книге

И ещё один интересный пример:

Изменить размер шрифта и выравнить по центру текст в label

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

Цикл While в VBA

Циклы в VBA, которые используют структуру Do..Loop (это while и until циклы) можно записывать с разным расположением фрагмента условия. Как видите на картинке выше, условие может проверяться после выполнения одной итерации, а может перед запуском цикла.
Самый популярный пример:

Отсортируйте по возрастанию сгенерированный массив методом пузырька

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

Цикл Until в VBA

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

Заставить пользователя ввести число

Почему заставить? Потому, что если пользователь закроет окно ввода, это его не спасёт, оно будет появляться вновь и вновь пока он не введёт любое число.

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

Источник: codetown.ru

Циклы в VBA

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

К циклам VBA относятся:

Далее мы подробно рассмотрим каждый из этих циклов.

Оператор цикла «For» в Visual Basic

Структура оператора цикла For в Visual Basic может быть организована в одной из двух форм: как цикл For … Next или как цикл For Each.

Цикл «For … Next»

Цикл For … Next использует переменную, которая последовательно принимает значения из заданного диапазона. С каждой сменой значения переменной выполняются действия, заключённые в теле цикла. Это легко понять из простого примера:

В этом простом цикле For … Next используется переменная i, которая последовательно принимает значения 1, 2, 3, … 10, и для каждого из этих значений выполняется код VBA, находящийся внутри цикла. Таким образом, данный цикл суммирует элементы массива iArray в переменной Total.

В приведённом выше примере шаг приращения цикла не указан, поэтому для пошагового увеличения переменной i от 1 до 10 по умолчанию используется приращение 1. Однако, в некоторых случаях требуется использовать другие значения приращения для цикла. Это можно сделать при помощи ключевого слова Step, как показано в следующем простом примере.

Так как в приведённом выше примере задан шаг приращения равный 0.1, то переменная dTotal для каждого повторения цикла принимает значения 0.0, 0.1, 0.2, 0.3, … 9.9, 10.0.

Для определения шага цикла в VBA можно использовать отрицательную величину, например, вот так:

Здесь шаг приращения равен -1, поэтому переменная i с каждым повторением цикла принимает значения 10, 9, 8, … 1.

Цикл «For Each»

Цикл For Each похож на цикл For … Next, но вместо того, чтобы перебирать последовательность значений для переменной-счётчика, цикл For Each выполняет набор действий для каждого объекта из указанной группы объектов. В следующем примере при помощи цикла For Each выполняется перечисление всех листов в текущей рабочей книге Excel:

Оператор прерывания цикла «Exit For»

Оператор Exit For применяется для прерывания цикла. Как только в коде встречается этот оператор, программа завершает выполнение цикла и переходит к выполнению операторов, находящихся в коде сразу после данного цикла. Это можно использовать, например, для поиска определённого значения в массиве. Для этого при помощи цикла просматривается каждый элемент массива. Как только искомый элемент найден, просматривать остальные нет необходимости – цикл прерывается.

Применение оператора Exit For продемонстрировано в следующем примере. Здесь цикл перебирает 100 записей массива и сравнивает каждую со значением переменной dVal. Если совпадение найдено, то цикл прерывается:

Цикл «Do While» в Visual Basic

Цикл Do While выполняет блок кода до тех пор, пока выполняется заданное условие. Далее приведён пример процедуры Sub, в которой при помощи цикла Do While выводятся последовательно числа Фибоначчи не превышающие 1000:

В приведённом примере условие iFib_Next