2 семестр / Программирование на VBA

Порядок выполнения: переменной Counter присваивается значение Start и проверяется условие: Counter£Start для положительного шага StepSize (Counter³Start для отрицательного шага StepSize); если условие неверно, то тело цикла Statements не выполняется и управление передается на оператор, следующий за Next. Если же условие выполняется, то выполняется тело цикла Statements, затем значение Counter изменяется на значение StepSize (увеличится в случае положительного значения StepSize, и уменьшается при отрицательном значении StepSize). Данный процесс будет выполняться пока значение Counter не достигнет значения End (если шаг положителен, цикл завершится, когда впервые выполнится условие Counter>Start; если шаг цикла отрицателен, условие его завершения – Counter<Start). Досрочно завершить цикл For…Next можно и с помощью оператора Exit For. Такие операторы могут быть расположены в тех местах тела цикла, где требуется из него выйти, не дожидаясь выполнения условия завершения.

Задание 1. Напишите процедуру, суммирующую все числа из диапазона, содержащую цикл For…Next.

Для этого:

ü введите процедуру (листинг 14):

Листинг 14 – Демонстрация цикла For…Next с возрастающим счетчиком

1Sub List5_14 ()

2Dim i As Integer

3Dim a As String

4Dim b As String

5Dim S As Long

7a = InputBox("Введите первое целое число:")

8b = InputBox ("Введите другое целое число:")

9S = 0

10For i = CInt(a) To CInt(b)

11S = S + i

12Next i

13MsgBox "Сумма чисел от " & a & _

14" до " & b & " равна " & S

15End Sub

Переменная i используется как счетчик цикла For…Next (буквы i, j, k, l, m, n чаще всего используются программистами в качестве счетчиков цикла); другие переменные используются для сохранения начального (a) и конечного значений (b), полученных от пользователя, и для сохранения суммы чисел (S). Обратите внимание на типы этих переменных. Переменные a и b в этой процедуре чаще всего используются для работы со строками (строки 7, 8, 13, 14) и только в одном операторе (строка 11) преобразованы в числа. Переменная S, по существу, должна иметь тип числовой, причем как можно больший, потому что неизвестно, насколько большой диапазон будет вводиться пользователем, который будет

81

испытывать этот код. В процедуре предполагается, что первое вводимое число меньше, чем второе. Иначе тело цикла не выполнится ни разу и значение переменной S будет равно 0, так как она инициализируется этим значением в строке 10. Со строки 10 начинается описание цикла For…Next. При обработке этой строки VBA сначала выполняет вызов функции CInt, которая преобразует пользовательский строковый ввод в числа типа Integer, а затем вставляет результирующие целые значения в оператор. VBA присваивает целый эквивалент значения, хранимого в a, переменной счетчика i. Затем значение в i сравнивается с целым эквивалентом числа, сохраненного в b. Если i меньше или равно значению в b, VBA выполняет тело цикла. Этот цикл имеет только один оператор в своем теле: выражение сложения и присваивания (в строке 11). В строке складывается текущее значение i с текущим содержимым переменной S, а затем результат сложения присваивается переменной S. Далее VBA переходит к оператору Next (в строке 12), который указывает на конец тела цикла For; VBA увеличивает переменную счетчика на 1 (по умолчанию) и возвращается к началу цикла (строка 10) и снова сравнивает значение в переменной счетчика i с числом, хранимым в переменной b. Как только значение в счетчике цикла превысит значение, определенное для конечного значения счетчика (а это зависит от вводимых данных), VBA прекращает выполнение цикла. Теперь, когда цикл закончился, VBA продолжает выполнение кода с оператора MsgBox в строке 13, который отображает два введенных числа и сумму всех целых в этом диапазоне.

Упражнение 1

Напишите процедуру, содержащую цикл For…Next с использованием шага изменения счетчика цикла.

Использование циклов, тестирующих условия до выполнения тела цикла

Для VBA-тестирования условия детерминанта цикла до выполнения тела

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

Первой конструкцией цикла, тестирующей свое условие детерминанта до выполнения цикла, является Do While со следующим синтаксисом:

Do While Condition

Statements

[Exit Do]

Loop

Condition – логическое выражение для детерминанта цикла; Statements – один, ни одного или несколько операторов, которые составляют тело цикла. Ключевое слово Loop после Statements указывает на окончание тела цикла и обозначает место, из которого VBA возвращается в начало цикла для проверки условия детерминанта (оператор Exit Do досрочно завершает цикл Do While).

Изображение оператора Do While на блок-схеме:

82

Counter

Statements

В операторе Do While выражение Condition находится в начале цикла, поэтому VBA проверяет условие детерминанта до выполнения цикла. Поскольку в этой форме используется ключевое слово While, VBA выполняет цикл, пока логическое выражение, представленное с помощью Condition, равно True.

При выполнении цикла Do While VBA сначала тестирует логическое выражение, представленное с помощью Condition; если оно равно True, VBA выполняет операторы, представленные с помощью Statements. При достижении ключевого слова Loop VBA возвращается в начало цикла и снова проверяет, равно ли True логическое выражение Condition. Если Condition равно True, VBA выполняет цикл снова; если выражение Condition равно False, VBA прерывает

цикл и продолжает выполнение кода с любых операторов после ключевого слова

Loop.

Заметьте, что если логическое выражение, представленное с помощью Condition, равно False, когда VBA выполняет оператор Do While в первый раз, VBA просто пропускает цикл, не выполняя его ни одного раза. Т.е. цикл Do While позволяет ни разу не выполнять операторы внутри него.

Задание 2. Напишите процедуру, суммирующую нечетные числа до тех пор, пока их сумма не превысит заданное число.

Для этого:

ü введите процедуру (листинг 15): Листинг 15 – Демонстрация цикла Do While

1Sub List5_15 ()

2Const ocTitle = "Сумматор нечетных чисел"

3

Dim S As Integer

'сумма нечетных чисел

4Dim S_Str As String 'строка с нечетными числами

5Dim a As Variant 'для приема данных пользователя

5Dim b As Variant 'предел суммы нечетных чисел

7b = InputBox("Введите максимальную сумму:", ocTitle)

8If Len(b) = 0 Then MsgBox "Вы нажали кнопку Cancel" : Exit Sub

9S_Str = "" 'инициализация выходной строки

10S = 0 'инициализация суммы S

11Do While S < b 'начало цикла

12a = InputBox("Введите число:", ocTitle)

13if Len(a)=0 Then MsgBox "Вы нажали кнопку Cancel" : Exit Do

14If (a Mod 2) <> 0 Then ' проверка на четность

15	S = S + a ' изменение суммы S
	83

16	S_Str = S_Str & a & " "

17End If

18Loop

19MsgBox Prompt:="Вы ввели следующие нечетные числа: " & _

20Chr(13) & S_Str & Chr(13) & "Их сумма равна " & S, Title:=ocTitle

21End Sub

Процедура Sub List5_15 () демонстрирует использование цикла Do While. Цикл в процедуре выполняется, пока сумма введенных пользователем нечетных чисел (нечетное (odd) число – это число, которое нельзя разделить на 2 без остатка) меньше b. B окне ввода можно использовать все (две) кнопки, так как операторы в строках 8 и 13 проверяют, не была ли нажата кнопка Cancel, и в первом случае (строка 8) прекращают выполнение процедуры, а во втором (строка

13) – цикла Do While.

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

Можно также использовать форму Do Until оператора Do для создания цикла, тестирующего условие детерминанта до выполнения тела цикла. Оператор Do Until имеет следующий синтаксис:

Do Until Condition

Statements

[Exit Do]

Loop

Condition – логическое выражение для детерминанта цикла, a Statements – это операторы VBA, составляющие тело цикла. Ключевое слово Loop после Statements указывает на конец тела цикла и также обозначает место, из которого VBA возвращается в начало цикла для проверки условия детерминанта (см. блок- схему оператора Do While). VBA тестирует условие детерминанта цикла Do Until до выполнения операторов цикла. Поскольку эта форма включает ключевое слово Until, VBA выполняет цикл пока логическое выражение, представленное выражением Condition, равно значению False. При выполнении цикла Do Until VBA сначала тестирует логическое выражение, представленное с помощью Condition; если оно равно False, VBA выполняет операторы, представленные с помощью Statements. После достижения ключевого слова Loop VBA возвращается в начало цикла и снова проверяет, равно ли логическое выражение Condition значению False. Если Condition равно False, VBA выполняет цикл снова; если же оно равно True, VBA прерывает цикл и продолжает выполнение кода с операторов после ключевого слова Loop.

Если Condition равно True, когда VBA встречает оператор Do Until в первый раз, VBA пропускает цикл без его выполнения. Т.е. цикл Do Until позволяет ни разу не выполнять операторы внутри него.

84

Упражнение 2

Напишите процедуру с использованием цикла Do Until, суммирующую четные числа до тех пор, пока их сумма не превысит заданное число.

Использование циклов, тестирующих условия после выполнения тела цикла

Для VBA-тестирования условий после выполнения тела цикла необходимо

поместить логическое выражение для детерминанта цикла в конец блока операторов, составляющих тело цикла, после ключевого слова Loop, которое сообщает о конце цикла. Первая рассматриваемая конструкция цикла, тестирующая условие детерминанта после выполнения тела цикла, – это Do…Loop While. Оператор Do…Loop While имеет следующий синтаксис:

Do

Statements

[Exit Do]

Loop While Condition

Statements – один, ни одного или несколько операторов VBA, составляющих тело цикла. Ключевое слово Loop после Statements обозначает конец тела цикла и также указывает место, из которого VBA возвращается в начало цикла; Condition представляет логическое выражение для детерминанта цикла (см. диаграмму ниже). В этой форме синтаксиса оператора Do…Loop While VBA проверяет условие детерминанта после выполнения операторов цикла. Поскольку эта форма оператора Do использует ключевое слово While, VBA выполняет цикл, пока логическое выражение, представленное с помощью Condition, равно True (оператор Exit Do досрочно завершает цикл Do While). При выполнении оператора Do…Loop While VBA сначала выполняет операторы, представленные с помощью Statements. Когда VBA достигает ключевых слов Loop While, выполняется тестирование логического выражения, представленного с помощью Condition; если это выражение равно True, VBA возвращается в начало цикла и снова выполняет тело цикла. Когда VBA снова достигает ключевых слов Loop While в конце цикла, снова выполняется проверка, является ли логическое выражение Condition все еще равным True. Если Condition равно True, VBA выполняет цикл снова; если – нет, VBA продолжает выполнение кода с любых операторов после строки, содержащей ключевое слово Loop, т.е. прекращает работу цикла.

Изображение оператора Do…Loop While на блок-схеме:

Statements

Counter

Заметьте, что независимо от значения логического выражения,

85

представленного с помощью Condition, этот цикл всегда выполняется, по крайней мере, один раз.

Задание 3. Напишите процедуру с использованием цикла Do…Loop While, которая неоднократно получает число от пользователя, прекращаясь, когда пользователь вводит четное число больше 10.

Для этого:

ü введите процедуру (листинг 16):

Листинг 16 – Демонстрация цикла Do…Loop While

1Sub List5_16 ()

2Const evTitle = "Остановка при четном, большем десяти"

3Dim EvenFlag As Boolean

4Dim a As Variant 'для приема данных пользователя

5

6EvenFlag = True

7Do

8a = InputBox("Введите число:", evTitle)

9If Len(a) = 0 Then Exit Do

10If a Mod 2 = 0 Then

11	If a > 10 Then
12	MsgBox prompt:="Вы ввели четное число, большее, чем 10" & _
13	" - Цикл заканчивается", Title:=evTitle
14	EvenFlag = False
15	Else
16	MsgBox Prompt:="Вы ввели четное число, меньшее " _
17	& "(или равное), чем 10", Title:=evTitle
18	End If
19	Else
20	MsgBox Prompt:="Bы ввели нечетное число", Title:=evTitle

21End If

22Loop While EvenFlag

23MsgBox Prompt:="Выполнение цикла прекращено", Title:=evTitle

24End Sub

Процедура List5_16 () показывает, как построить цикл Do…Loop While. Цикл в

этой процедуре демонстрирует управляемый условием цикл и выполняется до тех пор, пока пользователь не введет четное число, большее 10. На самом деле этим условием является получение значения False переменной EvenFlag, что происходит (в строке 14), когда код обнаруживает с помощью вложенных операторов If (в строках 10–11) факт введения пользователем четного числа, большего 10. Перед выполнением цикла переменной EvenFlag присваивается значение True. Внутри цикла организуется прием числа, и анализ его на предмет четности и превышения 10. Если эти последние проверки дают положительный результат, выводится сообщение (строки 12–13) об окончании цикла и переменная EvenFlag получает значение False, что приводит к окончанию цикла.

86

В строке 9 находится оператор, предупреждающий возникновение runtime- ошибок при работе с программой пользователей, склонных к экспериментам.

Для построения Do-цикла, тестирующего свое условие детерминанта после

выполнения тела цикла можно также использовать форму Do…Loop Until оператора Do. Оператор Do…Loop Until имеет следующий синтаксис:

Do

Statements

[Exit Do]

Loop Until Condition

Statements представляет операторы VBA, составляющие тело цикла; Condition – логическое выражение для детерминанта цикла (см. блок-схему оператора Do…Loop Until). VBA проверяет условие детерминанта цикла Do…Loop Until после выполнения тела цикла. Поскольку эта форма Do-оператора использует ключевое слово Until, VBA выполняет цикл, пока логическое выражение, представленное с помощью Condition, равно False. При выполнении оператора Do...Loop Until VBA сначала выполняет операторы, представленные с помощью Statements. Когда VBA достигает ключевого слова Loop, выполняется тестирование логического выражения, представленного с помощью Condition; если логическое выражение равно False, VBA возвращается в начало цикла и снова выполняет тело цикла. Когда VBA снова достигает ключевого слова Loop в конце цикла, снова выполняется проверка, является ли логическое выражение Condition равным False. Если Condition равно False, VBA выполняет цикл снова; если оно равно True, VBA продолжает выполнение кода с любых операторов после строки, содержащей ключевое слово Loop, т.е. прекращает работу цикла.

Заметьте, что этот цикл всегда выполняется, по крайней мере, один раз, независимо от значения логического выражения, представленного с помощью

Condition.

Упражнение 3

Напишите процедуру с использованием цикла Do…Loop Until, которая неоднократно получает число от пользователя, прекращаясь, когда пользователь вводит нечетное число меньшее 10.

Вложенные циклы

Можно помещать циклы внутрь других циклов, аналогично тому, как можно помещать операторы If…Then один в другой. Помещение одной структуры цикла в другую называют вложением (nesting) циклов. Можно помещать структуры циклов любого типа (смешанные For и Do циклы) в любой уровень.

При вложении циклов необходимо соблюдать следующие правила:

üпри вложении циклов For…Next каждый цикл должен иметь свою уникальную переменную счетчика;

üесли вы используете оператор Exit For или Exit Do во вложенном цикле, этим оператором заканчивается только выполняемый в данный момент цикл; VBA продолжает выполнение следующего цикла более высокого уровня.

87

Контрольные вопросы

1.Что такое итерация цикла и тело цикла?

2.Что такое цикл с фиксированным числом итераций?

3.Можно ли изменить количество итераций цикла For…Next?

4.Что такое неопределенный цикл?

5.Какая разница между фиксированными и неопределенными циклами?

6.Как в цикле Do… задать продолжение выполнения, пока условие детерминанта истинно, а как – пока условие детерминанта ложно?

7.Как в цикле Do…задать проверку условия детерминанта до выполнения тела цикла, а как – после выполнения?

Часть 6. МАССИВЫ. ОБЪЕКТЫ VBA ДЛЯ ВВОДА-ВЫВОДА

В этой части описываются массивы. Массив (array) – это коллекция переменных, которые имеют общие имя и базовый тип. Массив является удобным

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

Массив позволяет сохранять и манипулировать многими элементами данных посредством единственной переменной. Кроме уменьшения общего числа различных имен переменных, которые необходимо отслеживать, другим основным преимуществом использования массивов является то, что можно использовать циклы для легкой обработки различных элементов массивов. Объединяя массивы и структуры цикла (обычно For…Next), можно написать небольшое число операторов, которые обрабатывают большой объем данных.

Выполнение тех же задач с использованием отдельных переменных может потребовать написания сотен операторов.

Лабораторная работа № 6. Массивы. Свойства и методы объектов Range

и Selection

Цель занятия: Знать одномерные и многомерные массивы. Уметь использовать массивы. Знать свойства и уметь использовать методы объектов Range и Selection

Материалы к занятию: MS Excel 2003.

Размерность массива

В языках программирования обычно используются одномерные и многомерные массивы, одни из которых описывают относительно простые, а другие – более сложные объекты.

Наименее сложный массив – это просто список элементов данных; такого рода массив называется простым (simple) или одномерным (single-dimensional)

массивом. Каждый элемент данных, хранимых в массиве, называется элементом (element) массива. Элементы в массиве нумеруются от 0. Такая система

нумерации довольно распространена в программировании и называется нумерацией с нулевой базой (zero-based).

Для доступа к данным, хранящимся в определенном элементе массива,

88

следует указывать имя массива с последующим числом, называемым индексом (subscript или index) элемента. Индекс всегда заключается в круглые скобки. Можно использовать элемент массива в любом выражении VBA – точно так же, как используется значение константы или переменной в каком-либо выражении.

Одномерные массивы обычно используются для представления различных списков данных. Однако часто бывает необходимо представить таблицы данных в программах с организацией данных в формате строк и столбцов, подобно ячейкам в рабочих листах Excel. Для этого необходимо использовать многомерные (multidimensional) массивы. Поскольку вы уже имеете опыт работы с ячейками Excel, то нет необходимости в подробном разъяснении сущности двумерного массива. Адрес каждой ячейки листа состоит из двух чисел (измерений), одно из которых (номер строки) является первым индексом, а второе (номер столбца) – вторым индексом массива. VBA позволяет создавать массивы, имеющие до 60 измерений. Чаще всего в программировании используются одно – и двумерные массивы.

Число элементов в массиве обычно задается во время объявления массива. Объявление массива указывает VBA величину различных измерений массива (диапазон изменения каждого индекса). После того как массив объявлен, VBA выделяет достаточный объем памяти для всех элементов массива. Переменные типа массив подчиняются тем же правилам области действия, что и любые другие переменные.

VBA сохраняет зарезервированной область памяти для всех элементов в массиве, пока существует переменная типа массив. Подобные массивы называются статическими (static), потому что число элементов в массиве не меняется.

Выбор размера массива может быть затруднен, если неизвестно, сколько данных будет введено в массив, или если объем данных, собираемых для массива, значительно меняется. Для подобных ситуаций VBA поддерживает особый тип массивов, называемый динамическим (dynamic) массивом. Динамические массивы получили свое название, потому что можно изменять число элементов в массиве при выполнении VBA-программы. Динамический массив (в сочетании с правильным программированием) может увеличиваться или сжиматься (уменьшаться в размере), чтобы вмещать точно необходимое число элементов без напрасного расходования памяти. Для изменения размера динамического массива используйте оператор ReDim, описываемый далее в этой части.

Оператор Option Base

Обычно в VBA используются массивы с нулевой базой. В системе

нумерации с нулевой базой индекс для первого элемента в любом измерении массива является равным 0; массив с 10 элементами имеет индексы от 0 до 9.

VBA позволяет задавать начальное число для элементов массива. Можно

задавать нижнее число для индексов массива при объявлении массива (описывается далее в этой части), или использовать директиву компилятора Option Base для указания того, должна ли нумерация индексов начинаться с 0 или с 1. Директива компилятора Option Base имеет следующий синтаксис:

Option Base 0 | 1

89

оператор Option Base позволяет задавать 0 или 1 как начальное число по умолчанию для индексов массива. Если оператор Option Base не используется, VBA начинает нумерацию индексов массива с 0 (по умолчанию). Необходимо

помещать оператор Option Base в область объявлений модуля перед объявлениями любых переменных, констант или процедур. Нельзя помещать оператор Option Base внутри процедуры. Можно иметь только один оператор Option Base в модуле. Оператор Option Base влияет на все массивы, объявляемые в модуле, независимо от того, являются ли они локальными в процедуре или объявляются на модульном уровне.

Объявление массивов

Вы уже знакомы с оператором Dim, используемым для объявления переменных. Этот же оператор используется и для объявления массивов. Объявление массива с помощью оператора Dim имеет следующий синтаксис:

Dim VarName([Subscripts]) [As Type]

VarName – любое имя для массива, удовлетворяющее VBA-правилам для имен идентификаторов; Subscripts – измерение (измерения) массива. Можно объявлять массивы, имеющие до 60 измерений. Для одномерного массива включается один Subscripts; для двумерного массива – два (отделенные друг от друга запятой) и так далее; каждый Subscripts добавляет новое измерение в массив.

Можно также объявлять статические и динамические массивы, используя ключевые слова Public, Private и Static – точно так же, как для любой другой переменной и с тем же влиянием на область действия (Public, Private – на уровне модуля, Static – на уровне процедуры).

Оператор Subscripts имеет следующий синтаксис:

[lower То] upper [,[lower To] upper]…

здесь lower определяет нижний диапазон допустимых индексов для массива; upper – верхний предел. Заметьте, что только верхний предел является обязательным; часть lower To оператора Subscripts является необязательной. При определении только предела upper VBA нумерует элементы массива в зависимости от установки Option Base. Если действует установка Option Base l, VBA нумерует элементы в массиве от 1 до upper; иначе – от 0 до upper.

Включение части lower To оператора Subscripts помогает сделать код более легким и понятным, а также выявить ошибки программирования. Это также позволяет определять иной начальный индекс для массива, чем 0 или 1.

Подобно обычным объявлениям переменных можно объявлять определенный тип данных для массива, включая в объявление оператор As type. При этом type представляет любой допустимый тип VBA. Можно также объявлять массивы, имеющие определенный пользователем тип (создание пользовательских типов в учебно-методическом пособии не рассматривается). Если опустить type, все элементы в массиве имеют тип Variant. VBA инициализирует элементы числовых массивов нулями и элементы строковых массивов пустыми строками.

Заметьте, что оператор Subscripts является необязательным. Для создания динамического массива не используйте оператор Subscripts (необходимо

90