Тема 8. VBA: Процедуры. Создание пользовательских форм
С помощью VBA можно:
· создать собственное диалоговое окно для ввода или вывода информации;
· изменить меню приложений пакета Microsoft Office;
· управлять другими приложениями MS Office;
· объединить данные из нескольких приложений пакета MS Office в одном документе;
· создавать или изменять web - страницы.
В данном пособии уделено большое внимание непосредственному созданию пользовательских процедур для обработки объектов Excel, проектированию пользовательских форм и управлению ими.
Основными понятиями VBA, как и любого ООЯП являются объект, метод, свойство и событие. Поскольку в функции не могут содержаться команды, связанные с обработкой объектов, именно в этом разделе остановимся на основных понятиях языка.
Объектом VBA считается видимый элемент, имеющий представление на экране, на который можно некоторым образом воздействовать и изменять его характеристики.
Методом является действие, выполняемое над объектом, например удаление, копирование.
Свойство – это атрибут объекта, определяющий его характеристики, например тип шрифта, заливка ячейки.
Событие – действие, распознаваемое объектом или средой программирования, на который можно запрограммировать отклик, например щелчок мышкой, нажатие на клавишу, перемещение курсора мыши, наступление момента времени.
Суть программирования на VBA как раз и заключается в этих двух понятиях: событие и отклик на него. Поэтому действия, происходящие в системе, являются событиями, отклики на них – процедурами.
Процедура – это самостоятельная часть кода, которая имеет имя и может содержать аргументы, выполнять последовательность инструкций и изменять значения своих аргументов. Как правило, процедура предназначена для решения конкретной задачи.
Процедура имеет вид:
[Private]/ [Public] Sub имя [(список аргументов)]
[инструкции]
[Exit Sub]
[инструкции]
End Sub
Private – указывает на доступность процедуры другим процедурам только данного модуля; Public - указывает на доступность процедуры другим процедурам во всех модулях.
В проекте (комплекс объектов книги, форм и модулей) автоматически создаются модули для каждого листа книги и книги в целом. Пользователь также имеет возможность добавлять модули для создания процедур. Кроме того, модули автоматически создаются для каждой формы.
Пользовательская форма (UserForm) представляет собой диалоговое окно в стиле Windows-интерфейса для ввода-вывода информации с помощью различных ЭУ.
Сами процедуры создаются аналогично функциям. Для создания процедур активизируется редактор VBA (Alt + F11), для записи процедур добавляется в проект модуль (Insert/Module), при необходимости создания диалогового окна выполняется команда Insert/ UserForm.
Пример 5. Разберем задачу автоматизированного формирования расчетного листа средствами VBA.
Добавив в проект пользовательскую форму, спроектируем ее путем добавления различных ЭУ (рис. 13) и изменением свойств всех объектов в окне свойств (View/ Properties Window).
Для создания процедур обработки событий (нажатие на кнопки, выбор ФИО) на соответствующем ЭУ или самой форме требуется выполнить двойной щелчок. На рис.14 представлены необходимые нам процедуры для решения поставленной задачи.
Рис. 13. Пользовательская форма для формирования расчетного листа.
Для возможности решения задачи (выполнения процедуры) с помощью панели инструментов на соответствующем рабочем листе необходимо создать кнопку (Разработчик/ Вставить/ ЭУ формы/ Кнопка). При появлении диалогового окна (рис. 15) можно изменить название макроса, далее активизируем кнопку Создать для записи команд языка.
Рис.14. Процедуры формы
Процедура обработки такого события, как нажатие на кнопку, создается автоматически. Ее требуется дополнить всего одной командой (рис. 16).
На рис. 17 представлена сама кнопка и результаты работы процедур по автоматизированному формированию расчетного листа. Название кнопки на «Расчетный лист» изменяется по контекстному меню.
Рис.15 Создание макроса.
Рис. 16. Процедура активизации формы.
Рис. 17. Кнопка активизации формирования расчетного листа
В Excel существует возможность упростить создание процедур с помощью макрорекордера. Макрорекордер - магнитофон для записи макроса (процедуры), работающий по принципу «делай, как я». Все действия пользователя протоколируются и в дальнейшем могут быть проиграны. Т.е., Excel записывает все производимые над рабочим листом действия и интерпретирует их как последовательность команд языка. Данный метод позволяет быстро освоить язык VBA, изучая текст созданных процедур. Макрорекордер незаменим при необходимости форматирования объектов.
Алгоритм работы с макрорекордером:
· начать запись (Разработчик/ Запись макроса);
· выполнить требуемые действия;
· остановить запись (Разработчик / Остановить запись)
Перейдя в редактор VBA, можно проанализировать записанные макрорекордером команды.
Индивидуальное задание 9.Самостоятельно изучить работу макрорекордера и создать с его помощью процедуру форматирования расчетного листа. Команду вызова процедуры (Call имя процедуры) добавить в качестве последней команды в процедуру формирования расчетного листа Private Sub CommandButton2_Click (см. рис. 14).
Пример 4. С помощью VBA автоматизируем процесс начисления заработной платы.
Вначале добавим в проект пользовательскую форму (UserForm2) и спроектируем ее (рис. 18). При этом элемент Label2 (расположен ниже поля ФИО) будет предъявлять шифр сотрудника при выборе его фамилии. Элемент Label5 необходим для отображения показаний счетчика, позволяющего редактировать количество иждивенцев. ЭУ Label12, Label13 и Label14 предназначены для вывода суммы, дохода нарастающим итогом и НДФЛ.
Далее создадим процедуры по обработке различных событий, связанных как с самой формой, так и содержащимися в ней ЭУ (рис. 19 и 20).
Аналогично предыдущей задаче, на рабочем листе сформируем кнопку и запишем макрос, предъявляющий UserForm2 на экран (рис. 17).Далее изменим название кнопки.
Индивидуальное задание 10.Самостоятельно изучить процедуры, реализующие решение задачи и выполнить данную задачу.
Рис. 18. Пользовательская форма для начисления зарплаты
Процедура с именем Private Sub UserForm_Initialize() подготавливает форму к предъявлению на экран: очищает поля ввода со списком для выбора полей ФИО и Месяц, Label2 (шифр сотрудника) и Label5 (количество иждивенцев). Кроме того, заполняет Combobox1 фамилиями сотрудников и Combobox2 названиями месяцев.
В табл. 9 приведены программные имена используемых объектов.
Таблица 9. Отдельные объекты VBA
| № | Программное имя | Название, суть |
| Sheets("Название") | Лист рабочей книги с указанным названием | |
| Лист5 | Лист рабочей книги, указанный по счету с начала книги | |
| Cells(i, j) | Ячейка, где i – номер строки, j – номер столбца | |
| UserFormN | Пользовательская форма с номером N |
Рис. 19. Текст процедур (часть 1)
Рис. 20. Текст процедур (часть 2)
Тема 9. Процедура Workbook_Open()
Событие Workbook_Open() является стандартным событием рабочей книги. Код его обработки выполняется непосредственно при открытии файла рабочей книги, перед выводом на экран пользовательского интерфейса. Наиболее распространенными примерами применения данного события являются проверка имени пользователя и его прав на доступ к рабочей книге, создания пользовательских меню или панелей управления, активизация определенного листа в книге. Процедура по обработке данного события создается на модуле объекта Excel ЭтаКнига.
Пример 5. Разберем процедуру, автоматически выполняющуюся при открытии книги. Вводится пароль и имя пользователя. При верном пароле анализируется время, в зависимости от времени суток отображаются различные приветствия с именем пользователя. При неверном пароле выводится сообщение об этом, приложение закрывается.
Рис. 22 Форма для идентификации пользователя
Ниже представлены некоторые команды, которые могут быть использованы при решении задачи (табл. 10).
Таблица 10. Команды VBA
| Команда | Действие |
| Application.Quit | Закрыть приложение (Excel) |
| ThisWorkBook. Close | Закрыть рабочую книгу |
| Sheets(“Лист1”).Activate | Активизировать Лист1 |
| Application.Wait Now + TimeSerial(0, 0, k) | Осуществить задержку работы процедуры на k секунд. |
Рис. 23. Процедура
Индивидуальное задание 11.Спроектируйте форму и запишите соответствующие процедуры для решения задач, представленных в табл. 11. Номер варианта определяется преподавателем.
Таблица 11. Создание процедуры Workbook_Open()
| N | Задача |
| Ввод с клавиатуры имени пользователя. Проверить, если данное имя имеется в списке зарегистрированных пользователей (список находится на одном из листов рабочей книги), вывести приветствие, содержащее имя пользователя. | |
| Ввод пароля и имени. Если пароль неверный, имя – не является одним из зарегистрированных (список находится на одном из листов рабочей книги), вывести сообщение об отказе в доступе и закрыть приложение | |
| Ввод пароля. Если длина пароля соответствует установленной длине (например, пяти) и в нем встречается заранее известный символ (например, «я»), вывести форму с приветствием, содержащую текущий год и месяц в символьном виде (например, Июнь). | |
| Ввод имени пользователя и пароля. Если имя и пароль между собой совпадают, отобразить приветственное сообщение, содержащее имя и время года (например, «Привет, имя! За окном осень»), в противном случае книгу закрыть. | |
| Ввод имени пользователя и пароля. При неверном пароле или при верном пароле и текущем выходном дне вывести форму с отказом в доступе, текущей датой и днем недели, затем книгу закрыть. | |
| Ввод с клавиатуры имени пользователя и даты его рождения. Если возраст пользователя меньше 18 лет, вывести в форме сообщение «Имя, доступ отказан» и закрыть книгу. | |
| Ввод с клавиатуры имени пользователя и текущей даты. Если день – будний, в форме вывести «Привет,<имя>!» и активизировать Лист2. В противном случае вывести «В выходные надо отдыхать!» и закрыть книгу. | |
| Ввод пароля. При верном пароле отразить приветствие, содержащее день недели и месяц в символьном виде (например, Среда, Декабрь). В противном случае – отказ в доступе, и закрытие приложения. | |
| Ввод имени пользователя и даты рождения. В форме отразить приветствие с указанием дня недели даты рождения и название месяца. Например, «Привет имя, Вы родились в понедельник, месяц – Март». | |
| Ввод имени пользователя и даты рождения. В форме отразить приветствие с указанием количества лет, которое исполнится Вам в текущем году. Например, «Привет имя, Вам в этом году исполнится 18 лет» и активизировать Лист2. | |
| Ввод пароля и имени. Правильным пароль формируется в зависимости от даты Вашего рождения, может иметь вид «01101995» или «31122000». При верном пароле вывести приветствие «Привет, имя!». | |
| Ввод пароля и имени. Если пароль неверный – приложение требуется закрыть. При правильном пароле вывести сообщение, содержащее анализ количества символов в имени. Например, «Привет, имя. Ваше имя достаточно длинное» или «Привет, имя. А Ваше имя короткое ». | |
| Ввод имени. Если сегодня будний день, вывести приветствие в виде «Привет, имя». Если день выходной, отобразить сообщение о необходимости отдыха, затем закрыть приложение. | |
| Ввод пароля и имени. Правильным пароль формируется в зависимости от текущей даты, может иметь вид «01102013» или «31122015». При верном пароле вывести приветствие «Привет, имя!» и активизировать Лист2. | |
| Ввод пароля. Если пароль верный, вывести приветствие с названием дня недели, в противном случае отобразить сообщение об отказе в доступе, закрыть книгу. |
ЛИТЕРАТУРА
| 1. | Гарнаев А.Ю. Visual Basic 6.0 : Разработка приложений.- СПб.: БХВ-Петербург, 2001. -448 с. |
| 2. | Гарнаев А.Ю. Самоучитель VBA. СПб.: БХВ-Петербург, 2001. -512 с. |
| 3. | Истомин Е.П.Информатика и программирование : учебник / Е. П. Истомин, С. Ю. Неклюдов, В. И. Романченко - СПб. : Андреевский изд.дом, 2006. - 248 с. - ISBN 5-902894-05-0. |
| 4. | Могилев А.В. Информатика : учеб.пособие / А. В. Могилев, Е. К. Хеннер, Н. И. Пак - М. : Академия, 2006. - 336 с. |
| 5. | Могилев А.В. Практикум по информатике : учеб.пособие / А. В. Могилев, Н. И. Пак, Е. К. Хеннер ; под ред.Е.К.Хеннера. - 3-е изд.,испр. - М. : Академия, 2006. - 608 с. - ISBN 5-7695-3540-7. |
| 6. | Назаров С.В., Мельников П.П. Программирование на MS Visual Basic: Учеб. пособие – М.: Финансы и статистика, 2001.-320 с. |
| 7. | Румянцев С.С. Информатика [Электронный ресурс] : учебно-метод.пособие для студ.очн.и заочн.форм обучения экон.спец. / С. С. Румянцев, Н. Г. Павлова, В. Г. Соловьева ; ВГАВТ. - Н.Новгород, 2011. - 1 текст/файл. - Электронная версия печ.издания 2012г. |
ПРИЛОЖЕНИЕ
Стандартные функции VBA
| Функция | Возвращает |
| Функции по обработке символьных строк | |
| InStr(s,b) | Номер позиции первого вхождения строки b в строку s |
| Lcase(s) | Исходную строку s, в которой все символы строчные |
| Len(s) | Число символов в символьном выражении s |
| Left(s,p) | Часть сроки s слева, включающую в себя p символов |
| Mid(s,p1,p2) | Часть строки s из p2 символов, начиная с позиции р1 |
| Right(s,p) | Часть сроки s справа, включающую в себя p символов |
| StrConv(s,vbProperCase) | Исходную строку s, в которой первый символ прописной, все остальные строчные |
| StrReverse(s) | Обратную строку |
| Ucase(s) | Исходную строку s, в которой все символы прописные |
| Функции по обработке даты и времени | |
| Date | Текущую дату |
| Day(d) | День даты d |
| Time | Текущее время |
| Month(d) | Месяц даты d в числовом формате (от 1 до 12) |
| MonthName(d) | Наименование месяца даты d |
| Now | Текущую дату и время |
| WeekDay(d) | День недели даты d в числовом формате (от 1 до 7). Воскресенье является днем начала недели |
| WeekdayName(d) | Название дня недели даты d |
| Year(d) | Год даты d |
| Функции преобразования | |
| CDate(d) | Символьное представление даты d |
| Val(s) | Числовое представление символьного выражения s |
ОГЛАВЛЕНИЕ
| Введение………………………………………………………… | |
| Тема 1. Использование электронных таблиц как средства организации базы данных…………………………………....... | |
| Тема 2. Стандартные функции по обработке базы данных. Расчет налога на доходы физических лиц. Защита данных от некорректного ввода…………………………………………… | |
| Тема 3. VBA: Пользовательские функции…………………… | |
| Тема 4. Пользовательский формат числа……………………... | |
| Тема 5. Сводная таблица………………………………………. | |
| Тема 6. Форматирование расчетного листа с помощью стандартных функций…………………………………………. | |
| Тема 7. Элементы управления на рабочем листе……………. | |
| Тема 8. VBA: Процедуры. Создание пользовательских форм | |
| Тема 9. Процедура Workbook_Open()………………………… | |
| Литература……………………………………………………… | |
| Приложение…………………………………………………….. |