Е.А. Бессонов Access. Пользовательские функции

10

Select Case ПредСим

Case “A” To “Z”, “a” To “z”, “А” To “Я”, “а” To “я” Mid$(S,n,1) = LCase(ТекСим)

Case Else

Mid$(S,n,1) = UCase(ТекСим) End Select

ПредСим = ТекСим

Next n

Proper = CVar(S) End Function

Вначале аргумент Х проверяется встроенной функцией IsNull. Если аргумент пуст, то выполняется инструкция Exit Function. Происходит выход из процедуры без выполнения каких-либо действий. Если аргумент имеет значение, то оно преобразуется функцией Cstr в тип String (строка).

Цикл For перебирает все символы строки от первого до последнего. Номер последнего символа определяется встроенной функцией Len, возвращающей количество символов в строковом аргументе. В теле инструкции For очередной символ подвергается преобразованию.

Очередной символ выделяется функцией Mid$. Знак $ означает, что результат функции выдается в виде строки. Из строки S (первый аргумент) выделяется 1 символ (третий аргумент), начиная с позиции n (второй аргумент). Таким образом, переменная ТекСим теперь содержит очередной (текущий) символ.

Выражением в инструкции Select является переменная ПредСим (предыдущий символ). Вариант Case выполняется в случае, если предыдущий символ - буква. В этом случае текущий символ функцией LCase преобразуется к нижнему регистру, если не буква - к верхнему. Затем предыдущий символ заменяется на текущий. Если обрабатывается первый символ, то ПредСим имеет инициализированное (Empty) значение, и выполняется вариант Case Else.

DoCmd

Большие возможности предоставляют методы объекта DoCmd, реализующие выполнение макрокоманды из процедуры VBA DoCmd.макрокоманда [аргументы]

Макрокоманда - название макрокоманды на английском языке. По форме это метод объекта DoCmd.

11

Число и тип аргументов зависят от того, какую макрокоманду следует запустить. Например, макрокоманда ВыводНаЭкран имеет аргументы “Включить вывод”(с возможными значениями Да или Нет) и “Сообщение”. Первый аргумент используется для включения или отключения обновления экрана, а второй - для задания строки текста, которая будет выведена в строке состояния. По названию макрокоманды (например Echo) в справке можно найти описание аргументов и допустимых встроенных констант.

Пример

DoCmd.Echo False, “Обновление экрана отключено”

Если макрокоманда требует задания нескольких аргументов, то для их разделения используются запятые. Например, два аргумента макрокоманды Закрыть (Close), указывающие тип и имя закрываемого объекта базы данных, разделены запятой:

DoCmd.Close acForm, “Новый товар”

Первый аргумент, acForm, является встроенной константой MS Access, которая указывает, что закрываемый объект является формой.

Некоторые макрокоманды могут принимать необязательные аргументы. Если эти аргументы не будут указаны при вызове, то MS Access будет использовать их заданные по умолчанию значения. Так, например, макрокоманда Закрыть(Close) имеет аргументы “Тип объекта” и “Имя объекта”. Если опустить оба аргумента, MS Access закроет активный объект базы данных, какого бы типа он ни был: DoCmd.Close

Если требуется пропустить необязательный аргумент и в то же время указать аргумент, стоящий после необязательного, то нужно добавить в инструкцию запятую, которая отмечает место пропущенного аргумента. Например, приведенная ниже макрокоманда СдвигРазмер (MoveSize) использует заданные по умолчанию (текущие) значения для аргументов “Вправо” и “Вниз”, но определяет значения аргументов “Ширина” и “Высота”:

DoCmd.MoveSize , , 5000,3000

Некоторые макрокоманды в VBA выполнить нельзя.

Рассмотрим для примера макрокоманду ПанельИнструментов, которая выводит на экран или убирает с экрана любую стандартную или пользовательскую панель инструментов и имеет два аргумента. Первый аргумент задает имя панели. Второй аргумент определяет, скрыть панель (константа acToolbarNo) или вывести на экран (константа

12

acToolbarYes). Для этих целей можно использовать две следующие функции:

Function ПоказПанели() DoCmd.ShowToolbar “Панель 1”, acToolbarYes End Function

‘

Function СпрятатьПанель() DoCmd.ShowToolbar “Панель 1”, acToolbarNo End Function

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

Показ и скрытие панелей инструментов должны осуществляться в определенные моменты времени, когда наступают некоторые события (нажатие кнопки, переход к новой записи в форме, форматирование для печати раздела отчета и т. п.). Среди свойств многих объектов присутствуют свойства событий. Когда наступает событие, Access анализирует значение этого события. Если значение начинается со знака равенства, то оно воспринимается как выражение и предпринимается попытка его вычислить.

Пусть в некоторой форме заданы значения событий. Открытие =ПоказПанели()

Закрытие =СпрятатьПанель()

При каждом открытии формы наступает событие Открытие, и вычисляется соответствующее выражение. На экране появляется панель с именем “Панель 1”. При закрытии формы эта панель исчезает.

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

Function ПоказатьПанель(Имя, Показать) If Показать Then

DoCmd.ShowToolbar Имя, acToolbarYes

Else

DoCmd.ShowToolbar Имя, acToolbarNo End If

End Function

Свойства формы теперь надо записать иначе.

13

Открытие =ПоказатьПанель(“Панель 1”,Истина) Закрытие = ПоказатьПанель(“Панель 1”,Ложь)

В справке можно найти описание, формат любой макрокоманды и используемые константы. Так, если установить курсор на слове ShowToolbar и нажать F1, то можно получить по этому слову подробную справку.

Отметим, что в старых версиях у констант применялся префикс A_ (например A_Toolbar_Yes), а теперь ac (acToolbarYes). Для разделения отдельных слов ранее использовался знак подчеркивания (“_”). Теперь отдельные слова в составном имени пишутся слитно, но каждое слово пишется с заглавной буквы (например acToolbarNo). Access 2000 может воспринимать оба вида префиксов.

DoMenuItem

Очень мощной макрокомандой является RunCommand, которая позволяет выполнять команды меню или панелей инструментов. Для совместимости со старыми версиями Access сохранена макрокоманда DoMenuItem, которую рассмотрим на примере Access 2 с форматом DoCmd.DoMenuItem Меню, Название меню, Команда, Подкоманда, Версия

Эта макрокоманда позволяет выполнить любой пункт любого меню. Аргументы задаются номерами строк в соответствующих списках. Макрокоманда КомандаМеню имеет те же аргументы. Если раскрыть список Меню (первый аргумент), появится список всех допустимых для использования строк меню

.Номер	Меню	Константа
0	Форма	A_Formbar
1	Фильтр
2	База данных
3	Конструктор форм
4	Начало
5	Модуль
6	Запрос
7	Отчет
8	Макрос
9	Конструктор таблиц
10	Форма в режиме таблицы
11	Таблица в режиме таблицы

14

12Запрос в режиме таблицы

13Схема данных системы

Всписке макрокоманды КомандаМеню показывается второй столбец. Только он нужен для выбора аргумента. Иная ситуация в соответствующей инструкции DoMenuItem. В качестве аргумента Меню используется номер из первой колонки показанного выше списка или соответствующая константа (если есть). Так, например, если необходимо задать меню Форма, то в качестве первого аргумента инструкции DoMenuItem необходимо использовать число 0 или константу A_Formbar. Рекомендуется использовать константы, так как они делают текст процедуры более понятным.

После выбора первого аргумента следует приступить к выбору второго. Второй аргумент также выбирается из списка, который формируется в зависимости от того, каким выбран первый аргумент. Так, если в качестве первого аргумента выбрана константа A_Formbar, то список для выбора второго аргумента имеет вид:

0	Файл	A_File
1	Правка	A_EditMenu, A_Edit
2	Вид
3	Запись	A_RecordsMenu
4	Окно
5	Справка

Если в качестве первого аргумента выбран не 0 или A_Formbar, то список для “Название меню” будет другой. Всего таких вариантов 14. Пусть, например, выбраны аргументы A_Formbar и A_RecordsMenu. Для третьего аргумента инструкции DoMenuItem (КомандаМеню) появляется новый список.

0	Ввод данных
1	Перейти
2	Обновить
3	Быстрая сортировка
4	Изменить фильтр
5	Показать все записи
6	Разрешить изменение

Теперь можно, например, сформировать команду

DoCmd.DoMenuItem A_Formbar, A_RecordsMenu, 5

15

Эта команда отменяет действие фильтра.

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

0	Первая
1	Последняя
2	Следующая
3	Предыдущая
4	Новая

Команда

DoCmd.DoMenuItem A_Formbar, A_RecordsMenu, 1 , 1

обеспечит переход к последней записи формы. Подобные команды удобно связывать с кнопками или пунктами пользовательского меню.

Команда DomenuItem удобна, когда ведет к немедленному исполнению, а не к открытию окна диалога для задания дополнительных параметров.

Работа с датами

Процедуры могут обращаться к другим процедурам с запросом на выполнение некоторых преобразований. Ниже приводятся такие примеры из области оперирования датами и промежутками времени.

Function ДнейВМесяце (Дата) As Integer D1 = DateSerial (Year(Дата), Month(Дата), 1)

D2 = DateSerial (Year(Дата), Month(Дата) + 1, 1)

ДнейВМесяце = D2 - D1 End Function

Вначале функция ДнейВМесяце вычисляет две даты. D1 представляет собой первое число месяца, извлеченного из аргумента Дата, D2 - первое число следующего месяца. Следует учитывать, что дата в Access представляется в виде числа. Целая часть числа - количество дней после некоторой начальной даты, дробная - доля суток, прошедшая после полуночи. Разность дат - число. В нашем случае это число дней в месяце, к которому относится аргумент функции.

Function Високосный(Год) As Integer

Високосный = (ДнейВМесяце(DateSerial(Год,2,1)) = 29) End Function

Функция выдает результат сравнения числа 29 с числом дней в феврале года, заданного аргументом функции. Год в аргументе должен

16

быть задан четырехзначным числом. Результат сравнения - Истина или Ложь, но представленный целым числом (тип Integer). В Access логические значения представляются целыми числами. Ложь представляется нулем, Истина - числом -1. Любое число, отличное от 0, воспринимается как Истина.

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

Function ДнейНедели(Дата, ДеньНедели As Integer) As Integer

Месяц = Month(Дата)

День = DateSerial(Year(Дата), Месяц, 1) Do While WeekDay(День) <> ДеньНедели

День = День + 1

Loop n = 0

Do While Month(День) = Месяц n = n + 1

День=День + 7

Loop

ДнейНедели = n End Function

Под днем недели понимается номер в диапазоне от 1 до 7, причем воскресенье имеет номер 1, вторник -3, а суббота -7. Функции также передается дата (аргумент Дата), из которой с помощью встроенной функции Month извлекается номер месяца. Переменной День присваивается дата, относящаяся к первому числу этого месяца. Затем используется цикл вида

Do While Условие Инструкции

Loop

Цикл Do...Loop обычно применяется, когда заранее нельзя определить число повторений цикла. Тело цикла (инструкции) выполняется, пока (while) выполняется условие или до (until) выполнения условия. Если условие выполняется, то выполняются и инструкции (тело цикла), в противном случае - выход из цикла. Ключевое слово Loop возвращает к началу цикла, и действия повторяются. Конструкцию While Условие

17

можно расположить и после слова Loop. В этом случае проверка на окончание цикла выполняется после выполнения тела цикла и гарантируется выполнение тела цикла хотя бы один раз. Если слово While заменить на Until, то тело цикла будет выполняться, если условие не выполняется.

Вусловии рассматриваемого цикла с помощью встроенной функции WeekDay определяется номер дня недели первого числа месяца и сравнивается с аргументом ДеньНедели. При успехе сравнения осуществляется выход из цикла. Переменная День содержит первое число месяца. В случае, если дни недели не совпадают, к дате День прибавляется единица, заново проверяется условие и т. д. В любом случае после выхода из цикла переменная День содержит дату, у которой день недели соответствует дню недели аргумента ДеньНедели.

Далее выполняется еще один цикл Do…While, в котором к дате День прибавляется 7, пока дата относится к тому же месяцу. В теле цикла используется счетчик n. До цикла переменная n получает нулевое значение. В теле цикла к n добавляется единица.

После выхода из цикла значение переменной n равно числу дней месяца, когда день недели равен заданному в аргументе. Это число и становится значением функции. Если задано обращение к функции в виде ДнейНедели (Date, 6), то функция выдает число пятниц в текущем месяце.

Для учета праздников надо создать соответствующий массив дат. Function Праздники()

ReDim Праздник(1 to 10) Праздник(1) = #1/1/2001# Праздник(2) = #1/2/2001# Праздник(3) = #1/7/2001# Праздник(4) = #3/8/2001# Праздник(5) = #5/1/2001# Праздник(6) = #5/2/2001# Праздник(7) = #5/9/2002# Праздник(8) = #6/12/2001# Праздник(9) = #11/7/2001# Праздник(10) = #12/12/2001# End Function

Вразделе описаний модуля должен быть описан массив Праздник

спомощью инструкции

18

Dim Праздник()

Массив доступен в любой процедуре модуля. После имени массива в круглых скобках с помощью числовых констант можно указать размерность массива. В нашем случае скобки пусты. Это значит, что массив динамический. Его размерность можно объявить позднее с помощью инструкции ReDim.

Функция Праздники формирует массив дат, соответствующих праздничным дням. Инструкция ReDim задает текущую размерность массива - 10 элементов с номерами индексов от 1 до 10. Поскольку конструкция As после скобок не указана, элементы массива будут иметь вариантный тип. Следующие за ReDim инструкции присваивают элементам массива даты праздников с помощью констант. Признаком даты у констант служат символы “#”. Если состав праздников изменится, в программу нужно будет внести изменения. Для внесения изменений нужны знания VBA, что является недостатком данного подхода. Если праздники хранить в таблице Access, то обновление списка праздников сможет осуществить оператор базы данных, не владеющий программированием на VBA.

Список праздников можно использовать для определения количества праздников в месяце.

Function ЧислоПраздников(Д1, Д2) Dim n As Integer,i As Integer

Call Праздники

For i = LBound(Праздник) To UBound(Праздник)

If Праздник(i)>=Д1 And Праздник(i)<=Д2 Then n = n + 1

End If

Next

ЧислоПраздников = n End Function

Функция ЧислоПраздников определяет количество праздников между датами Д1 и Д2. Инструкция Call вызывает функцию Праздники, которая создает модульную переменную Праздник (массив праздников).

В цикле For ... Next использованы встроенные функции LBound и UBound. LBound выдает нижнюю границу индексов аргумента-массива (1), а UBound - верхнюю (10). В теле цикла с помощью переменной n

19

подсчитывается, сколько праздников из массива Праздник попадает в заданный диапазон дат.

Function ПраздниковВМесяце(D As Variant) As Integer Dim X, D1 As Date, D2 As Date

X = Праздники()

Д1 = DateSerial(Year(D), Month(D), 1)

Д2 = DateSerial(Year(D), Month(D) + 1, 1) - 1

ПраздниковВМесяце = ЧислоПраздников(Д1, Д2) End Function

Количество праздничных дней месяца вычисляется с помощью функции ЧислоПраздников. Для этого вначале вычисляются 2 даты - первое число месяца Д1 и последнее число этого же месяца Д2. Последнее число определяется как первое число следующего месяца минус единица.

Для инициализации модульного массива Праздник используется вспомогательная переменная X, которой присваивается выражение правой части. Отметим, что в этом случае к названию функции справа добавляются круглые скобки. При вызове функции с помощью инструкции Call круглые скобки после имени функции не указываются.

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

Задание

Самостоятельно составить пользовательскую функцию, выполняющую задачу в проектируемой базе данных. Запустить программу из окна отладки (Immediate) для различных значений аргументов. Сравнить полученные значения с значениями, вычисленными вручную.