Практическая часть

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

Для изучения материала данного раздела откройте файл Recording.xls и внимательно рассмотрите каждый пример с включенной функцией записи макросов. Записанный код может немного отличаться от приведенного в файле, поскольку упражнения допускают получение различных вариантов решения.

ПРИМЕР 1. ВВОД ФОРМУЛЫ

В этом примере, показанном на рис. 1, у пользователя запрашивается диапазон и формула суммирования значений этого диапазона.

Рис. 1. Рабочий лист примера 1

Записанный код и внесенные в него изменения показаны ниже в процедуре SumFormula и SumFormula1. Если вы представляете себе, как в Excel осуществится управление диапазонами, то точно знаете, что сначала диапазон необходим выделить, и только после этого можно приступать к его изменению. Таким образом при записи макроса во время работы с диапазонами в записанном коде будет присутствовать метод Select. На самом деле использовать этот метод совсем не обязательно. Если операция над диапазоном выполняется в коде VBA, выделять диапазон не стоит. Как можно заметить в модифицированной версии процедуры, метод Select вообще не используется. Тем не менее, в этой версии имеется ссылка на лист Пример1; она указывает, что рабочий диапазон находится на листе Пример1.

Обратите внимание, как в записанном макросе диапазонам присваиваются имена. Для этого используется метод Add коллекции Names объекта ActiveWorkbool. Метод Add принимает два аргумента: назначаемое имя и адрес диапазона. Второй аргумент вводится в формате R1C1 (R1C1 notation). Например, адрес R7C2 обозначает строку 7 и столбец 2, т.е. указывает ячейку В7. В этом и заключается сложность изучения записанного кода. В исправленном варианте процедуры, показанной ни применен более простой способ обращения к диапазонам (для этого используете свойствоNameобъектаRange).

Рис. 2. Записанный код

Рис. 3. Исправленный код

ПРИМЕР 2. СОЗДАНИЕ ДИАГРАММЫ

В этом примере, показанном на рис. 4, предлагается создать диаграмму (которая отображена на рис. 5) на том же листе, на котором находятся исходные данные.

Рис. 4. Рабочий лист примера 2

Рис. 5. Диаграмма примера 1

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

Sub ChartOnSheet()

' ChartOnSheet Макрос

' Макрос записан 17.04.2008 (шумейко ирина)

Charts.Add

ActiveChart.ChartType = xlColumnClustered

ActiveChart.SetSourceData Source:=Sheets("Пример2").Range("A5:B10"), _

PlotBy:=xlColumns

ActiveChart.Location Where:=xlLocationAsObject, Name:="Пример2"

With ActiveChart

.HasTitle = True

.ChartTitle.Characters.Text = "Распределение оценок"

.Axes(xlCategory, xlPrimary).HasTitle = False

.Axes(xlValue, xlPrimary).HasTitle = False

End With

ActiveChart.HasLegend = False

ActiveSheet.Shapes("Chart 3").IncrementLeft 67.5

ActiveSheet.Shapes("Chart 3").IncrementTop 10.5

ActiveWindow.Visible = False

Windows("RecordingFinished.xls").Activate

Range("A2").Select

End Sub

' Очищенный макрос

Sub ChartOnSheet1()

Charts.Add

With ActiveChart

.ChartType = xlColumnClustered

.SetSourceData Source:=Sheets("Пример2").Range("A5:B10"), _

PlotBy:=xlColumns

.Location Where:=xlLocationAsObject, Name:="Пример5"

.HasTitle = True

.ChartTitle.Characters.Text = "Распределение оценок"

.Axes(xlCategory, xlPrimary).HasTitle = False

.Axes(xlValue, xlPrimary).HasTitle = False

.HasLegend = False

End With

With ActiveSheet.Shapes("Chart 3")

.IncrementLeft 67.5

.IncrementTop 10.5

End With

ActiveWindow.Visible = False

Windows("RecordingFinished.xls").Activate

Range("A2").Select

End Sub

EndSub

Упражнение

В книге Practical Management Science используется соглашение, в соответствии с которым исходные значения модели выделяются синей границей. Создать синюю границу вокруг диапазона в Excel несложно. Достаточно воспользоваться командой Формат - Ячейки. Но эта операция требует многих щелчков мышью. Целесообразна создать макрос, который будет добавлять синюю границу выделенному диапазону, особенно если эта операция выполняется неоднократно. Затем макрос можно назначить кнопке на панели инструментов, чтобы всегда иметь возможность воспользоваться им. В этом упражнении предоставляется пошаговое описание процедуры создания такого макроса и связывания его с кнопкой на панели инструментов.

Откройте пустую книгу и выберите любой диапазон. Так как создание границы для диапазона не является общепринятой операцией, попрактикуйтесь в выполнении этой задачи перед включением функции записи макросов. (Воспользуйтесь командой Формат - Ячейки, перейдите на вкладку Граница, выберите цвет и стан границы, а затем щелкните на кнопке Внешние.) Когда создание синей границы да диапазона перестанет вызывать у вас затруднения, выберите другой диапазон, включите функцию записи макросов, назовите макрос BlueBorder и сохраните его в файле Personal.xls. После этого выполните ту последовательность действий по созданию синей границы, которая выполнялась вами ранее, и отключите функции) записи макросов после получения необходимого обрамления.

Теперь создайте новую панель инструментов и разместите на ней кнопку, кота-рая будет запускать макрос BlueBorder. Для создания такой панели инструмента воспользуйтесь следующими действиями.

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

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

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

4. Щелкните правой кнопкой мыши на выбранной кнопке, чтобы отобразить контекстное меню. Самой важной для вас командой в этом меню является Назначить макрос. Выберите эту команду меню, а затем опциюBlueBorder из списка доступных макросов.

5. Это Меню, позволяет также изменить всплывающую подсказку, которая отображается при наведении указателя мыши на кнопку. Для этого необходимо изменить свойство Имя — укажите значение Синяя граница.

6. Наконец, это меню позволяет изменить пиктограмму кнопки на более подходящую. Выберите команду Изменить значок на кнопке. Это приведет к запуску программы рисования, которая позволяет нарисовать собственную пиктограмму.

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

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

Функция записи макросов служит двум основным целям.

l. Предоставляет отличный способ изучить соответствие между операциями Excelи кодомVBA.

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

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

1. Записанный код обычно далек от идеального и перегружен лишними операторами.

2. Функция записи макросов не может генерировать циклические конструкт и операторы ветвления, которые являются самыми используемыми в VBA.

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