СУБД 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. Алгоритм решения задачи представлен в виде блок-схемы и приведен в прилож. 12.

 

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

Исходный текст программы представлен ниже. Он содержит типичные синтаксические, смысловые и логические ошибки, допускаемые студентами. Программа оформлена в виде процедуры и должна находиться в стандартном модуле с именем 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

 

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

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

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

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

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

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

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

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

12. Создание программ обработка цифровой информации

 

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

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

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

При решении большинства финансово – экономических задач пользователь многократно выполняет расчеты тех или иных показателей, значения которых описываются следующими основными типами данных: Integer, Long, Single, Currency, Variant (см. прилож. 13). При этом могут использоваться математические (+,-,*,/,\,^,Mod), логические (And, Or, Xor, Not) операции и операции отношений (<, >, <=, >=, <>, =), а также встроенные (стандартные) функции (см. прилож. 14).

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

 

Пример программы

Требуется написать программу расчета значения выражения

при следующих значениях параметров:

- целые положительные числа в диапазоне от 0 до 30000;

- любое вещественное число;

- любое неотрицательное число в денежном формате (р.)

 

'Пример 1

'Лабораторная работа №2

'Первый вариант. Здесь не учитываются допустимые значения 'параметров, формат результата и не отслеживается нажатие 'кнопки Cancel

Sub Lab_rab_2 ()

Dim A, B, C As Integer, X, Y As Single

A = Val(InputBox("Введите A", "Ввод исходных данных"))

B = Val(InputBox("Введите B", "Ввод исходных данных"))

C = Val(InputBox("Введите C", "Ввод исходных данных"))

X = Val(InputBox("Введите X", "Ввод исходных данных"))

Y = (a * b) / Abs(x ^ 3) + c

MsgBox Y

End Sub

 

'Пример 2

'Лабораторная работа №2

Sub Lab_rab_2()

Dim A, B, C As Integer, X, Y As Single, Z As Currency

A = Val(InputBox("Введите A", "Ввод исходных данных"))

If A < 0 Then

MsgBox "Значение параметра A не может быть меньше нуля", _

vbExclamation, "Внимание! "

Exit Sub

End If

B = Val(InputBox("Введите B", "Ввод исходных данных"))

If B < 0 Then

MsgBox "Значение параметра B не может быть меньше нуля", _

vbExclamation, "Внимание! "

Exit Sub

End If

C = Val(InputBox("Введите C", "Ввод исходных данных"))

If C < 0 Then

MsgBox "Значение параметра C не может быть меньше нуля", _

vbExclamation, "Внимание! "

Exit Sub

End If

X = Val(InputBox("Введите X", "Ввод исходных данных"))

If X = 0 Then

MsgBox "Значение X не может быть равно нулю!", _

vbCritical, "Недопустимое значение"

Exit Sub

End If

Y = (A * B) / Abs(X ^ 3) + C

z = MsgBox("Y равен " & Format(Y, "Currency"))

End Sub

 

 

Задание

1. Выполните постановку задачи, опишите экономико-математическую модель и метод решения, составьте алгоритм решения и представьте их преподавателю.

2. В соответствии с вариантом задания (см. табл. 3) создайте процедуру вычисления значения математического выражения с описанием типов параметров (см. Пример 1).

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

4. Модифицируйте процедуру, добавив в программу блоки логической проверки на критические и недопустимые значения параметров (см. Пример 2).

5. С использованием функции Format() выполните специфицированное форматирование искомого значения созданной Вами функции.

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

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

8. Представьте работу преподавателю.

 

Таблица 3

Варианты заданий

№	Выражение	Диапазон значений и типы данных
		a, c – любое вещественное; b – целое неотрицательное; x - целое положительное; <=100; y – положительное, в %
		a, c – любое целое; x - вещественное; y – положительное, с двумя знаками после точки
		a – отрицательное целое, > -1000; c – положительное вещественное; x – любое целое; y – положительное, в денежном формате, р.
		a – положительное целое, < 10; b – отрицательное вещественное; x – положительное вещественное; y – положительное, без разделителя тысяч
		a – любое вещественное; b – положительное целое, < 2500; x – положительное целое; y – положительное, в %
		a, b – целое неотрицательное; x - целое положительное; <=1000; y – положительное, в денежном формате, р.
		a, b – положительные целые, <12345; x – любое вещественное; y – положительное, с разделителем тысяч
		a – отрицательное вещественное; b – положительное вещественное; x – целое, <10; y – положительное, с двумя знаками после точки
		a – любое целое, <>0; b – любое вещественное; x – положительное вещественное; y – положительное, в денежном формате (р.)
		a – любое вещественное; b – неотрицательное вещественное; x –целое, >-200 и <350; y – положительное, с разделителем тысяч

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

1. Какие типы числовых данных использованы Вами в программе? Чем они отличаются?

2. Объясните смысл использованных в программе выражений. Назовите использованные Вами встроенные функции.

3. В чем заключается отличие между процедурой и функцией?

4. Каким образом может быть организовано обращение к процедуре?

5. * Как можно сохранить используемые значения параметров для последующей обработки?

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

7. * Добавьте в процедуру блоки, реализующие выход из процедуры при нажатии кнопок Cancel.