1.2 Использование макросов в книгах Excel. Порядок записи и запуска макросов. Структура макроса. Особенности личной книги макросов

За каждой кнопкой панелей инструментовExcelзакреплена небольшая программа – макрос. Макрос представляет собой последовательность макрокоманд и макрофункций.

Макросы Microsoft Office Excel— это простые, легко адаптируемые и вместе с тем мощные инструменты, позволяющие пользователю экономить свое время и работать более продуктивно [1].

Макросы Excel— это фрагменты кода, которые хранятся в книге и позволяют автоматизировать некоторые процедуры систематизации данных.

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

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

В большинстве программ Microsoft Office, включая Excel, в качестве языка программирования макросов используется Visual Basic для приложений (VBA).

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

При записи макроса все необходимые действия записываются программой записи макроса.

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

1. нажать кнопку Microsoft Office, а затем щелкните Параметры Excel;

2. в категории Личная настройка в группе Основные параметры работы с Excel установите флажок Показывать вкладку "Разработчик" на ленте, а затем нажмите кнопку ОК.

Для установки уровня безопасности, временно разрешающего выполнение всех макросов, выполните следующие действия:

1. на вкладке Разработчик в группе Код (см. рис. 4) нажать кнопку Безопасность макросов;

Рисунок 4 – Окно группы Код

2. в группе Параметры макросов выберать переключатель Включить все макросы (не рекомендуется, возможен запуск опасной программы), а затем дважды нажать кнопку ОК [1].

Порядок записи макроса(применимо кExcel2007):

1. На вкладке Разработчик в группе Код нажать кнопку Запись макроса (см. рис. 5).

Рисунок 5 – Окно запись макроса

2. В поле Имя макроса ввести имя макроса.

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

3. Чтобы назначить в сочетании с клавишей CTRL клавишу быстрого вызова для запуска макроса, в поле Сочетание клавиш ввести любую строчную или прописную букву.

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

4. В списке Сохранить в выбрать книгу, в которой необходимо сохранить макрос.

Если нужно, чтобы данный макрос был всегда доступен при работе в приложении Excel, выберите вариант Личная книга макросов. При выборе варианта Личная книга макросов создается скрытая личная книга макросов (Personal.xlsb) (если она еще не существует), в которой сохраняется данный макрос.

5. Ввести описание макроса в поле Описание.

6. Для начала записи макроса нажать кнопку ОК. На экран будет выведена панель инструментов Остановить запись. Панель содержит две кнопки: [Остановить запись] и [Относительная ссылка].

Если панель Остановить запись не появилась, то ее можно отобразить командой Вид►Панели инструментов►Остановить запись.

7. Выполнить щелчок на кнопке [Относительная ссылка] для того, чтобы макрос в дальнейшем выполнялся для любого выделенного блока ячеек, а не той ячейки, которая была активна при записи макроса.

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

9. На вкладке Разработчик в группе Код нажать кнопку Остановить запись .

Можно также нажать кнопку Остановить запись слева от строки состояния [1].

Существует несколько способов выполнения макроса(применимо кExcel2007):

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

• Запуск макроса нажатием клавиши CTRL в сочетании с клавишей быстрого вызова.

• Запуск макроса нажатием кнопки на панели быстрого доступа.

• Запуск макроса щелчком области графического объекта.

• Автоматический запуск макроса во время открытия книги.

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

1. Открыть книгу, содержащую нужный макрос.

2. На вкладке Разработчик в группе Код нажать кнопку Макросы.

3. В поле Имя макроса ввести имя макроса, который нужно выполнить.

4. Выполнить одно из следующих действий:

• для запуска макроса в книге Excel нажать кнопку Выполнить. Для запуска макроса можно также нажать клавиши CTRL+F8. Для прекращения выполнения макроса нажать клавишу ESC.

• для запуска макроса из модуля Microsoft Visual Basic нажать кнопку Изменить, а затем в меню Run выберите команду Run Sub/UserForm или нажать клавишу F5 [1].

Запуск макроса нажатием клавиши CTRL в сочетании с клавишей быстрого вызова:

1. В поле Имя макроса выбрать макрос, которому нужно назначить сочетание клавиши CTRL с клавишей быстрого вызова.

2. Нажать кнопку Параметры.

3. В поле Сочетание клавиш ввести нужную прописную или строчную буквы.

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

4. Ввести описание макроса в поле Описание.

5. Нажать кнопку ОК, а затем — кнопку Отмена.

Запуск макроса нажатием кнопки на панели быстрого доступа:

1. Нажать кнопку Microsoft Office , а затем щелкните Параметры Excel.

2. В категории Настройка в списке Выбрать команды выбрать пункт Макрос.

3. Выберать в списке созданный макрос и нажать кнопку Добавить.

4. Чтобы изменить изображение на кнопке макроса, выбрать макрос в поле, в которое он был добавлен, и нажать кнопку Изменить.

5. В поле Символ выберать нужное изображение для кнопки.

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

7. Нажать кнопку ОК, и кнопка макроса будет добавлена на панель быстрого доступа .

8. На панели быстрого доступа нажать кнопку макроса.

Запуск макроса щелчком области графического объекта:

1. Выделить на листе графический объект (рисунок, клип, фигуру или рисунок SmartArt).

2. Для создания активной области на существующем объекте нажать кнопку Фигуры в группе Иллюстрации на вкладке Вставка, выбрать одну из фигур и нарисовать ее на существующем объекте (см. рис. 6).

Рисунок 6 – Окно группы Иллюстрации

3. Щелкнуть созданную активную область правой кнопкой мыши, а затем выбрать пункт Назначить макрос в контекстном меню.

4. Выполнить одно из следующих действий:

• Чтобы назначить графическому объекту существующий макрос, дважды щелкнуть нужный макрос или ввести его имя в поле Имя макроса.

• Чтобы записать новый макрос для назначения выделенному графическому объекту, нажать кнопку Записать. После завершения записи макроса нажать кнопку Остановить запись на вкладке Разработчик в группе Код.

Можно также нажать кнопку Остановить запись слева от строки состояния.

Для редактирования существующего макроса щелкните его имя в поле Имя макроса, а затем нажмите кнопку Изменить.

5. Нажать кнопку ОК.

6. На листе выбрать активную область.

7. На вкладке Формат в группе Стили фигур выполнить следующие действия:

• щелкнуть стрелку рядом с кнопкой Заливка фигуры и выбрать вариант Нет заливки;

• щелкнуть стрелку возле кнопки Контур фигуры и выберать вариант Нет контура [1].

Автоматический запуск макроса во время открытия книги:

Если макрос записан и сохранен с именем "Auto_open", он будет запускаться при каждом открытии книги, содержащей этот макрос. Другим способом автоматического запуска макроса при открытии книги является написание процедуры на языке VBA (Visual Basic для приложений) в событии Open книги с помощью редактора Visual Basic. Событие Open представляет собой встроенное событие книги, запускающее код макроса при каждом открытии книги.

Создание макроса «Авто_открыть»:

1. Для сохранения макроса с конкретной книгой сначала нужно открыть эту книгу.

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

3. В поле Имя макроса ввести Авто_открыть.

4. В списке Сохранить в выберать книгу, в которой нужно сохранить макрос.

5. Нажать кнопку ОК, а затем выполнить действия, которые нужно записать.

6. На вкладке Разработчик в группе Код нажать кнопку Остановить запись .

Чтобы предотвратить автоматическое выполнение макроса "Auto_Open" при запуске Microsoft Excel, во время запуска нужно удерживать нажатой клавишу SHIFT.

Создание процедуры на языке VBA для события Open книги:

1. Сохранить и закрыть все открытые книги.

2. Открыть или создать книгу, в которую нужно добавить макрос.

3. На вкладке Разработчик в группе Код нажать кнопку Visual Basic.

4. В окне обозревателя проектов щелкнуть правой кнопкой мыши объект ЭтаКнига, а затем в контекстном меню выбрать команду View Code (просмотр кода).

Если окно проекта не отображено, в меню Вид щелкнуть пункт Окно проекта.

5. В списке объектов, расположенном над окном кода, выбрать пункт Workbook.

6. Будет автоматически создана пустая процедура для события Open, похожая на приведенную ниже:

Private Sub Workbook_Open()

End Sub

7. Добавить в процедуру следующие строки текста программы:

Private Sub Workbook_Open()

MsgBox Date

Worksheets("Sheet1").Range("A1").Value = Date

End Sub

8. Переключиться на приложение Excel и сохранить данную книгу как книгу с поддержкой макросов (.XLSM).

9. Закрыть и снова открыть книгу. При повторном открытии файла запускается процедура «Workbook_Open», отображающая в окне сообщения текущую дату.

10. В окне сообщения нажать кнопку ОК.

Ячейка A1 листа 1 также содержит дату, являющуюся результатом запуска процедуры «Workbook_Open».

Структура макроса.

Каждый макрос VBA начинается со слова Sub(сокращение слова subprogram – подпрограмма), следом за которым идет имя макроса. Строку с ключевым словом Sub и именем макроса называют строкой объявления макроса. Имя макроса должно начинаться с буквы, но может содержать и числа. Имя макроса не может включать в себя пробелы или знаки препинания. В Excel максимальная длина имени макроса – 64 символа, а в Word она может составлять до 80 символов. За именем макроса всегда следуют пустые круглые скобки. Сразу за объявлением макроса следует тело макроса, которое может содержать, либо не содержать комментарии.Комментарий– это строка в макросе VBA, которая не содержит инструкций, это поясняющий текст. Строки комментариев начинаются с прямого апострофа ('). Используются комментарии для того, чтобы код подпрограммы был более понятен при чтении.

Каждая строка в теле макроса состоит из одного или нескольких операторов VBA. Оператор VBA– это последовательность ключевых слов и других символов, которые совместно составляют одну полную инструкцию [3]. На одной строке можно записать несколько инструкций, разделив их двоеточием, но, как правило, каждую инструкцию начинают с новой строки, это улучшает наглядность текста программ. За телом макроса следует строка, содержащая ключевыеслова End Sub, что свидетельствует о том, что достигнут конец макроса. При выполнении инструкции End Sub никаких видимых действий не происходит, но при этом освобождается вся память, которая использовалась для выполнения подпрограммы. При просмотре макросов можно заметить, что различные части текста макроса отображаются различными цветами. Так, например, комментарии имеют зеленый цвет шрифта, а ключевые слова, имеющие строго определенный смысл отображаются синим цветом. Остальной текст макроса отображается черным цветом. При возникновении ошибок в программе, часть кода подкрашивается красным цветом. Для выделения различных секций макроса используются отступы в коде. Каждый уровень отступа помогает отделять одну часть макроса от другой. Отступы делать не обязательно, макросы будут выполняться независимо от них, размещение строк с отступом выполняется макрорекордером и хорошими программистами для более легкого чтения их программ. В коде макроса зачастую можно встретить сочетание пробела и символа подчеркивания (_) в конце строки. Эта комбинация называетсясимволом продолжения строкии сообщает о том, что следующая строка макроса должна быть присоединена к текущей строке для образования единого оператора.

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

Описание:	хранение общих функций и процедур для персонального использования
Требования:	нет
Достоинства:	возможность хранения пользовательских функций для работы с данными (UDF)
Недостатки:	сложности с тиражированием

Для общих программ и макросов можно использовать, так называемую, личную книгу макросов – по умолчанию это файл PERSONAL.XLS (в Excel 2007-2010 PERSONAL.XLSB). Файл с данным именем создается при записи макроса средствами Excel. Этот файл будет автоматически загружаться каждый раз при запуске Excel. Местонахождение данного файла в каталоге Windows: ПОЛЬЗОВАТЕЛЬ\Application Data\Microsoft\Excel\XLSTART. На самом деле Excel будет запускать автоматически все файлы из данного каталога, независимо от имен файлов.

Макросы, сохраненные в личной книге макросов, называются глобальными.

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