Опубликован: 01.04.2010 | Уровень: для всех | Доступ: платный | ВУЗ: Национальный исследовательский университет "Высшая Школа Экономики"
Лекция 1:

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

Лекция 1: 12 || Лекция 2 >
Аннотация: В лекции рассказывается о записи макроса с использованием макрорекодера. С помощью макрорекордера удобно создавать процедуры, которые автоматизируют рутинные операции: форматирование таблиц, создание диаграмм и сводных таблиц, работу со списками данных и т.д. Во второй части лекции рассматриваются вопросы применения абсолютных и относительных ссылок в формулах на рабочем листе и в процедурах VBA.

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

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

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

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

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

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

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

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

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

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

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

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

Важно:
  • Макрорекордер особенно удобен для записи действий по обработке объектов, так как помогает получить представление об имеющихся у объекта свойствах и методах и способах их применения.
  • Для получения контекстной справки по любому объекту, свойству или методу достаточно выделить его в процедуре и нажать клавишу F1.

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

Команды и пиктограммы при использовании макрорекордера
Действия Команды меню Пиктограмма или функциональные клавиши вызова Содержание статусной строки
Начать запись макроса Сервис-Макрос-Начать запись (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
Советы:
  • Не записывайте макрорекордером слишком большие макросы. Всегда можно объединить несколько макросов в одну процедуру или записать в макросе оператор вызова другого макроса.
  • Добавляйте комментарии к командам макроса.
  • Помните, что все действия, касающиеся макрорекордера, выполняются только для рабочего или диаграммного листа, но не для редактора VB.
  • При использовании макрорекордера пользуйтесь абсолютными или относительными ссылками на ячейки в соответствии с необходимостью.

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

Пример

Пусть требуется рассчитать размер вклада через год при условии, что первоначальная сумма составляла 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.

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

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

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

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

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

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

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

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

Меню процедур

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

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

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

Рис. 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) и выберите из списка процедур нужный макрос.

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

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

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

Лекция 1: 12 || Лекция 2 >
Дмитрий Денисов
Дмитрий Денисов

Еще раз повторяю вопрос  - отправил договор и сканы документов по почте - в начале августа, до  5 го, внес предоплату - летом действовала скидка - чтобы пройти курс в августе, но я уезжал в Москву. По возвращению - и  в течение месяца - никакого ответа, никаких зачислений, ничего. Как проверить, пришли ли мои документы в Интуит, предоплата, почему нет зачисления.

Дмитрий Денисов
Дмитрий Денисов

Как записаться на курс, оплатить, не ориентируюсь в программе.

Ранее у Вас учился.

Работаю преподавателем, есть справка. Как получить скидку, т.е. оплата 2000


 

Елизавета Cафронова
Елизавета Cафронова
Россия, Выкса, НИТУ МИССиС
Иван Чуприков
Иван Чуприков
Россия, пгт. Чульман