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

Введение

Всем нам приходится — кому реже, кому чаще — повторять одни и те же действия и операции в Excel. Любая офисная работа предполагает некую «рутинную составляющую» — одни и те же еженедельные отчеты, одни и те же действия по обработке поступивших данных, заполнение однообразных таблиц или бланков и т.д. Использование макросов и пользовательских функций позволяет автоматизировать эти операции, перекладывая монотонную однообразную работу на плечи Excel. Другим поводом для использования макросов в вашей работе может стать необходимость добавить в Microsoft Excel недостающие, но нужные вам функции. Например функцию сборки данных с разных листов на один итоговый лист, разнесения данных обратно, вывод суммы прописью и т.д.

Макрос — это запрограммированная последовательность действий (программа, процедура), записанная на языке программирования Visual Basic for Applications (VBA). Мы можем запускать макрос сколько угодно раз, заставляя Excel выполнять последовательность любых  нужных нам действий, которые нам не хочется выполнять вручную.

В принципе, существует великое множество языков программирования (Pascal, Fortran, C++, C#, Java, ASP, PHP…), но для всех программ пакета Microsoft Office стандартом является именно встроенный язык VBA. Команды этого языка понимает любое офисное приложение, будь то Excel, Word, Outlook или Access.

Способ 1. Создание макросов в редакторе Visual Basic

Для ввода команд и формирования программы, т.е. создания макроса необходимо открыть специальное окно — редактор программ на VBA, встроенный в Microsoft Excel.

• В старых версиях (Excel 2003 и старше) для этого идем в меню Сервис — Макрос — Редактор Visual Basic (Toos — Macro — Visual Basic Editor).
• В новых версиях (Excel 2007 и новее) для этого нужно сначала отобразить вкладку Разработчик (Developer). Выбираем Файл — Параметры — Настройка ленты (File — Options — Customize Ribbon) и включаем в правой части окна флажок Разработчик (Developer). Теперь на появившейся вкладке нам будут доступны основные инструменты для работы с макросами, в том числе и нужная нам кнопка Редактор Visual Basic (Visual Basic Editor)
  
  
  
  :

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

Макросы (т.е. наборы команд на языке VBA) хранятся в программных модулях. В любой книге Excel мы можем создать любое количество программных модулей и разместить там наши макросы. Один модуль может содержать любое количество макросов. Доступ ко всем модулям осуществляется с помощью окна Project Explorer в левом верхнем углу редактора (если его не видно, нажмите CTRL+R). Программные модули бывают нескольких типов для разных ситуаций:

• Обычные модули — используются в большинстве случаев, когда речь идет о макросах. Для создания такого модуля выберите в меню Insert — Module. В появившееся окно нового пустого модуля можно вводить команды на VBA, набирая их с клавиатуры или копируя их из другого модуля, с этого сайта или еще откуда нибудь:

  

• Модуль Эта книга — также виден в левом верхнем углу редактора Visual Basic в окне, которое называется Project Explorer. В этот модуль обычно записываются макросы, которые должны выполнятся при наступлении каких-либо событий в книге (открытие или сохранение книги, печать файла и т.п.):

  

• Модуль листа — доступен через Project Explorer и через контекстное меню листа, т.е. правой кнопкой мыши по ярлычку листа — команда Исходный текст (View Source). Сюда записывают макросы, которые должны выполняться при наступлении определенных событий на листе (изменение данных в ячейках, пересчет листа, копирование или удаление листа и т.д.)

  

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

Давайте разберем приведенный выше в качестве примера макрос Zamena:

• Любой макрос должен начинаться с оператора Sub, за которым идет имя макроса и список аргументов (входных значений) в скобках. Если аргументов нет, то скобки надо оставить пустыми.
• Любой макрос должен заканчиваться оператором End Sub.
• Все, что находится между Sub и End Sub — тело макроса, т.е. команды, которые будут выполняться при запуске макроса. В данном случае макрос выделяет ячейку заливает выделенных диапазон (Selection) желтым цветом (код = 6) и затем проходит в цикле по всем ячейкам, заменяя формулы на значения. В конце выводится окно сообщения (MsgBox).

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

Способ 2. Запись макросов макрорекордером

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

• Макрорекордер записывает только те действия, которые выполняются в пределах окна Microsoft Excel. Как только вы закрываете Excel или переключаетесь в другую программу — запись останавливается.
• Макрорекордер может записать только те действия, для которых есть команды меню или кнопки в Excel. Программист же может написать макрос, который делает то, что Excel никогда не умел (сортировку по цвету, например или что-то подобное).
• Если во время записи макроса макрорекордером вы ошиблись — ошибка будет записана. Однако смело можете давить на кнопку отмены последнего действия (Undo) — во время записи макроса макрорекордером она не просто возрвращает Вас в предыдущее состояние, но и стирает последнюю записанную команду на VBA.

Чтобы включить запись необходимо:

• в Excel 2003 и старше — выбрать в меню Сервис — Макрос — Начать запись (Tools — Macro — Record New Macro)
• в Excel 2007 и новее — нажать кнопку Запись макроса (Record macro) на вкладке Разработчик (Developer)

Затем необходимо настроить параметры записываемого макроса в окне Запись макроса:

• Имя макроса — подойдет любое имя на русском или английском языке. Имя должно начинаться с буквы и не содержать пробелов и знаков препинания.
• Сочетание клавиш — будет потом использоваться для быстрого запуска макроса. Если забудете сочетание или вообще его не введете, то макрос можно будет запустить через меню Сервис — Макрос — Макросы — Выполнить (Tools — Macro — Macros — Run) или с помощью кнопки Макросы (Macros) на вкладке Разработчик (Developer) или нажав ALT+F8.
• Сохранить в… — здесь задается место, куда будет сохранен текст макроса, т.е. набор команд на VBA из которых и состоит макрос.:
• Эта книга — макрос сохраняется в модуль текущей книги и, как следствие, будет выполнятся только пока эта книга открыта в Excel
• Новая книга — макрос сохраняется в шаблон, на основе которого создается любая новая пустая книга в Excel, т.е. макрос будет содержаться во всех новых книгах, создаваемых на данном компьютере начиная с текущего момента
• Личная книга макросов — это специальная книга Excel  с именем Personal.xls, которая используется как хранилище макросов. Все макросы из Personal.xls загружаются в память при старте Excel и могут быть запущены в любой момент и в любой книге.

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

Запуск и редактирование макросов

Управление всеми доступными макросами производится в окне, которое можно открыть с помощью кнопки Макросы (Macros) на вкладке Разработчик (Developer) или — в старых версиях Excel — через меню Сервис — Макрос — Макросы (Tools — Macro — Macros):

• Любой выделенный в списке макрос можно запустить кнопкой Выполнить (Run).
• Кнопка Параметры (Options) позволяет посмотреть и отредактировать сочетание клавиш для быстрого запуска макроса.
• Кнопка Изменить (Edit) открывает редактор Visual Basic (см. выше) и позволяет просмотреть и отредактировать текст макроса на VBA.

Создание кнопки для запуска макросов

Чтобы не запоминать сочетание клавиш для запуска макроса, лучше создать кнопку и назначить ей нужный макрос. Кнопка может быть нескольких типов:

Кнопка на панели инструментов в Excel 2003 и старше

Откройте меню Сервис — Настройка (Tools — Customize) и перейдите на вкладку Команды (Commands). В категории Макросы легко найти веселый желтый «колобок» — Настраиваемую кнопку (Custom button):

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

Кнопка на панели быстрого доступа в Excel 2007 и новее

Щелкните правой кнопкой мыши по панели быстрого доступа в левом верхнем углу окна Excel и выберите команду Настройка панели быстрого доступа (Customise Quick Access Toolbar):

Затем в открывшемся окне выберите категорию Макросы и при помощи кнопки Добавить (Add) перенесите выбранный макрос в правую половину окна, т.е. на панель быстрого доступа:

Кнопка на листе

Этот способ подходит для любой версии Excel. Мы добавим кнопку запуска макроса прямо на рабочий лист, как графический объект. Для этого:

• В Excel 2003 и старше — откройте панель инструментов Формы через меню Вид — Панели инструментов — Формы (View — Toolbars — Forms)
• В Excel 2007 и новее — откройте выпадающий список Вставить (Insert) на вкладке Разработчик (Developer) 

Выберите объект Кнопка (Button):

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

Создание пользовательских функций на VBA

Создание пользовательских функций или, как их иногда еще называют, UDF-функций (User Defined Functions) принципиально не отличается от создания макроса в обычном программном модуле. Разница только в том, что макрос выполняет последовательность действий с объектами книги (ячейками, формулами и значениями, листами, диаграммами и т.д.), а пользовательская функция — только с теми значениями, которые мы передадим ей как аргументы (исходные данные для расчета).

Чтобы создать пользовательскую функцию для расчета, например, налога на добавленную стоимость (НДС) откроем редактор VBA, добавим новый модуль через меню Insert — Module и введем туда текст нашей функции:

Обратите внимание, что в отличие от макросов функции имеют заголовок Function вместо Sub и непустой список аргументов (в нашем случае это Summa). После ввода кода наша функция становится доступна в обычном окне Мастера функций (Вставка — Функция) в категории Определенные пользователем (User Defined):

После выбора функции выделяем ячейки с аргументами (с суммой, для которой надо посчитать НДС) как в случае с обычной функцией:

Если Вы задались вопросом «Как запустить макрорекордер и записать макрос в Excel?» то эта статья для Вас. Рассмотрим процесс запуска макрорекордера и записи макроса в разных версиях Excel. Для большей определенности запишем макрос, выделяющий столбец «А» электронной таблицы. 

Microsoft Excel 2003

Запускаем приложение, заходим в главное меню Сервис/Макрос и выбираем пункт «Начать запись…»

После выбора пункта меню «Начать запись» появляется окно, в котором присваиваем нашему макросу имя «Макрос1», сочетание горячих клавиш присваивать не будем, для сохранения выберем личную книгу макросов, описание оставим то, которое есть по умолчанию. Жмем ОК.

С этого момента все наши действия записываются макрорекордером, автоматически переводятся на язык программирования Visual Basic for Application и записываются в модуль личной книги макросов.

В самом начале статьи мы условились, что нашим действием будет выделение столбца «А», поэтому выделяем столбец и останавливаем запись выбором меню «Остановить запись», которая находится также в меню Сервис/Макрос.

Вот собственно и все, макрос записан.

Посмотрим теперь, как работает записанный нами макрос. Для этого снимем выделение со столбца, зайдем в меню Сервис/Макрос/Макросы либо нажмем сочетание клавиш Alt+F8, выберем из списка макрос с нашим названием «Макрос1» и нажмем кнопку «Выполнить». Обратите внимание, что с помощью этого окна можно не только запустить макрос, можно также внести изменения в его программный код, а также удалить макрос.

Результатом выполнения макроса станет выделенный столбец «А», строго в соответствии с тем действием, которое мы записали. Если записанный Вами макрос не запустился, то Вам следует изменить параметры системы безопасности.

Для просмотра программного кода записанного макроса, запускаем редактор Visual Basic через меню Сервис/Макрос/Редактор Visual Basic, либо нажатием сочетания клавиш Alt+F11. В открывшемся окне редактора видим новый модуль Module1 в проекте VBAProject(PERSONAL.XLS), в котором сохранен программный код записанного нами макроса.

 

Microsoft Excel 2007

Запускаем приложение. Кнопка «Начать запись…» в Excel 2007 расположена сразу в нескольких местах, самое доступное из которых в левом нижнем углу окна приложения, в строке состояния.

Еще одно расположение откуда доступен запуск макрорекордера на вкладке «Вид», в группе «Макросы». Прежде чем продолжить, остановимся на том, что такое лента и что такое вкладка на этой ленте. Лента — это широкая полоса в верхней части окна программы, на которой размещены тематические наборы команд, собранные во вкладках и в группах. Вкладка в свою очередь, представляет собой часть ленты с командами, сгруппированными по задачам, которые обычно выполняются совместно. Группа — это еще более локализованный набор команд, сформированный по принципу наибольшей близости по типу выполняемой задачи. Непосредственно в группах уже находятся кнопки для выполнения команд. 

И еще одно место где можно включить запись макроса макрорекордером — это вкладка «Разработчик», в том случае, если она предварительно была отображена на ленте. По умолчанию эта вкладка в Excel 2007 не отображается.

На вкладке «Разработчик» расположены все доступные инструменты для работы с макросами. О том как ее отобразить описано в статье «Работа с макросами VBA. Инструменты для работы с макросами«.

После нажатия, кнопка «Запись макроса» меняет свое название на «Остановить запись». Выполнение и просмотр кода макроса осуществляется точно также, как в Excel 2003. 

Microsoft Excel 2010

В Excel 2010 расположение кнопок записи и остановки записи макроса абсолютно аналогичное версии Excel 2007. Это левый нижний угол окна приложения в строке состояния, вкладка «Вид» и вкладка «Разработчик». В этой версии Excel, редактор Visual Basic устанавливается как отдельный компонент пакета с наименованием «Общие средства Office» и называется «Visual Basic для приложений», поэтому если у Вас возникли проблемы с доступностью макрорекрордера или редактора Visual Basic, то Вам необходимо вернуться на этап установки пакета Microsoft Office и доустановить недостающий компонент. Кроме того, может возникнуть проблема при запуске самого макроса, связанная с настройками системы безопасности.

5.1. Макросы

VBA
(Visual
Basic
for
Applications)
— это объектно-ориентированный язык
макропрограммирования высокого уровня,
встроенный во все программы пакета
Microsoft
Office.

Преимущества:

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

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

Язык
VBA
является расширением Visual
Basic.

Макрос
(или макрокоманда) — последовательность
команд и функций, записанных в модуле
VBA,
позволяющая автоматизировать выполнение
основных операций.

Существует
два способа создания макроса:

• записывать свои действия во время
  работы с рабочим листом;

• написать
  макрос, используя алгоритмический язык
  Visual
  Basic.

Наиболее
простым методом создания макрокоманды
является ее запись с помощью макрорекордера,
который протоколирует все выполняемые
пользователем действия и представляет
их в виде программного кода VBA.

5.1.1.
Макрорекордер

Макрорекордер
протоколирует операции, выполняемые
пользователем, и преобразует их в
последовательность операторов VBA.

Процесс работы макрорекордера можно
условно разделить на четыре этапа:

• запуск макрорекордера;

• присвоение макросу имени;

• выполнение записываемых операций;

• завершение записи.

Начало записи

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

Для
начала записи макроса необходимо
активизировать чистый рабочий лист,
выбрав в меню Сервис
команду Макрос,
затем – Начать
запись.
На экране появится диалоговое окно
Запись
макроса,
в котором следует указать имя макроса,
ввести его краткое описание, задать
комбинацию клавиш для запуска создаваемого
макроса и определить его место хранения.

Примечание:

• Имя макроса может состоять максимум
  из 255 символов. Оно не должно содержать
  пробелов и специальных символов. Между
  фрагментами длинных имен макросов
  вместо пробелов вставляются символы
  подчеркивания (_).

• При назначении комбинации клавиш для
  запуска макроса учитывается регистр
  символов.

Рис. 5.1. Запись
макроса

После
щелчка на кнопке ОК в диалоговом окне
запись макроса появится панель
инструментов.

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

–
относительная
ссылка
– определяется режим адресации при
записи макроса. Если кнопка не
активизирована, по умолчанию применяются
абсолютные ссылки.

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

Рис.
5.2. Строка состояния

ПРИМЕР
(рис. 5.3):

• Отменить
  отображение сетки (рис. 5.3-а);

• Задать
  для всех ячеек денежный формат (рис.
  5.3-б);

• Установить
  вид и размер шрифта текста ячеек (рис.
  5.3-в).

Выполнив
перечисленные действия, необходимо
остановить запись (рис. 5.3-д).

Рис. 5.3-ж иллюстрирует результат выполнения
макроса.

а) б) в)

д) е) ж)

Рис.
5.3. Создание макроса при помощи
макрорекордера

Запуск
макроса осуществляется активизацией
команды Сервис
– Макрос – Макросы,
где отмечается нужный макрос, или нажав
сочетание клавиш Ctrl
и любой другой установленной.

Рис.
5.4. Окно макроса

5.1.2.
Панель инструментов для работы с
макросами

Для
отображения панели работы с макросами
(рис. 5.5) необходимо:

Вид
– Панель инструментов – Visual
Basic.

Рис.
5.5. Панель VBA

Таблица
5.1. Основные кнопки панели инструментов
VBA

Кнопка	Название	Описание
	Выполнить макрос	Открывается диалоговое окно Макрос, где имеется возможность выбора необходимого макроса для записи или редактирования
	Записать макрос	Запись макроса (остановка записи макроса)
	Безопасность	Задается уровень защиты от макровирусов и указываются надежные разработчики макросов
	Редактор Visual Basic	Запускается редактор или активизируется
	Элементы управления	Выводи панель Элементы управления для вставки необходимых элементов
	Режим конструктора	Кнопка действует как выключатель
	Режим сценариев	Запускает редактор Microsoft Script Editor для создания HTML- и XML-страниц

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

Записанные
макросы можно редактировать команда
Сервис
– Макрос – Реактор Visual
Basic,
можно также воспользоваться кнопкой
редактор Visual
Basic
на панели инструментов или клавишами
Alt+F11.

После
активизации указанных элементов
открывается окно Microsoft
Visual
Basic.
Для редактирования макроса необходимо
в окне Project
(Проект) открыть папку Modules
(Модули) и выбрать имя модуля.

Рис.
5.6. Окно Microsoft
Visual
Basic
с примером кода макроса

В
vba
текст макроса оформляется в виде
процедуры Sub.
Данная процедура создается на основе
событий (например,
изменение размера окна или щелчок на
кнопке)
и методов. Каждому событию подставлены
в соответствии методы, которые задают
необходимость выполнения определенных
операций при наступлении данного события
(пример,
изменение активного листа, с изменением
которого может измениться команда
меню).
В VBA
пользователь может задавать собственные
события и ставить им в соответствии
методы.

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

Для
активизации режима отладки можно
воспользоваться пошаговым выполнением.
Для этого меню Debug
(Отладка) – Step
Info
(Пошагово)
или клавиши F8.
Кроме того, можно устанавливать точки
для остановки выполнения макроса и его
редактирования.

Рис.
5.7. Пошаговая отладка макроса

5.1.4. Создание панели инструментов для
запуска макросов

Можно создавать собственные панели
инструментов. Меню Сервис – Настройка
– на вкладке Панели Инструментов кнопка
Создать (рис. 5.8).

Рис. 5.8. Создание
панели инструментов

Для вставки кнопок необходимо перейти
на вкладку Команды, где представлены
все доступные элементы панелей
инструментов и все команды, которые
объединены в категории.

Рис. 5.9. Настройка
панели инструментов: назначение макроса

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

5.2. Элементы управления на рабочем листе

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

Виды
элементов управления:
ActiveX
и элементы управления панели инструментов
Формы.

Элементы
управления ActiveX
способны работать с макросами VBA
и веб-сценариями. Они являются независимыми
программными компонентами. Кнопки для
вставки этих элементов управления
рассоложены на панели инструментов
Элементы управления.

Элементы
управления панели инструментов Формы
пакета M.Office
обеспечивают совместимость с ранними
версиями Excel
и могут использоваться на листах макросов
XLM.

Рис.
5.10. Элементы управления

5.2.1.
Элементы управления ActiveX

Для открытия панели инструментов
необходимо правой кнопкой мыши щелкнуть
на любой другой панели и в появившемся
списке выбрать пункт Элемент управления
или команда Вид – панели инструментов
– Элементы управления.

Рис.5.11.
Панель элементов управления

Таблица 5.1. Элементы
управления

	Режим конструктора	Переводит Excel в режим конструктора и отключение всех элементов на рабочем листе
	Свойства	Активизация окна Свойства с перечнем свойств листа, активного на данный момент
	Исходный текст	Открытие окна редактора Microsoft Visual Basic
	Флажок	Позволяет задать или отменить определенный параметр
	Поле	Добавление области, в которой можно вводить и редактировать текст
	Кнопка	Создание кнопки, предназначенной для запуска Visual Basic
	Переключатель	Для выбора одного или нескольких параметров
	Список	Размещение списка, где можно выбрать один или несколько элементов
	Поле со списком	Создание поля со списком, предоставляющего пользователю возможность ввести текст либо выбрать элемент из списка
	Выключатель	Добавление на рабочее поле выключателя
	Счетчик	Используется для увеличения или уменьшения числовых значений
	Полоса прокрутки	Создание вертикальной полосы прокрутки
	Надпись	Надпись в виде текста
	Рисунок	Изображении
	Другие элементы	Добавление дополнительных элементов управления

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

Имена
элементам даются автоматически в
зависимости от типа объекта (Label1,
label2
и т.д.). Данные имена используются для
работы с макросами и несут смысловую
нагрузку. Для переименования необходимо
открыть окно Свойства (Properties)
на панели инструментов Элементы
управления – задать новое имя в строке
Name
– Enter.

Для
переименования самого элемента необходимо
в режиме конструктора выделить элемент
правой кнопкой мыши – Объект – Edit
или Caption
в Свойствах.

Для
форматирования элемента управления
ActiveX
можно воспользоваться двумя окнами:

– с
помощью окна Формат элемента управления
определяются цвет и вид линий контура,
размеры, позиция и др. (рис. 5.12).

Рис.5.12.
Окно Формат элемента управления

– в
окне Properties
(Свойства) задаются свойства элементов
управления, определяющие как общие
характеристики объекта (размер, цвет и
др.), так и поведение элемента управления.

Диалоговое
окно Properties

Свойства
изменяются в режиме конструктора,
устанавливая нужные значения в окне
Properties

• Name
  – определяет имя, под которым объект
  фигурирует в программе.

• Accelerator
  – предназначен для быстрого доступа
  к объекту с помощью клавиатуры.

• AutoSize
  – автоматически изменяет размеры
  элемента управления, чтобы на экране
  был виден весь его заголовок.

• BackColor
  – определяет цвет фона.

• BackStyle
  – указывает, будет ли метка прозрачной.

• BarderStyle
  – определяет, выводится ли рамка на
  экран. Если это так, то ее цвет зависит
  от свойства BorderColor.

• Caption
  – выводит текст заголовка.

• Enabled
  – определяет, можно ли выделить объект
  (по отношению к тени).

• Font
  – открывает диалоговое окно Шрифт для
  изменения стиля и размера шрифта.

• ForeColor
  – определяет цвет текста заголовка.

• Height
  – устанавливает высоту элемента
  управления, которую также можно изменить
  изменяя ее мышью.

Рис.5.13.
Вид окна Properties

• Left
  – устанавливает расстояние между левым
  краем элемента управления и левым краем
  рабочего листа.

• MousePointer
  – определяет вид указателя мыши, когда
  он находится на элементе управления.

• Picture
  –
  выбор рисунка.

• Shadow
  – принимает значение Истина или Ложь
  в зависимости от того, имеет ли объект
  тень.

• SpecialEffect
  – определяет вид объекта.

• Top
  – указывает расстояние между верхними
  краями элемента управления и рабочего
  листа.

• Visible
  – определяет, является ли элемент
  видимым.

• Width
  – задает ширину объекта.

• WordWrap
  – предназначен для оптимального
  размещения заголовка на объекте.

5.2.2.
Элементы управления панели инструментов
Формы

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

Для открытия панели инструментов
необходимо правой кнопкой мыши щелкнуть
на любой другой панели и в появившемся
списке выбрать пункт Элемент управления
или команда Вид – Панели инструментов
– Формы.

Рис.5.14.
Панель инструментов Формы

Таблица 5.2.
Дополнительные элементы управления
панели Формы

	Группа	Для создания на рабочем листе рамки для группы элементов управления
	Свойство элемента	Открывается диалоговое окно Формат элемента управления, в котором задаются параметры элемента управления
	Сетка	Сокрытие или отображение на рабочем листе сетки (разлинованных ячеек)

Для форматирования элемента управления
Форма используется диалоговое окно
Формат элемента управления, которое
открывается нажатием правой кнопкой
мыши на элемент – команда Формат объекта.

5.2.3. Создание выпадающего списка при
помощи меню Проверка

Пример использования выпадающего списка
через ЭУ Список:

1. Простой

Сочетание
клавиш Alt
+ стрелка вниз позволят из введенных
уже данных выбрать один вид в текущую
ячейку:

Рис.
5.15. Простое задние выпадающего списка

1. Стандартный

а) В свободных ячейках вне рабочей
области создается диапазон данных,
которые должны быть в дальнейшем
«выпадать» через Список. Для удобства
диапазону этих данных может быть
присвоено Имя (Вставка – Имя – Присвоить).

б) Выделяются ячейки, в которых необходимо
получить выпадающий список, Данные –
Проверка. На вкладке Параметры: Тип
данных – Список, Источник – диапазон
данных или Присвоенное имя.

а) б)

в) г)

д) е) ж)

Рис. 5.16. Стандартное
задание выпадающего списка

Вопросы для самоконтроля

1. В чем достоинство создания макроса?

2. Какими способами можно создать макрос?

3. В
   чем заключаются достоинства и недостатки
   макрорекордера?

4. Как
   можно провести редактирование макроса?

5. Что
   такое элемент управления?

6. Виды
   элементов управления?

7. Для
   чего необходим режим конструктора?

8. Как
   можно произвести форматирование
   элемента управления?

Глоссарий

VBA
(Visual
Basic
for
Applications)
— это объектно-ориентированный язык
макропрограммирования высокого уровня,
встроенный во все программы пакета
Microsoft
Office.

Макрос (или макрокоманда)
— последовательность команд и функций,
записанных в модуле VBA,
позволяющая автоматизировать выполнение
основных операций.

Макрорекордер
— наиболее простой метод создания
макрокоманды, при котором протоколируются
все выполняемые пользователем действия
и представляется в виде программного
кода VBA.

Элемент управления
— размещаемые на рабочих листах и в
диалоговых окнах объекты, предназначенные
для отображения, ввода и вычисления
данных.

Режим конструктора
переводит Excel
в режим отключения всех элементов
управления на рабочем листе

Принципы работы с макросами в Excell Online

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

Как создать макросы в Эксель Онлайн

Определённая последовательность действий, записанная на языке программирования VBA, позволяет избавиться от рутинной работы. Тем более это важно, когда выполнение задач в Excel либо трудоемко, либо вообще невозможно встроенными инструментами. Они могут многое – от замены текста в документе до работы со строками таблицы. Чтобы создать макрос, придерживайтесь готовой инструкции:

1. Открыв лист таблицы, в верхней панели инструментов выберите вкладку «Расширения».
2. Перейдите на пункт «Макросы», и далее проследуйте в раздел «Записать макрос».
3. Внизу экрана откроется рабочее окно, где следует выбрать тип ссылок – абсолютные или относительные.
4. Перейдите к таблице и выполните все действия, которые вы планируете записать для макроса.
5. Для сохранения макроса нажмите «Сохранить».
6. Сервис предложит дать название созданному макросу, введите его и нажмите кнопку «Сохранить».

Обратите внимание! Создавая макрос,  в таблице автоматически создается скрипт, для его изменения используйте в рабочей панели инструмент «Инструменты/Редактор скриптов».

Запуск готового макроса можно осуществить вручную, что обычно и применяется пользователями. Для этого откройте на панели «Расширения/Макросы», и выберите нужный вариант, который уже имеет у вас название.

Принципы работы с макросами в Excel

При создании автоматических действий в приложении, вы можете использовать два типа выполнения работ:

1. Абсолютные ссылки. В этом случае макрос, который вы применяете, работает только в ячейке, где он записан изначально.
2. Относительные ссылки. Этот вариант позволяет применить записанные действия не только к выбранной ячейке, но и с соседними с ней.

Макрос можно изменить, дав ему новое название, отредактировать его, или назначить горячие клавиши. Для этого перейдите в меню инструментов «Расширения», и выполните следующее:

1. Выберите пункт «Макросы».
2. В списке действий нажмите на строку «Настроить макросы».
3. Внесите все нужные изменения.
4. Для удаления или редактирования скрипта, используйте кнопку «Еще».
5. Нажмите на кнопку «Обновить».

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

1. Откройте рабочий лист таблицы.
2. На панели сверху нажмите «Инструменты».
3. Перейдите на пункт «Редактор скриптов».
4. Выберите строчку «Изменить».
5. В данных тапните по пункту «Триггеры текущего периода».
6. Снизу справа на экране тапните по кнопке «Добавление триггера», и установите подходящий вариант.
7. Нажмите кнопку «Сохранить».

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

Вкладка «Разработчик»

Запись макроса осуществляется встроенным рекордером, кнопка запуска которого находится на вкладке «Разработчик» главного меню программы Excel. По умолчанию эта вкладка не отображается. Добавить вкладку на панель можно следующим образом:

Excel 2010-2016

Переходим по пути: Файл — Параметры — Настроить ленту. В разделе «Настроить ленту» справа в списке «Основные вкладки» устанавливаем флажок «Разработчик» и нажимаем кнопку «OK».

Excel 2007

Переходим по пути: значок «Microsoft Office» — Параметры Excel — категория «Основные», в категории «Основные» устанавливаем флажок «Показывать вкладку «Разработчик» на ленте» и нажимаем кнопку «OK».

Excel 2000-2003

Здесь ничего отображать не надо: кнопки по работе с макросами и редактором VBA находятся в главном меню на вкладке «Сервис» — «Макрос».

На самом деле в Excel для работы с макросами используются не кнопки, а пункты меню, но, для удобства, продолжу называть их кнопками.

Запись макроса

Excel 2007-2016

На ленте во вкладке «Разработчик» нажмите кнопку «Запись макроса».

Excel 2000-2003

Пройдите по пунктам меню «Сервис» — «Макрос» — «Начать запись».

Откроется окно:

Окно «Запись макроса»

Имя макроса: можно изменить или оставить по умолчанию («Макрос» с очередным номером). Если у вас уже есть макрос с таким именем, он будет записан в новый программный модуль, который будет создан автоматически.

Сочетание клавиш: добавьте букву, если хотите запускать макрос сочетанием клавиш Ctrl+«буква». В какой раскладке вы впишите букву (например, «й» или «q»), в той и будет запускаться макрос. Если буква будет вписана в верхнем регистре («Й» или «Q»), используйте для запускa макроса сочетание клавиш Ctrl+Shift+«буква».

Сохранить в: выберите книгу, в которой должен быть сохранен макрос:

• «Эта книга» — макрос будет записан в книге, из которой рекордер был запущен (нажата кнопка «Запись макроса»).
• «Новая книга» — откроется новая книга и макрос будет записан в ней.
• «Личная книга макросов» — макрос запишется в Личную книгу макросов и будет доступен из всех открытых книг Excel.

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

Заполнив окно, нажимаем кнопку «OK», кнопка «Запись макроса» меняет свое название на «Остановить запись», и можно приступать, собственно, к записи макроса. Выполните на рабочем листе операции, последовательность которых вы хотите автоматизировать и, после их завершения, нажмите кнопку «Остановить запись». Макрос будет записан и появится в списке макросов.

Список макросов

Окно со списком макросов открывается во всех версиях Excel нажатием кнопки «Макросы». Выглядит оно следующим образом:

Список макросов во всех открытых книгах

• Выполнить — выделенный макрос будет запущен на выполнение.
• Войти — переход к коду макроса, запущенному и поставленному на паузу, как при обнаружении ошибки в коде.
• Изменить — переход к коду макроса для редактирования.
• Создать — кнопка станет активной при записи имени нового макроса в поле «Имя макроса».
• Удалить — удаление выделенного макроса.
• Параметры… — редактирование сочетания клавиш для запуска макроса и его описания.
• Находится в: — здесь можно выбрать все открытые книги или одну из них.
• Описание — текст, добавленный в одноименное поле при создании макроса или редактировании его параметров.

Запуск макроса

Запустить записанный макрос можно следующими способами:

• Сочетанием клавиш, указанным в параметрах макроса.
• Кнопкой «Выполнить» из окна списка макросов.
• Кнопкой «Run Sub» или клавишей «F5» из редактора VBA.
• Кнопкой, добавленной на лист Excel из раздела «Элементы управления формы» и которой назначен запускаемый макрос.

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

Пример записи простого макроса

Выполните следующие действия:

Excel 2007-2016

1. Откройте книгу Excel или создайте новую и сохраните ее как книгу с поддержкой макросов (.xlsm).
2. На вкладке «Разработчик» нажмите кнопку «Запись макроса».
3. В окне «Запись макроса» нажмите кнопку «OK», оставив параметры по умолчанию.
4. Перейдите на вкладку «Главная» и выберите любой цвет заливки ячеек.
5. Нажмите кнопку «Остановить запись».

Excel 2000-2003

1. Откройте книгу Excel или создайте новую и сохраните ее с расширением по умолчанию (.xls).
2. Включите запись макроса, пройдя по пунктам меню «Сервис» — «Макрос» — «Начать запись».
3. В окне «Запись макроса» нажмите кнопку «OK», оставив параметры по умолчанию.
4. На панели инструментов выберите любой цвет заливки ячеек.
5. Нажмите кнопку «Остановить запись».

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

Откройте список макросов, выберите записанный макрос и нажмите кнопку «Изменить». Вы увидите код вашего макроса:

Sub Макрос1() ‘ ‘ Макрос1 Макрос ‘ ‘     With Selection.Interior         .Pattern = xlSolid         .PatternColorIndex = xlAutomatic         .Color = 5296274         .TintAndShade = 0         .PatternTintAndShade = 0     End With End Sub

Число (5296274) в вашем примере будет другим, в зависимости от выбранного цвета заливки.

Выберите на активном листе любую ячейку или диапазон, вернитесь в редактор VBA и нажмите клавишу «F5» (курсор должен находиться в любом месте внутри кода запускаемого макроса). На активном листе выделенный диапазон окрасится в выбранный при записи макроса цвет.

Рекордер макросов может записывать много лишних строк, без которых можно обойтись. Точно также макрос будет работать и в такой редакции:

Sub Макрос2()   Selection.Interior.Color = 5296274 End Sub

Скопируйте этот код и вставьте его внизу, под вашим макросом, в редакторе VBA (числовое значение цвета можете изменить на свое или оставить, как в примере). Проверьте его работоспособность, выбрав незакрашенные ячейки и запустив макрос клавишей «F5», не забыв о том, что курсор должен находиться внутри этого макроса.

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

Назначение макроса кнопке

Теперь давайте назначим записанный макрос (любой из двух) кнопке, добавленной на лист Excel из раздела «Элементы управления формы»:

Excel 2007-2016

1. На вкладке «Разработчик» нажмите кнопку «Вставить» и в разделе «Элементы управления формы» кликните по изображению кнопки.
2. Переведите курсор на табличную область листа, курсор примет вид крестика, и кликните левой кнопкой мыши.
3. В открывшемся окне «Назначить макрос объекту» выберите один из новых макросов и нажмите кнопку «OK». Если нажать кнопку «Отмена», новая кнопка будет создана без назначенного макроса. Его можно будет назначить позднее, кликнув по ней правой кнопкой мыши и выбрав в контекстном меню «Назначить макрос…»

Excel 2000-2003

1. Отобразите панель инструментов «Формы», пройдя по пунктам меню «Вид» — «Панели инструментов» — «Формы».
2. Кликните по Кнопке на панели инструментов «Формы», затем переведите курсор на табличную часть рабочего листа, нажмите левую кнопку мыши и, не отпуская ее, нарисуйте прямоугольник (контур кнопки) нужного размера, затем отпустите кнопку мыши.
3. В открывшемся окне «Назначить макрос объекту» выберите один из новых макросов и нажмите кнопку «OK». Если нажать кнопку «Отмена», новая кнопка будет создана без назначенного макроса. Его можно будет назначить позднее, кликнув по ней правой кнопкой мыши и выбрав в контекстном меню «Назначить макрос…»

Кнопка создана. Выберите ячейку или диапазон и нажмите созданную кнопку. Цвет фона изменится.

Вы можете записать еще один такой же макрос, только выбрать на палитре «Нет заливки», создать еще одну кнопку и назначить ей вновь созданный макрос. Теперь, первая кнопка будет окрашивать выделенный диапазон, а вторая очищать его от заливки.

Сокращенный вариант кода по очистке фона будет выглядеть так:

Sub Макрос3()   Selection.Interior.Pattern = xlNone End Sub

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

Остановка макроса (процедуры)

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

1. Если панель инструментов редактора VBA доступна, нажмите кнопку Break или Reset.
2. Если редактор VBA не доступен, нажмите сочетание клавиш Ctrl+Break.
3. Если сочетание клавиш Ctrl+Break не срабатывает, закройте программу Excel через Диспетчер задач.
4. Если Диспетчер задач долго не открывается, остается только выключение компьютера большой кнопкой или кнопкой «Перезагрузка».

---