Пару недель назад я столкнулся с проблемой разработки электронной таблицы, которая рассчитывает интервалы рабочего времени на основе графика работы и графика производства. На первый взгляд задача не сложная, но в процессе проектирования я понял, что избежать использования петель не удастся.
Стандартный набор функций Excel не поддерживает ничего подобного циклам, но выход был: использовать циклические ссылки и итерации.
Первым шагом в работе с циклическими ссылками является включение итеративных вычислений в Excel. В версии Microsoft Office XP это делается в меню «Сервис — Параметры»:
В открывшемся окне установите соответствующий флажок:
Также обратите внимание на поле «Ограничение количества итераций»: оно определяет, сколько раз будет выполняться расчет для всех циклических ссылок, если вы не установите ограничение самостоятельно.
Информация: В Excel 2007 итерационные вычисления включены в пункте меню «Файл — Параметры Excel» в разделе «Формулы».
Рассмотрим пример использования итерационных вычислений в виде цикла.
Введите формулу в ячейку A1:
Если значение предельного количества итераций равно 100, результат вычислений в ячейке A1 будет равен 100. Теперь изменим формулу:
Как видите, мы ограничили количество итераций числом в ячейке A2, равным 10. Чтобы проиллюстрировать, как происходят итерации, давайте добавим формулу в ячейку B2:
Как видите, каждый шаг итерации (новое значение A1) добавляется в ячейку B1 до тех пор, пока не будут выполнены итерации.
Примечание: Возможно, результат в B2 будет другим — для его решения выполните следующие действия: После ввода формулы в ячейку B1 перейдите в ячейку A1, поместите курсор в поле редактирования формулы и нажмите Enter — итерации будут пересчитаны. Для правильной работы итераций необходимо задать ряд дополнительных условий, ограничивающих их количество, и указать условие для восстановления текущего значения поля до исходного значения. Все ячейки с круговой ссылкой пересчитываются при изменении ячеек, от которых они зависят.
Бывают ситуации, когда программе VBA необходимо выполнять один и тот же набор действий несколько раз подряд (то есть повторять один и тот же блок кода несколько раз). Это можно сделать с помощью циклов VBA.
Циклы VBA включают:
- За цикл
- Сделать цикл while
- Сделайте макияж до менструации
Далее мы более подробно рассмотрим каждый из этих циклов.
Оператор цикла «For» в Visual Basic
Структура оператора цикла For в Visual Basic может быть организована в одной из двух форм: цикл For… Next или цикл For Each.
Цикл «For … Next»
Цикл For… Next использует переменную, которая последовательно принимает значения из заданного диапазона. Каждый раз, когда значение переменной изменяется, выполняются действия, содержащиеся в теле цикла. Это легко понять на простом примере:
Для i = от 1 до 10 Total = Total + iArray (i) Next i
Этот простой цикл For… Next использует переменную i, которая последовательно принимает значения 1, 2, 3,… 10, и для каждого из этих значений выполняется код VBA внутри цикла. Таким образом, этот цикл суммирует элементы iArray в переменной Total.
В приведенном выше примере приращение цикла не указано, поэтому приращение используется по умолчанию для увеличения переменной i с 1 до 10. Однако в некоторых случаях необходимо использовать другие значения приращения для цикла. Это можно сделать с помощью ключевого слова Step, как показано в следующем простом примере.
Для d = от 0 до 10 Шаг 0,1 dTotal = dTotal + d Далее d
Поскольку в приведенном выше примере шаг приращения установлен на 0,1, переменная dTotal для каждого повторения цикла принимает значения 0,0, 0,1, 0,2, 0,3,… 9,9, 10,0.
Чтобы определить шаг цикла в VBA, вы можете использовать отрицательное значение, например, так:
Для i = от 10 до 1 Шаг -1 iArray (i) = i Далее i
Здесь приращение равно -1, поэтому переменная i при каждом повторении цикла принимает значения 10, 9, 8,… 1.
Цикл «For Each»
Цикл For Each похож на цикл For… Next, но вместо перебора последовательности значений для переменной счетчика цикл For Each выполняет серию действий с каждым объектом в указанной группе объектов. В следующем примере цикл For Each используется для перечисления всех листов в текущей книге Excel:
Развернуть wSheet как рабочий лист Для каждого wSheet на рабочих листах MsgBox «Sheet Found:» & wSheet.Name Next wSheet
Оператор прерывания цикла «Exit For»
Оператор Exit For используется для разрыва цикла. Как только этот оператор встречается в коде, программа завершает выполнение цикла и приступает к выполнению операторов, которые находятся в коде, сразу после этого цикла. Это можно использовать, например, для поиска определенного значения в массиве. Для этого с помощью цикла сканируется каждый элемент массива. Как только требуемый элемент найден, остальные исследовать не нужно — цикл прерывается.
В следующем примере показано использование оператора Exit For. Здесь цикл перебирает более 100 записей массива и сравнивает каждую со значением переменной dVal. Если совпадение найдено, цикл завершается:
Для i = от 1 до 100 Если dValues (i) = dVal Then IndexVal = i Выход для End If Next i
Цикл «Do While» в Visual Basic
Цикл Do While Loop выполняет блок кода до тех пор, пока не будет выполнено указанное условие. Ниже приведен пример процедуры Sub, в которой числа Фибоначчи не более 1000 отображаются последовательно с использованием цикла Do While:
Подпроцедура выводит числа Фибоначчи, не превышающие 1000. Счетчик Sub Fibonacci () Dim i As Integer для указания позиции элемента в последовательности. Dim iFib As Integer »сохраняет текущее значение последовательности. Dim iFib_Next As Integer» сохраняет следующее значение последовательность Dim iStep As Integer ‘сохраняет размер следующего приращения’ инициализирует переменные i и iFib_Next i = 1 iFib_Next = 0 ‘цикл Do While будет выполняться до тех пор, пока значение’ текущее число Фибоначчи не превысит 1000 Do While iFib_Next В данном примере условие iFib_Next
Другой способ реализовать цикл Do While — поместить условие в конец цикла, а не в начало. В этом случае цикл будет выполняться хотя бы один раз, независимо от того, выполняется ли условие.
Схематично такой цикл Do While с выбранным в конце условием будет выглядеть так:
Выполнить… Цикл, пока iFib_Next Цикл «Do Until» в Visual Basic
Цикл Do until очень похож на цикл Do While: блок кода в теле цикла выполняется снова и снова, пока не будет выполнено указанное условие (результат условного выражения — True). Следующая процедура Sub использует цикл Do Until для извлечения значений из всех ячеек в столбце A рабочего листа до тех пор, пока в столбце не будет найдена пустая ячейка:
iRow = 1 Do until IsEmpty (Cells (iRow, 1)) ‘Текущее значение ячейки сохраняется в массиве dCellValues (iRow) = Cells (iRow, 1). Значение iRow = iRow + 1 Loop
В приведенном выше примере условие IsEmpty (Cells (iRow, 1)) находится в начале конструкции Do until, поэтому цикл будет выполняться хотя бы один раз, если первая взятая ячейка не пуста.