Пример 1. Цель: Научиться создавать и обрабатывать макросы

Цель: Научиться создавать и обрабатывать макросы.

Ход работы:

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

Краткие теоретические сведения.

Visual Basic for Application (VBA) - один из самых простых в изучении и применении языков программирования для автоматизации приложений, входящих в пакет Microsoft Office. Язык VBA является общим языком программирования, подходящий для всех приложений. Этот язык является версией популярного языка Visual Basic, но имеет отличия:

• VBA используется для автоматизации существующего приложения (в нашем случае приложение Excel).

• VBA использует среду приложения.

• Выполнение VBA-приложений требует доступ к приложению, в котором они созданы.

Понятие макроса

Средство записи Excel позволяет записывать последовательности действий пользователя, а затем преобразует их в код VBA. Основное назначение макросов - это автоматизация работы пользователя. Кроме этого созданный код макроса может служить основой для дальнейших разработок.

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

Каждому макросу задается имя, а для быстрого запуска макроса можно создать или присвоить ему «горячую» клавишу (клавишу, по нажатию на которую будет производиться запуск макроса). После запуска макрос будет автоматически выполнен тем приложением, в котором он создан и запущен. При выполнении макроса компьютер воспроизведет все действия пользователя. Макрос - это имеющая имя последовательность заданных пользователем команд, и действий хранящаяся в форме программы на языке VBA, которую может выполнить Excel.

Общий алгоритм записи макроса:

- Выбрать вкладку Вид – Макросы-Запись макроса.

- В диалоговом окне «Запись макроса» дать имя макросу. Первым символом имени макроса должна быть буква. В имени макроса не допускается использование пробелов.

- Выполнить действия, которые нужно записать.

- Нажать кнопку «Остановить запись».

Пример 1

Создадим простой макрос, который изменяет шрифт и цвет в выделенном диапазоне ячеек. Эти действия в Excel можно выполнить многими различными способами (например, применение стилей, автоформатирования и т.д.), этот пример продемонстрирует последовательность действий пользователя, необходимые для записи макроса.

1. Откройте новую книгу.

2. В ячейку A1 введите ваше имя, в ячейку A2 введите фамилию, в ячейку A3 - город, где вы родились, в ячейку A4 - название страны.

3. Установите курсор в ячейке A1.

4. Выполните команду Вид – Макросы-Запись макроса. Появится диалоговое окно.

5. Введите название макроса «ЦветШрифт» и нажмите клавишу «ОК». На экране появится панель инструментов «Остановить запись».

6. На вкладке Главная выполните команду Формат-Формат ячеек. Появится диалоговое окно Формат ячеек. Перейдите на вкладку Шрифт.

7. Установите размер шрифта 18 пунктов, цвет - зеленый. Подтвердите выполнение операции.

8. На вкладке Вид выполните команду Макросы - Остановить запись. Запись макроса завершена.

Выполнение макросов

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

Пример 1 (продолжение)

1. Установите курсор в ячейку A2.

2. На вкладке Вид выполните команду Макросы-Макросы. Появится диалоговое окно.

3. Выделите макрос «Цвет_Шрифт» и щелкните по кнопке «Выполнить». Шрифт в ячейке A2 станет зеленым и размером 18 пунктов.

4. Выделите ячейки A3 и A4 и снова выполните макрос «Цвет_Шрифт».

5. Сохраните файл с именем «Задание1».

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

Во время записи макроса Excel запоминает ваши действия и преобразует их в код VBA. Можно просмотреть полученный код и отредактировать его, если в этом есть необходимость. Продолжим работу сфайлом « Задание1 ».

1. Для просмотра созданного макроса выполните команду Макросы-Макросы. Появится уже знакомое диалоговое окно Макрос.

2. Выделить макрос «ЦветШрифт» и щелкните по кнопке «Изменить». Откроется окно редактора.

При записи макроса мы выполнили всего 2 действия. Это изменение размера шрифта и цвета, но в коде макроса записалась вся информация, представленная на вкладке Шрифт диалогового окна Формат ячеек. Поэтому отредактируем текст макроса, удалив лишние строки. Редактировать можно непосредственно в редакторе Visual Basic.

3. Отредактируйте текст макроса. После редактирования текст будет такой:

4. Закройте окно редактора и вернитесь на лист Excel.

5. В ячейку A4 введите слово Проверка.

6. Оставьте эту ячейку активной.

7. Запустите макрос на выполнение. Вы видите, что исправленный макрос работает точно также как и прежний.

8. Сохраните файл с тем же именем « Задание1 ».

Назначение макроса графическим изображениям

Разрабатывая приложение надо думать о том, чтобы создать легкий и удобный интерфейс для выполнения задач автоматизации. Запускать макрос на выполнение, применяя команды меню или кнопки на панели Visual Basic, требует определенных знаний и навыков. А если приложением будет пользоваться пользователь, незнакомый с этими возможностями? Для запуска макроса можно использовать любой элемент, находящийся на рабочем листе. Например, можно использовать для запуска любое графическое изображение.

Продолжим работу с файлом «Занятие 1».:

1. Откройте файл «Задание1».

2. На вкладке Вставка выберете иконку Фигуры и нарисуйте на листе любую автофигуру.

3. Щелкните правой кнопкой мыши на изображении и в открывшемся контекстном меню выберите команду Назначить макрос. Отобразится диалоговое окно Назначить макрос объекту,

4. Выберите макрос «Цвет_Шрифт» и подтвердите выполнение операции.

5. Щелкните вне графического изображения, чтобы снять выделение с объекта.

6. Введите в любую ячейку листа число 500.

7. Оставьте ячейку активной.

8. Щелкните по графическому изображению. Макрос «Цвет_шрифт» должен выполниться

Удаление макросов из списка макросов

Для того, чтобы удалить макрос из списка макросов диалогового окна Макросы надо:

1. Выполнить команду Макросы - Макросы на вкладке Вид.

2. Выделить макрос, подлежащий удалению и щелкнуть по кнопке «Удалить».

3. Подтвердить выполнение операции в специальном окне, которое появится.

4. Сохранить рабочую книгу.

Ограниченность макросов

В Excel многие процедуры можно выполнить с помощью макросов, но макросы имеют и ограничения. С помощью макросов нельзя выполнить различные действия в зависимости от содержимого ячейки, нельзя вызвать некоторые диалоговые окна Excel, например, сохранить документ, нельзя отобразить и использовать пользовательские формы ввода данных. Эти ограничения приводят к необходимости создания программ на VBA.

II. Выполните самостоятельно задания.

1. Создайте новый файл с именем «Задание2». Задание выполняйте на Листе 1. Введите в диапазон A1:A6 любые числа. Создайте макрос для очистки диапазона. С помощью панели инструментов создайте фигуру с надписью «Очистить» и назначьте ей созданный макрос.

2. На Листе 1 введите в диапазон C2:C6 фамилии ваших друзей, а в диапазон D2:D6 занесите их телефоны. Создайте две кнопки с названиями «Выделенное скопировать» и «Вставить». По нажатию на первую кнопку выделенный диапазон должен копироваться в буфер обмена. По нажатию на вторую кнопку информация из буфера обмена должна выводиться в текущую ячейку.

3. На Листе 2 в первый столбец введите исходные данные, начиная с первой строки:

Необходимо фамилии и телефоны разнести по разным столбцам

В Excel существует специальная команда для выполнения этой операции Данные-Текст по столбцам.

Технология работы:

- выделите все ячейки первого столбца, где занесены исходные данные;

- выполните команду Данные-Текст по столбцам, появится диалоговое окно

В этом окне укажите формат данных - с разделителями. Нажмите кнопку «Далее».

Во втором диалоговом окне укажите символом - разделителем является пробел Нажмите кнопку «Далее».

В третьем диалоговом окне установите для каждого столбца данных формат данных. Для 1-ого столбца - текстовый формат. Для второго столбца - также текстовый. В окне «Поместить в» укажите ячейку С1 и нажмите «Готово».

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

Сохраните задание.

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

Технология работы

1этап Подведение промежуточных итогов.

1. Запустите Excel. Новому файлу присвойте имя «Задание 2».

2. Лист 1 переименуйте в Задание 2.

3. На листе Задание 2 подготовьте таблицу, представленную ниже.

4. В столбце «Сумма» значение рассчитайте по формуле

5. Отсортируйте таблицу по столбцу «Товар».

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

Таблица с информацией о товарах

Товар	Модель №	Название	Стоимость	Цена	Кол-во	Сумма
Ксерокс	C210GLS	Деловой	1 430,00	1 858,00		813 804,00
Ксерокс	C300GLS	Деловой	1 716,00	2 231,00		1 438 995,00
Ксерокс	C400GLS	Деловой	4 270,00	5 551,00		2 964 234,00
Ксерокс	C100GLS	Персональный	827,00	1 076,00		606 864,00
Ксерокс	C100GLS	Персональный	1 608,00	2 090,00		1 185 030,00
Ксерокс	C400GLS	Персональный	6 000,00	7 200,00		2 361 600,00
Ксерокс	C420GLS	Персональный	6 415,00	8 340,00		3 294 300,00
Ксерокс	C110GLS	Профессиональный Плюс	923,00	1 291,00		815 912,00
Ксерокс	C310GLS	Профессиональный Плюс	2 965,00	3 855,00		1 684 635,00
Ксерокс	C410GLS	Профессиональный Плюс	5124,00	6 661,00		2 724 349,00
Ксерокс	C500GLS	Профессиональный Плюс	7 378,00	9 591,00		2 858118,00
Факс	F250G	Деловой	2 551,00	3 316,00		984 852,00
Факс	F350G	Деловой	2 761,00	3 589,00		1 568 393,00
Факс	F450G	Деловой	3 815,00	4 960,00		1 433 440,00
Факс	F550G	Деловой	5 614,00	7 298,00		788184,00
Факс	F100G	Профессиональный	1 840,00	2 090,00		877 800,00
Факс	F150G	Профессиональный	1 730,00	2 392,00		1 516 528,00
Факс	F300G	Профессиональный	2 761,00	3 589,00		1 568 393,00
Факс	F400G	Профессиональный	3 513,00	4 567,00		1 479 708,00
Факс	F200G	Профессиональный Плюс	2 076,00	2 698,00		1 165 536,00
Факс	F500G	Профессиональный Плюс	4 878,00	6 342,00		1 338162,00

Рассмотрим использование команды Итоги. Подсчитаем, на какую сумму было продано отдельно ксероксов и отдельно факсов.

1. Установим курсор на любую ячейку отсортированной таблицы, таблица была отсортирована по заданию в предыдущем пункте.

2. Выполним команду Данные - Промежуточные итоги. В появившемся диалоговом окне Промежуточные итоги в строке При каждом изменении в выберем из открывшегося списка название графы «Товары», в строке Операция - функцию Сумма, в строке Добавить итоги по -выберем название графы «Сумма», а также для подсчета общего количества суммы по графе «Сумма». Активизируем опцию Итоги под данным. Нажмем клавишу (ОК)

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

Детали структурированных листов можно скрывать, щелкая на пронумерованных кнопках уровней структуры. Чтобы увидеть только общий итог по всем данным, следует щелкнуть на кнопке первого уровня, а для получения детальной картины всех элементов можно воспользоваться кнопкой третьего уровня. Знак «+» работает как кнопка воображения деталей, знак «-» работает как кнопка скрытия.

Для удаления структуры и итоговых значений с рабочего листа в случае неправильного получения результатов нужно выполнить команду Данные—Итоги. В появившемся окне щелкнуть по кнопке Убрать все.

2 этап Создание макросов

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

III. Ответьте на вопросы.

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

1. Что такое Visual Basic for Application.

2. Понятие макроса.

3. Порядок записи простых макросов.

4. Выполнение макросов.

5. Редактирование макросов.

6. Назначение макроса графическим изображениям.

7. Удаление макросов из списка макросов.

8. Ограниченность макросов.

Форма отчета: Файлы с результатами заданий. Ответы на контрольные вопросы.

ПРАКТИЧЕСКАЯ РАБОТА №2
Тема: Знакомство с Visual Basic for Application.