|
Макросы в Excel
Если при работе с Microsoft
Excel для Windows у вас возникла необходимость несколько раз выполнить одну и ту
же последовательность действий, то вы можете записать эту последовательность под
определенным именем. Записанная под определенным именем последовательность
действий называется Записанный макрос можно вызывать для
выполнения из основного меню при помощи кнопки на панели инструментов или на
рабочей области, а также комбинации клавиш. Макрос может быть назначен так же
графическому объекту. В следующей
главе мы рассмотрим вопросы: Назначение макроса графическому объекту и кнопке
пользователя Создание макросов К
часто повторяющимся действиям можно отнести выбор нужного формата. Процедуру
создания макросов мы рассмотрим на примере макроса "Число", который будет
устанавливать для выделенной области числовой формат. Требуемый числовой формат
создадим заранее в категории форматов Сервис Запись
макроса Начать запись Введите
в поле ввода Имя макроса название создаваемого макроса — Число. Помимо
стандартной информации (когда и кем был записан макрос) вы можете ввести в поле
ввода краткое описание назначения макроса. После этого нажмите
кнопку OK. Начиная с этого момента осуществляется запись макроса. На экране
появляется кнопка . Если вы нажмете на ней кнопку мыши,
то запись макроса будет закончена. Другим признаком режима записи макроса
является появление слова "Запись" в строке состояния. Теперь вам нужно
выполнить последовательность действий, которая и будет записана в макрос. В
нашем случае последовательность действий следующая: выделите ячейки, которые вы
собираетесь форматировать, и нажмите Ctrl+1; в появившемся окне диалога "Формат
ячеек" выберите вкладку "Число", а на ней категорию форматов В заключение вам остается завершить запись макроса. Кроме
нажатия на кнопку вы можете воспользоваться
альтернативным способом: в списке опций пункта основного меню Сервис выберите
отсутствует с экрана, то ее легко можно восстановить.
Для этого нажмите правую кнопку мыши на любой панели инструментов и в
появившемся списке выберите пункт Появится набор кнопок,
который содержит кнопку Нажмите на ней кнопку мыши и,
удерживая ее нажатой, переместите кнопку на нужную вам панель или просто на
экран. Для выхода из режима настройки панелей нажмите кнопку
После окончания записи макроса он появляется в списке макросов
под именем "Число". Для вызова макроса выполните команду Выполнить В Excel предусмотрена возможность
вызывать макрос при помощи клавиши быстрого вызова или назначить макросу пункт
меню в списке опций меню . Обе эти возможности можно реализовать
одновременно. Для этого при выполнении 3 шага процедуры создания макроса, после
ввода имени, нужно нажать кнопку Назначить макросу пункт меню или клавишу быстрого вызова можно и
после того как макрос создан. Для этого выполните команду . Появится окно диалога, в котором вы можете сделать все
необходимые назначения. При записи макроса можно включать режим с
относительными ссылками. Этот режим позволяет использовать записанный макрос для
любой области рабочей книги. Режим с относительными ссылками нужно включать при
записи макросов, которые используют ссылки на адреса рабочей книги. Разберем
два примера макросов. Один записан в обычном режиме, а другой— с относительными
ссылками, после чего сравним их работу. При составлении различных отчетов и сводок в качестве названий
строк часто используются названия месяцев. Создадим макрос, который будет
выводить на экран столбец с названиями месяцев в ячейках A1:A12. Для этого
выполните следующие действия: ·Выполните команду Сервис Запись макроса Начать запись.
Появляется окно диалога "Запись макроса". ·Введите в поле ввода Имя макроса
название создаваемого макроса Месяц_абс. В поле ввода Описание введите текст:
Вводит названия месяцев. Нажмите кнопку Параметры. Появится окно диалога "Запись
макроса". ·Введите клавишу быстрого вызова макроса. Для этого перейдите в поле
ввода Ctrl, переключитесь на латинский регистр, удалите находящуюся там букву и
введите букву m. После этого нажмите кнопку OK. Начиная с этого момента,
осуществляется запись макроса. ·Выполните последовательность действий, которую
будет выполнять макрос: установите курсор в ячейку A1; введите слово январь;
поместите указатель мыши в правый нижний угол ячейки A1, при этом указатель мыши
изменит вид на черный крестик; нажмите кнопку мыши и, удерживая ее нажатой,
продолжите выделение до ячейки A12. Если вы не выполните условие 1 шага, то есть курсор будет
находиться в ячейке A1, то макрос будет правильно работать только в том случае,
когда вы перед нажатием клавиш Ctrl+m поместите курсор в ячейку A1. Макрос
записан и теперь нажатие клавиш Ctrl+m вызовет появление в ячейках A1:A12
названий месяцев. При записи макроса мы не включали режим записи с
относительными ссылками, поэтому названия месяцев будут появляться только в
ячейках A1:A12. Для устранения этого неудобства предназначен режим записи с
относительными ссылками. В этом примере вам предстоит создать макрос с относительными
ссылками для размещения заголовка таблицы продаж компьютеров в произвольном
месте рабочей книги. ·Введите в поле
ввода Имя макроса название создаваемого макроса — Заголовок. В поле ввода
Появится окно диалога "Запись макроса". ·Выполните последовательность действий, которую будет выполнять макрос:
введите заголовок таблицы: Тип компьютера; нажмите клавишу Enter; введите тип 1
компьютера CP_35_SR и нажмите клавишу Tab. Аналогично введите следующие типы
компьютеров CP_40_SR, CP_55_LS, CP_55_SX. Выделите четыре ячейки с введенными
типами компьютеров, выберите команду Формат Столбец, а затем команду Подгон
ширины (поскольку названия типов компьютеров не умещаются полностью в
ячейке). После окончания
записи макроса в меню команды Сервис появится пункт с названием Типы ЭВМ,
котороемы ввели на 4 шаге. Для выполнения макроса "Заголовок" нужно нажать
кнопку мыши на пункте с названием Типы ЭВМ. В том месте таблицы, где расположен
курсор, появится заголовок следующего вида: Тип
компьютераCP_35_SRCP_40_SRCP_55_LSCP_55_SX Этот макрос отличается от макроса,
созданного в примере №1 тем, что заголовок таблицы можно поместить в любом месте
текущей рабочей книги. В этом разделе мы
рассмотрим вопросы изменения текста макроса, его удаления и переименования.
Здесь же будет рассказано о том, как записать новую последовательность действий
в уже существующий макрос. Одним из способов внесения изменений в записанный
макрос является запись нового макроса. Однако это не всегда удобно, а часто
бывает и утомительно из-за большого объема работы. В следующем примере мы
изменим формат вводимых чисел в макросе "Число". Для
входа в режим редактирования макроса выполните команду Появляется окно диалога "Макрос", в котором после выбора нужного для
редактирования макроса становятся доступными кнопки, в том числе кнопка
Редактировать можно и непосредственно в модуле с
текстом макроса. Текст макроса "Число" расположен в модуле 1, расположенном
после 16 листа рабочей книги. Используя кнопки прокрутки листов найдите модуль 1
и нажмите кнопку мыши на его ярлычке. Перед вами появится текст макроса "Число",
записанный на языке Visual Basic: End
Sub Первые две строки составляют комментарий, последующие — текст макроса.
Редактирование сводится к тому, что вместо форматного кода "# #??" вводится
форматный код "# #??,00". Действуя аналогичным образом, вы можете внести более
серьезные изменения в текст макроса. Фактически вы имеете возможность изменять
текст программы Visual Basic, реализующий макрос. Это открывает перед вами
большие возможности в написании собственных программ. Если макрос назначен клавише быстрого доступа, графическому
объекту или кнопке, то процесс удаления является стандартным. Вы выбираете опцию
Макрос пункта Сервис основного меню. Появляется окно диалога "Макрос",
содержащее список макросов текущей рабочей книги. После выбора макроса, который
вы предполагаете удалить, станут доступными кнопки окна диалога "Макрос", в том
числе кнопка Макрос можно удалить и другим способом. Если макрос записан
в отдельном модуле, то нужно удалить модуль. Для этого нужно сделать его
активным, а затем нажать правую кнопку мыши на его ярлычке. Появится окно
диалога, в котором нужно выбрать команду . Если модуль содержит и
другие макросы, то нужно выделить весь текст удаляемого макроса и нажать клавишу
Del на клавиатуре. Определенную трудность представляет процесс
удаления строк с названиями макросов из пункта основного меню. Для
их удаления вы можете использовать окно диалога "Редактор меню", которое
открывается нажатием кнопки , расположенной на панели
инструментов "Visual Basic". Вы можете выбрать требующийся пункт меню из списка
Меню. При этом в следующем списке Элементы меню появляются опции этого пункта
меню. Вы можете выбрать и удалить нужную строку нажатием кнопки
окна диалога восстанавливает стандартное
состояние всех пунктов основного меню Excel. восстановить ранее сделанные в пункты меню назначения с помощью клавиш
Ctrl+Z невозможно. Можно только закрыть файл без сохранения всех сделанных
изменений, а затем открыть его снова. Окно диалога "Редактор меню" можно
вызвать и без использования кнопки Редактор меню. Для этого нужно выполнить
следующие действия: ·Сделайте активным какой-нибудь модуль. Если в рабочей
книге нет ни одного макроса, то создайте какой-нибудь макрос (например, не
выполняющий никаких действий). текущего меню.
Появится ниспадающее меню, в котором присутствуют дополнительные опции, одной из
которых является Редактор меню Не
создавайте макрос, восстанавливающий пункты меню. Делайте эту работу вручную и
вы избежите неприятностей, связанных с потерей информации. Специальной команды для переименования макроса нет, а часто
возникает потребность исправить имя или присвоить макросу совершенно новое
наименование. Для этого нужно войти в режим редактирования макроса и в тексте
программы исправить заголовок. Изменим имя макроса "Месяцы" на "Месяцы_с_абсолютными_адресами".
Для этого войдите в режим редактирования макроса "Месяцы". Ниже расположен текст
программы. Selection.AutoFill
Destination:=ActiveCell.Range("A1:A12"); _ Первая строка является
комментарием. Заменим "Месяцы" на "Месяцы_с_абсолютными_адресами".
Соответствующим образом изменим комментарий. В результате получим новый текст
программы макроса: ActiveCell.FormulaR1C1 =
"январь" ActiveCell.Range("A1:A12").Select Новое
имя автоматически заменит старое в списках макросов. По клавише быстрого вызова
будет вызываться макрос с новым именем. Но не все замены происходят
автоматически. Если ваш макросу были назначены графические объекты или кнопки,
то вам нужно будет самим сделать для них переназначения. Если вы хотите добавить в макрос новые
действия, то вам нужно включить соответствующий режим записи макроса. Для этого
выполните следующие действия: В тексте программы установите курсор в место ввода новых
действий. Выполните команду Сервис Запись макроса Отметить позицию.
Теперь ваши действия при записи макроса будут записываться в программу с того
места, где вы установили курсор. Выполните команду Сервис Запись макроса
Записать с отметки. Начиная с этого момента все ваши действия будут записываться
в макрос. Рассмотрим конкретный пример добавления
новых записей. Полезно сначала выполнить макрос, в который вы собираетесь
добавлять новые записи. Тогда вам легче будет ориентироваться, в каком месте
таблицы установить указатель перед тем как начать выполнять
действия. Внесем
изменения в макрос "Заголовок", который был записан в примере 6.2. Изменения
будут касаться формата заголовка. Расположим заголовок "Типы компьютеров" в
центре строки над названиями типов и введем другой шрифт для
заголовка. Приведем текст программы, реализующий макрос "Заголовок" и укажем
место расположения курсора строкой с комментарием "'место установки
курсора". ActiveCell.FormulaR1C1 = "Тип компьютера" ActiveCell.Offset(0; 1).Range("A1").Select ActiveCell.FormulaR1C1 = "CP_55_LS" ActiveCell.Offset(0; -3).Range("A1:D1").Select Для внесения новых записей в макрос "Заголовок" выполните следующие
действия: ·Для того чтобы определить позицию, с которой будут
записываться новые действия, выполните первые 5 шагов алгоритма внесения
изменений в макрос. ·Перед тем как выполнить 6 шаг алгоритма внесения
изменений в макрос, запустите макрос "Заголовок". Это позволит вам определить
положение курсора на рабочем листе, где будут вноситься новые
действия. ·Выделите четыре ячейки,
включающие заголовок "Тип компьютера". ·Выберите вкладку "Выравнивание", в которой установите флажок
·Выберите вкладку "Шрифт", в которой установите стиль Italic и
размер шрифта 16. Нажмите кнопку OK, чтобы выйти из режима форматирования
ячейки. После
завершения записи новых действий в макрос "Заголовок" мы получили новый текст
программы: ActiveCell.FormulaR1C1 = "Тип компьютера"
ActiveCell.Offset(0; 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "CP_55_LS"
ActiveCell.Offset(0; -3).Range("A1:D1").Select
With Selection .Orientation =
xlHorizontal .Strikethrough = False .ColorIndex = xlAutomatic Теперь результат работы макроса "Заголовок" имеет следующий
вид: Назначение макроса графическому объекту Макрос может
вызываться не только нажатием клавиш быстрого вызова, но и нажатием кнопки мыши
на графическом объекте. В этом разделе рассказывается о том, как организовать
такой вызов макроса. Для назначения макроса графическому объекту необходимо
наличие такого объекта на экране. Представлены три графических объекта, которым
назначены макросы. Для создания графических объектов была вызвана панель
инструментов "Рисование". Для этого необходимо выполнить команду Вид Панели
инструментов, а затем в окне диалога "Панели инструментов" установить флажок на
строке Чтобы нарисовать закрашенный овал
или прямоугольник, нажмите кнопку мыши на соответствующем рисунке на панели
"Рисование". Вам будет предложено выбрать место для объекта. Поместите указатель
мыши в нужное место таблицы и, удерживая нажатой кнопку мыши, нарисуйте объект —
прямоугольник или овал. Для выбора заливки объекта нажмите правую кнопку мыши
на активном объекте. Появится меню, в котором выберите пункт Надписи на объектах создаются следующим
образом: Удерживая нажатой кнопку мыши, выделите место для
текста. Введите требуемый текст и нажмите кнопку мыши вне текстового окна.
Это необходимо сделать для того, чтобы войти в режим выравнивания текста внутри
текстового окна. Нажмите правую кнопку мыши на текстовом окне. Появится
меню, в котором выберите пункт Формат объекта. В результате появится окно
диалога "Форматирование объекта", в котором перейдите на вкладку
"Выравнивание". Установите переключатели вертикального и горизонтального
выравнивания в положение по центру и выберите вкладку "Шрифт". Нажатие Enter при вводе текста
не завершает ввод, а лишь переводит на следующую строку. Вы получили надпись
на объекте. Обратите внимание, что надпись "Заголовок" сделана под графическим
объектом "прямоугольник". После того как было найдено удачное расположение
надписи на объекте, была нажата кнопка На задний план, расположенная на панели
инструментов "Рисование" и текст оказался под объектом. Мы построили
графические объекты. Теперь назначим этим графическим объектам макросы. Для
этого выполните следующие действия: ·Выполните команду Сервис Назначить
макрос. Появится окно диалога со списком макросов. ·Нажмите кнопку OK. Вы выйдете из режима назначения макроса.
Графический объект остается активным. Нажмите кнопку мыши в любом месте вне
объекта. Объект становится активным, когда на его границе появляются черные
квадратики. Если графический объект не назначен макросу, то чтобы сделать его
активным достаточно нажать на нем кнопку мыши. Если назначение макроса
графическому объекту уже сделано, то чтобы сделать его активным нужно нажать на
нем одновременно кнопку мыши и клавишу Ctrl. Назначить макрос графическому
объекту можно и другим способом. Нажмите правую кнопку мыши на объекте. Из
появившегося меню выберите команду Макрос назначен графическому объекту. Если теперь
поместить указатель мыши на этот объект, то белый крестик перейдет в изображение
руки с отогнутым указательным пальцем. Нажатие левой кнопки мыши приводит к
выполнению макроса, который назначен этому объекту. Назначим макрос "Число" графическому объекту
"стрелка". Для этого выполните следующие действия: ·Установите курсор
в нужное место таблицы и, удерживая нажатой кнопку мыши, проведите
стрелку. ·Нажмите кнопку Текстовое поле на панели инструментов "Рисование"
и, удерживая нажатой кнопку мыши, выделите место для текста. ·Нажмите правую кнопку
мыши на текстовом окне. Появится меню, в котором выберите пункт Формат объекта.
После этого появится окно диалога "Форматирование объекта". В этом окне нам
понадобятся вкладки: "Выравнивание", "Шрифт" и "Вид". Во вкладке "Выравнивание"
установите переключатели вертикального и горизонтального выравнивания в
положение по центру. Во вкладке " Шрифт" выберите шрифт TimesET, стиль Italic,
размер 16. Во вкладке "Вид" установите переключатель Рамка в положение Нет, в
результате чего рамка вокруг текста будет удалена. ·Нажмите кнопку На задний план, расположенную
на панели инструментов "Рисование". Текст оказался под стрелкой. ·Нажмите
правую кнопку мыши, когда ее указатель находится на объекте. Появится меню со
списком команд. . Появится окно диалога
"Назначить макрос объекту" со списком созданных макросов. ·Нажмите
кнопку OK. Вы назначили макрос графическому объекту. Нажмите кнопку мыши вне
объекта, чтобы он перестал быть активным. Если поместить указатель мыши на
изображение стрелки, то белый крестик перейдет в изображение руки с отогнутым
указательным пальцем. Нажатие левой кнопку мыши приводит к выполнению макроса
"Число". Представлены три
рисунка, которым назначены созданные нами макросы "Месяц", "Заголовок" и
"Число". Назначение макроса рисунку ничем не отличается от назначения макроса
графическому объекту, которое было рассмотрено ранее. Рисунки взяты из каталога
Windows. Они содержатся в файлах WINLOGO.BMP, EGYPT.BMP и ARCHES.BMP. ·Выполните
команду Вставка Рисунок. Появится окно диалога выбора рисунка. ·Выберите
файл WINLOGO.BMP и выбранный рисунок появится на рабочем листе Excel. ·Из
всплывающего меню выберите команду Назначить макрос. Появится окно диалога со
списком макросов, в котором выберите требуемый. Нажатие кнопки OK завершает
процесс назначения. В ячейках A1:A3 вы можете увидеть результат работы макроса
"Число", который был назначен рисунку "winlogo". Для получения результата мы
выделили ячейки A1:A3 и переместили указатель мыши на рисунок "winlogo". Нажатие
кнопки мыши привело к выполнению макроса "Число", то есть к форматированию ячеек
A1:A3. Служебной кнопке Excel также можно
назначить макрос. При этом она потеряет свое первоначальное предназначение. Для
восстановления служебной кнопки сначала удалите ее с панели в режиме настройки,
а затем переместите оригинал из соответствующей категории кнопок на требуемую
панель инструментов. 1Заполнение ячейки числами, являющимися членами
арифметической прогрессии с разностью равной 0,5 В результате работы макроса "Прогрессия" выделенные в строке
ячейки заполняются числами, являющимися членами арифметической прогрессии с
разностью равной 0,5 или с шагом 0,5. ·Введите в ячейку A1 число 1000 и выделите
ячейки A1:C1. ·Введите в поле ввода Имя макроса
название записываемого макроса Прогрессия и нажмите кнопку Параметры. Окно
диалога "Запись макроса" расширяется. ·Нажмите кнопку OK в
окне диалога "Запись макроса", которое после этого закроется. Начиная с этого
момента, все ваши действия будут записываться в макрос. В строке состояния
появится слово "Запись". ·Установите флажок по строкам. Это
означает, что прогрессия будет строится в выделенной строке. ·Установите
флажок арифметическая. Это означает, что ячейки в строке будет заполняться
числами, являющимися членами арифметической прогрессии. ·Нажмите кнопку OK,
чтобы выйти из окна диалога "Прогрессия". Теперь нужно закончить запись
макроса. Итак,
результаты работы макроса "Прогрессия". Он запускается нажатием Ctrl+p. Перед
этим нужно ввести первый член прогрессии и выделить ячейки в строке, где будут
размещаться члены арифметической прогрессии с разностью 0,5. Если первая ячейка среди выделенных не содержит число, то
заполнение числами не произойдет и ячейки останутся без изменения. Этот макрос будет восстанавливать стандартную
диаграмму Excel. Если вы строили графики с использованием пользовательского
автоформата диаграмм, то для перехода к стандартной диаграмме Excel вам
достаточно будет запустить этот макрос. ·Выполните команду Сервис Запись
макроса Начать запись. Появится окно диалога "Запись макроса". ·Перейдите на латинский регистр и введите букву d для клавиши
быстрого вызова макроса. ·Нажмите кнопку OK в окне диалога "Запись
макроса". Начиная с этого момента все ваши действия будут записываться в макрос.
В строке состояния появится слово "Запись". ·Выберите
вкладку "Диаграмма". В поле ввода списка Формат диаграммы по умолчанию
отображается название последнего использовавшегося пользовательского автоформата
диаграммы. ·В списке Формат диаграммы по умолчанию с помощью полосы
прокрутки найдите значение Встроенная и нажмите кнопку
OK. До
запуска макроса "Диаграмма" в качестве диаграммы по умолчанию строилась
диаграмма формата Линии .После нажатия клавиш Ctrl+d в качестве диаграммы по
умолчанию будет использоваться Встроенная. Макрос изменяет значение формата для
стандартной диаграммы. Обе диаграммы построены на наборе данных A1:E4, но с
использованием разных форматов. Действие
этого макроса аналогично нажатию кнопки Вставить значения. В ячейке остается
только результат работы формулы, а сама формула исчезает. Это очень важная
функция, поэтому приведем алгоритм записи соответствующих действий в макрос,
который впоследствии будет вызываться нажатием Ctrl+v. ·Для наглядности введите в ячейки
A1 и A2 какие-нибудь формулы, например, следующего вида: =COS(D1), =COS(D2). В
ячейках D1 и D2 расположены числа. ·Выполните команду Сервис Запись макроса Начать запись. Появится окно
диалога "Запись макроса". ·Перейдите на латинский регистр и введите букву v для клавиши
быстрого вызова макроса. ·Нажмите кнопку OK в окне диалога "Запись макроса".
Начиная с этого момента все ваши действия будут записываться в
макрос. ·Нажмите мышью в
первую ячейку выделенной области. В нашем случае это ячейка
A1. ·Установите признак Значения. ·Нажмите мышью
в первую ячейку выделенной области. В нашем случае это ячейка A1. Работа с
макросом "Значение" осуществляется следующим образом. Сначала выделите блок
ячеек, в которых присутствуют формулы, а затем нажмите Ctrl+v. В выделенных
ячейках останутся только значения формул.
Если вы хотите использовать макрос "Значение" вместо команды Специальная
вставка, запишите его с относительными ссылками. 1Макрос представляет собой записанную последовательность определенных
действий. 3Назначение существующему макросу клавиши быстрого вызова и пункта
меню выполняется с помощью команды Сервис Макрос. 5Редактировать текст макроса можно непосредственно в модуле с
текстом. 6Для удаления макроса нажмите мышью на его имени. При этом станут
доступными кнопки окна диалога "Макрос", в том числе кнопка Удалить. 8Введите в
поле ввода Имя макроса название создаваемого макроса и запишите новый
макрос. 9Вы можете вносить изменения непосредственно в текст программы, в
которой на языке Visual Basic for Applications записана последовательность
действий макроса. —при помощи кнопки на панели
инструментов —нажатием кнопкой мыши на графическом
объекте
| |
