Приложение 2. Элементы языка VBA

Здесь мы дадим краткое описание элементов языка программирования VBA (Visual Basic for Application), которое позволит понять те незначительные по объему программные коды, которые используются в данной книге, и выполнить рекомендуемые упражнения. Надеемся, что этих сведений окажется достаточно для новичков, чтобы начать самостоятельно осваивать программирование на этом языке.
Описание переменных
Переменная, как следует из названия, может принимать различные значения в процессе выполнения программы. Переменные характеризуются именем и типом данных. В соответствии с типом данных переменной отводится место в памяти определенного размера. При присвоении переменной некоторого значения (определенного типа) оно заносится в этот участок памяти. Чтобы ввести переменную в программу, ее нужно описать. Раздел описаний переменных процедуры обычно помещают после заголовка процедуры. В VBA допускается неявное описание переменных. Это означает, что все неописанные переменные считаются переменными типа Variant (смотрите справку в Access по типам переменных). Чтобы избежать возможных ошибок, рекомендуется явно описывать переменные. Это рекомендуется делать также и для повышения быстродействия приложения. Чтобы сделать явное описание переменных обязательным, установите флажок Require Variable Declaration (обязательное описание переменных) на вкладке Editor , выполнив команду Tools\Options в окне редактора VBA.
ПРИМЕЧАНИя
Для быстрого перехода в окно редактора VBA можно использовать комбинацию клавиш ALT+F11.
Установка свойства Require Variable Declaration приводит к тому, что в каждый новый модуль в раздел описаний будет добавлен оператор Option Explicit. Естественно, этот оператор можно написать и вручную.
Инструкция Dim предназначена для описания переменных на уровне модуля или процедуры. Например, следующая инструкция описывает переменную НоваяПеременная типа Integer (целое).

Dim НоваяПеременная As Integer

После имени переменной и перед типом используется служебное слово As. Если не указать тип переменной, например,

Dim НоваяПеременная

то такая переменная будет иметь тип Variant. Со всеми имеющимися типами данных можно ознакомиться по справке (в том числе и с инструкцией Dim). Кроме того, имеется возможность создавать собственные типы данных с помощью инструкции Type.
Несколько переменных можно описывать с помощью одной инструкции Dim, перечислив их через запятую. Также можно написать несколько таких инструкций. Это связано с тем, что в VBA принято записывать по одному оператору на строку.
Переменные, описанные с помощью ключевого слова Dim на уровне модуля, доступны для всех процедур в данном модуле. Переменные, описанные на уровне процедуры, доступны только в данной процедуре.
Поэтому, чтобы расширить область действия переменных, скажем, на все событийные процедуры формы, используется раздел описаний общей области формы. Описанные на этом уровне переменные могут использоваться во всех процедурах, связанных с данной формой (рис. 5.6). Чтобы сделать какие-либо переменные доступными для всех форм проекта, их можно описать в разделе описаний модуля, используя для этого служебное слово Public.

Оператор присваивания
Оператор присваивания задает новое значение переменной. Общий вид этого оператора есть

X = Y

где Х – имя переменной, а Y – выражение того же типа, что и тип переменной Х. Оператор присваивания работает следующим образом. Сначала вычисляется значение выражения в правой части оператора, затем это значение присваивается переменной Х, т.е. заносится в соответствующую ячейку памяти. Поэтому нет никакой ошибки, например, в такой конструкции

X = X + 1,

в результате выполнения которой значение переменной Х увеличится на единицу.
Оператор присваивания используется также для изменения значения свойств различных объектов. Например, строка

Заголовок.Caption = "Накладная на списание №"

изменяет текст надписи (свойство Caption) с именем Заголовок.

Условный и безусловный переход
Переходы используются в программе для разветвления алгоритма. Любая программа выполняется последовательно, т.е. оператор за оператором. В VBA принято записывать каждый оператор языка на отдельной строке. Поэтому можно сказать, что программа (процедура) выполняется построчно, от первой строки и до последней. Однако эту жесткую последовательность можно нарушить, передав управление на некоторую строку, минуя определенное количество строк. Передача управления бывает безусловной и условной. Безусловная передача управления происходит, когда выполняется оператор GoTo Метка. Метка имеет имя и ставится в том месте программы, куда передается управление. После метки следует поставить двоеточие. В качестве примера можно обратиться к процедуре Списание_Возврат, текст которой приведен в Приложении 1. Строка

On Error GoTo Err_

выполняет безусловный переход на метку с именем Err_ в том случае, если при выполнении программы возникнет ошибка.
Одним из операторов условной передачи управления является оператор IF…THEN…ELSE. Он имеет следующий синтаксис:

IF Условие THEN
группа операторов 1
ELSE
группа операторов 2
END IF

Оператор работает следующим образом. Если Условие выполняется (т.е. истинно), то управление передается группе операторов 1, после чего выполняется оператор, идущий за END IF, то есть пропускается группа операторов 2. Если Условие нарушается (т.е. ложно), то пропускается группа операторов 1, а выполняется группа операторов 2 и далее. При этом допускается использование вложенных условных операторов, т.е. в группе операторов 1 и/или 2 могут находиться другие условные операторы.
Условие – это выражение, результат которого имеет логический тип данных (Boolean). Такой тип дают, например, операции сравнения:


> Больше
>= Больше или равно
= Равно
< Меньше
<= Меньше или равно
<> Не равно

Операции сравнения могут соединяться союзами AND (И) OR (ИЛИ) и иметь отрицание NOT (НЕ). Ниже приведено несколько примеров условных выражений:

Условие Значение
2>5 Ложь
Not (2>5) Истина
2<=5 AND 3<>4 Истина
2<=5 OR 3=4 Истина

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

Инструкция For...Next
Этот оператор цикла повторяет выполнение группы инструкций указанное число раз. Синтаксис оператора имеет вид

For счетчик = начало To конец [Step шаг]
[инструкции]
[Exit For]
[инструкции]
Next [счетчик]

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


Элемент Описание
Счетчик Числовая переменная, используемая в качестве счетчика цикла. Эта переменная не может принадлежать к типу Boolean (логический) или быть элементом массива.
Начало Начальное значение переменной счетчик.
Конец Конечное значение переменной счетчик.
Шаг Необязательный элемент. Значение, на которое изменяется счетчик при каждом выполнении тела цикла. Если это значение не задано, по умолчанию шаг принимается равным единице.
Инструкции Одна или несколько инструкций между For и Next, которые выполняются указанное число раз.

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

S = 0
For i = 1 To 10
S = S + i
Next

вычисляет сумму целых чисел от 1 до 10 и заносит результат в переменную S.

Инструкция Do...Loop
Инструкция Do...Loop используется для выполнения наборов инструкций неопределенное число раз. Набор инструкций повторяется, пока некоторое условие имеет значение True, либо, наоборот, пока оно не примет значение True.

А) Повторение инструкций, пока условие имеет значение True
Имеется два способа проверки условия в инструкции Do...Loop с помощью ключевого слова While: условие проверяется до входа в цикл (цикл с предусловием) или условие проверяется после хотя бы однократного выполнения цикла ( цикл с постусловием).
В следующей процедуре ChkFirstWhile условие проверяется до входа в цикл. Если myNum задать равным 9 вместо 20, инструкции внутри цикла выполняться не будут.

Sub ChkFirstWhile()
counter = 0
myNum = 20
Do While myNum > 10
myNum = myNum - 1
counter = counter + 1
Loop
MsgBox "Выполнено " & counter & " итераций цикла."
End Sub

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

Sub ChkLastWhile()
counter = 0
myNum = 9
Do
myNum = myNum - 1
counter = counter + 1
Loop While myNum > 10
MsgBox "В цикле выполнено " & counter & " итераций."
End Sub

В) Повторение инструкций, пока условие не примет значение True
Имеется два способа проверки условия в инструкции Do...Loop с помощью ключевого слова Until: условие проверяется до входа в цикл (как продемонстрировано в процедуре ChkFirstUntil), или условие проверяется после хотя бы однократного выполнения цикла (как показано в процедуре ChkLastUntil). Итерации продолжаются, пока условие имеет значение False.

Sub ChkFirstUntil()
counter = 0
myNum = 20
Do Until myNum = 10
myNum = myNum - 1
counter = counter + 1
Loop
MsgBox "В цикле выполнено " & counter & " итераций."
End Sub

Sub ChkLastUntil()
counter = 0
myNum = 1
Do
myNum = myNum + 1
counter = counter + 1
Loop Until myNum = 10
MsgBox "В цикле выполнено " & counter & " итераций."
End Sub

Инструкцию Do...Loop также можно завершить до ее логического завершения с помощью инструкции Exit Do. Например, для завершения бесконечного цикла используется инструкция Exit Do в инструкции If...Then...Else. В следующем примере переменной myNum присваивается значение, приводящее к бесконечному циклу. Инструкция If...Then...Else проверяет условие на значение myNum, а затем завершает инструкцию Do...Loop, предотвращая тем самым бесконечный цикл.

Sub ExitExample()
counter = 0
myNum = 9
Do Until myNum = 10
myNum = myNum - 1
counter = counter + 1
If myNum < 10 Then Exit Do
Loop
MsgBox "В цикле выполнено " & counter & " итераций."
End Sub

Такое множество способов организации цикла придает дополнительную гибкость программированию, когда при реализации того или иного алгоритма можно выбрать наиболее легкий для понимания кода вариант.
ПРИМЕЧАНИЕ
Все приведенные здесь примеры можно выполнить непосредственно в Access. Для этого проделайте следующее:
Перейдите на вкладку Модули в окне базы данных.(рис.2.1).
Нажмите кнопку Создать.
Введите в окне редактора текст процедуры и нажмите кнопку F5 (выполнить). Если у вас будет несколько процедур в окне, то перед нажатием кнопки F5 установите курсор в любом месте тела процедуры, которую требуется выполнить.
При выходе ответьте «Нет» на вопрос о сохранении модуля, в противном случае введите имя модуля.
Диалог с пользователем
Функция Inputbox
Функция Inputbox позволяет задать значения переменных с клавиатуры в процессе выполнения программы. Эта функция выводит на экран диалоговое окно, содержащее сообщение и поле ввода, устанавливает режим ожидания ввода текста пользователем и нажатия кнопки, а затем возвращает значение типа String (строка), содержащее текст, введенный в поле.
Команда имеет следующий синтаксис

InputBox(текст[, заголовок] [, ЗначениеПоУмолчанию] [, Хпозиция] [, Упозиция] [, ФайлСправки, раздел])

Здесь текст – единственный обязательный аргумент, представляющий собой строковое выражение, отображаемое как сообщение в диалоговом окне. Максимальная длина строки текст составляет приблизительно 1024 символов и зависит от ширины используемых символов. Строковое значение может содержать нескольких физических строк. Для разделения строк допускается использование символа возврата каретки (Chr(13)), символа перевода строки (Chr(10)) или комбинации этих символов (Chr(13) & Chr(10)).
Заголовок - строковое выражение, отображаемое в заголовке диалогового окна. Если этот аргумент опущен, в заголовок помещается имя приложения.
ЗначениеПоУмолчанию - строковое выражение, отображаемое в поле ввода как используемое по умолчанию, если пользователь не введет другую строку. Если этот аргумент опущен, поле ввода изображается пустым.
Хпозиция - числовое выражение, задающее расстояние по горизонтали между левой границей диалогового окна и левым краем экрана (в твипах; 1 твип = 1/567 сантиметра). Если этот аргумент опущен, диалоговое окно выравнивается по центру экрана по горизонтали.
Упозиция - числовое выражение, задающее расстояние по вертикали между верхней границей диалогового окна и верхним краем экрана (в твипах). Если этот аргумент опущен, диалоговое окно помещается по вертикали примерно на одну треть высоты экрана.
ФайлСправки - строковое выражение, определяющее имя файла справки, содержащего справочные сведения о данном диалоговом окне. Если этот аргумент указан, необходимо указать также аргумент раздел.
Раздел - числовое выражение, определяющее номер соответствующего раздела справочной системы. Если этот аргумент указан, необходимо указать также аргумент ФайлСправки.
Если указаны оба аргумента, ФайлСправки и раздел, пользователь имеет возможность нажатием клавиши F1 вызвать контекстную справку.
Если пользователь нажимает кнопку OK или клавишу ENTER, функция InputBox возвращает содержимое поля ввода. Если пользователь нажимает кнопку Отмена, функция возвращает пустую строку ("").
Функция MsgBox
Во время работы программы часто возникает необходимость проинформировать пользователя о необходимости выполнения некоторых действий или о произошедшей ошибке. Такие сообщения можно предусмотреть с помощью функции MsgBox, которая обеспечивает вывод на экран стандартного модального окна с сообщением. Кроме того, имеется возможность поместить в данном окне ряд кнопок (например, Да, Нет, Отмена) и проанализировать, какая из них была нажата. В соответствии с тем, какая кнопка была нажата, можно выполнить разные ветви программы, т.е. разветвление алгоритма осуществляется по желанию пользователя.
Итак, функция MsgBox выводит на экран диалоговое окно, содержащее сообщение, устанавливает режим ожидания нажатия кнопки пользователем, а затем возвращает значение типа Integer, указывающее, какая кнопка была нажата. Функция имеет следующий синтаксис

MsgBox(текст[, кнопки] [, заголовок] [,ФайлСправки, раздел])

Здесь аргументы текст, заголовок, ФайлСправки и раздел имеют тот же смысл, что и для функции InputBox, описанной выше.
Ниже перечислены допустимые значения аргумента кнопки:

Константа Значение Описание
VbOKOnly 0 Отображается только кнопка "OK".
VbOKCancel 1 Отображаются кнопки "OK" и "Отмена" (Cancel).
VbAbortRetryIgnore 2 Отображаются кнопки "Прервать" (Abort), "Повторить" (Retry) и "Пропустить" (Ignore).
VbYesNoCancel 3 Отображаются кнопки "Да" (Yes), "Нет" (No) и "Отмена" (Cancel).
VbYesNo 4 Отображаются кнопки "Да" (Yes) и "Нет" (No).
VbRetryCancel 5 Отображаются кнопки "Повторить" (Retry) и "Отмена" (Cancel).
VbCritical 16 Используется значок "Критическое сообщение".
VbQuestion 32 Используется значок "Предупреждающий запрос".
VbExclamation 48 Используется значок "Предупреждение".
VbInformation 64 Используется значок "Информационное сообщение".
VbDefaultButton1 0 При отображении окна фокус будет иметь первая кнопка, т.е. при нажатии клавиши ENTER будет отрабатывать именно эта кнопка.
VbDefaultButton2 256 При отображении окна фокус будет иметь вторая кнопка.
VbDefaultButton3 512 При отображении окна фокус будет иметь третья кнопка.
VbDefaultButton4 768 При отображении окна фокус будет иметь четвертая кнопка.
VbApplicationModal 0 Модальное окно на уровне приложения: чтобы продолжить работу с текущим приложением, необходимо ответить на данное сообщение.
VbSystemModal 4096 Модальное окно на уровне системы: все приложения будут недоступны до тех пор, пока пользователь не ответит на данное сообщение (не работает в 32-разрядных операционных системах типа Windows 95и выше).

Первая группа значений (0–5) указывает число и тип кнопок, отображаемых в окне диалога, вторая группа (16, 32, 48, 64) задает тип используемого значка, третья (0, 256, 512, 768) определяет кнопку, которая является основной, а четвертая (0, 4096) - модальность окна сообщения. При определении значения аргумента кнопки следует суммировать не более одного значения из каждой группы.

В следующем примере функция InputBox используется для ввода целого числа в качестве значения переменной процедуры. MsgBox используется для создания форматированного сообщения об ошибке. Обратите внимание на коды Chr(10), разделяющие строки сообщения.
Sub CustomMessage()
Dim strMsg As String, strInput As String

' Инициализирует строку.
strMsg = " Число вне диапазона. " & Chr(10) & "Введено число, меньшее 1 " & _
" или превышающее 10. " & Chr(10) & "Нажмите кнопку 'OK' для повторения " _
& " ввода числа."

' Приглашение пользователю ввести число.
strInput = InputBox("Введите число в диапазоне от 1 до 10.")
' Проверка, не нажата ли кнопка «Отмена».
If strInput <> "" Then

' Проверка введенного пользователем значения.
Do While strInput < 0 Or strInput > 10
If MsgBox(strMsg, vbOKCancel, "Ошибка!") = vbOK Then
strInput = InputBox("Введите число в диапазоне от 1 до 10.")
Else
Exit Sub
End If
Loop

' Выводит допустимое число, введенное пользователем.
MsgBox "Введено число " & strInput & "."
Else
Exit Sub
End If
End Sub

Объект Recordset
Объект Recordset представляет набор записей в основной таблице или набор записей, который получается в результате выполнения запроса. Аналогом этого объекта в языке SQL является курсор.
Объекты Recordset используются для обработки данных в базе данных на уровне записи. При работе с объектами доступа к данным (мы рассматриваем здесь только библиотеку DAO – Data Access Objects) многие операции выполняются с помощью объектов Recordset. Каждый объект Recordset состоит из записей (строк) и полей (столбцов).
При создании нового объекта Recordset используется метод OpenRecordset:

Set наборЗаписей = объект.OpenRecordset (источник, тип, параметры, блокировки)

Синтаксис метода OpenRecordset содержит следующие элементы.


Элемент Описание
наборЗаписей Объектная переменная, представляющая открываемый объект Recordset, которая может быть описана следующим образом:
Dim наборЗаписей As Recordset
объект Объектная переменная, представляющая существующий объект, используемый при создании нового объекта Recordset. Ссылку на текущую базу данных дает объект CurrentDb.
источник Выражение или переменная типа String, определяющая источник записей для нового объекта Recordset. В качестве источника записей можно указать имя таблицы или запроса, а также инструкцию SQL, которая возвращает записи.
тип Необязательный. Константа, указывающая тип открываемого объекта Recordset (смотри справку MS Access).
параметры Необязательный. Произвольная комбинация констант, задающих характеристики нового объекта Recordset (смотри справку MS Access).
блокировки Необязательный. Константа, определяющая тип блокировки объекта Recordset (смотри справку MS Access). Блокировки предназначены для согласованного изменения данных при одновременной работе с данными многих пользователей.

Некоторые свойства объекта Recordset
BOF возвращает значение, показывающее, расположен ли указатель текущей записи в позиции перед первой записью объекта Recordset.
EOF возвращает значение, показывающее, расположен ли указатель текущей записи в позиции после последней записи объекта Recordset.
Bookmark устанавливает или возвращает закладку, которая однозначно определяет текущую запись в Recordset.
RecordCount возвращает число записей, к которым был осуществлен доступ в объекте Recordset
NoMatch показывает, была ли найдена нужная запись в результате вызова метода Seek или одного из методов группы Find (смотри справку MS Access относительно поиска в наборе записей). Возвращаемое значение имеет тип Boolean и содержит значение True, если нужная запись не найдена. При открытии или создании объекта Recordset его свойство NoMatch получает значение False.

Некоторые методы объекта Recordset
AddNew создает и добавляет новую запись в объект Recordset, к которому он применяется. Данный метод присваивает всем полям записи значения Null (пустые значения, присваивающиеся полям объекта Recordset табличного типа по умолчанию) или значения по умолчанию, определенные пользователем. После внесения изменений в новую запись следует вызвать метод Update для сохранения изменений и добавления записи в объект Recordset. До вызова метода Update изменения в базу данных не заносятся.
Delete удаляет текущую запись в обновляемом объекте Recordset.
Edit копирует текущую запись из обновляемого объекта Recordset в буфер копирования для последующего изменения.
Requery обновляет данные в объекте Recordset с помощью повторного выполнения запроса, на котором базируется указанный объект.
FindFirst, FindLast, FindNext, FindPrevious находят в объектах Recordset типа динамических и статических наборов записей первую, последнюю, следующую или предыдущую запись, удовлетворяющую заданным условиям, и делают эту запись текущей записью.
MoveFirst, MoveLast, MoveNext, MovePrevious находят в указанном объекте Recordset первую, последнюю, следующую или предыдущую запись и делают эту запись текущей записью.

Отметим, что свойства и методы отделяются от объекта Recordset точкой.

В следующем примере открывается динамический объект Recordset и отображается число записей в этом объекте, содержащий записи о товарах, подлежащих списанию.

Sub Списание()
Dim dbs As Database, rst As Recordset
Dim strSQL As String, crit As String

' Возвращает ссылку на текущую базу данных.
Set dbs = CurrentDb
crit = "Сп"

‘Запрос на языке SQL
strSQL = "Select * from [Списание/Возврат] where Списание = '" & crit & "'")

Set rst = dbs.OpenRecordset(strSQL) ‘Создает объект
‘ Recordset с именем rst
rst.MoveLast
Debug.Print rst.RecordCount ‘Вывод в окно отладки количества
‘записей в наборе rst
rst.Close ‘Закрывает объект Recordset
End Sub

Объект DoCmd
Методы, определенные для объекта DoCmd (команда), позволяют запускать макрокоманды Microsoft Access из программ Visual Basic. С помощью макрокоманд выполняют такие действия как закрытие окон, открытие форм и задание значений элементов управления. Например, метод OpenForm объекта DoCmd позволяет открыть форму, а метод Hourglass изменить вид указателя на значок Windows «Занято» (песочные часы).