- •2.1. Алфавит и словарь языка
- •2.2. Переменные
- •2.3. Константы
- •2.4. Строковые переменные
- •2.5. Массивы
- •2.6. Пользовательские типы данных
- •2.7. Операции VBA
- •2.8. Объектная модель Excel
- •2.8.1. Свойства и методы объектов
- •2.8.2. Отображение объектной модели Excel
- •2.8.2.1. Иерархия объектной модели
- •2.8.2.2. Ссылка на объекты в коде VBA
- •2.8.3. Работа с объектами
- •2.8.3.1. Задание свойств объекта
- •2.8.3.2. Использование методов объекта
- •2.8.3.3. Переменные-объекты
- •2.8.3.4. Коллекции
- •2.8.3.5. Метод Add
- •2.8.3.6. Свойство Count
- •2.8.4. Свойства и методы основных объектов Excel
- •2.8.4.1. Объект Application
- •2.8.4.2. Объект Worksheet
- •2.8.4.3. Объект Range
- •2.8.5. Просмотр объектов
- •2.8.6. Использование справочной системы
- •2.8.6.1. Использование функциональной клавиши F1
- •2.8.6.2. Использование помощника
- •2.9. Ввод и вывод данных
- •2.9.3. Метод InputBox
- •2.9.4. Объединение текстовых строк
- •2.10. Концепция событий Excel
- •2.10.1. Типы событий Excel
- •2.10.2. События объекта Workbook
- •2.10.3. События объекта Worksheet
- •2.10.4. События объекта Application
- •2.10.5. События объекта UserForm
- •2.10.6. События, не связанные с конкретными объектами
- •2.10.6.1. Метод OnKey
- •2.10.6.2. Событие OnTime
- •2.11. Формы пользователя
- •2.11.1. Свойства, методы и события экранных форм
- •2.11.1.1. Некоторые свойства форм
- •2.11.1.2. Некоторые методы форм
- •2.11.1.3. Некоторые события форм
- •2.11.2. Элементы управления
- •2.11.2.1. Элемент управления Label (Надпись)
- •2.11.2.1.1. Окно свойств формы
- •2.11.2.2. Элемент управления CommandButton (Кнопка)
- •2.11.2.3. Элемент управления TextBox (Текстовое поле)
- •2.11.2.4. Элемент управления ComboBox (комбинированный список)
- •2.11.2.5. Элемент управления ListBox (список)
- •2.11.2.6. Элемент управления CheckBox (Флажок)
- •2.11.2.7. Элемент управления ToggleButton (Выключатель)
- •2.11.2.8. Элемент управления OptionButton (Переключатель)
- •2.11.2.9. Элемент управления Image (Рисунок)
- •2.11.2.12. Элемент управления RefEdit (Поле со свёртыванием)
- •2.11.2.13. Элементы управления на рабочем листе
- •2.12. Инструкции VBA
- •2.12.1. Оператор присваивания
- •2.12.2. Инструкция Set
- •2.12.3. Циклы
- •2.12.3.1. Инструкция For… Next
- •2.12.3.2. Инструкция While…Wend
- •2.12.3.3. Инструкция Do... Loop
- •2.12.3.4. Инструкция For Each…Next
- •2.12.4. Инструкции перехода
- •2.12.4.1. Инструкция условного перехода If…Then…Else
- •2.12.4.2. Инструкция Select…Case
- •2.12.4.3. Инструкция безусловного перехода GoTo
- •2.12.4.4. Инструкции перехода к обработчику ошибок On Error
- •2.12.4.5. Инструкции прерывания выполнения блока Exit
- •2.13. Функции VBA
- •2.13.1. Встроенные функции
- •2.13.1.1. Математические функции
- •2.13.1.2. Функции преобразования данных
- •2.13.1.3. Функции даты и времени
- •2.13.1.4. Строковые функции
- •2.13.1.5. Примеры использования функций VBA
- •2.13.1.5.1. Удаление ненужных символов
- •2.13.1.5.2. Определение длины строки
- •2.13.1.5.3. Сравнение и поиск строк
- •2.13.1.5.4. Выделение части строки
- •2.13.1.5.5. Форматирование значений данных
- •2.13.2. Функции, определённые пользователем
- •2.14. Файлы VBA
- •2.14.1. Типы файлов в VBA
- •2.14.2. Открытие и закрытие файла
- •2.14.3. Ввод данных в файл последовательного доступа
- •2.14.4. Вывод данных из файла последовательного доступа
- •2.14.5. Работа с файлом произвольного доступа
- •2.15. Создание процедуры
- •2.16. Выполнение процедуры
- •2.17. Сохранение процедуры
- •2.18. Запись последовательности действий пользователя
- •2.19. Просмотр кода макроса
- •3.1. Тестирование и отладка как этап разработки приложений
- •3.1.1. Отладка
- •3.1.1.1. Режим останова
- •3.1.1.2. Использование окна Immediate
- •3.1.1.2.1. Просмотр значений в окне Immediate
- •3.1.1.3. Пошаговое выполнение программ
- •3.1.2. Исправление ошибок
- •Библиографический список
- •Оглавление
78
2.12.3. Циклы
Циклы предназначены для многократного выполнения группы инструкций. ВVBA для организации циклов используются инструкции For...
Next, White…Wend, Do …Loop и For Each…Next.
2.12.3.1. Инструкция For… Next
Эта инструкция повторяет выполнение группы инструкций указанное число раз.
Формат инструкции For… Next
For Счетчик = Начало То Конец [Step Шаг] [Инструкции1]
[Exit For] [Инструкции2]
Next [Счётчик]
Формат инструкции For… Next содержит следующие элементы:
Элемент инструкции |
Описание элемента |
Счетчик |
Обязательный. Числовая переменная, используемая в каче- |
|
стве счётчика цикла |
Начало |
Обязательный. Начальное значение для Счетчик |
Конец |
Обязательный. Конечное значение для Счетчик |
Шаг |
Необязательный. Шаг изменения значения Счетчик после |
|
каждого цикла. По умолчанию используется значение 1 |
Инструкции |
Необязательный. Один или более операторов, выполняемых |
|
определённое число раз |
Пример1
В приведённой ниже процедуре TestFor подсчитывается сумма нечётных чисел в заданном диапазоне, причём числа формируются в цикле с нарастанием на 1.
Option Explicit_________________________________________________
Sub TestFor()
'Подсчёт суммы нечётных чисел в заданном диапазоне
Dim i As Integer
Dim n As Integer
Dim Sum As Integer
Dim r As Integer
n = InputBox("Ведите количество чисел", "Количество нечётных чисел") r = 0
For i = 1 To n Step 2 '(сумма нечётных чисел от 1 до n) Sum = Sum + i
r = r + 1
79
Next i
MsgBox Prompt:="Сумма " & r & " нечётных чисел в диапазоне от 1 до " & n & _" равна " & Sum, Buttons:=vbExclamation, _
Title:="Количество нечётных чисел" End Sub
Ниже приведены диалоговые окна, появляющиеся при выполнении мак-
роса TestFor.
Пример2
В приведённой ниже процедуре TestFor1 подсчитывается сумма нечётных чисел в диапазоне A1:J10 таблицы Excel.
В этой процедуре используются два цикла: внешний цикл с параметром цикла i и вложенный цикл с параметром цикла j.
Option Explicit_________________________________________________
Sub TestFor1()
'Подсчёт суммы нечётных чисел в заданном диапазоне
Dim i As Integer
Dim j As Integer
Dim m As Integer
Dim n As Integer
Dim Sum As Integer
Dim r As Integer
n = InputBox("Ведите количество чисел в столбце", _ "Количество нечётных чисел")
m = InputBox("Ведите количество чисел в строке", _ "Количество нечётных чисел")
r = 0
For i = 1 To n Step 2 '(сумма нечётных чисел от 1 до n)
80
For j = 1 To m Step 1 Sum = Sum + Cells(i, j) r = r + 1
Next j Next i
MsgBox Prompt:="Сумма " & r & " нечётных чисел в диапазоне" _
&Chr(13) + Chr(10) _
&" от 1 до " & n * m & " равна " & Sum, Buttons:=vbExclamation End Sub
Впроцедуре TestFor2 реализован алгоритм ввода данных с клавиатуры в ячейки рабочего листа; заполнение производится последовательно столбец за столбцом двумя циклами.
Sub TestFor2() Dim i As Integer Dim j As Integer Dim m As Integer Dim n As Integer
m = InputBox("Ведите количество чисел в столбце", _ "Количество нечётных чисел")
n = InputBox("Ведите количество чисел в строке", _ "Количество нечётных чисел")
For i = 1 To n Step 1 '(сумма нечётных чисел от 1 до n) For j = 1 To m Step 1
Cells(j, i) = InputBox("Ведите значение в ячейку " & Chr(64 + i) & j, _ "Ввод данных в таблицу")
Next j Next i End Sub
Впроцедуре TestFor2 функция Chr(64 + i) возвращает (выдаёт значение) строку, содержащую символ, код которого указан в качестве аргумента. Код 65 соответствует латинской букве A, 66 – букве B и т.д. Ниже показано диалоговое окно, соответствующее функции InputBox во вложенном цикле цикле процедуры TestFor2.
2.12.3.2. Инструкция While…Wend
Инструкция While…Wend выполняет некоторую последовательность инструкцийдотехпор,пока заданноеусловиеимеетзначениеTrue(Истина).
Формат инструкции While…Wend
81
While Условие [Инструкции]
Wend
Формат инструкции While…Wendсодержит следующие элементы:
Элемент инструкции |
Описание элемента |
Условие |
Обязательный. Числовое или строковое выражение, резуль- |
|
татом вычисления которого являются значения True или |
|
False |
Инструкции |
Необязательный. Одна или более инструкций, выполняе- |
|
мых, если условие равно значению True |
Если Условие имеет значение True, то выполняется весь набор инструкций, расположенных до инструкции Wend. После этого управление возвращается инструкции While и опять проверяется условие. Если Условие имеет значение True, то снова выполняются все инструкции до Wend, В противном случае выполнение программы передается на инструкцию, следующую за инструкцией Wend.
Циклы While...Wend могут иметь любую глубину вложенности. При этом каждая инструкция Wend соответствует предшествующей инструк-
ции While.
Пример
Подсчёт суммы четных чисел от 0 до 10.
Sub TWW()
Dim k As Integer
Dim i As Integer
While k < 10
k = k + i
i = i + 2
Wend
MsgBox "i=" & i & " k=" & k
End Sub
Ниже приведён результат выполнения процедуры TWW.
2.12.3.3. Инструкция Do... Loop
Для выполнения инструкциb For необходимо задать параметры. Эти параметры могут вычисляться или задаваться во время выполнения программы, как в последней процедуре, которые точно определяют, сколько раз должен выполняться инструкция цикла. Иногда это свойство
82
инструкции For является ограничительным, и в этом случае VBA предлагает другую инструкцию – Do... Loop, которая также может организовать повторяющиеся вычисления, но момент прекращения вычислений здесь определяется с помощью логических условий.
Существует два типа инструкции Do: Do While и Do Until. Первый из них повторяет выполнение блока инструкции до тех пор, пока заданное логическое условие истинно. Второй инструкция, Do Until, выполняет циклические вычисления до тех пор, пока изначально ложное логическое условие не станет истинным. Формат этих инструкций следующий:
Формат1
Do [{While | Until} условие] [инструкции]
[Exit Do] [инструкции]
Loop
Формат2
Do
[инструкции] [Exit Do] [инструкции]
Loop [{While | Until} условие]
Формат инструкции Do …Loop содержит следующие элементы:
Элемент инструкции |
Описание элемента |
Условие |
Необязательный. Численное или строковое выражение, |
|
имеющее значение True или False |
Инструкции |
Одна или более инструкций, которые повторяются, если ус- |
|
ловие равно значению True, или до тех пор, пока оно не |
|
станет равным True |
В теле инструкции (цикла) может быть любое количество инструкций Exit Do, которые являются дополнительными средствами для окончания цикла (выхода из инструкции Do … Loop). Инструкция Exit Do часто используется внутри Do … Loop с применением инструкции If…Then , который проверяет некоторое дополнительное условие для выхода из цикла.
Различие между этими двумя форматами состоит в следующем. В соответствии с форматом 1 логическое условие помещается в начало цикла. В этом случае, если логическое условие не выполняется, то инструкции цикла не выполняются ни разу. Во втором формате логическое условие помещено в конце цикла. Поэтому инструкции цикла в любом случае будут выполнены хотя бы один раз. Инструкция Exit Do используется для экстренного выхода из цикла; обычно для этого применяются условные инструкции If и Select Case.
Пример
В процедуре DWU пользователю предлагается ввести свое имя или выйти из программы. Здесь значение vbYesNo функции MsgBox присваивается переменной iResponse, которое анализируется инструкцией
83
If…Then…Else, и в случае равенства его внутренней константе vbYes (нажата кнопка Ok в диалоговом окне функции MsgBox) и переменная sName имеет пустое значение, выдаётся сообщение "Вы не ввели имя", и происходит выход из процедуры по инструкции Exit Sub. (Инструкция If…Then…Else будет рассмотрена позже). Если же имя введено (даже пробел!), то произойдёт выход из цикла по условию sName <> "", и выводится диалоговое окно с введённым именем.
Sub DWU()
Dim sName As String Dim iResponse As Integer sName = ""
Do While sName = ""
sName = InputBox("Введите свое имя: ") If sName = "" Then
iResponse = MsgBox("Вы хотите выйти из программы?", vbYesNo) If iResponse = vbYes Then
MsgBox "Вы не ввели имя " Exit Sub
End If End If Loop
MsgBox "Вы ввели имя " & sName End Sub
При запуске процедуры DWU выводится окно,
и если имя введено, то выводится окно с сообщением введённого имени.
Если же имя не введено и нажата кнопка OK или Cancel, то выводится диалоговое окно