Макросы MS Excel

Создание и вызов макросов

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

Макрос — это набор инструкций на VBA, или программа.

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

С точки зрения VBA макрос представляет собой процедуру Sub без параметров, написанную в программном модуле. Любая процедура, удовлетворяющая приведенным выше условиям, будет макросом (будет вызываться с помощью стандартных средств для вызова макросов). Большинство рассмотренных выше примеров являются макросами, а это значит, что мы уже умеем их создавать. Однако в Excel макросы можно создавать и иначе, просто записывая последовательность действий с помощью специальной функции записи макросов. Это позволяет создавать макросы без программирования.

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

• 1. На вкладке РАЗРАБОТЧИК в группе Код нажать кнопку Запись макроса.
• 2. В появившемся окне задать необходимые параметры. На начальном этапе изучения можно принимать параметры, указанные по умолчанию. Нажать ОК.
• 3. Выполнить необходимые действия. Они будут записываться в виде программы на языке VBA.
• 4. На вкладке РАЗРАБОТЧИК в группе Код нажать кнопку Остановить запись [?]. Можно также нажать на аналогичную кнопку, которая имеется в строке состояния Excel.

Для просмотра текста макроса перейдите в редактор и откройте нужный модуль. Если в проекте до записи макроса программных модулей не было, новый макрос будет находиться в модуле Modulel. Можно также на вкладке РАЗРАБОТЧИК в группе Код нажать кнопку Макросы, в открывшемся окне выбрать имя нужного макроса и нажать на кнопку Изменить.

Задать или изменить комбинацию клавиш, которая будет использоваться для вызова макроса, или его описание, можно в момент создания макроса или позднее, выполнив команду РАЗРАБОТЧИК / Код / Макросы. В открывшемся окне Макрос выбрать имя нужного макроса и нажать на кнопку Параметры. При назначении макросу комбинации клавиш, которая уже используется в Excel, данная команда имеет преимущество перед зарезервированной операцией.

При использовании функции записи макросов обращайте внимание, где сохраняется макрос. Если нужно, чтобы макрос всегда был доступен при работе в Excel на вашем компьютере, выберите вариант Личная книга макросов. В этом случае макрос сохраняется в специальном файле Personal, xlsb (личной книге макросов), который автоматически загружается при каждом запуске Excel. Учтите, что на другом компьютере макрос вам будет недоступен (там другая личная книга макросов). Если при сохранении вы выбираете вариант Эта Книга, макрос сохраняется в вашей рабочей книге, поэтому при переносе файла на другой компьютер вы сможете пользоваться этим макросом.

Для удаления макроса в окне Макрос выберите рабочую книгу, где находится макрос, выберите имя макроса, который нужно удалить, и нажмите на кнопку Удалить. Другой способ удалить макрос — в редакторе VBA открыть модуль, содержащий макрос, и удалить текст макроса средствами редактора.

Если макрос, который требуется удалить, хранится в скрытой личной книге макросов, то для ее отображения на вкладке ВИД в группе Окно нажмите кнопку Отобразить окно, в появившемся окне выберите пункт PERSONAL, а затем нажмите кнопку ОК. Чтобы скрыть личную книгу макросов воспользуйтесь командой ВИД / Окно / Скрыть окно.

Для выполнения макроса можно использовать несколько способов.

• 1. Команда Run / Run Sub / User Form, или кнопка Щ, или клавиша F5 в редакторе VBA (курсор должен находиться внутри макроса).
• 2. В диалоговом окне Макрос (команда РАЗРАБОТЧИК / Код / Макросы) выбрать имя макроса и нажать кнопку Выполнить.
• 3. С помощью комбинации клавиш, назначенной макросу.
• 4. Можно связать макрос с кнопкой на панели быстрого доступа или в настраиваемой группе на ленте.
• 5. Если назначить макрос объекту, графическому объекту или элементу управления, размещенным на рабочем листе, то нужно просто щелкнуть мышкой на этом элементе.

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

Чтобы связать макрос с кнопкой на панели быстрого доступа нужно открыть окно, изображенное на рис. 10.3 (команда ФАЙЛ / Параметры / Панель быстрого доступа).

Рис. 10.3. Настройка панели быстрого доступа В списке Выбрать команды из выберите пункт Макросы. С помощью кнопки Добавить переместите нужный макрос в правый список. Кнопка Изменить позволяет изменить значок кнопки макроса и имя, которое отображается при наведении указателя мыши на кнопку. Список Настройка панели быстрого доступа определяет для каких рабочих книг настраивается панель. Для макросов из личной книги макросов нужно в этом списке выбирать значение Для всех документов.

Чтобы добавить кнопку макроса в настраиваемую группу на ленте откройте окно, изображенное на рис. 10.4 (команда ФАЙЛ / Параметры / Настроить ленту).

Выделите вкладку, на которую нужно добавить настраиваемую группу и нажмите кнопку Создать группу. На выбранную вкладку будет добавлена группа Новая группа (настраиваемая). Чтобы изменить имя группы, воспользуйтесь кнопкой Переименование. В списке Выбрать команды выберите пункт Макросы. Выберите макрос, который нужно добавить в новую группу, и нажмите кнопку Добавить. Макрос будет добавлен в созданную группу. Чтобы изменить имя и значок кнопки для макроса, снова используйте кнопку Переименование.

Рис. 10.4. Создание настраиваемой группы на ленте.

Вы можете также разместить на рабочем листе различные элементы управления (кнопки, поля и др.) и связать их с макросом. Чаще всего для этой цели используются кнопки. Чтобы добавить элемент управления на рабочий лист, на вкладке РАЗРАБОТЧИК в группе Элементы управления нажмите кнопку Вставить, а затем в разделе Элементы управления формы выберите нужный элемент и щелкните на листе место, где должен быть расположен левый верхний угол выбранного элемента. Все действия с элементом, в том числе удаление, перемещение, изменение свойств, выполняются с помощью контекстного меню.

Чтобы связать элемент управления с макросом, щелкните его правой кнопкой мыши и выберите команду Назначить макрос. Откроется диалоговое окно Назначить макрос объекту. Выберите книгу в списке Находится в. Выберите имя макроса в списке или введите его в поле Имя макроса. Для существующего макроса нажмите кнопку ОК. Если нажать на кнопку Записать, то начнется запись нового макроса с помощью функции записи макроса. Если нажать на кнопку Создать, то будет создай новый макрос в редакторе VBA.

Совершенно аналогично можно назначить макрос графическим объектам (рисункам, фигурам), размещенным на листе.

Пример 10.21.

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

Решение

• 1. Установите курсор на любой ячейке рабочего листа, содержащей текст.
• 2. Выполните команду РАЗРАБОТЧИК / Код/ Записать макрос.
• 3. В появившемся окне нажмите ОК, оставив предложенные значения параметров по умолчанию. Если вы первый раз записываете макрос, то его имя будет Макрос!
• 4. Установите следующие параметры форматирования ячейки: шрифт — Arial, размер шрифта — 14, цвет шрифта — красный. Остановите запись макроса, воспользовавшись кнопкой на вкладке РАЗРАБОТЧИК. При записи макроса не делайте лишних операций, так как все ваши действия записываются.
• 5. Проверим работу макроса. Для этого измените форматирование вашей ячейки с текстом и установите на нее курсор. Выведите на экран окно Макрос (команда РАЗРАБОТЧИК / Код / Макросы). В появившемся окне выберите имя макроса (Макрос 1) и нажмите на кнопку Выполнить. Текст в ячейке должен быть отформатирован так, как мы это сделали при записи макроса.
• 6. Снова откройте окно Макрос. Выберите Макрос 1 и нажмите на кнопку Параметры.
• 7. В появившемся окне задайте сочетание клавиш и описание. Введенный текст вы увидите в нижней части окна Макрос, когда в списке макросов курсор находится на имени макроса — Макрос 1.
• 8. Закройте окно Макрос и вызовите ваш макрос с помощью сочетания клавиш. Не забудьте предварительно изменить форматирование ячейки с текстом.
• 9. Откройте текст созданного макроса. Для этого в окне Макрос выберите имя макроса и нажмите па кнопку Изменить. Вы видите, что система добавила в вашу рабочую книгу новый модуль и разместила в нем процедуру Макрос1, которая выглядит примерно так (конкретные значения зависят от настроек вашего компьютера и инструментов, которые вы использовали при форматировании):

Sub Макрос]. ().

I.

' Макрос]. Макрос.

I.

With Selection. Font .Name = «Arial» .

.Size = 11.

.Strikethrough = False. Superscript = False. Subscript = False. OutlineFont = False. Shadow = False.

.Underline = xlUnderlineStyleNone. ThemeColor = xlThemeColorLightl. TintAndShade = 0. ThemeFont = xlThemeFontNone.

End With.

With Selection. Font .Name = «Arial» .

.Size = 14.

.Strikethrough = False. Superscript = False. Subscript = False. OutlineFont = False. Shadow = False.

.Underline = xlUnderlineStyleNone. ThemeColor = xlThemeColorLightl. TintAndShade = 0. ThemeFont = xlThemeFontNone.

End With.

With Selection. Font .Color = -16 776 961.

.TintAndShade = О End With.

End Sub.

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

Sub Макрос2().

With Selection. Font .Name = «Arial» .

.Size = 14. Color = -16 776 961 End With.

End Sub.

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

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

Обратите внимание, как задается цвет. Использованная числовая константа соответствует некоторому оттенку красного цвета. Запомнить такую константу невозможно, да и не нужно. Всегда можно записать макрос и потом воспользоваться автоматически созданным значением. Однако в языке есть и другие способы задания цветов:

— для основных цветов существуют именованные константы (для красного это vbRed), поэтому можно было бы эту инструкцию записать в виде.

. Color = vbRed.

В справке можно посмотреть, какие еще есть константы цвета, для чего нужно установить курсор на имени константы и нажать клавишу F1;

— можно использовать функцию RGB (от англ, red-green-blue — красный, зеленый, синий). У этой функции три аргумента, каждый из которых может иметь значение от 0 до 255 и задает долю красного, зеленого и синего компонента в цвете. RGB (255, 255, 255) соответствует белому цвету, RGB (0, 0, 0) — черному. Если аргументы имеют одинаковые значения, то получим оттенок серого. Самому яркому красному цвету соответствует значение RGB (255, 0, 0).

Пример 10.22.

Рассмотрим, как при автоматическом создании макроса записывается вставка формулы в ячейку.

Решение

В качестве примера запишите макрос, который вставляет в ячейку С7 формулу =ОКРУГЛ (А7+$Л$ 1 *В7;2) (функция округляет значение первого аргумента до двух знаков после запятой). В тексте макроса появится инструкция:

ActiveCell.FormulaRICl = «=ROUND (RC[-2]+R1C1*RC[-1], 2)» .

Как видим, строка с формулой присваивается свойству FormulaRICl, но выглядит формула иначе. Во-первых, используется другое имя функции. На самом деле в формуле использовано имя нашей функции в англоязычной версии. Во-вторых, ссылки на ячейки выглядят необычно. Дело в том, что для ссылок на ячейки в Excel используются два стиля: А1 и R1C1.

Обычно вы работаете со ссылками в стиле Л1: ссылка состоит из имени столбца (обозначаются латинскими буквами) и номера строки. Признаком абсолютной ссылки является знак доллара перед именем строки или столбца.

Макросы используют стиль R1C1. В этом случае после буквы R указывается номер строки ячейки, после буквы С — номер столбца. Для задания относительной ссылки в стиле R1C1 указывается смещение, но отношению к активной ячейке (в квадратных скобках). Знак определяет направление смещения. Например, RC[-1] (относительная ссылка на ячейку, расположенную в той же строке в предыдущем столбце), R[3]C[2] (относительная ссылка на ячейку, расположенную на три строки ниже и на два столбца правее), R2C2 (абсолютная ссылка на ячейку, расположенную во второй строке во втором столбце), R[-l] (относительная ссылка на предыдущую строку), R (абсолютная ссылка на текущую строку).

При автоматическом создании макросов формулы всегда записываются в таком виде, но если вы сами пишете текст макроса, то вместо свойства FormulaRICl можно использовать свойство FormulaLocal. В этом случае вы можете использовать в формуле формат А1 и привычные (с учетом национальных настроек) названия функций:

ActiveCell.FormulaLocal = «=ОКРУГЛ (А7+$А$ 1*В7;2)» .

Выше уже отмечалось, что в своих программах вы можете вызывать встроенные функции Excel, используя свойство WorksheetFunction объекта Application. Встроенные функции являются методами WorksheetFunction. Имена методов соответствуют названиям функций в англоязычной версии. Однако часто мы знаем название нужной функции только в русской версии. В этом случае нужно записать макрос, который вставляет в ячейку формулу, содержащую обращение к функции. Проанализировав текст макроса, вы узнаете нужное имя функции. А функцию ОКРУГЛ в программе на VBA мы можем вызвать, например, так:

z = WorksheetFunction. Round (х, 2).