Методические указания к выполнению работы Пример создания макроса

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

• Первый лист «Исходные данные» - таблица исходных данных

  	A	B	C	D	E	F
  1	Квартиросъемщики
  2	ФИО	Улица	Дом	Квартира	Человек	Площадь
  3	Петров А.И.	Ленина	1	1	1	30
  4	Иванов С.П.	Кирова	2	1	2	40
  5	Сидоров Н.В.	Радищева	3	2	3	50
  6	Романов К.Е.	Кирова	2	2	2	30
  7
  8	Тарифы на человека
  9	Отопление	Газ	Хол. вода	Радио
  10	50	10	20	5

• Второй лист «Квитанция» - шаблон квитанции оплаты

  	A	B	C	D
  1	Квитанция
  2	ФИО
  3	Адрес: улица		дом
  4	Проживает человек
  5	Услуга
  6	Отопление
  7	Газ
  8	Хол.вода
  9	Радио
  10	Итого:

Замечание. Выполнение задания разбивается на 6 этапов.

Порядок работы

Этап №1. Создание листов рабочей книги.

Создать рабочую книгу из двух листов. Содержимое и названия листов должно соответствовать заданию.

Этап №2. Создание программного модуля.

Цель этапа. Так как макрос представляет собой программу, которая размещается в программном модуле. Поэтому начать создание макроса нужно с создания программного модуля.

 Создать в рабочей книге программный модуль. Для этого выполнить команду меню СЕРВИС\Макрос\Макросы. Открывается окно Макрос (рис.1).

 В появившемся окне задать имя макроса Kvit (без пробелов), в строке Находится в выбирать Эта книга и нажать кнопку Создать (рис.1).

 Открывается редактор Visual Basic for Applications. Вид экрана – рис.2

Р ис.1. Окно создания нового макроса.

Рис.2. Вид экрана редактора Visual Basic for Applications

Этап №3. Создание внешнего вида окна.

Цель этапа. Макрос предназначен для формирования квитанции. Выбор фамилии плательщика должен происходить из диалогового окна со списком фамилий и кнопкой ОК. Данный этап начинает формирование внешнего вида диалогового окна.

 Командой ВСТАВКА \ User Form создать заготовку пустого окна (рис.3). На экране появляются:

- окно проектировщика форм «Книга 1 – User Form 1(User Form)», которое представляет собой заготовку будущего окна диалога и предназначено для задания его внешнего вида;

- панель элементов «Элементы управления», содержащая кнопки для добавления в окно стандартных элементов интерфейса Windows.

Рис. 3. Вид окна с пустой формой диалогового окна макроса.

П равила работы. Поместить элемент на форму можно, нажав кнопку на панели элементов, а затем щелкнуть левой кнопкой мыши в нужном месте окна. Будем использовать компоненты: метка кнопка А), поле со списком (кнопка на панели элементов управления), строка ввода (кнопка ab| на панели элементов управления), кнопки (кнопка ).

Разместить компоненты в окне проектировщика форм согласно рис. 4 сначала левый столбец, затем правый.

О собенность. При помещении компонента на форму название помещается в поле с выпадающим списком окна со свойствами объекта.

Рис.4. Внешний вид окна макроса

Этап №4. Задание свойств компонентов окна.

Цель этапа. Ввести осмысленные подписи к компонентам окна. Данный этап завершает формирование внешнего вида диалогового окна.

Задать текст метки 1 (Label1). Для этого активизировать метку - щелкнуть левой кнопкой мыши в окне макроса на метке Label1. Метка выделится по периметру квадратиками.

 Найти в списке окна Properties строку с надписью Caption. Это свойство компонента Label1, отвечающее за текст, выведенный в метке.

 Щелкнуть левой кнопкой мыши в правой части строки, набрать текст «Фамилия» и нажать Enter. Набранный текст появится в окне.

 По аналоги задать надписи для остальных меток (рис.5). Если размеры метки будут недостаточны для вывода введенного текста, то изменить размер, взявшись левой кнопкой мыши за квадратики, расположенный по периметру метки.

 Задать название кнопки. Для этого активизировать кнопку, найти в списке свойств строку Caption и ввести текст «Формирование квитанции» (рис.5).

 Задать заголовок формы: активизировать форму, в строке свойства Caption задать «Формирование квитанции». Текст поместиться в заголовок окна. Внешний вид окна сформирован.

 Задать вывод окна при запуске макроса из меню Сервис\Макросы. Для этого в окне с заголовком Книга1 – Module1 (Code) в процедуру

Sub Kvit()

End Sub

добавить строку UserForm1.Show

 Выйти из редактора VBА – команда File\Close and Return to Microsoft Excel.

 Сохранить книгу в своей папке под именем Макрос.

 Проверить работу макроса: выполнить команду Сервис\Макрос\Макросы. В окне «Макрос» в строке «Имя макроса» выбрать Kvit и нажать кнопку «Выполнить». На экране должно появиться окно макроса.

Окно пока не выполняет никаких действий. Поэтому закрыть окно и открыть редактор VBA.

Этап №5. Программирование работы окна

Цель этапа. Задать реакцию на действия пользователя в окне. Этап посвящен программированию процедур, отвечающих за реакцию макроса на действия пользователя.

Внимание. Анализ задачи показывает, что в макросе должны выполняться такие действия:

1. При работе с окном макроса выпадающий список «Фамилия» должен быть заполнен данными из 1 столбца таблицы «Квартиросъемщики». Формирование этого списка должно выполняться перед появлением окна на экране. При появлении окна генерируется событие Activate, связанное с формой. Обработчик данного события может выполнить все подготовительные действия по формированию окончательного вида окна. Итак, первое обрабатываемое макросом событие - это Activate, связанное с окном.

2. При выборе из списка фамилии нужно, чтобы остальные поля автоматически заполнялись данными об этом человеке, взятыми из таблицы на листе «Исходные данные». При выборе строки из списка возникает событие Change. Обработчик данного события может выполнить все действия, изменяющие содержимое компонентов. Итак, второе обрабатываемое макросом событие - это Change, связанное со списком.

3. Нажатие на кнопку «Формирование квитанции» должно приводить к заполнению шаблона квитанции на листе «Квитанция» данными, содержащимися в окне. Нажатие на кнопке вызывает событие Click, относящееся к кнопке. Если сделать обработчик данного события, то можно выполнять все действия, изменяющие содержимое шаблона на листе «Квитанция». Итак, третье обрабатываемое макросом событие - это Change, связанное с кнопкой.

 Вывести на экран окно редактора кода. Для этого выполнить двойной щелчок левой кнопкой мыши на пустом месте формы. Открывается окно редактора кода (рис.6).

 Создать обработчик события Activate. Для этого в поле выбора компонента (см. рис.6) выбрать UserForm. В окне генерируется заготовка процедуры обработки щелчка кнопки в окне.

 Выбрать в списке возможных событий (см.рис.6) Activate. При этом в редакторе кода генерируется такой шаблон:

Private Sub UserForm_Activate()

End Sub

Вводим между этими строкам код метода

Dim i As Integer

i = 0

While Worksheets("Исходные данные").Cells(3 + i, 1) <> ""

ComboBox1.AddItem Worksheets("Исходные данные").Cells(3 + i, 1)

i = i + 1

Wend

Внимание!. Пояснения к фрагменту:

1. ComboBox1 – имя переменной, отвечающей за объект «Поле ввода со списком».

2. AddItem – метод, добавляющий в конец списка выбора указанное далее строковое значение. Нумерация строк в списке выбора начинается с нуля. Доступ к номеру можно получить с помощью свойства ListIndex.

3. Worksheets – коллекция, содержащая объекты для доступа к листам активной рабочей книги. В скобках указывается порядковый номер листа (нумерация с 1) или его название в двойных кавычках.

4. Cells – обращение к листу рабочей книги как к двумерной таблице. В скобках на 1-м месте указан порядковый номер строки, на втором порядковый номер столбца. Можно использовать константы, переменные и т.п.

Порядок работы фрагмента: В первой строке объявляется переменная i целого типа, необходимая для работы цикла. В следующей строке этой переменной присваивается значение 0. Затем организуется цикл, который выполняет проверку содержимого ячейки, расположенной на пересечении строки 3+i и столбца 1. Если i=0, это ячейка А3 листа «исходные данные» (см. задание), если i=1, это А4, i=2 – это А5 и т.д.). В случае, когда ячейка не пуста, ее содержимое добавляется в качестве очередной строки в список выбора. Затем увеличивается значение переменной i и проверяется следующая ячейка. При попадании на пустую ячейку (в примере А7) работа цикла завершается.

 Добавить обработчик события выбора строки из списка. Для этого перейти щелчком мыши в окно проектировщика форм «Книга 1 – User Form 1(User Form)».

Выполнить двойной щелчок на списке с подписью «Фамилия». В редакторе кода появится заготовка для процедуры обработки выбора строки из списка:

Private Sub ComboBox1_Change()

End Sub

 Заполнить ее кодом:

i = ComboBox1.ListIndex

TextBox1.Value = Worksheets("Исходные данные").Cells(3 + i, 2)

TextBox4.Value = Worksheets("Исходные данные").Cells(3 + i, 3)

TextBox2.Value = Worksheets("Исходные данные").Cells(3 + i, 4)

TextBox3.Value = Worksheets("Исходные данные").Cells(3 + i, 6)

TextBox5.Value = Worksheets("Исходные данные").Cells(3 + i, 5)

ot = TextBox3.Value * Worksheets("Исходные данные").Cells(10, 1)

TextBox6.Value = ot

gaz =TextBox5.Value * Worksheets("Исходные данные").Cells(10, 2)

TextBox7.Value = gaz

voda=TextBox5.Value * Worksheets("Исходные данные").Cells(10, 3)

TextBox8.Value = voda

TextBox9.Value = Worksheets("Исходные данные").Cells(10, 4)

TextBox10.Value=ot+gaz+voda+Worksheets("Исходные данные").Cells (10,4)

Внимание!. Пояснения к фрагменту:

1. TextBox1.Value – обращение к содержимому TextBox1 как к числовому значению. Если используем другое свойство (TextBox1.Text), то обращаемся к тому же самому как к текстовому значению. В данном случае происходит задание содержимого строки ввода TextBox1.

2. Cells – обращение к листу рабочей книги как к матрице. В скобках на 1-м месте - № строки, на втором - № столбца. Можно использовать константы, переменные и т.п. Например, следующий код заполняет вычисленными значениями ячейки 3-го столбца строк с 13 по 20 при щелчке на форме:

Private Sub UserForm_Click()

For counter = 13 To 20

Worksheets(1).Cells(counter, 3).Value = counter * 5

Next counter

End Sub

Создать обработчик нажатия на кнопке «Формирование квитанции». Для этого выполнить двойной щелчок на кнопке. Появится заготовка процедуры:

Private Sub CommandButton1_Click()

End Sub

Заполнить ее кодом:

Worksheets("Квитанция").Range("B2") = ComboBox1.Value

Worksheets("Квитанция").Range("B3") = TextBox1.Value

Worksheets("Квитанция").Range("D3") = TextBox4.Value

Worksheets("Квитанция").Range("B4") = TextBox5.Value

Worksheets("Квитанция").Range("B6") = TextBox6.Value

Worksheets("Квитанция").Range("B7") = TextBox7.Value

Worksheets("Квитанция").Range("B8") = TextBox8.Value

Worksheets("Квитанция").Range("B9") = TextBox9.Value

Worksheets("Квитанция").Range("B10") = TextBox10.Value

Внимание!. Пояснения к фрагменту:

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

 Добавить кнопку для закрытия окна. Для этого поместить в окно проектировщика форм «Книга 1 – User Form 1(User Form)» еще одну кнопку (см.рис.7).

Р ис.7. Внешний вид окна макроса.

Задать ей заголовок «Закрыть»

Сгенерировать обработчик события щелчка на кнопке, для чего выполнить двойной щелчок на кнопке «Закрыть». Появится заготовка процедуры:

Private Sub CommandButton2_Click()

End Sub

 В заготовку поместить строку:

Unload Me

Внимание!. Пояснения к фрагменту:

Unload – оператор, удаляющий объект из памяти. За оператором следует название объекта, который удаляется. Можно указать имя, например UserForm1, или ссылку на форму, которой принадлежит метод – Me. Данная строка производит удаление текущего окно из памяти. При этом оно закрывается на экране и удаляется из памяти.

Для того, чтобы можно было запускать макрос из меню Сервис\Макросы, в окне редактора с заголовком, например, Книга1 – Module1 (Code) в процедуру

Sub Kvit()

End Sub

добавить строку

UserForm1.Show.

Внимание!. Пояснения к фрагменту:

UserForm1.Show – обращение к процедуре Show, которая при запуске макроса выводит на экран созданное окно.

Этап №6. Проверка работы макроса Kvit

Цель этапа. Проверить правильность реакции макроса на действия пользователя в окне.

Запустить макрос на выполнение. Для этого из главного меню редактора VBA выполнить команду Run или нажать клавишу F5. При появлении окна Macros нажать в нем кнопку Run. Макрос начинает работу. Вид экрана представлен на рис.8.

Рис.8. Вид окна Excel с запущенным на выполнение макросом Kvit.

Проверить работу макроса. Для этого выбрать в списке фамилия строку «Сидоров Н.В.». Остальные элементы окна должны заполниться информацией, соответствующей данному квартиросъемщику (рис.9).

Р ис.9. Вид окна макроса Kvit с данными о квартиросъемщике Сидорове Н.В.

Сформировать квитанцию для Сидорова Н.В. Для чего нажать в окне макроса кнопку «Формирование квитанции».

 Закрыть макрос нажатием на кнопку «Закрыть». Происходит возврат в окно редактора VBA.

Выйти из редактора VBА, используя команду File, Close and Return to Microsoft Excel (или нажать комбинацию Alt+Q) ой.

Перейти на лист «Квитанция». Его внешний вид должен соответствовать рис.10.

Р ис.9. Вид листа «Квитанция» после работы макроса с фамилией Сидоров Н.В.

 Сохранить книгу в своей папке под именем «Квитанция».

 Проверить работу макроса, запустив его из рабочей книги. Для этого выполнить команду Сервис\Макрос\Макросы. В окне Макрос в строке «Находится в» выбрать «Эта книга», в строке имя выбрать Kvit и нажать «Выполнить».

 Выбрать другую фамилию, сформировать квитанцию, закрыть макрос и проверить правильность заполнения квитанции.

 Закрыть рабочую книгу.

Индивидуальные задания.

1. Создать рабочую книгу Excel, содержащую заданное согласно варианту количество листов и макрос.

2. Разработать в виде отдельного файла Word инструкцию пользователя по работе этим документом Excel.

Вариант 1. Формирование пустой зачетной ведомости

Создать документ Excel, позволяющий формировать пустую экзаменационную ведомость для выбранной группой. Списки 5 групп по 15 человек оформить в виде отдельных листов рабочей книги Excel. Оформление листа – табл.9:

Таблица 9.

Группа №	28	Факультет ФМФ	ИВТ
№п/п	ФИО	№ зач. книжки
1	Андреев С.Н.	14567
2	Баранов Н.И.	14589
3	Воронина Т.П.	14556
• • •

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

Таблица 10.

Список предметов
Предмет	Преподаватель	Дата

Д ля формирования ведомости создать макрос, который при запуске выводит на экран окно, представленное на рис.10. Пользователь выбирает нужную группу и нужный предмет. После этого в отдельных строках появляется дата и фамилия преподавателя. Щелчок на кнопке «Формировать» приводит к формированию пустой ведомости заданной формы (рис.11) на отдельном листе «Ведомость» рабочей книги.

Рис.10. Вид окна макроса, формирующего ведомость

	№ п/п	ФИО	№ зачет. книжки	Оценка	Подпись
	• • •
Итого:
отлично
Хорошо
удовлетворительно
неудовлетворительно
н/я
Экзаменатор: <ФИО> _________________ Подпись_____________________

Экзаменационная ведомость

Факультет

Группа Дата____________

Дисциплина _______________________ Экзаменатор ________________________

Рис.11. Вид пустой ведомости, формируемой макросом.