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

Министерство образования Российской Федерации

Государственное учреждение

Кузбасский государственный технический университет

Кафедра вычислительной техники

и информационных технологий

ACCESS

Пользовательские функции

Методические указания к лабораторной работе для студентов специальности “Экономика и управление на предприятиях ” по курсу “Автоматизация экономических расчетов ”

Составитель Е.А. Бессонов

Утверждены на заседании кафедры Протокол № 10 от 26.06.01

Рекомендованы к печати учебнометодической комиссией специальности

060800

Протокол № 1 от 27.09.01

Электронная копия хранится в библиотеке главного корпуса ГУ КузГТУ

Кемерово 2002

1

Цель работы:

1.Знакомство с VBA (язык Visual Basic для приложений) - основным средством разработки приложений Microsoft Office.

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

Предполагается предварительное знакомство читателя с основными объектами Access - таблицами, запросами, формами, отчетами и макросами. VBA применяется для тех же целей, что и макросы, но имеет более широкие возможности.

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

Пользовательские функции хранятся в модулях.

Модули

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

Если процедура Sub (процедура-подпрограмма) или процедура Function (процедура-функция или просто функция) может вызываться из различных мест (формы, отчеты, другие модули), то их размещают в стандартном модуле. Кроме стандартных модулей, применяются еще модули класса, а также модули форм и отчетов. Особенностью стандартного модуля является то, что он принадлежит к числу основных объектов базы данных и представлен в списке модулей окна базы данных. Для создания нового стандартного модуля необходимо в окне базы данных выбрать ярлык “Модули” и нажать кнопку Создать.

Пользовательские процедуры-функции создаются в стандартных модулях. Для того чтобы отредактировать процедуру в стандартном модуле, нужно выбрать ярлык “Модули”, щелкнуть по имени модуля и нажать кнопку Конструктор.

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

2

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

Когда вы открываете модуль в режиме конструктора, Access выводит раздел описаний этого модуля. В разделе описаний объявляются модульные переменные, доступные из всех процедур модуля. Сразу после создания окно модуля имеет вид:

Инструкция Option Compare Database означает, что во всех процедурах модуля сравнение строк производится по алфавиту, а не по шифрам (номерам) символов кодовой таблицы. В раздел описаний рекомендуется добавить еще строку Option Explicit. Эта инструкция предписывает явно объявлять все переменные во всех процедурах модуля.

Функции

Функция записывается в формате Function ИмяФункции(Аргументы) [As Тип] Инструкции

End Function

Function, End и As - ключевые слова. Остальные слова формата должны быть заменены на слова пользователя.

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

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

Для создания новой функции необходимо написать слово Function , имя функции и нажать Enter. При этом осуществится переход к новой (пустой) строке. После имени функции автоматически вставляются круглые скобки. Вслед за пустой появляется строка “End Function”.

3

Тип - тип результата. Квадратные скобки свидетельствуют о том, что конструкция As тип необязательна. Если она отсутствует, то значение функции имеет тип Variant (любой тип).

Инструкции - последовательность инструкций языка VBA. На одной строке можно разместить несколько инструкций, разделив их двоеточиями.

Пример

Function Завтра()

Завтра = “Завтра будет “ & (Date + 1) End Function

Функция Завтра аргументов не имеет. Еe результат имеет тип Variant. В теле процедуры (между первой и последней строкой) присутствует всего одна инструкция - инструкция присваивания. Признаком присваивания служит знак равенства. Как и в других языках программирования, справа от знака присваивания располагается выражение. Выражение вычисляется и его результат присваивается переменной левой части.

В выражении с помощью оператора & осуществляется конкатенация (сцепление, соединение) двух строк. Первая строка - “Завтра будет”, а вторая - выражение в круглых скобках. Результат вычисления выражения – дата. Функция Date вычисляет текущую дату и прибавляет один день. VBA преобразует полученную дату в строку и осуществляет конкатенацию.

Запуск функции

Результат работы функции можно получить, если запустить ее, например, из окна отладки. Для вызова окна отладки надо выполнить команду меню View|Immediate Window (Вид|Окно отладки). В появившемся окне отладки нужно напечатать:

? Завтра()

Вопросительный знак означает команду печати выражения, то есть выдачу результата выражения в следующей строке окна отладки. Курсор должен находиться в конце выражения. Если теперь нажать ENTER, в следующей строке появится текст вида “Завтра будет 26.10.2001”.

Можно задать свой формат представления даты, если второй операнд выражения в операторе присваивания функции Завтра представить в виде:

Format(Date + 1, ”d mmmm yyyy г”)

4

Функция Format вычисляет дату (первый аргумент) и форматирует ее в соответствии с шаблоном, заданным вторым аргументом. В шаблоне используются те же символы, что и в функции Format в запросах.

Примеры

В следующем примере используются аргументы функции и переменные .

Function ЗаглавнаяПервая(X) Dim Слово

Слово = Trim(X)

ЗаглавнаяПервая = UCase(Left(Слово, 1)) & Mid(Слово, 2) End Function

Единственный аргумент Х в заголовке функции не описан. По умолчанию он имеет тип Variant.

Инструкция Dim описывает локальную (доступную только в данной процедуре) переменную с именем “Слово”. Тип этой переменной в инструкции Dim не указан. По умолчанию ей приписывается тип Variant, то есть она может принимать значения любого типа. После имени переменной можно указать тип с помощью конструкции As тип. Так, если записать инструкцию в виде

Dim Слово As String

то переменная Слово получила бы тип String (строковый). Переменная Слово в этом случае могла бы содержать только символьные строки, зато занимала бы в памяти значительно меньше места. Помимо самой строки переменная содержит целое число - количество символов в строке. Переменная типа Variant содержит код типа и превосходит по размеру все другие типы.

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

Последняя инструкция функции - также присваивание. В выражении правой части использованы встроенные функции:

UCase преобразует все символы аргумента в заглавные; Left(X, n) сохраняет в аргументе X n символов слева;

Mid(X, n, m) возвращает m символов аргумента X, начиная с n-го. Если аргумент m опущен, возвращаются все символы, начиная с n-го (счет ведется с 1).

5

В результате первый символ Слова преобразуется в заглавный и соединяется с помощью операции конкатенации с остающимися без изменения символами Слова, начиная со второго. Результат присваивается переменной ЗаглавнаяПервая. Имя переменной совпадает с именем процедуры-функции, а тип переменной присваивается в заголовке процедуры. Если переменной с именем функции в теле цикла присваивается значение, то именно оно будет возвращено в точку вызова.

Следующий пример демонстрирует реализацию циклических вычислений с помощью инструкции For.

For пц = НачальноеЗначение To КонечноеЗначение Тело цикла

Next [пц]

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

Пример

Function Буквы() Dim i As Integer For i = 192 To 223

Debug.Print i & “ “ & Chr(i) Next i

End Function

Тело цикла составляет инструкция вида Debug.Print X. Если окно отладки открыто, строка Х выдается в нем. В нашем случае строка - результат вычисления выражения, в котором осуществляется конкатенация трех частей: i, “ “ и Chr(i).

i - целое число в диапазоне от 192 до 223 включительно. Операция конкатенации осуществляется над строковыми (тип String) переменными, поэтому VBA автоматически преобразует число в строку.

“ “ - пробел - строка из одного символа.

Chr(i) - символ, который получается путем преобразования целого числа i с помощью встроенной функции Chr. При этом i рассматривается как номер символа в кодовой таблице.

В результате выполнения функции Буквы в окне отладки появляется столбец значений вида:

192А

193Б

6

...

223Я

Функция Буквы значения в точку вызова не возвращает (в теле

процедуры переменной Буквы значение не присваивается). По существу функция Буквы работает как процедура Sub, но запускается как

Function.

Некоторые встроенные функции возвращают пустое значение (Null), если хотя бы один из аргументов пуст. Между тем пустые значения нередко появляются в формах и таблицах, если какое-либо поле пропускается при заполнении. Часто пустое значение целесообразно трактовать как нуль. В этом случае следует преобразовать предварительно пустые значения в нули. Для этого можно использовать функцию

Function ПустоНуль(X) If IsNull(X) Then

ПустоНуль = 0 Else

ПустоНуль = X End If

End Function

В данном примере используется инструкция If вида If Условие Then

Да-инструкции

Else

Нет-инструкции

End If

Вначале вычисляется условие (логическое выражение). Если оно истинно, выполняются Да-инструкции, ели ложно - Нет-инструкции. Значением функции ПустоНуль будет 0, если аргумент X равен Null. В противном случае функция возвращает значение своего аргумента.

Следующая функция оперирует датами. Function Возраст(X) As Integer

Dim Годы As Integer If IsDate(X) Then

Годы = Year(Date) - Year(X)

If DateSerial(Year(Date), Month(X), Day(X)) > Date Then

Годы = Годы - 1 End If

7

Возраст = Годы

End If

End Function

Функция возвращает целое число (тип Integer) - число полных прожитых лет между датами X (дата рождения) и Date (сегодня).

Инструкция Dim объявляет целое число - переменную Годы. Аргумент функции имеет тип Variant и может принимать значение любого типа. Функция рассчитана на обработку только дат. Поэтому следует проверить тип аргумента с помощью встроенной функции IsDate. Если аргумент - дата, производится его обработка. В противном случае происходит выход из процедуры. Переменная Возраст будет иметь инициализированное значение.

VBA инициализирует локальные переменные во время компиляции. Числовым переменным присваиваются нулевые значения. Переменные типа Variant получают значение Empty (отсутствие значения). Строковые переменные инициализируются строками нулевой длины (пустые строки). Переменные процедуры инициализируются каждый раз, когда процедура запускается.

Если аргумент - дата, выполняется тело инструкции If, состоящее из трех инструкций.

В первой инструкции используется встроенная функция Year, выделяющая год из даты. Целочисленной переменной Годы присваивается разность между текущим годом и годом рождения.

Условие инструкции If осуществляет проверку - наступил ли день рождения. Для этого сравниваются сегодняшняя дата Date и дата, сформированная встроенной функцией DateSerial. Эта функция строит дату из аргументов Год, Месяц и День, которые извлекаются из дат с помощью встроенных функций Year, Month и Day. Если день рождения еще не наступил, то от числа лет Годы отнимается единица.

Функцию Возраст можно использовать не только для определения числа прожитых лет. Пусть, например, выполняется запрос

SELECT DISTINCTROW Сотрудники.Фамилия,

Сотрудники.[Дата найма], Возраст([Дата найма]) As [Стаж работы] FROM Сотрудники

ORDER BY Возраст([Дата найма]) DESC;

После выполнения запроса в столбце “Стаж работы” будет выведено количество отработанных на предприятии полных лет для каждого работника.

8

Формат инструкции выбора: SELECT CASE выражение

Case значения 1 Инструкции 1 Case значения 2 Инструкции 2

...

[Case Else

Инструкции Else] End Select

Вначале вычисляется выражение. Затем выполняется та из групп инструкций, в список значения N которой входит значение выражения. Если ни в одной из групп Case нет соответствующего значения, выполняется инструкция Else. Если группа Case Else отсутствует, то не выполняется ничего.

Function ДеньПолучки(ByVal D As Variant) As Variant Dim P

If Not IsNull(D) Then

P = DateSerial(Year(D), Month(D) + IIf(Day(D)<=5,0,1), 5) Select Case WeekDay(P)

Case 1

D = P + 1 Case 7

D = P + 2 Case 6

D = P - 1 Case Else

D = P End Select

ДеньПолучки = D

Else

ДеньПолучки = Null End If

End Function

Функция возвращает пятое число следующего (или текущего, если число <=5) месяца, когда выдается зарплата. Если в учреждении зарплата выдается в иной срок, в процедуру надо внести изменения. Если дата выдачи зарплаты попадает на субботу или воскресение, то выдача за-

9

держивается соответственно на 2 или 1 день. Если дата выдачи зарплаты попадает на пятницу, то выдачу производят в четверг, то есть на день вперед.

Функции передается аргумент - любая дата в текущем месяце. Перед аргументом D стоит слово ByVal (по значению). Это значит, что из точки вызова передается не адрес данного, а его копия (значение). Процедура не сможет изменить аргумент в точке вызова.

Проще всего аргумент проверяется встроенной функцией IsNull. Если аргумент пуст, то выполняется ветвь Else оператора If, и функция возвращает пустое (Null) значение.

Если аргумент D не пуст, временной переменной P присваивается дата пятого числа текущего или следующего месяца, вычисленная функцией IIf. Если число месяца аргумента <5, то месяц даты P – текущий, в противном случае – следующий.

Инструкция Select уточняет дату выдачи зарплаты с учетом дня недели. При этом учитывается порядок дней недели, принятый в Windows (Воскресение - 1, Пятница -6, Суббота -7). Затем полученное значение присваивается имени функции.

Всписок значений группы Case могут входить конструкции a To b (от a до b включительно) или отдельные значения, разделенные запятыми. На панели управления Windows может быть выбран и другой символ для разделения элементов списка.

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

икаждый символ, расположенный после небуквенного (например пробела или знака препинания), - заглавные, а остальные символы - строчные.

Function Proper(X) Dim n As Integer Dim S As String

Dim ТекСим As String Dim ПредСим As String If IsNull(X) Then

Exit Function End If

S = CStr(X)

For n = 1 To Len(S) ТекСим = Mid$(S,n,1)