Основы VBA (visual Basic for Application)

Кафедра экономической теории и моделирования экономических процессов

ИНФОРМАЦИОННЫЕ ТЕХНОЛОГИИ В УПРАВЛЕНИИ

(ЧАСТЬ 2)

Методические указания

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

для студентов направлений

081100.62 «Государственное и муниципальное управление»

очной формы обучения

 

 

Курган 2013

Кафедра: «Экономическая теория и моделирование экономических процессов»

 

Дисциплина: «Информационные технологии в управлении» часть 2

 

 

Составили: ст. преподаватель Филимонов С.М.

ассистент Студентова Е.А.

 

Утверждены на заседании кафедры «28» сентября 2013 г.

 

 

Рекомендованы методическим советом университета

«____» ___________2013 г.

 

 

СОДЕРЖАНИЕ

 

ВВЕДЕНИЕ. 4

1 ОСНОВЫ VBA (VISUAL BASIC FOR APPLICATION) 5

2 ОСНОВЫ РАБОТЫ С БАЗАМИ ДАННЫХ В MS ACCESS. 15

3 БЛОК-СХЕМЫ И АЛГОРИТМИЗАЦИЯ.. 29

СПИСОК ЛИТЕРАТУРЫ.. 39

 

 

ВВЕДЕНИЕ

 

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

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

В результате изучения дисциплины студенты должны иметь представление:

- об основных этапах решения задач с помощью ЭВМ, методах и средствах сбора, обработки, хранения, передачи и накопления информации;

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

Знать:

- основные понятия автоматизированной обработки информации;

- общий состав и структуру персональных ЭВМ и вычислительных систем;

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

Уметь:

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

Практический курс «Экономическая информатика» разделен на две части. Ко второй части курса относятся: изучение основ VBA (Visual Basic for Application), основ работы с базами данных в Microsoft Access, а также закрепление теоретических знаний и получение практических навыков по теме «Блок-схемы и алгоритмизация».

ОСНОВЫ VBA (VISUAL BASIC FOR APPLICATION)

 

Практическое задание 1

Цель – научиться создавать пользовательские функции

Примечание: Одной из возможностей VBA является создание новой функции MS Excel, которую впоследствии можно использовать аналогично встроенным функциям (СУММ, МАКС, ЕСЛИ и др.). Это целесообразно в тех случаях, если необходимой функции нет в стандартном наборе встроенных функций MS Excel, например формулы Пифагора, а ею приходится часто пользоваться.

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

Для этого:

1 Создайте новую книгу MS Excel и перейдите в редактор VB. Для открытия редактора выберите в Меню Разработчик / Код / Visual Basic или нажмите комбинацию клавиш [Alt+F11].

2 Создайте новый модуль, выполнив команду Insert→Module.

3 В новом модуле выполните команду Insert→Procedure, чтобы открыть диалоговое окно «Add Procedure» для создания новой процедуры (рисунок 1).

 

 

Рисунок 1 – Диалоговое окно «Add Procedure»

 

4 В диалоговом окне «Add Procedure» задайте имя «Пифагор» и выберите тип «Function». Нажмите кнопку <ОК>.

5 В окне кода между двумя появившимися строчками напишите программный код для данной функции, учитывая, что для нахождения длины гипотенузы по формуле Пифагора нужно знать значения длин двух катетов a и b:

 

Public Function Пифагор(a, b)

Пифагор = (a^2 + b^2) ^ (1 / 2)

End Function

 

Оператор «^» означает возведение числа в степень.

1 Закройте редактор VB и воспользуйтесь созданной функцией. В ячейки А1, В1 и С1 введите соответственно символы а, b, и с; в ячейки А2 и В2 – значения длин катетов, а в ячейку С2 вставьте функцию, воспользовавшись командой Вставка→Функция и выбрав созданную функцию в категории «Определенные пользователем» диалогового окна «Мастер функций» (рисунок 2).

2 Добавьте к созданной функции описание, поясняющее ее назначение. Для этого выполните команду Меню Разработчик / Код / Макросы и, набрав в поле «Имя макроса» диалогового окна «Макрос» название данной функции, введите описание, нажав кнопку «Параметры» (рисунок 3).

 

 

Рисунок 2 – Результат выполнения функции

 

 

Рисунок 3 – Диалоговые окна «Макрос» и «Параметры макроса»

 

Аналогичным образом создайте функцию, математически определенную как y = sin(πx)e^−2x и постройте ее график.

Для этого:

1 В редакторе VB в новом модуле создайте функцию с именем «Y» и напишите для нее программный код:

 

Public Function Y(x)

Y = Sin(Application.Pi*x)*Exp(-2*x)

End Function

 

Здесь воспользовались стандартной функцией Pi, которая возвращает значение постоянной π. Так как она не является внутренней функцией VBA, то ее необходимо записать в виде Application.Pi.

2 Введите в ячейки А1 и В1 соответственно «х» и «y», в ячейки А2 и А3 – значения х, например, -0,5 и -0,4 соответственно, и с помощью маркера автозаполнения скопируйте значения в ячейки А4:А12.

3 В ячейку В2 вставьте формулу «=Y(A2)» и также с помощью маркера автозаполнения скопируйте ее в ячейки В3:В12.

4 Выделите диапазон ячеек В2:В12 и с помощью Мастера диаграмм постройте график данной функции (подписи по оси Х должны быть – значения х) (рисунок 4).

 

 

Рисунок 4 – Результат выполнения функции и ее график

Практическое задание 2

Цель – научиться создавать диалоговые окна.

Примечание: В VBA имеется два стандартных диалоговых окна для взаимодействия с пользователем:

1 InputBox используется для ввода информации в отдельном диалоговом окне и имеет следующий синтаксис (в квадратных скобках указаны необязательные параметры):

InputBox («Текст сообщения», [«Текст заголовка диалогового окна»]).

2 MsgBox используется в качестве диалогового окна вывода сообщений и имеет синтаксис:

MsgBox «Текст сообщения», [Кнопки + Иконки], [«Текст заголовка диалогового окна»],

где Кнопки + Иконки – параметры, задающие отображаемые кнопки и значки диалогового окна.

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

Для этого:

1 Создайте новую книгу MS Excel и перейдите в редактор VB.

2 Создайте новый модуль, выполнив команду Insert→Module.

3 В новом модуле выполните команду Insert→Procedure, чтобы открыть диалоговое окно «Add Procedure» для создания новой процедуры.

4 В диалоговом окне «Add Procedure» задайте имя «Pythagor» и выберите тип «Sub» (рисунок 5). Нажмите кнопку <ОК>. Процедуры, в отличие от рассмотренных ранее функций, не возвращают значений, а только выполняют последовательность действий.

 

 

Рисунок 5 – Диалоговое окно «Add Procedure»

 

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

 

Public Sub Pythagor()

a = InputBox(«Введите длину катета a»)

b = InputBox(«Введите длину катета b»)

c = (a ^ 2 + b ^2) ^ (1 / 2)

MsgBox («Длина гипотенузы равна» & c)

End Sub

 

Оператор «&» объединяет две строки.

6 Выполнение процедур происходит аналогично выполнению записанных макросов. Поэтому создайте командную кнопку на рабочем листе для запуска процедуры (рисунок 6).

 

Рисунок 6 – Диалоговые окна при выполнении процедуры

 

Также запустить диалоговые окна можно из редактора VBA, выполнив команду Меню Run / Run Sub/UserForm. Появится диалоговое окно выбора макроса – выберите название вашей процедуры и нажмите кнопку Run. Также в редакторе можно нажать F5 на клавиатуре для быстрого запуска процедуры.

Практическое задание 3

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

Примечание: VBA позволяет создавать и использовать экранные формы, разработанные пользователем. Такие формы представляют собой объекты класса UserForm. Для создания новой формы пользователя необходимо в MS Excel перейти в редактор VB и выполнить команду Insert→UserForm. В результате откроется окно конструктора форм (рисунок 7). При создании формы автоматически отображается панель элементов управления «Toolbox», содержащая кнопки, с помощью которых элементы управления можно разместить на создаваемой форме (аналогично тому, как командные кнопки размещались на рабочем листе). Если данная панель инструментов не отображается, выполните команду View→ToolBox.

После размещения элемента управления на форме с помощью окна свойств (Properties) обычно задаются свойства выделенного объекта. В случае, если окно неактивно вызвать его можно в меню View→Properties Window, или нажав на клавиатуре F4.

 

Рисунок 7 – Окно конструктора форм

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

Для этого:

1 Откройте рабочую книгу, содержащую функцию «Пифагор». Обратите внимание, что если вы решили создавать форму в новой книге, то функцию Пифагора нужно будет прописать заново, иначе расчет станет невозможным.

2 Перейдите в редактор VB и выполните команду Insert→UserForm для создания нового макета пользовательской формы.

3 Выделите форму, щелкнув по ней левой кнопкой мыши, в окне «Properties» найдите свойство «Caption» (данное свойство хранит заголовок формы, текст на кнопке и т.п., т.е. текст, связанный с объектом) и установите для него значение «Теорема Пифагора».

4 Добавьте на форму объект «CommandButton» (кнопка), три объекта «TextBox» (текстовое поле) и три объекта «Label» (метка, текст на форме).

5 Для удобства обращения к объектам, измените их имена. Для этого необходимо изменить свойство «Name». Объекту TextBox1 задайте имя «a», TexxtBox2 – «b», TextBox3 – «c».

6 Для объекта Label1 установите значение свойства Caption – «a=», для Label2 – «b=», для Label3 – «c=», для CommandButton1 – «Вычислить» (рисунок 8).

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

8 В окне кода между двумя появившимися строчками напишите программный код для данной кнопки:

 

Private Sub CommandButton1_Click()

c.Text = Пифагор(a.Text, b.Text)

End Sub

 

Свойство «Text» хранит текст, введенный в текстовые поля.

 

 

Рисунок 8 – Конструктор формы «Теорема Пифагора»

 

9 Перейдите на «Лист1» и создайте кнопку для открытия формы. Перейдите в режим конструктора, выбрав в Меню Разработчик / Элементы управления / Режим конструктора. Выделите текст данной кнопки и переименуйте ее, введя название «Открыть форму».

10 Перейдем в редактор VB, выделив кнопку и выбрав в Меню Разработчик / Элементы управления / Просмотр кода. В появившемся модуле «Лист1» введите программный код:

 

Private Sub CommandButton1_Click()

UserForm1.Show

End Sub

 

В результате при нажатии на кнопку будет отображаться форма «Теорема Пифагора» (для этого нужно выйти из режима конструктора). Форму также можно отобразить из редактора VB, когда открыт конструктор форм, командой Run→Run Sub/UserForm или нажав на клавиатуре F5.

 

Практическое задание 4

Цель – решение задач с условием с использованием функции если.

 

Для организации процесса вычислений в зависимости от какого-либо Условия служит условный оператор If/Then/Else.

Если в зависимости от некоторого Условия, необходимо выполнить только одно действие, то используется однострочная форма оператора If/Then/Else - If Условие Then Оператор1 [Else Оператор2].

Если Условие выполняется, то выполняется Оператор1, в противном случае выполняется Оператор2.

Если в зависимости от некоторого Условия необходимо выполнить только несколько действий, то используется многострочная форма оператора If/Then/Else:

If Условие Then

БлокОператоров1

[Else

БлокОператоров2]

End If

 

Если Условие выполняется, то выполняется БлокОператоров, в противном случае, выполняется БлокОператоров2.

Блоки операторов могут содержать сколько угодно операторов.

Может возникнуть ситуация, когда при невыполнении Условия требуется проверить еще одно Условие. В этом случае используется следующая многострочная форма:

 

If Условие1 Then

БлокОператоров1

[ElseIf Условие2 Then

БлокОператоров2]

……….....................

[Else

БлокОператоров]

End If

 

Если Условие1 выполняется, то выполняется БлокОператоров1, в противном случае проверяется Условие2. Если оно выполняется, то выполняется БлокОператоров2 и т.д. Если ни одно из Условий не выполняется, то выполняется БлокОператоров. В данной конструкции может содержаться сколько угодно блоков ElseIf.

Условие может быть как простым (например, a > 5), так и составным (например, a > 5 и b > 2). Для объединения простых Условий используются логические операторы And (И – два или более Условий выполняются одновременно), Or(ИЛИ – выполняется хотя бы одно из Условий), Not (НЕ – отрицание Условия).

Решим следующую задачу: На банковский вклад начисляются проценты в сумме 20 % годовых, если сумма вклада превышает 200 000 р., 15 % годовых, если сумма от 100 000 до 200 000 р., 10 % годовых - на суммы до 50 000 р., 12 % годовых - на остальные суммы. Рассчитать сумму полученных вкладчиком процентов по истечении срока.

Для решения поставленной задачи запустим редактор VBA и создадим новую процедуру (Insert – Module; Insert – Procedure; тип Function). Назовем ее «Процент». Предполагается, что пользователь должен ввести значение суммы (назовем сумму переменной S), а программа в зависимости от этой суммы рассчитать сумму вклада с учетом того, какой процент на данную сумму должен быть начислен.

 

Public Function Процент (S)

If S > 200000 Then

Процент = S + S * 0.2

ElseIf S <= 200000 And S > 100000 Then

Процент = S + S * 0.15

ElseIf S < 50000 Then

Процент = S + S * 0.1

Else

Процент = S + S * 0.12

End If

End Function

 

Решение задачи на листе Excel будет выглядеть следующим образом.

Рисунок 9 – Решение задачи с использованием функции Процент

 

Аналогичным образом пропишите в VBA и оформите на листе Excel следующую задачу: Рассчитать стоимость заказа в типографии, если действуют следующие расценки: печать до 100 экземпляров – 10 р. за лист; от 100 до 1000 экземпляров – 7 р. за лист; свыше 1000 – 5 р. за лист. Пропишите формулу и рассчитайте на рабочем листе Excel стоимость печати для 50, 150, 500, 900, 1000, 1200 и 1500 экземпляров.