СУБД MS Access. Автоматизация приложения средствами макросов и модулей VBA

Цель работы –приобретение практических навыков создания макросов и модулей в СУБД MS Access и использования языка программирования Visual Basic for Application (VBA) для решения финансово-экономических задач средствами СУБД MS Access.

После выполнения работы студент должен знать основные понятия объектов реляционной базы данных, характеристики макросов и модулей и уметьсоздавать макросы и модули и использовать их для решения финансово-экономических задач средствами СУБД MS Access.

Справочная информация

Макросы и модули используются для автоматизации процесса обработки базы данных.

Макрос – это набор стандартных команд СУБД MS Access, которые называются макрокомандами (см. Справочная система СУБД MS Access à Общие сведения о макросах). Полный перечень макрокоманд можно увидеть, воспользовавшись командой (СозданиеàМакрос). Макрокоманды могут содержать аргументы, детализирующие действия этих макрокоманд.

Модуль — это набор описаний, процедур и функций на языке Visual Basic for Application, собранных в одну программную единицу. Различают стандартные модули, являющиеся объектами базы данных, модули форм или отчетов, являющиеся частью этих объектов, и модули классов. Более подробно модули будут рассмотрены позднее.

Обучающее задание

1. Загрузите СУБД MS Access, откройте Учебную базу данных, активизируйте объект Модули(СозданиеàМодуль).

2. Изучите с преподавателем среду VBA.

3. В окне программы введите код:

Public Function Password()

Dim p As String

p = InputBox("Ваше имя?", "Введите пароль")

If p = "Иван Иванович" Then 'Введите свое имя

Password=True

MsgBox ("Здравствуйте, уважаемый Иван Иванович!")

Else

MsgBox ("Мы с Вами не знакомы! До свидания!")

Password=False

DoCmd.Quit

End If

End Function

4. Откомпилируйте созданную функцию (DebugàCompile) и проверьте ее работоспособность, запустив на выполнение (RunàRun Sub/UserForm или F5).

5. В случае наличия ошибок отладьте модуль.

6. Сохраните созданный модуль под именем «Пароль» (FileàSave) и перейдите в MS Access.

7. Активизируйте объектМакросы (СозданиеàМакрос) и создайте макрос автоматической загрузки под именем Autoexec, для чего введите следующие макрокоманды (см. табл. 2):

Таблица 2

Перечень макрокоманд

Условие	Макрокоманда	Имя аргумента	Значение аргумента
Password()=Истина	ОткрытьФорму	Имя формы Режим	Заставка Форма
…	ВыделитьОбъект	Тип объекта Имя объекта	Форма Заставка
…	Развернуть

8. Сохраните макрос под именем «Autoexec» и закройте окно конструктора макросов.

9. Закройте и снова загрузите СУБД MS Access, открыв Учебную базу данных.

10.Прокомментируйте результаты работы с приложением.

Самостоятельное задание и контрольные вопросы

1. Создайте подобные модули и макросы для личной базы данных (см. Задание №2 на самостоятельную работу).

2. Представьте результаты их работы преподавателю

3. Назовите основные объекты, с которыми Вы работали при выполнении обучающего и самостоятельного заданий.

4. Какие выражения VBA были использованы Вами при создании модулей и макросов в учебной и личной базе данных? Что означают эти выражения?

Программирование

Написание и отладка программ средствами редактора VBA

Цель работы –изучение редактора VBA, способов создания и отладки программ.

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

Справочная информация

Подготовка экономических задач к решению на ЭВМ – достаточно сложный процесс, состоящий из пяти этапов: постановка задачи; формализация задачи и выбор метода ее решения; разработка алгоритма решения задачи; создание программы решения задачи; отладка программы.

Остановимся более подробно на двух последних этапах.

Программой называется написанная на языке программирования логически завершенная последовательность действий, выполнение которых приводит к искомому результату. К настоящему времени разработано и используется большое количество языков программирования, среди которых наиболее широко распространенным, простым и доступным для пользователей является Visual Basic for Application (VBA) - визуализированный Basic для приложений. VBA относится к объектно–ориентированным языкам и использует технологию визуального программирования.

Все программы VBA представляются в виде процедур и функций, которые хранятся в модулях.

Процедуры и функций являются фрагментами программного кода, заключенными между операторами Sub … End Sub и Function … End Function. Функция отличается от процедуры тем, что ее имя выступает в качестве переменной и используется для возвращения значения переменной в точку обращения к функции.

Различают четыре типа модулей: стандартные модули, модули форм, модули отчетов и модули класса.

Стандартные модули или модули проекта являются объектами приложения и могут вызываться из любого места в нем. Они загружаются в оперативную память при первом обращении к любой процедуре модуля или ссылке на переменную в этом модуле и остаются в памяти до закрытия приложения. Создание стандартных модулей осуществляется в редакторе VBA (меню InsertàModule) или в окне базы данных (Создание àМодули).

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

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

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

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

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

Различают три типа ошибок – синтаксические, смысловые и логические.

Синтаксические ошибки связаны с неправильной записью или употреблением конструкций языка программирования. Данные ошибки легко устраняются на стадии компиляции программы (окно редактора VBA à меню Debug à Compile).

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

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

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

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

Пример

1. Постановка задачи:Требуется создать программу расчета текущей рентабельности некоторого предприятия за прошедшие сутки.

Периодичность решения – ежедневно. Задача реализуется на ПЭВМ стандартной комплектации. Входная оперативная информация – сведения о количестве статей доходов и расходов, значения статей доходов и расходов в рублях за предыдущие сутки – вводится с клавиатуры. Промежуточная информация не накапливается и не сохраняется. Выходная информация – значение текущей рентабельности в % - выводится на дисплей в виде диалогового окна. Допустимый диапазон изменения числа статей доходов и расходов – от 0 до 10, допустимый диапазон изменения значений статей доходов и расходов – от 0 до 1 000 000,00 руб. Значения статей доходов и расходов не могут быть отрицательными. Источником возникновения информации являются оперативные данные бухгалтерии.

2. Экономико-математическая модель задачи следующая:

* 100%, ,

где: i – индекс статьи доходов, i=1,2,…, M;

M – число статей доходов; 0<=M<=10;

j – индекс статьи расходов, j=1,2,…, N;

N – число статей расходов; 0<= N<=10;

d_i – значение i-ой статьи доходов, руб; 0<=d_i<=1000000;

r_j – значение j-той статьи расходов, руб; 0<= r_j <=1000000.

В качестве метода решения данной задачи используется метод подстановок.

3. Алгоритм решения задачи представлен в виде блок-схемы и приведен в прилож. 11.

Программа решения задачи

Исходный текст программы представлен ниже. Он содержит типичные синтаксические, смысловые и логические ошибки, допускаемые студентами. Программа оформлена в виде процедуры и должна находиться в стандартном модуле с именем Module2. Для ее создания необходимо в окне базы данных выполнить команду СоздатьàМодули или войти в окно редактора VBA (Alt+F11àРедактор VBAà меню InsernàModule).

Option Compare Database

Sub Текущая_рентабельность_c_ошибками()

Dim d(), r() As Currency

Dim Sum_d, Sum_r, rent, i, m, j, n As Integer

m = InputBox("Введите число статей доходов", "Доходы")

If m = Empti Or m<=0 Then

Exit Sub

n = Val(ImputBox("Введите число статей расходов", "Расходы"))

If m = Empty Or n<=0 Or n>=20 Then Exit Sub

ReDim d(m), r(1 To n) ‘Переопределение размеров массивов d(), r()

Sum_d = 0

Sum_r = 0

For i = 1 To m

Met1: d(i) = Val(InputBox("Введите значение статей _

доходов", "Доходы"))

If d(i)<=0 Or d(j)>1000000 then

Msgbox "Значение дохода не может быть меньше 0 _

и больше 1000000 руб."

Goto met1

End if

Sum_d = Sum_d + d(i)

For j = 1 To n Step -1

r(j) = Val(InputBox("Введите значение статей _

расходов", "Расходы"))

Sum_r = Sum_r + r(i)

Next j

rent = (Sum_d - Sum_r) / Sum_r

MsgBox "Значение текущей рентабельности равно " & _

rent & "%"

Print rent

EndSub

End Sub

Задание

1. Изучите с преподавателем редактор VBA.

2. Перепишите текст предложенной процедуры расчета текущей рентабельности в стандартный модуль.

3. Откомпилируйте программу (окно редактора VBA à DebugàCompile) и в процессе компиляции устраните все синтаксические ошибки.

4. Запустите программу на выполнение (окно редактора VBAàRunàRun Sub (F5)) и с использованием средств отладки на контрольном примере устраните все смысловые и логические ошибки. Результаты запишите.

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

6. Отладьте программу, проверьте ее работоспособность, в том числе с критическими и недопустимыми значениями параметров, и представьте работу преподавателю. Переименуйте процедуру, присвоив ей имя «Текущая_рентабельность_без_ошибок()».

7. Создайте в Кнопочной_форме кнопку для запуска процедуры расчета текущей рентабельности, для этого:

7.1. Выделите Кнопочную_форму и войдите в режим Конструктора; на панели инструментов выключите кнопку Мастеров;

7.2. Создайте кнопку, присвойте её подписи значение «Расчет текущей рентабельности»;

7.3. Для этой кнопки создайте (выделить кнопкуàокно Свойства à вкладка События à событие Нажатие кнопки àПостроитель à Программы) следующую процедуру:

Private Sub Кнопка21_Click() ‘ в имени процедуры № кнопки

‘присваивается приложением

Текущая_рентабельность_без_ошибок

End Sub

Перейдите в режим Формы и проверьте работу кнопки

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

1. Назовите и прокомментируйте основные этапы решения экономических задач на ЭВМ.

2. Перечислите все синтаксические, смысловые и логические ошибки, допущенные в исходном тексте предложенной программы.

3. Каковы причины возникновения указанных Вами ошибок?

4. Найдите отличие алгоритма от программы и устраните его.

5. *Предложите усовершенствованный вариант программы расчета текущей рентабельности.

6. Объясните, почему в модуле Кнопочной_формы процедуре нажатия кнопки присвоено имя Private Sub Кнопка21_Click().