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

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

2-е издание, исправленное

Бессонова И.А.
Белоусова С.Н.

Национальный Открытый Университет “ИНТУИТ”
2016

2

УДК [004.438VBA:004.67](07)
ББК 39
Б43
Основные принципы и концепции программирования на языке VBA в Excel / Белоусова С.Н.,
Бессонова И.А. - M.: Национальный Открытый Университет “ИНТУИТ”, 2016 (Основы
информационных технологий)
ISBN 978-5-9963-0258-1

В курсе лекций рассматриваются ключевые принципы программирования на VBA в MS Excel.
Язык Visual Basic for Application (VBA) – объектно-ориентированный язык программирования,
базируется на командах и синтаксисе языка Basic. VBA встроен в офисную среду и позволяет
манипулировать объектами всех офисных приложений. Язык VBA позволяет сочетать простые
методы по созданию документов (использование команд меню или технология перетаскивания
мышью) и программные методы для разработки эффективного пользовательского приложения. Чаще
всего основным документом, вокруг которого пользователь строит свое приложение, является MS
Excel. Поэтому данные лекции посвящены программированию именно для этого офисного пакета. На
большом количестве примеров рассматриваются основные конструкции языка, приемы разработки
макропроцедур.

(c) ООО “ИНТУИТ.РУ”, 2010-2016
(c) Бессонова И.А., Белоусова С.Н., 2010-2016

3

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

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

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

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

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

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

1. отслеживает действия пользователя, выполняемые при помощи команд меню или

клавиатурных эквивалентов (“горячих” клавиш),

2. генерирует и записывает соответствующие макрокоманды до тех пор, пока

пользователь не остановит процесс записи.

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

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

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

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

Если в процессе записи макроса была допущена ошибка, то можно отменить

4

последние команды ( Правка-Отменить ) или повторить запись некоторых команд.

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

5

Советы:

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

6

Рис. 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) можно набрать имя выполняемого макроса или выбрать его из
списка имеющихся макросов.

7

Рис. 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.

Внимание:

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

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

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

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

8

инструментов-Настройка (View-Toolbars-Customize), вкладка Команды (Commands)
(см. рис. 1.7).

После выбора в левом окне строки Макросы (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.

9

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

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

По умолчанию после запуска макрорекодера кнопка
отжата, что означает, что при

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

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

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

Можно установить использование относительных ссылок при записи процедур Для
этого после запуска макрорекордера нажмите кнопку Относительная ссылка
на

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

Важно:

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

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

Примеры

1. Процедура Макрос1 закрашивает ячейки интервала B1:C5 желтым цветом (рис.

1.8).

10