Тема 5. Создание макросов и собственных функций рабочего листа

Запись макросов — это чрезвычайнополезный инструмент, но не забывайте о следующих моментах.

• Запись лучше всего выполнять для простых макросов или небольшого фрагмента более сложного макроса.

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

• Созданная в результате записи программа зависит от определенных вами настроек.

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

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

1. Начните с пустой рабочей книги.

2. Убедитесь, что окно Excel полностью не развернуто. Добейтесь, чтобы на экране оставалось свободное место.

3. Нажмите < Alt+F11>, чтобы запустить окно VBE. и убедитесь, что окно также не максимизировано.

4. Измените размер и разместите окна Excel и VBE так, чтобы они были видны. (Лучше всего при этом свернуть окна других неиспользуемых приложений.)

5. Активизируйте Excel, выберите команду Сервис, Макрос, Начать запись и щелкните на кнопке ОК, чтобы запустить функцию записи макросов. Excel добавляет новый модуль (поя названием Modulel) и сохраняет его в текущем листе.

6. Перейдите к окну VBE.

7. В окне Project Explorer дважды щелкните на Module1, чтобы отобразить содержимое модуля в окне кода.

8. Закройте окно Project Explorer, чтобы освободить место под окно кода.

Теперь поработайте на рабочем листе, выбирая разные команды Excel. Посмотрите, как генерируется код в окне, представляющем модуль VBA. Вам следует выполнить несколько действий: выделить ячейки, ввести данные, изменить формат ячеек. Далее используйте меню и панели инструментов, создайте диаграмму, поработайте с графическими объектами и т.д.

При записи последовательности действий Excel обычно использует абсолютные ссылки на ячейки. Другими словами, при выделении ячейки Excel делает ее активной (а не исходную ячейку, активную при запуске программы). Проверим на примере, как это работает. Выполните следующие действия и проанализируйте получаемый результат.

1. Активизируйте рабочий лист и запустите функцию записи макросов.

2. Активизируйте ячейку В1.

3. Введите в ячейку В1 Янв.

4. Перейдите в ячейку С1 и введите Фев.

5. Продолжайте этот процесс, пока в ячейках B1: G1 не будут введены аббревиатуры первых шести месяцев года.

6. Щелкните на ячейке В1, чтобы снова сделать ее активной.

7. Остановите запись макроса.

Excel генерирует следующий код;

Sub Macro()

Range(" B1").Select

ActiveCell.FormulaRlCl = " Янв"

Range(" C1").Select

ActiveCell.FormulaRlCl = " Фев"

Range(" D1").Select

ActiveCell.FormulaRlCl = " Map"

Range(" E1").Select

ActiveCell.FormulaRlCl = " Anp"

Range(" F1").Select

ActiveCell.FormulaRlCl = " Май"

Range(" G1").Select

ActiveCell.FormulaRlCl = " Июнь"

Range(" B1").Select

End Sub

Чтобы выполнить этот макрос, выполните команду Сервис, Макрос, Макросы (или нажмите < Alt+F8>), выберите Макрос1 (или название записанного макроса) и щелкните на кнопке Выполнить.

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

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

При записи макроса отображается панель инструментов Остановить запись, состоящая только из двух кнопок. Вы можете изменить способ записи действий Excel, щелкнув на кнопке Относительная ссылка на панели инструментов Остановить запись. Это кнопка переключатель. Когда она находится в нажатом состоянии, используется относительный режим записи. Если кнопка — в нормальном состоянии, выполняется запись в абсолютном режиме. Вы можете изменить режим записи в любое необходимое вам время, даже в процессе выполнения записываемых операций.

Чтобы увидеть этот процесс, вначале следует очистить ячейки в диапазоне B1: D1, а затем выполнить следующие действия.

1. Перейдите к ячейке В1.

2. Выберите Сервис, Макрос, Начать запись.

3. Назовите макрос Относительный.

4. Щелкните на кнопке ОК, чтобы начать запись.

5. Щелкните на кнопке Относительная ссылка (на панели инструментов Остановить запись), чтобы изменить режим записи на относительный. При щелчке кнопка переходит в нажатое состояние.

6. Введите названия первых шести месяцев года в ячейки B1: G1 (как в предыдущем примере).

7. Выберите ячейку В1.

8. Остановите запись макроса.

Если установить относительный режим записи, созданный Excel код приобретет иной вид:

Sub Macro2()

ActiveCell.FormulaRlCl = " Янв"

ActiveCell.Offset(0, 1).Range(" A1").Select

ActiveCell.FormulaRlCl = " Фев"

ActiveCell.Offset(0, 1).Range(" A1").Select

ActiveCell.FormulaRlCl = " Map"

ActiveCell.Offset(0, 1).Range(" A1").Select

ActiveCell.FormulaRlCl - " Anp"

ActiveCell.Offset(0, 1).Range(" A1").Select

ActiveCell.FormulaRlCl и " Май"

ActiveCell.Offset(0, 1).Range(" A1").Select

ActiveCell.FormulaRlCl = " Июнь

ActiveCell.Offset(0, 1).Range(" A1").Select

End Sub

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

На первый взгляд, макрос выглядит несколько сложно, хотя на самом деле он довольно прост. Первый оператор вводит Янв в активную ячейку. (Используется активная ячейка, так как перед оператором не указан оператор, активизации ячейки.) Следующий оператор использует свойство Offset для перемещения курсора на одну ячейку вправо. Следующий оператор вставляет в нее текст и т.д. В отличие от предыдущего, данный макрос всегда начинает ввод текста в активной ячейке.

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

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

Sub Macro3

ActiveCell.Offset(0, 0) = " Янв"

ActiveCell.Offset(0, 1) = " Фев"

ActiveCell.offset(0, 2) = " Map1

ActiveCell.OffsetlO, 3) = " Anp"

ActiveCell.Offset(0, 4) = " Май"

ActiveCell.offsetlO, 5) = " Июнь"

End Sub

Данный макрос можно еще более упростить, используя конструкцию With-End With:

Sub Macros ()

With ActiveCell

.Offset(0, 0) = " Янв"

.Offset(0, 1) = " Фев"

.Offset(0, 2) = " Map"

.Offset(0, 3) = " Anp"

.Offset(0, 4) = " Май"

.Offset(0, 5) = " Июнь"

End Sub

Также вы можете выполнить все описанное выше в одном операторе:

Sub Macro54()

ActiveCell.Resize*, б)=Аrrау(" Янв", " Фев", " Map", " Anp", " Май", " Июнь")

End Sub

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

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

Вам предоставлена возможность ввести название процедуры, которую записываете. По умолчанию Excel использует названия Макрос1, Макрос2 и т.д. для каждого записываемого макроса Вы можете использовать имя по умолчанию и изменять это название позже. Однако лучше сразу называть макрос правильным именем.

Параметр Сочетание клавиш позволяет выполнить макрос с помощью комбинации клавиш. Например, вводя в этом поле w (в нижнем регистре), вы можете выполнить макрос, нажав комбинацию клавиш < Ctri+W>. При вводесимвола W (в верхнем регистре) макрос запускается по нажатию комбинации < Ctrl+Shift+W>.

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

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

По желанию вы можете записывать его либо в новой рабочей книге (Excel открывает пустую рабочую книгу), либо в личной книге макросов.

Личная книга макросов

Если вы создаете макросы VBA, которые считаете особенно полезными, можете сохранить их в личной книге макросов для дальнейшего использования. Это рабочая книга под названием Personal.xls, которая хранится в папке xlstart. При записи макроса одним из вариантов является запись в личной книге макросов. Файл Personal.xls не существует, пока вы не запишете в него хотя бы один макрос.

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

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

Итак, вы уже знаете о том, что запись действий при выполнении всего лишь одной команды (Файл, Параметры страницы) приводит к генерированию огромного объема кода VBA.

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

Кроме того, функция записи макросов не всегда генерирует получение наиболее эффективного кода. Проанализировав созданную программу, вы увидите, что, как правило. Excel анализирует, чтовыделено (т.е. определяет активный объект), и затем использует в генерируемых операторах объект Selection. Далее представлен пример записи при выделении диапазона ячеек, использовании кнопок на панели инструментов Форматирование (с целью изменить числовой формат) и применении полужирного и курсивного начертания.

Range(" Al: С5").Select

Selection.NumberFormat = " #, ##0.00"

Selection.Font.Bold = True

Selection.Font.Italic = True

Если вы используете для записи этого макроса диалоговое окно Формат, то обнаружите, что Excel записывает очень много лишнего. Запись щелчков на кнопках панели инструментов часто дает более эффективный код.

Мы описали лишь один способ выполнения указанных действий. Вы можете также использовать более эффективную конструкцию With-End With:

Range(" A1: C5").Select

With Selection i

.NumberFormat = " #, ##0, 00"

.Font.Bold = True

.Font.Italic = True

End With

Вы также можете избежать применения метода Select и сделать код еще лучше:

With Range(" A1: C5")

.NumberFormat = " #, ##0.00"

.Font.Bold = True

.Font.Italic = True

End With

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

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

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

Ниже представлено выражение.

Range(" A1").Value

Чтобы протестировать выражение, его необходимо выполнить. Для этого часто используется функция MsgBox:

MsgBox Range(" Al").Value

Чтобы выполнить предлагаемые примеры, поместите оператор в процедуре модуля VBA следующим образом:

Sub Test()

1 Здесь вводится оператор

End Sub

Затем перенесите курсор в любое место процедуры и нажмите < F5>, чтобы выполнить ее. Кроме того, убедитесь, что код выполняется в правильном контексте. Например, если оператор ссылается на лист Лист1, удостоверьтесь, что в рабочей книге действительно есть лист с названием Лист1.