9.2Программирование в среде VBA
9.2.1Настройка рабочей среды редактора Visual Basic
|
& Настройка рабочей |
среды |
редактораVisual |
Basic |
предполагает |
|
|||
активизацию необходимых окон (рис. 9.8): |
|
|
|
|
|||||
· |
окно проекта Project – по умолчанию располагается в левом верхнем углу |
|
|||||||
редактора |
и |
отображает |
иерархическую |
структуру |
проектов , |
файло |
|||
открытых в данный момент, и объектов, содержащихся в этих файлах. |
|
||||||||
· |
окно свойств Properties – располагается в левом нижнем углу редактора и |
|
|||||||
содержит свойства объекта, выделенного в окне Project. |
|
|
|
||||||
· |
окно программного кода Code – |
открывается |
при |
создании модуля |
в |
||||
правой части окна редактора и отображает программный код выделенного модуля. Если оно распахнуто, то его название не видно.
Рис. 9.8 Окно редактора Visual Basic, открытое в приложении Excel
163
Задание 1. Изучить интерфейс и настроить рабочую среду редактораVisual
Basic. В |
папке Программирование создать книгуVBA_Excel.xlsm. В |
|||
проекте |
этой |
книги создать два модуля с именамиПодпрограммы и |
||
Функции для последующего размещения в них процедур. |
|
|||
|
|
Р е к о м е н д а ц и и п о в ы п о л н е н и ю |
|
|
· Откройте |
табличный процессорExcel и |
сохраните |
созданную по |
|
умолчанию книгу с именем VBA_Excel.xlsm (тип файла выбрать Книга Excel |
||||
с поддержкой макросов) в папке Программирование. |
|
|||
· Откройте |
редактор Visual Basic кнопкой |
[Visual Basic] |
на вкладке |
|
Разработчик в группеКод. В открывшемся окне редактора могут быть открыты различные окна.
·Закройте в окне Microsoft Visual Basic все вложенные окна.
·Активизируйте окно проектаProject (рис. 9.9) командой View/Project Explorer.
·Активизируйте окно свойствProperties командой View/ Properties Window.
· Перейдите из редактораVisual Basic в окно приложения с |
помощью |
кнопки с изображением значка приложения(для Excel – кнопка |
[View |
Microsoft Excel]) или с помощью кнопок приложений на панели задач. |
|
Примечание! Не путать кнопку 
с кнопками закрытия окон 
.
·Создайте в проекте рабочей книги VBA_Excel.xlsm два модуля:
-снова перейдите в редактор Visual Basic;
- в окне Project выделите проект VBAProject(VBA_Excel.xlsm) и
вставьте модуль командой Insert/Module;
-в окне Properties на вкладке Alphabetic в поле (Name) введите новое имя модуля – Подпрограммы и нажмите [Enter];
-аналогично вставьте второй модуль и задайте ему имяФункции. В
результате окно редактора будет иметь вид, представленный на рис. 9.9.
Примечание. Для удаления созданного модуля, необходимо щелкнуть на нем правой
164
клавишей мыши и в контекстном меню выбрать командуRemove Module. В открывшемся далее окне нажать кнопку [Нет].
·Закройте редактор Visual Basic.
·Сохраните книгу VBA_Excel.
9.2.2 Создание процедур-подпрограмм
& Каждый модуль проекта может содержать одну или несколько программ, называемых процедурами, которые складываются из инструкций.
Чаще всего используются процедуры-подпрограммы и процедуры-функции.
Процедуры-подпрограммы включают любые инструкции ограничиваются инструкциями Sub и End Sub. В общем случае синтаксис записи процедуры-подпрограммы имеет вид:
Sub Имя (аргументы)
|
инструкции |
|
|
|
|
End Sub |
|
|
|
Создание |
процедур-подпрограмм |
рассмотрим |
на |
примерах |
приложениях Excel и Word.
Задание 2. В книге VBA_Excel в модуле Подпрограммы создать
программу, которая вычисляет среднее значение любых трех чисел. Ввод исходных данных и вывод результата осуществить с помощью диалоговых окон.
Р е к о м е н д а ц и и п о в ы п о л н е н и ю
·В книге VBA_Excel откройте редактор Visual Basic.
·В модуле Подпрограммы создайте процедуру согласно заданию:
- выделите модуль Подпрограммы и вставьте процедуру командой
Insert/Procedure;
- в открывшемся окнеAdd Procedure в полеName укажите имя процедуры – Среднее; в группе переключателейType выберите тип процедуры – Sub (процедура-подпрограмма); в группе переключателей
165
Scope выберите вид доступа к процедуре– Public (доступна для всех модулей текущего проекта) и нажмите [OK]. В результате в окне программного кода модуля появится заготовка для новой процедуры;
- введите текст процедуры(рис. 9.9) между открывающей Public Sub
Среднее() и закрывающей End Sub инструкциями.
Примечание. |
При вводе текста программы определенные термины |
выделяются |
||||||
различным цветом в зависимости от их назначения. После нажатия клавиши [Enter] в |
||||||||
конце |
строки |
выполняется |
проверка |
синтаксиса |
введенной |
.инстПрукции |
||
обнаружении |
ошибки строка |
выделяется |
красным цветом и на |
экран |
выводитс |
|||
сообщение, поясняющее ее характер. В открывшемся окне, сообщающем об ошибке, нажмите кнопку [ОК]. Ошибочный оператор будет выделен.
Рис. 9.9 Окно кода модуля Подпрограммы с процедурой Среднее
& Пояснения по содержанию программы:
Комментарии – пояснительный текст, начинающийся с символа апостроф ( ' ) и выделяемый в программе зеленым цветом;
Оператор Dim – служит для объявления переменных. Переменная – это имя, которое дается области компьютерной памяти, используемой для временного хранения данных. Ключевое слово As связывает имя переменной с типом данных. Тип данных определяет множество допустимых значений,
166
которое может принимать переменная, |
например тип Integer используется |
||||
для хранения |
целых чисел |
от-32768 до +32767; тип Double – для чисел с |
|||
плавающей точкой примерно от -1,79е308 до 1,79е308. |
|
||||
Инструкция присваивания p=(a1+a2+a3)/3 –вычисляет среднее значение |
|||||
и присваивает результат переменной p. |
|
|
|
||
Функция |
InputBox |
организует |
диалог с пользователем. Функция |
||
MsgBox – организует вывод указанных данных в диалоговом окне. |
|
||||
· Запустите |
процедуру Среднее нажатием клавиши[F5] или |
командой |
|||
меню Run/Run Sub/UserForm или |
|
|
|
||
одноименной кнопкой |
|
панели |
|||
инструментов. В появившемся окне введите первое число, например 8, и
нажмите [ОК]. Затем в следующих окнах введите еще два числа, например 2
и 10, нажимая после ввода[ОК]. В появившемся окне с результатом выполнения программы нажмите [ОК].
Примечание. При обнаружении ошибки в программе на экран выводится сообщение о ее характере. В окне сообщения нужно нажать[ОК] или [Debug] (в зависимости от вида ошибки). В коде программы ошибочный оператор выделится желтым цветом. Нужно
остановить работы программы кнопкой
[Reset], исправить ошибку и запустить программу еще раз.
· Закройте окно редактора и сохраните книгу VBA_Excel. |
|
Задание 3. В книге VBA_Excel в модуле Подпрограммы |
создать |
программу, которая рассчитывает сумму вознаграждения сотрудника в зависимости от вводимого в диалоговом режиме значения объема продаж по условию: при объеме продаж на сумму до600 000 руб. вознаграждение составляет 1%; более 600 000 руб. – 2%. Вывод результата осуществить в диалоговом окне.
Р е к о м е н д а ц и и п о в ы п о л н е н и ю
·В книге VBA_Excel откройте редактор Visual Basic.
·В модуле Подпрограммы создайте новую процедуру-подпрограмму
Вознаграждение (рис. 9.10).
167
Рис. 9.10 Окно кода модуля Подпрограммы с процедурой Вознаграждение
&Пояснения по содержанию программы:
·функция InputBox организует диалог с пользователем;
· в |
процедуре |
выбор |
действий, зависящих |
от |
некоторых |
условий, |
осуществляется условным оператором If (аналогичен встроенной функции
Если в Excel), который может использоваться в различных конструкциях
(табл. 9.3).
Таблица 9.3
Синтаксические конструкции оператора If
Вид оператора If |
|
Алгоритм выполнения |
|
|
|
|
|||
If (условие) Then |
Используется для проверки одного условия: |
|
|||||||
операторы1 |
если |
условие |
|
истинно, |
то |
выполняются |
|
||
Else |
операторы1; |
а |
если условие |
ложно, |
то |
|
|||
операторы2 |
выполняются операторы2. Ветвь Else |
может |
|
||||||
End If |
отсутствовать. |
|
|
|
|
|
|
|
|
If (условие1) Then |
Используется |
|
для |
проверки |
нескольких |
||||
операторы1 |
условий: |
если условие1 |
истинно, |
то |
|||||
ElseIf (условие2) Then |
выполняются |
операторы1; а если условие1 |
|||||||
операторы2 |
ложно, то проверяется условие2 и т.д. Если ни |
||||||||
ElseIf (условие3) Then |
одно |
из |
|
проверяемых |
|
условий |
|||
операторы3 |
выполняется, то работают операторы. |
|
|
|
|||||
Else |
|
|
|
|
|
|
|
|
|
операторы |
|
|
|
|
|
|
|
|
|
End If |
|
|
|
|
|
|
|
|
|
168
В рассматриваемой процедуре используется оператор If первого вида.
· Запустите процедуру Вознаграждение. На экране появится диалоговое окно, в котором введите значение объема продаж, например 25 800, и
нажмите [ОК]. В открывшемся окне с результатом нажмите [ОК].
·Проверьте работу программы на других примерах.
·Закройте окно редактора и сохраните книгу VBA_Excel.
Задание 4. В книге VBA_Excel в модуле Подпрограммы создать
программу, которая вычисляет сумму значений ячеек 1:АА9 и записывает результат в ячейку А10.
Р е к о м е н д а ц и и п о в ы п о л н е н и ю
· В книге VBA_Excel перейдите на новый лист, заполните ячейки А1:А9
произвольными числами.
·Откройте редактор Visual Basic.
·В модуле Подпрограммы создайте новую процедуру-подпрограмму
СуммаЯчеек (рис. 9.11).
Рис. 9.11 Окно кода модуля Подпрограммы с процедурой СуммаЯчеек
& Пояснения по содержанию программы:
В процедуре организация циклов с фиксированным числом шагов осуществляется оператором цикла FOR, который используется в следующей
169
конструкции:
FOR счетчик=начало Tо конец Step приращение
операторы
Next счетчик
Переменная счетчик задает количество циклов выполнения операторов.
Она изменяется от значенияначало до значения конец с шагом приращение.
Запись Step приращение может отсутствовать при шаге 1.
Доступ к содержимому ячеек можно осуществлять с помощью объекта
Cells(i,j), где i – номер строки, j – номер столбца, на пересечении которых находится ячейка.
·Запустите процедуру СуммаЯчеек, перейдите в Excel и просмотрите результат.
·Закройте окно редактора и приложения Excel, сохранив книгу VBA_Excel.
Задание 5. В документе VBA_Word в модулеПодпрограммы создать доступную для всех документов программу, которая в текстовом документе знаки длинное тире «—» заменяет на знаки короткого тире «–».
Р е к о м е н д а ц и и п о в ы п о л н е н и ю
·Откройте текстовый процессор Word.
·Сохраните созданный по умолчанию документ с именем VBA_Word.docm
в папке Программирование.
·Откройте редактор Visual Basic.
·Активизируйте окна проекта Project и свойств Properties, если их нет.
·Создайте в проекте VBA_Word новый модуль Подпрограммы.
· В |
модуле Подпрограммы |
создайте |
процедуру-подпрограмму |
ЗаменаТире (рис. 9.12).
170
Рис. 9.12 Окно кода модуля Подпрограммы с процедурой Word_VBA
& Пояснения по содержанию программы:
Инструкция ActiveDocument.Characters.Count считает число символов в текущем документе.
Инструкция ActiveDocument.Characters(i) выбирает i-ый символ.
Инструкция ActiveDocument.Characters(i).Select выделяет i-ый символ.
Функция Chr(n) возвращает строку из одного, с
соответствующего коду символаn. (n принимает значения от0 до 255).
Например, Chr(11) = «¿», Chr(13) = «¶».
Примечание. Код символа можно просмотреть в полеКод знака окна Символ (открывается на вкладкеВставка), выбрав в спискеШрифт – (обычный текст), в списке из – ASCII(дес.)
Инструкция Selection.Text заменяет выделенный текст.
·Проверьте работу программы:
-перейдите из редактора Visual Basic в окно приложения;
-скопируйте в документ Word с любой Web-страницы фрагмент текста,
содержащего длинные тире (—) или вставьте эти знаки в текст командой
Вставка/Символ;
-выделите текст и запустите процедуру ЗаменаТире из окна Макрос.
·Закройте приложение Word, сохранив документ.
171
9.2.3 Создание процедур-функций
& При работе вExcel часто возникает необходимость в вычислениях,
для которых нет подходящей функции. В этом случае можно создать
пользовательскую |
функцию. Пользовательские |
функции |
работают |
аналогично встроенным функциям и могут |
использоваться |
только |
|
формулах ячеек рабочего листа. Отличие этих |
функций от командных |
||
макросов состоит в |
том, что они используют значения, передаваемые их |
||
аргументам, производят некоторые вычисления и возвращают результат в точку вызова.
Пользовательские функции Excel - это VBA процедуры-функции,
которые ограничиваются инструкциямиFunction и End Function. Внутри обязательно должна находиться инструкция присваивания некого значения имени функции. Имя функции должно быть уникальным, не содержать пробелов и символов пунктуации.
В общем случае синтаксис записи процедуры-функции имеет вид:
Function Имя ([аргументы])
инструкции
Имя_функции=выражение
End Function
Задание 6. В книге VBA_Excel в модуле Функции создать
пользовательскую функцию СуммаСкидки для расчета в таблице Сведения о продажах (рис. 9.13) суммы скидки в зависимости от выручки от продаж по условию: для выручки на сумму до 200 000 руб скидка не начисляется; на сумму от 200 000 до 300 000 руб – скидка 3%; от 300 000 до 400 000 руб –
5%; от 400 000 до 500 000 руб – 6%; более 500 000 руб – 8%.
Р е к о м е н д а ц и и п о в ы п о л н е н и ю
· В книге VBA_Excel создайте таблицу Сведения о продажах (рис. 9.13).
172
|
|
Рис. 9.13 Таблица Сведения о продажах |
|
|
||
· |
Для |
столбцов Цена товара и Сумма |
скидки установите |
денежный |
|
|
формат с отображением двух знаков после запятой. |
|
|
||||
· |
Откройте редактор Visual Basic. |
|
|
|
||
· |
В |
модуле Функции |
вставьте |
процедуру-функцию |
с |
име |
СуммаСкидки, типа – Function, вида доступа к процедуре – Public. |
|
|
||||
· |
Введите аргументы функции СуммаСкидки() – Продажа и Цена и текст |
|
||||
процедуры (рис. 9.14). |
|
|
|
|
||
Рис.9.14 Окно кода модуля Функции с процедурой СуммаСкидки
173
Примечание. Аргументы функции перечисляются через запятую.
·Закройте редактор Visual Basic.
·Вычислите сумму скидки в таблице:
-в ячейку D4 командой Вставка/Функция из категории Определенные пользователем вставьте функцию СуммаСкидки и нажмите [ОК];
-в окне Аргументы функции в полеПродажа введите ссылкуВ4
щелчком мыши по этой ячейке на рабочем листе, в поле Цена – С4 и
нажмите [ОК]. В результате в ячейке D4 появится формула:
|
|
=СуммаСкидки(B4;C4) |
|
|
||
- скопируйте |
формулу |
из |
ячейкиD4 в |
диапазон D5:D9 |
с |
|
использованием маркера автозаполнения. |
|
|
||||
· Сохраните книгу VBA_Excel. |
|
|
|
|
||
Задание 7. |
Продемонстрировать |
|
преподавателю |
результаты |
рабо |
|
процедур Среднее, Вознаграждение и СуммаЯчеек, использование |
||||||
функции |
СуммаСкидки в |
файлеVBA_Excel и |
результаты |
работы |
||
процедуры ЗаменаТире в файле VBA_Word. |
|
|
||||
Задания для самостоятельной работы |
|
|
||||
Задание 8. |
В Excel создать программу, которая рассчитывает сумму скидки |
|||||
на покупку мебели по вводимому в диалоговом режиме значению суммы покупки по условию: при покупке на сумму до2 000 000 руб. скидка составляет 2%; более 2 000 000 руб. – 4%. Вывод результата осуществить в диалоговом окне.
Задание 9. В Excel создать программу, которая вычисляет среднее значение чисел, расположенных в ячейках В1:В10 и заносит результат в ячейку В12.
Индивидуальные задания
1. В среде Word создать программу, которая заменяет в документе символ X 174
на символ Y согласно варианту, заданному преподавателем из табл. 9.4.
Таблица 9.4
Заменяемые символы
|
Вариант |
|
Символ X |
|
Символ Y |
|
|
1 |
|
! |
|
¡ |
|
|
2 |
|
® |
|
æ |
|
|
3 |
|
* |
|
¤ |
|
|
4 |
|
? |
|
¿ |
|
|
5 |
|
+ |
|
× |
|
|
6 |
|
= |
|
~ |
|
|
7 |
|
„ |
|
” |
|
|
8 |
|
a |
|
@ |
|
|
9 |
|
S |
|
$ |
|
|
10 |
|
Y |
|
Ÿ |
|
|
11 |
|
^ |
|
ˆ |
|
|
12 |
|
~ |
|
˜ |
|
|
13 |
|
\ |
|
/ |
|
|
14 |
|
‰ |
|
% |
|
|
15 |
|
| |
|
¦ |
|
2. В табличном |
процессореExcel создать |
таблицу Ведомость со |
||||
столбцами: ФИО, |
Номер бригады, Вид |
детали, Количество |
||||
деталей, шт., Стоимость деталей, руб, Премия, руб. Заполнить 4
столбца таблицы исходными данными согласно варианту, заданному преподавателем из табл. 9.5, с учетом следующего:
-работают две бригады рабочих;
-общее количество работников 5 чел (один из рабочих должен носить вашу фамилию);
-рабочие производят детали трех видов– А, В, С (внесите конкретные названия);
-стоимость одной детали вида А – X руб, вида В – Y руб, вида С – Z
руб;
-каждый рабочий производит детали одного вида.
3.Создать пользовательскую функцию Стоимость_деталей для вычисления
значений столбца Стоимость деталей, руб. Стоимость деталей
175
рассчитывается как произведение количества на стоимость одной детали
заданного вида.
4.Создать пользовательскую функцию Премия для расчета размера премии по условию: при стоимости произведенных деталей на сумму большеN
|
руб. премия составляет 10% от этой суммы для рабочихI бригады и 12% |
||||||||
|
для рабочих II бригады. |
|
|
|
|
||||
5. |
Используя |
пользовательскую |
функциюПремия |
заполнить столбец |
|||||
|
Премия, руб. расчетными формулами. |
|
|
|
|||||
6. |
Обеспечить подведение итоговых сумм премий по каждой бригаде. |
||||||||
|
|
|
|
|
|
|
|
Таблица 9.5 |
|
|
|
|
Исходные данные для таблицы Ведомость |
||||||
|
|
|
|
|
|
|
|
|
|
|
|
Вариант |
X |
|
Y |
Z |
N |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
1 |
|
10 |
|
12 |
15 |
500 |
|
|
|
2 |
|
11 |
|
13 |
16 |
600 |
|
|
|
3 |
|
15 |
|
17 |
21 |
520 |
|
|
|
4 |
|
14 |
|
16 |
20 |
700 |
|
|
|
5 |
|
17 |
|
15 |
14 |
570 |
|
|
|
6 |
|
11 |
|
17 |
14 |
550 |
|
|
|
7 |
|
12 |
|
13 |
17 |
650 |
|
|
|
8 |
|
20 |
|
17 |
21 |
700 |
|
|
|
9 |
|
14 |
|
18 |
19 |
500 |
|
|
|
10 |
|
19 |
|
15 |
9 |
530 |
|
|
|
11 |
|
11 |
|
11 |
15 |
510 |
|
|
|
12 |
|
13 |
|
16 |
14 |
460 |
|
|
|
13 |
|
19 |
|
20 |
19 |
540 |
|
|
|
14 |
|
14 |
|
19 |
22 |
600 |
|
|
|
15 |
|
13 |
|
14 |
21 |
590 |
|
176