Создание макроса с помощью авторекодера

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

Excel 2007/2010/2013

Как следует из рис. 1, интерфейсы рассматриваемых приложений практически не отличаются. Основное отличие состоит в том, что в Excel 2007 используется кнопка , а в Excel 2010 и Excel 2013 используется кнопка .

Для записи макроса в этих приложениях используется кнопка ленты на вкладке Разработчик. Вкладка Разработчик в Excel 2007, Excel 2010 и Excel 2013 по умолчанию не показана. Для вывода её на экран нужно выполнить следующие действия, которые различаются в Excel 2007 и Excel 2010/2013.

Excel 2007

1. Щелкнуть значок Кнопка Microsoft Office , а затем в появившемся диалоговом окне “Microsoft Excel” щёлкнуть вкладку , находящуюся внизу окна.

2. В группе параметров Основные

Рис. 2. Группа параметров Основные

установить флажок Показывать вкладку "Разработчик" на ленте, а затем нажать кнопку ОК.

Excel 2010 / 2013

1. На вкладке Файл выбрать Параметры, чтобы открыть диалоговое окно Параметры Excel (рис. 3).

Рис. 3. Вид диалогового окна «Параметры Excel»

Щелкнуть Настройка ленты в левой части диалогового окна.

2. В разделе Выбрать команды, расположенном слева в окне, выбрать Часто используемые команды.

3. В разделе Настройка ленты, который находится справа в диалоговом окне, выбрать Основные вкладки, а затем установить флажок Разработчик.

Нажать кнопку ОК.

Установка уровня безопасности

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

Excel 2003

Excel 2003 поддерживает следующие уровни безопасности макросов:

- очень высокий;

- высокий;

- средний;

- низкий.

В Excel 2003 макросы VBA выполняются только при условии, что выбран параметр " Доверять всем установленным надстройкам и шаблонам " (в Excel 2003 на вкладке Надежные издатели в диалоговом окне Безопасность) и макросы (с подписью или без) хранятся в определенной надежной папке на жестком диске пользователя. Если эти условия не соблюдены, то при использовании параметра " Очень высокий " макросы VBA не выполняются в Excel 2003.

Если уровень безопасности высокий, могут выполняться только исполняемые файлы (EXE или COM), подписанные утвержденным надежным источником (то есть имеющие сертификат). В противном случае все исполняемые модули, связанные с документами или внедренные в них, автоматически отключаются без предупреждения, когда пользователь открывает документы. При установке всех приложений Office 2003 по умолчанию задается параметр "Высокий".

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

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

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

Чтобы включить или отключить макросы в Excel 2003 вам необходимо зайти в меню Сервис | Макрос | Безопасность (рис. 4).

Рис. 4. Вид меню «Сервис»

В открывшемся окне (рис. 5) переставьте переключатель Уровень безопасности в положение Средняя или Низкая.

Рис. 5. Вид окна «Безопасность»

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

В последующих версиях Microsoft Office модель безопасности претерпела значительные изменения. В приведенной ниже таблице параметры безопасности макросов Excel 2003 приблизительно сопоставляются с аналогичными параметрами Excel 2010.

Параметр Excel 2003	Эквивалент в Excel 2010
Очень высокий	Отключить все макросы без уведомления
Высокий	Отключить все макросы кроме макросов с цифровой подписью
Средний	Отключить все макросы с уведомлением
Низкий	Включить все макросы (не рекомендуется, возможен запуск опасной программы)

Excel 2007/2010 / 2013

1. Щёлкнуть по вкладке Разработчик и в группе Код (рис.6) щёлкнуть по кнопке “Безопасность макросов” .

Рис. 6. Вид группы кнопок «Код»

2. В появившемся окне “Центр управления безопасностью” (рис. 7) в группе “Параметры макросов” выбрать переключатель “Включить все макросы (не рекомендуется, возможен запуск опасной программы)” и нажать кнопку ОК.

Рис. 7. Центр управления безопасностью

Окно Центр управления безопасностью можно вывести из BackStage выбрав последовательность Файл → Параметры → Центр управления безопасностью → Параметры центра управления безопасностью → Параметры макросов.

Примечание

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

Работа с авторекордером

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

Запись_макроса_исходная

Запись_макроса_отредактированная

Запись_макроса _ шаблон

Каждая последующая книга создается на основе предыдущей и в нее вносятся необходимые изменения.

Запись последовательности действий пользователя с автоматическим преобразованием их в код VBA осуществляется с помощью специальной функции записи макросов Excel. Как это делается, рассмотрим на примере создания таблицы “Поставка товаров” (рис.8).

 

Поставка товаров
Код товара	Наименование товара	Единица измерения	Цена	Количество	Сумма
	Товар 1	шт.	10,00р.		50,00р.
	Товар 2	шт.	15,00р.		150,00р.
	Товар 3	шт.	20,00р.		400,00р.
	Товар 4	кг.	20,00р.		210,00р.
				Итого:	810,00р.

Рис. 8. Таблица “Поставка товаров”

Суть задачи состоит в следующем:

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

Порядок действий следующий.

- Открыть рабочую книгу; по умолчанию активизируется Лист1.

- Выполнить команду Сервис ® Макрос ® Начать запись (вExcel 2003). В Excel 2007 / 2010 / 2013 на вкладке Разработчик в группе Код нажать Запись макроса.

Рис. 9. Лента MS Excel с открытой вкладкой Разработчик

Можно на вкладке Вид нажать кнопку Макросы и выбрать в появившемся меню опцию Запись макроса.

Рис. 10. Лента MS Excel с открытой вкладкой Вид

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

Рис. 11. Окно записи имени макроса

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

- ввод заголовка и “шапки” таблицы,

- форматирование таблицы,

- ввод расчётных формул,

- ввод исходных данных таблицы.

Для программного решения этих задач создадим следующие макросы:

· ЗаголовокШапка,

· Формулы,

· Форматирование,

· ВводДанных.

Поскольку в именах VBA, в т.ч. макросов, не допускаются пробелы, их рекомендуется заменять символом “_” (подчёркивание, например, Заголовок_шапка) или писать слова слитно, начиная каждое слово с прописной (заглавной) буквы (ЗаголовокШапка). Используем второй вариант – ЗаголовокШапка.

После ввода имени макроса щёлкнуть кнопку OK, чтобы принять параметры по умолчанию.

Excel вставит новый модуль VBA в проект.

Начиная с этого момента, Excel будет преобразовывать действия пользователя в код VBA. В Excel 2003 при записи в окно добавляется плавающая панель инструментов (рис. 12), содержащая две кнопки: левая – “Остановить запись”(синий квадрат) и правая – “Относительная ссылка”:

Рис. 12. Панель останова записи макроса

Если эта панель не появилась, то нужно выполнить команду Вид ® Панели инструментов ® Настройка и в появившемся окне на вкладке Панели инструментов выставить флажок “ Остановить запись ”).

В Excel 2007 / 2010 / 2013 на вкладке Разработчик в группе Код нажать Остановить запись.

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

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

Для реализации относительной формы записи следует включить кнопку Относительные ссылки в группе Макросы на вкладке Вид или в группе Код на вкладке Разработчик. Метод записи можно изменять в любой момент записи макроса.