Использование макрорекордера. Абсолютные и относительные ссылки

1.1 Использование макрорекордера

Для использования макрорекордера не требуется никаких знаний по программированию.

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

Инструментальное средство Macrorecorder (Макрорекордер):

1. отслеживает действия пользователя, выполняемые при помощи команд меню или клавиатурных эквивалентов ("горячих" клавиш),
2. генерирует и записывает соответствующие макрокоманды до тех пор, пока пользователь не остановит процесс записи.

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

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

Особое внимание необходимо уделить следующим вопросам:

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

Если в процессе записи макроса была допущена ошибка, то можно отменить последние команды ( Правка-Отменить ) или повторить запись некоторых команд.

Впоследствии можно отредактировать или расширить макрос, дописав в него недостающие команды при помощи редактора VB или при помощи макрорекордера. Записанные при помощи макрорекордера действия с объектами можно скопировать и вставить в текст процедуры, разрабатываемой на VBA.

Сочетание макрорекордера и редактора VB позволяет создать канву программы, включить в нее операторы обработки объектов MS Excel, организовать интерактивный режим работы, предусмотреть использование собственных меню и панелей инструментов.

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

Команды и пиктограммы при использовании макрорекордера

Действия	Команды меню	Пиктограмма или функциональные клавиши вызова	Содержание статусной строки
Начать запись макроса	Сервис-Макрос-Начать запись (Tools-Record Macro-Record New Macro)		Запись (Recording)
Остановить запись макроса	Сервис-Макрос-Остановить запись (Tools-Record Macro-Stop Macro)		Готово (Ready)
Редактировать макрос	Сервис-Макрос-Редактор Visual Basic (Tools-Macro-Visual Basic Editor)	Alt+F11
Выполнить макрос	Сервис-Макрос-Макросы (Tools-Macro-Macros)
Относительная ссылка
Сохранить макрос	Макрос автоматически сохраняется в составе рабочей книги. Возможно сохранение в Личной книге Макросов (Personal.xls), которая находится в папке Xlstart

Запись макроса

Пример

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

Включите запись макроса командой Сервис-Макрос-Начать запись (Tools-Record Macro-Record New Macro).

В поле Имя макроса (Macro Name) введите имя будущей процедуры. По умолчанию имя состоит из слова "Макрос" ( "Macro" ) и порядкового номера создаваемого макроса ( рис. 1.1). Нажатие на OK включает запись.

Рис. 1.1. Запись нового макроса при помощи макрорекордера

В статусной строке появится сообщение Запись (Recording), а на экране возникнет панель инструментов ( рис. 1.2), первая кнопка которой - Остановить запись (Stop Macro). Если этой панели на экране нет, то необходимо включить ее с помощью команды Сервис-Настройка(Tools Options), вкладка Панели инструментов. В списке возможных панелей инструментов пометьте Остановить запись (Stop Macro).

Рис. 1.2.

Введите данные и формулы в соответствии с рис. 1.3 (a) и остановите запись макроса. Перейдите в редактор VB, нажав клавиши Alt+F11.

Рис. 1.3. Расчет размера вклада: (a) - формулы, (b) - значения

Созданная процедура записана в модуле Module1 с именем Макрос1. В ячейке B13 стоит формула расчета нового размера вклада по формуле сложных процентов. Ссылки на ячейки с данными записаны в стиле R1C1 и являются относительными по отношению к ячейке B13 ( рис. 1.4).

Рис. 1.4. Процедура записанная в модуле Module1 с именем Макрос1

Запуск макроса

Активизируйте другой рабочий лист - Лист2 и проверьте правильность выполнения тех же расчетов на новом листе. Для этого выполните команду Сервис-Макрос-Макросы (Tools-Macro-Macros).

В диалоге ( рис. 1.5) можно набрать имя выполняемого макроса или выбрать его из списка имеющихся макросов.

Рис. 1.5. Меню процедур

Нажатие на командную кнопку Выполнить (Run) этого диалога активизирует выбранный макрос. Этот диалог можно вызвать, нажав на кнопку Выполнить макрос (Run Macro)панели инструментов Visual Basic ( рис. 1.6).

Рис. 1.6. Панель инструментов Visual Basic для рабочих листов MS Excel

Для быстрого запуска макроса можно использовать "горячие" клавиши ( Shortcut ), задав их в диалоге Запись макроса (см. рис. 1.1) или нажав кнопку Параметры (Options) в диалоге на рис. 1.5. Задается нецифровая клавиша, нажатие которой в сочетании с клавишей Ctrl запустит макрос. Дополнительно можно использовать клавишу Shift.

При задании "горячих" клавиш в макросе автоматически появляется комментарий - сообщение о наличии таких клавиш, например, ' Сочетание клавиш: Ctrl+t.

Удобно связать макрос или любую процедуру с объектами рабочей книги или рабочего листа. Можно для запуска макроса

• создать команду меню;
• создать кнопку на панели инструментов;
• создать кнопку на рабочем листе;
• связать процедуру с графическим объектом.

Первые две возможности реализуются при помощи команды Вид-Панели инструментов-Настройка (View-Toolbars-Customize), вкладка Команды (Commands) (см. рис. 1.7).

После выбора в левом окне строки Макросы (Custom) справа доступны две возможности: Настраиваемая кнопка и Настраиваемая команда меню. Переместите выбранную опцию на панель инструментов или в меню соответственно. Сразу становится доступной кнопка Изменить выделенный объект. В новом диалоговом окне настройте вид кнопки или измените команду меню, затем выполните команду Назначить макрос (Assign Macro) и выберите из списка процедур нужный макрос.

Рис. 1.7. Назначение макроса кнопке панели инструментов или команде меню: (a) - выбор команды меню или кнопки, (b) - команды для настраиваемой кнопки

Для привязки к графическому объекту выполните команду Назначить макрос из контекстного меню для выбранного графического объекта.