Теперь самое время еще раз проверить, отображается ли на ленте Excel вкладка Разработчик.
Средство записи макросов – чрезвычайно полезный инструмент, но не забывайте о следующих моментах.
• Запись лучше всего выполнять для простых макросов или небольшого фрагмента более сложного макроса.
• Не все выполняемые в Excel действия могут записываться.
• Команда записи макросов не может генерировать код, который включает циклические структуры (т.е. повторяющиеся операторы), а также присваивать переменные, выполнять условные операторы, отображать диалоговые окна и т.д.
• Средство записи макросов всегда создает процедуры типа Sub. С ее помощью невозможно создать процедуру типа Function.
• Созданная в результате записи программа зависит от определенных вами настроек.
• Придется часто дорабатывать записанный код, чтобы удалить лишние команды.
Что записывается
Средство записи макросов Excel преобразует действия, выполненные с помощью мыши и клавиатуры, в код VBA. Принцип выполнения можно описать на нескольких страницах, но целесообразнее рассмотреть пример. Выполните следующие действия.
1. Начните с пустой рабочей книги.
2. Убедитесь, что окно Excel полностью не развернуто. Добейтесь, чтобы на экране оставалось свободное место.
3. Нажмите клавиши <Alt+F11> для открытия окна VBE. Убедитесь в том, что это окно развернуто не полностью. Если это условие не выполняется, одновременный просмотр окон VBE и Excel невозможен.
4. Измените размер и разместите окна Excel и VBE таким образом, чтобы возможен был их просмотр (для достижения наилучших результатов сверните окна всех выполняющихся приложений).
5. Перейдите в окно Excel, выберите команду Разработчик→Код→Запись макроса и щелкните на кнопке ОК для запуска функции записи макросов.
6. Перейдите в окно VBE.
7. В окне Project Explorer дважды щелкните на узле Module1 для отображения модуля в окне кода.
8. Закройте окно Project Explorer в среде VBE для лучшего просмотра окна кода.
Размер окон зависит от выбранного разрешения экрана. Если вы являетесь счастливым обладателем двухмониторной системы, на первом мониторе отобразите окно VBA, а на втором – окно Excel.
Теперь поработайте на рабочем листе, выбирая разные команды Excel. Посмотрите, как генерируется код в окне, представляющем модуль VBA. Вам следует выполнить несколько действий: выделить ячейки, ввести данные, изменить формат ячеек. Далее используйте команды ленты, создайте диаграмму, поработайте с графическими объектами и т.д. Все прояснится, когда на экране появится код программы.
Абсолютный или относительный
При записи последовательности действий Excel обычно использует абсолютные ссылки на ячейки. Другими словами, выделяя ячейку, Excel делает ее активной (а не исходную ячейку, активную при запуске программы). Проверим на примере, как это работает. Выполните следующие действия и проанализируйте полученный результат.
1. Активизируйте рабочий лист и запустите функцию записи макросов.
2. Активизируйте ячейку В1.
3. Введите в ячейку В1 Янв.
4. Перейдите в ячейку С1 и введите Фев.
5. Продолжайте этот процесс, пока в ячейках В1:G1 не будут введены аббревиатуры первых шести месяцев года.
6. Щелкните на ячейке В1 для ее повторной активизации.
7. Остановите запись макроса и просмотрите полученный код в окне редактора VBE.
Excel сгенерирует следующий код.
Sub Macro1()
Range(“B1”).Select
ActiveCell.FormulaR1C1 = “Янв”
Range(“C1”).Select
ActiveCell.FormulaR1C1 = “Фев”
Range(“D1”).Select
ActiveCell.FormulaR1C1 = “Map”
Range(“E1”).Select
ActiveCell.FormulaR1C1 = “Anp”
Range(“F1”).Select
ActiveCell.FormulaR1C1 = “Май”
Range(“G1”).Select
ActiveCell.FormulaR1C1 = “Июн”
Range(“B1”).Select
End Sub
Для запуска макроса на выполнение выберите команду Разработчик→Код→Макросы либо нажмите комбинацию клавиш <Alt+F8>, выберите Макрос1 (либо название записанного макроса) и щелкните на кнопке Выполнить.
Макрос вновь выполнит действия, записанные ранее. Действия выполняются независимо от того, активны ли соответствующие ячейки на рабочем листе. При записи макроса с использованием абсолютных ссылок вы всегда будете получать одни и те же результаты.
В некоторых случаях требуется, чтобы записанный макрос работал с относительными адресами ячеек. Например, такой макрос обычно вводит названия месяцев в активной ячейке. В таком случае для записи макроса используется относительная форма записи.
Для реализации относительной формы записи воспользуйтесь командой Разработчик→Код→Относительные ссылки. Эта кнопка играет роль переключателя. Как только она окрашивается в другой цвет, это служит признаком записи кода с относительными ссылками. Если кнопка окрашена в стандартный цвет, записывается код с абсолютными ссылками. Метод записи может быть изменен в любой момент, даже в середине процесса записи.
Чтобы увидеть, как работает относительная форма записи макросов, очистите содержимое ячеек В1:G1 и выполните следующие действия.
1. Активизируйте ячейку В1.
2. Выберите команду Разработчик→Код→Запись макроса.
3. Для начала записи щелкните на кнопке ОК.
4. Щелкните на кнопке Относительные ссылки для выбора относительного режима записи. При этом цвет данной кнопки изменится.
5. В ячейки B1:G1 введите сокращенные названия первых шести месяцев, как в предыдущем примере.
6. Выделите ячейку В1.
7. Остановите запись макроса.
Если установить относительный режим записи, созданный Excel код приобретет иной вид.
Sub Масrо2()
ActiveCell.FormulaR1C1 = “Янв”
ActiveCell.Offset(0, 1).Range(“А1”).Select
ActiveCell.FormulaR1C1 = “Фев”
ActiveCell.Offset(0, 1).Range(“A1”).Select
ActiveCell.FormulaR1C1 = “Map”
ActiveCell.Offset(0, 1).Range(“A1”).Select
ActiveCell.FormulaR1C1 = “Anp”
ActiveCell.Offset(0, 1).Range(“A1”).Select
ActiveCell.FormulaR1C1 = “Май”
ActiveCell.Offset(0, 1).Range(“A1”).Select
ActiveCell.FormulaR1C1 = “Июн”
ActiveCell.Offset(0, -5).Range(“A1”).Select
End Sub
Для вызова этого макроса на выполнение активизируйте рабочий лист и выберите команду Разработчик→Код→Макросы. Выберите имя нужного макроса и щелкните на кнопке Выполнить.
В рассматриваемом примере процедура была незначительно изменена (мы активизировали начальную ячейку перед началом записи). Это важная операция при записи макроса, использующего в качестве основы активную ячейку.
Макрос кажется сложным, хотя на самом деле это не так. Первый оператор вводит Янв в активную ячейку (используется активная ячейка, так как перед оператором не указан оператор активизации ячейки). Следующий оператор использует метод Select (Выделение) (наравне со свойством Offset (Смещение)) для перемещения курсора на одну ячейку вправо. Следующий оператор вставляет в нее текст и т.д. В результате исходная ячейка выделяется путем вычисления относительного адреса (смещения). В отличие от предыдущего, данный макрос всегда начинает ввод текста в активной ячейке.
Функция записи макросов работает в двух различных режимах, причем важно знать, в каком режиме записи макроса вы находитесь в данный момент, иначе результат может не совпадать с ожидаемым.
В этом макросе сгенерирован код, который будто бы ссылается на ячейку А1, что может показаться странным, поскольку ячейка А1 в макросе не используется. Это побочный эффект функции записи макросов. На данном этапе макрос работает так, как требуется.
Кстати, код, сгенерированный Excel, намного сложнее, чем необходимо, и представляет не самый эффективный способ программирования описанной операции. Следующий макрос, который был введен вручную, представляет собой более простой и быстрый способ выполнить те же действия. В примере показано, что не обязательно выделять ячейку перед помещением в нее информации – это важный момент, который существенно ускоряет работу макроса.
Sub Масrо3()
ActiveCell.Offset(0, 0) = “Янв”
ActiveCell.Offset(0, 1) = “Фев”
ActiveCell.Offset(0, 2) = “Map”
ActiveCell.Offset(0, 3) = “Anp”
ActiveCell.Offset(0, 4) = “Май”
ActiveCell.Offset(0, 5) = “Июн”
End Sub
Данный макрос можно еще больше упростить с помощью конструкции With-End With.
Sub Macro4()
With ActiveCell
.Offset(0, 0) = “Янв”
.Offset(0, 1) = “Фев”
.Offset(0, 2) = “Map”
.Offset(0, 3) = “Anp”
.Offset(0, 4) = “Май”
.Offset(0, 5) = “Июн”
End With
End Sub
Если же вы гениальный программист на VBA, можете поразить своих коллег, выполнив все описанные выше действия в одном операторе.
Sub Масrо5()
ActiveCell.Resize(, 6) = Array(“Янв”,“Фев”,“Мар”,“Апр”,“Май”,“Июн”)
End Sub
Параметры записи
В процессе записи действий, применяемых для создания VBA-кода, в распоряжении пользователя оказывается ряд параметров диалогового окна Запись макроса. Все они описаны ниже.
• Имя макроса (Macro Name). Предоставляет возможность ввести название записываемой процедуры. По умолчанию Excel использует названия Макрос1, Макрос2 и т.д. для каждого записываемого макроса. Вы можете использовать имя по умолчанию и изменить его позже, однако лучше сразу назвать макрос правильным именем.
• Комбинация клавиш (Shortcut Key). Позволяет выполнить макрос с помощью комбинации клавиш. Например, введя в данном поле w (в нижнем регистре), вы можете выполнить макрос. Для этого нажмите комбинацию клавиш <Ctrl+W>. После ввода символа W (в верхнем регистре) макрос запускается по нажатию комбинации клавиш <Ctrl+Shift+W>. Помните о том, что комбинация клавиш, назначенная макросу, переопределяет встроенную комбинацию клавиш (если она есть). Например, если для вызова макроса воспользоваться клавишами <Ctrl+B>, вы не сможете применить эти клавиши для выбора полужирного стиля выделения ячеек. Вы вправе в любой момент добавить или изменить комбинацию клавиш, поэтому необязательно задавать параметр при записи макроса.
• Параметр Сохранить в (Store Macro In). Указывает Excel, где должен храниться макрос, который записывается. По умолчанию Excel помещает записанный макрос в модуль активной рабочей книги. По желанию можно записать его либо в новой рабочей книге (Excel открывает пустую рабочую книгу), либо в личной книге макросов.
Excel запоминает ваш выбор, поэтому в следующий раз по умолчанию макрос записывается туда же, куда он записывался в предыдущий раз.
• Описание (Description). При желании в поле Описание можно ввести описание макроса. Введенный текст отображается в начале кода макроса в виде комментария.
Личная книга макросов
В процессе создания макрос можно сохранить в личной книге макросов (Personal Macro Workbook). Здесь можно сохранить макросы VBA, которые вы считаете особенно полезными. Эта рабочая книга называется Personal.xlsb и хранится в папке XLStart. Загрузка данной рабочей книги происходит после запуска Excel, причем вы сразу же получаете доступ к хранящимся там макросам. Книга Personal.xlsb скрыта, поэтому при обычной работе в Excel она не видна. Файл Personal.xlsb не существует до тех пор, пока не будет записан первый макрос.
Улучшение записанных макросов
Вы знаете о том, что запись действий при выполнении всего лишь одной команды (Разметка страницы→Параметры страницы→Ориентация) приводит к генерированию большого объема кода VBA. Во многих случаях записанный код включает ненужные команды, которые следует удалять вручную.
Кроме того, функция записи макросов не всегда гарантирует получение эффективного кода. Работая с созданной программой, вы можете обнаружить следующее: как правило, Excel анализирует то, что выделено (т.е. определяет активный объект), а затем использует в генерируемых операторах объект Selection. Например, ниже приводится пример кода, который генерируется при выделении диапазона ячеек и использовании некоторых кнопок вкладки Главная для изменения числового форматирования, а также применения полужирного и курсивного стилей.
Range(“A1:С5”).Select
Selection.Style = “Comma”
Selection.Font.Bold = True
Selection.Font.Italic = True
Записанный код VBA работает, но он представляет собой всего лишь один способ выполнения этих действий. Можно также воспользоваться более эффективной конструкцией With-End With, как показано ниже.
Range(“A1:С5”).Select
With Selection
.Style = “Comma”
.Font.Bold = True
.Font.Italic = True
End With
Можно избежать применения метода Select, в результате чего появится более эффективный код.
With Range(“A1:С5”)
.Style = “Comma”
.Font.Bold = True
.Font.Italic = True
End With
Если в вашем приложении важна скорость выполнения операций, то необходимо тщательно анализировать любой записанный код VBA, чтобы сделать его как можно более эффективным.
Конечно, придется разобраться в VBA, прежде чем приступить к улучшению кода записанных макросов. А пока примите к сведению, что записанный код VBА не всегда является наилучшим и наиболее эффективным решением задачи.
Код может состоять из единственного оператора. В этом случае используется окно отладки VBE (Immediate). Оно применяется для “немедленного” выполнения операторов – без создания процедуры. Если окно отладки не отображается, нажмите клавиши <Ctrl+G>.
Введите в окно отладки оператор VBA и нажмите клавишу <Enter>. Чтобы проверить выражение в окне отладки, введите перед ним знак вопроса (?) – символ вызова команды Print. Например, в окно отладки можно ввести следующий код:
? Range(“A1”).Value
Результат выполнения выражения отображается в следующей строке окна отладки.