Редактирование макросов

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

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

1.   Переключитесь на лист, содержащий введенный ранее адрес, и выделите ячейку А6 с названием фирмы.

2.   В меню Сервис выберите команду Макрос и затем — Начать запись, чтобы открыть диалоговое окно Запись макроса. Введите название МасгоТеmp в поле Имя макроса и нажмите кнопку ОК. Excel выведет на экран панель инструментов. Остановка записи.

3.   Выберите в меню Формат (Format) команду Ячейки (Cells). Затем в списке шрифтов на вкладке Шрифт (Font) открывшегося диалогового окна выберите шрифт Arial (или другой установленный шрифт) размером 14 пунктов и настройте его как полужирный курсив. Затем нажмите кнопку ОК, чтобы применить это форматирование.

4.   Щелкните на кнопке Остановить запись панели инструментов Остановка записи.

5.   В меню Сервис выберите команду Макрос. В диалоговом окне Макрос выберите имя

MacroTemp и нажмите кнопку Изменить (Edit).

Окно проекта по-прежнему содержит список объектов, принадлежащих активной книге: три листа, объект, представляющий книгу в целом, и один модуль. Но в окне, показывающем содержимое модуля, теперь уже два макроса, что видно из рис. 31.8.

Рис. 31.8. Макрос MacroTemp, содержащий только что записанные инструкции форматирования

6.   Выделите все строки конструкции With…End With и затем в меню Edit (Правка)

выберите команду Сору (Копировать).

7.   Прокрутите окно вверх, чтобы вывести на экран макрос Адрес_Компании.

8.   Щелкните в начале строки, содержащей инструкцию

Range("A7").Select

Затем нажмите клавишу Enter, чтобы вставить пустую строку, и укажите точку вставки, щелкнув в начале образовавшейся строки.

9.   В меню Edit (Правка) выберите команду Paste ( Вставить).

10.   Прокрутите окно до макроса MacroTemp и удалите этот макрос, от инструкции Sub

no инструкцию End Sub.

Теперь текст макроса Адрес_Компании выглядит так, как показано далее (за ис-

ключением комментариев):

Sub Адрес_Компании() Range("A6").Select

ActiveCell.FormulaR1C1 = "Региональная Торговая Компания" With Selection.Font

.Name = "Arial Cyr"

.FontStyle = "полужирный курсив"

.Size = 14

.Strikethrough = False

.Superscript = False

.Subscript = False

.OutlineFont = False

.Shadow = False

.Underline = xlUnderlineStyleNone

.ColorIndex = xlAutomatic End With

Range("A7").Select

ActiveCell.FormulaR1C1 = "Морская улица, 15" Range("A8").Select

ActiveCell.FormulaR1C1 = "325611, Кузнецк, Россия" Range("A9").Select

End Sub

Чтобы проверить работу измененного макроса, вернитесь в Excel (нажмите клавиши Alt+F11 или щелкните на кнопке Excel на Панели задач Windows) и нажмите клавиши Ctrl+Shift+A.

Конструкция With…End With задает ряд свойств объекта, в данном случае — шрифта текущего выделения. Эта конструкция представляет собой краткую форму записи последовательности инструкций VBA, которые иначе имели бы следующий вид:

Selection.Font.Name = "Arial" Selection.Font.FontStyle = "полужирный курсив" Selection.Font.Size = 14

и т. д. В макросе Адрес_Компании оба объекта, ActiveCell и Selection, указывают на один и тот же диапазон, а именно ячейку Аб. Поскольку ряд параметров форматирования шрифта можно применить к целому диапазону, Excel использует ключевое слово Selection вместо ActiveCell. Задание свойств внутри структур With…End With, кроме упрощения инструкций, уменьшает время выполнения макроса.

Абсолютные и относительные ссылки в макросах

В макросе Адрес_Компании обращение к ячейкам осуществляется в виде абсолютных ссылок в формате «А1», и поэтому макрос всегда помещает отформатированный адрес фирмы в ячейку А6.

В VBA указание на ячейки записывается в виде абсолютных ссылок, если вы не нажмете кнопку Относительная ссылка (Relative Reference) на панели инструментов

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

Предположим, что вместо занесения названия и адреса фирмы в диапазон А6:А8 активного листа вы хотите вставить адрес в ячейку, которая окажется активной на момент запуска макроса. Для этого нужно создать новую версию макроса Адрес_Компании (назовем его Адрес_КомпанииОтн), где будут действовать уже относительные ссылки, в порядке, описанном ниже.

1.   Переключитесь в рабочий лист и очистите ячейки А6:А8.

2.   В меню Сервис выберите команду Макрос и затем — Начать запись. В диалоговом окне Запись макроса укажите Адрес_КомпанииОтн в качестве имени макроса, в поле Сочетание клавиш введите строчную букву а и в поле Описание — текст Ввод адреса фирмы в произвольную ячейку. Нажмите кнопку ОК, и Excel выведет на экран панель инструментов Остановка записи.

3.   На панели инструментов Остановка записи щелкните на кнопке Относительная ссылка.

4.   В меню Формат выберите команду Ячейки. Задайте 14-пунктный шрифт Arial и полужирный курсив, а затем нажмите кнопку ОК.

5.   Введите текст Региональная Торговая Компания. В ячейку ниже введите часть адреса Морская улица, 15. И в ячейку под ней введите 325611, Кузнецк, Россия.

6.   Щелкните на кнопке Остановить запись.

Проверьте созданный макрос, выделяя разные ячейки и нажимая клавиши Ctrl+a. Если вы переключитесь в VBA, выберете команду Сервис ? Макрос ? Макросы, найдете макрос Адрес_КомпанииОтн и нажмете кнопку Изменить, то увидите следующий набор инструкций:

Sub Адрес_КомпанииОтн() With Selection.Font

.Name = "Arial Cyr"

.FontStyle = "полужирный курсив"

.Size = 14

.Strikethrough = False

.Superscript = False

.Subscript = False

.OutlineFont = False

.Shadow = False

.Underline = xlUnderlineStyleNone

.ColorIndex = xlAutomatic End With

ActiveCell.FormulaR1C1 = "Региональная Торговая Компания" ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = "Морская улица, 15" ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = "325611, Кузнецк, Россия" ActiveCell.Offset(1, 0).Range("A1").Select

End Sub

Первоначальный макрос Адрес_Компании вводит адрес фирмы в диапазон А6:А8

независимо от того, какая ячейка является активной при его запуске. Новый же

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

инструкции для активизации первой ячейки, поскольку она уже и так выделена.

Кроме того, чтобы сделать активной вторую ячейку, для первоначального макроса была сгенерирована инструкция

Range("A7").Select

В то же время в новой версии ей соответствует предложение

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

Чтобы перейти из активной ячейки в следующую, расположенную ниже, ячейку, в новом макросе VBA применяет к выделенному объекту метод Offset с двумя аргументами, задающими смещение по строкам и по столбцам. Ключевое слово Range возвращает диапазон, имеющий ту же размерность, что и аргумент метода. В данном случае аргумент А1 определяет диапазон, суженный до одной ячейки. Наконец, метод Select выделяет этот диапазон, как и в первоначальном макросе.

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

Макросы и процедуры

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

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

В качестве примера разделим макрос Адрес_КомпанииОтн на две части:

1.   Сначала в Excel выберите команду Сервис ? Макрос ? Макросы, затем найдите макрос Адрес_КомпанииОтн и нажмите кнопку Изменить. Выделите инструкции, которые форматируют название фирмы:

With Selection.Font

.Name = "Arial"

.FontStyle = "полужирный курсив"

.Size = 14

. Stn’kethrough = False

.Superscript = False

.Subscript = False

.OutlineFont = False

.Shadow = False

.Underline = xlUnderlineStyleNone

.Colorlndex = xlAutomatic End With

2.   В меню Edit (Правка) редактора VBA выберите команду Cut (Вырезать).

3.   Щелкните ниже инструкции End Sub в конце макроса Адрес_КомпанииОтн, затем введите Sub Шрифт_Компании().

4.   Редактор VBA вставит инструкцию End Sub. Добавьте пустую строку между инструкциями Sub и End Sub и в меню Edit (Правка) выберите команду Paste (Вставить), чтобы поместить на ее место вырезанные инструкции.

Мы создали новый макрос Шрифт_Компании, переместив инструкции форматирования шрифта из макроса Адрес_КомпанииОтн. Как уже отмечалось, чтобы запустить из одного макроса другой, вы должны указать в нем имя вызываемой процедуры. Измените макрос Адрес_КомпанииОтн так, чтобы в нем присутствовал вызов для Шрифт_Компании, то есть выполните следующие действия:

1.   Щелкните в конце инструкции

ActiveCell.FormulaR1C1 = "Региональная Торговая Компания"

Нажмите клавишу Enter, чтобы вставить новую строку.

2.   Введите имя Шрифт_Компании. После этого наши два макроса должны выглядеть так:

Sub Адрес_КомпанииОтн()

Шрифт_Компании

ActiveCell.FormulaR1C1 = "Региональная Торговая Компания" ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = "Морская улица, 15" ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = "325611, Кузнецк, Россия" ActiveCell.Offset(1, 0).Range("A1").Select

End Sub

Sub Шрифт_Компании() With Selection.Font

.Name = "Arial Cyr"

.FontStyle = "полужирный курсив"

.Size = 14

.Strikethrough = False

.Superscript = False

End Sub

.Subscript = False

.OutlineFont = False

.Shadow = False

.Underline = xlUnderlineStyleNone

.ColorIndex = xlAutomatic End With

Когда вы запустите макрос Адрес_КомпанииОтн, нажав клавиши Ctrl+a, Excel будет последовательно выполнять инструкции этого макроса, начиная с первой. Встретив инструкцию, обращающуюся к процедуре Шрифт_Компании, программа перейдет к первой строке этого макроса. При достижении инструкции End Sub в конце макроса Шрифт_Компании Excel возвратится в точку, расположенную непосредственно за вызовом Шрифт_Компании в макросе Адрес_КомпанииОтн, и продолжит выполнение последнего, пока не дойдет до инструкции End Sub.

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

При записи макроса Адрес_Компании мы предложили в качестве места его расположения модуль, принадлежащий активной книге. Но такой макрос становится доступен только после открытия его собственной книги.

Если необходимо, чтобы макрос был доступен всегда, сохраните его в личной книге макросов. Эта книга обычно скрыта, новы можете вывести ее на экран, выбрав в меню Окно (Window) команду Отобразить (Unhide) и дважды щелкнув на книге Personal в диалоговом окне Вывод на экран скрытого окна книги (Unhide). Если личная книга макросов еще пуста, элемент Personal.xls в списке этого окна не появится. Поэтому перед записью любого макроса выберите пункт Личная книга макросов (Personal Macro Workbook) в раскрывающемся списке Сохранить в (Store Macro In) диалогового окна Запись макроса. Файл личной книги макросов Personal.xls будет помещен в папку XLStart. Теперь книга Personal.xls, как и любая другая, находящаяся там, будет открываться автоматически каждый раз при запуске Excel. Поскольку личная книга макросов всегда доступна при работе в Excel, имеет смысл в нее помещать макросы, которые могут потребоваться вам в любых рабочих книгах.

Резюме

Прочитав данную главу, вы научились создавать макросы путем их записи. Как вы убедились, запись макросов, как правило, влечет за собой создание избыточных инструкций. Так, например, в макросе Шрифт_Компании строки

. Strikethrough = False

.Superscript = False

.Subscript = False

.OutlineFont = False

. Shadow = False

.Underline = xlUnderlineStyleNone

.Colorlndex = xlAutomatic

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

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

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

Источник: Эффективная работа: Microsoft Office Excel 2003 / М. Додж, К. Стинсон — СПб.: Питер, 2005. — 1088 с: ил.