Основные принципы и концепции программирования на языке VBA в Excel

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

Показывать лекцию целиком

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

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

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

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

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

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

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

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

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

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

    Впоследствии можно отредактировать или расширить макрос, дописав в него недостающие команды при помощи редактора 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).

    В поле ). Нажатие на OK включает запись.

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

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

    (рис 1.2)

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

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

    Созданная процедура записана в модуле ).

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

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

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

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

    Нажатие на командную кнопку (рис 1.5) панели инструментов ).

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

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

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

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

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

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

    Первые две возможности реализуются при помощи команды ).

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

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

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

    1.2 Абсолютные и относительные ссылки

    Запись абсолютных и относительных ссылок в формулах на рабочем листе

    Например, если активна ячейка A4, то ссылку на ячейку D3 можно задать как D3 (стиль A1 ) или R[-1]C[3] (стиль R1C1 ).

    При записи формулы в стиле A1 признаком абсолютной ссылки является знак доллара ($) перед адресом строки и/или столбца. В стиле R1C1 для задания абсолютной ссылки используются индексы ячейки. Например, на ячейку D3 указывает абсолютная ссылка $D$3 в стиле A1 и абсолютная ссылка R3C4 в стиле R1C1.

    Запись абсолютных и относительных ссылок в процедурах VBA

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

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

    Команда вычисления размера вклада в ячейке B13 с использованием абсолютных ссылок (набор адреса ячейки со знаками $ во время записи макроса) будет выглядеть так:

    ActiveCell.FormulaR1C1 = "=R10C2*(1+R12C2/12)^R11C2"

    Можно установить использование относительных ссылок при записи процедур Для этого после запуска макрорекордера нажмите кнопку на панели инструментов ). Кнопка будет изображена на оранжевом фоне. Относительные ссылки будут использоваться до конца текущего сеанса работы в MS Excel или до повторного нажатия кнопки Относительная ссылка.

    Важно:
  • Абсолютные ссылки всегда указывают на конкретную ячейку и команды, записанные с абсолютными ссылками, всегда выполняются с теми же ячейками, которые обрабатывались при создании макроса, независимо от положения активной ячейки, другими словами, независимо от выбранного объекта Range.
  • Относительные ссылки рассматриваются относительно выбранного объекта Range. Чтобы с помощью макроса обрабатывать произвольные ячейки, следует записать его команды с относительными ссылками.
  • Чтобы макрос всегда выполнял макрокоманды для определенной ячейки, а затем для других ячеек, связанных с предыдущей, нужно использовать абсолютные ссылки при записи команд для первой ячейки и относительные ссылки при записи команд для ячеек, связанных с первой.
    •

    Другой способ задания относительных ссылок - использование свойства Offset объекта Range.

    Примеры

  • Процедура ).(рис 1.8) Процедура Макрос1 записана с абсолютными ссылкамиВыделение интервала B1:C5 записано с абсолютными ссылками. При повторном выполнении процедуры снова будет выделен тот же интервал B1:C5, независимо от того, какая ячейка будет активной перед запуском процедуры.
  • Процедура (рис 1.9) Процедура Макрос2 записана с относительными ссылкамиВ команде выделения интервала объект Range смещен относительно активной ячейки - свойство Offset устанавливает смещение на один столбец. Процедура Макрос2 окрасит интервал из двух столбцов и пяти строк, начинающийся в соседнем по отношению к активной ячейке столбце.
    •
    Предыдущее занятие Следующее занятие
    Вернуться к учебному плану
    ^