Тема: Основы программирования в интегрированной среде Visual Basic. Инструментальная среда разработки Visual Basic Application (VBA). Макрорекордер. Использование макросов

Цель работы:

- научиться создавать, редактировать, удалять макросы;

- освоить работу с макросами.

Теоретические сведения

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

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

Модуль – совокупность описаний, инструкций и процедур, сохраненная под общим именем. Существуют модули двух типов: стандартный модуль и модуль класса Visual Basic.

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

- макрорекордер записывает только те действия, которые выполняются в пределах окна Microsoft Excel. Закрытие Excel или переключение в другую программу приведет к остановке записи;

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

- если во время записи макроса макрорекордером была введена ошибка, она будет записана. Однако можно нажать на кнопку отмены последнего действия (Undo) - во время записи макроса макрорекордером она не просто возвращает предыдущее состояние, но и стирает последнюю записанную команду на VBA.

Создание макроса в среде Microsoft 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, рисунок 10.1).

Рисунок 10.1 Окно вызова редактора Visual Basic

Задание 1. На вкладке Разработка в группе Код выберите команду Visual Basic (рисунок 10.2).

Рисунок 10.2 Редактор Visual Basic

При необходимости в меню Insert выберите команду Module. Модули будут созданы автоматически для всех листов книги.

В окне программы модуля введите или скопируйте нужный текст макроса. Для запуска данного макроса из окна модуля нажмите клавишу F5.

Когда макрос будет создан, в меню File выберите команду Close and Return to Microsoft Excel.

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

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

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

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

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

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

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

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

- в поле Имя макроса введите имя макроса.

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

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

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

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

Если нужно, чтобы данный макрос был всегда доступен при работе в приложении Excel, выберите вариант Личная книга макросов. При выборе варианта Личная книга макросов создается скрытая личная книга макросов (Personal.xlsb) (если она еще не существует), в которой сохраняется данный макрос. В Microsoft Windows Vista эта книга сохраняется в папке C:\Users\имя_пользователя\Application Data\Microsoft\Excel\XLStart. В Microsoft Windows XP эта книга сохраняется в папке C:\Documents and Settings\имя_пользователя\Application Data\Microsoft\Excel\XLStart, откуда она будет автоматически загружаться при каждом запуске приложения Excel. Если необходимо автоматически выполнять макрос из личной книги в другой книге, необходимо также сохранить эту книгу в папке XLStart, чтобы при запуске приложения Excel открывались обе книги.

- введите описание макроса в поле Описание;

- для начала записи макроса нажмите кнопку ОК;

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

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

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

Задание 3. Копирование части макроса для создания другого макроса

- откройте книгу, содержащую макрос, который нужно скопировать;

- на вкладке Разработчик в группе Код нажмите кнопку Макросы;

- в поле Имя макроса выберите имя макроса, который нужно скопировать;

- нажмите кнопку Изменить;

- в окне редактора Visual Basic выделите строки макроса, которые нужно скопировать. Для копирования макроса целиком включите в выделенную часть строки слова Sub и End Sub;

- в меню Edit выберите команду Copy. Можно также щелкнуть правой кнопкой мыши, а затем в контекстном меню выбрать команду Copy или нажать клавиши CTRL+C;

- в поле Procedure окна кода выберите модуль, куда нужно поместить текст;

- в меню Edit выберите команду Paste.

Можно также щелкнуть правой кнопкой мыши, а затем в контекстном меню выбрать команду Paste или нажать клавиши CTRL+V. Личную книгу макросов (Personal.xls) редактировать нельзя, поскольку она является скрытой книгой, которая всегда открыта. Сначала его необходимо отобразить с помощью команды Показать. Файл также можно открыть в редакторе Visual Basic, нажав клавиши ALT+F11.

Задание 4. Назначение макроса объекту, графическому объекту и элементу управления

- щелкните на листе правой кнопкой мыши объект, графический объект или элемент управления, которому нужно назначить существующий макрос, а затем в контекстном меню выберите команду Назначить макрос;

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

Задание 5. Удаление макроса. Выполните одну из процедур, описанных ниже:

- откройте книгу, содержащую макрос, который нужно удалить;

- если макрос, который требуется удалить, хранится в личной книге макросов (Personal.xlsb) и эта книга скрыта, для ее отображения выполните указанные ниже действия:

1) на вкладке Вид в группе Окно нажмите кнопку Отобразить окно;

2) в разделе Показать скрытое окно книги выберите пункт "PERSONAL" и нажмите кнопку ОК;

3) в списке Находится в выберите рабочую книгу с макросом, который требуется удалить. Например, выберите вариант Эта книга;

4) в поле Имя макроса выберите имя макроса, который нужно удалить;

5) нажмите кнопку Удалить.

Задание 6. Вывод на экран названий месяцев. При составлении различных отчетов и сводок в качестве названий строк часто используются названия месяцев. Создадим макрос, который будет выводить на экран столбец с названиями месяцев в ячейках A1:A12. Для этого выполните следующие действия:

- установите курсор в какую-нибудь ячейку книги, за исключением A1;

- откройте окно редактораVisual Basic;

- введите в поле ввода Имя макроса название создаваемого макроса Месяц_абс. В поле ввода Описание введите текст: Введите названия месяцев. Нажмите кнопку Параметры. Появится окно диалога “Запись макроса”;

- введите клавишу быстрого вызова макроса. Для этого перейдите в поле ввода Ctrl, переключитесь на латинский регистр, удалите находящуюся там букву и введите букву m. После этого нажмите кнопку OK. Начиная с этого момента, осуществляется запись макроса;

- выполните последовательность действий, которую будет выполнять макрос: установите курсор в ячейку A1; введите слово январь; поместите указатель мыши в правый нижний угол ячейки A1, при этом указатель мыши изменит вид на черный крестик; нажмите кнопку мыши и, удерживая ее нажатой, продолжите выделение до ячейки A12;

- нажмите на кнопку Остановить макрос.

Если вы не выполните условие 1 шага, то есть курсор будет находиться в ячейке A1, то макрос будет правильно работать только в том случае, когда вы перед нажатием клавиш Ctrl+m поместите курсор в ячейку A1.

Макрос записан и теперь нажатие клавиш Ctrl+m вызовет появление в ячейках A1:A12 названий месяцев. При записи макроса мы не включали режим записи с относительными ссылками, поэтому названия месяцев будут появляться только в ячейках A1:A12. Для устранения этого неудобства предназначен режим записи

Задание 7. Создание макроса с относительными ссылками. В этом примере создадим макрос с относительными ссылками для размещения заголовка таблицы продаж компьютеров в произвольном месте рабочей книги. Перед созданием макроса включите режим записи с относительными ссылками. Для этого выполните следующие действия:

- выполните команду Запись макроса - С относительными ссылками;

- введите в поле ввода Имя макроса название создаваемого макроса — Заголовок. В поле ввода Описание введите текст: Введите названия компьютеров;

- нажмите кнопку Параметры. Появится окно диалога “Запись макроса”;

- введите наименование добавляемого пункта меню: Типы ЭВМ. Нажмите кнопку OK;

- выполните последовательность действий, которую будет выполнять макрос: введите заголовок таблицы: Тип компьютера; нажмите клавишу Enter; введите тип 1 компьютера CP_35_SR и нажмите клавишу Tab. Аналогично введите следующие типы компьютеров CP_40_SR, CP_55_LS, CP_55_SX. Выделите четыре ячейки с введенными типами компьютеров, выберите команду Формат - Столбец, а затем команду Подгон ширины (поскольку названия типов компьютеров не умещаются полностью в ячейке);

- Нажмите на кнопку Остановить макрос.

После окончания записи макроса в меню команды Сервис появится пункт с названием Типы ЭВМ.

Для выполнения макроса “Заголовок” нужно нажать кнопку мыши на пункте с названием Типы ЭВМ. В том месте таблицы, где расположен курсор, появится заголовок следующего вида: Тип компьютера CP_35_SR CP_40_SR CP_55_LS CP_55_SX

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

Задание 8. Макрос “арифметическая прогрессия”. В результате работы макроса “арифметическая прогрессия” выделенные в строке ячейки заполняются числами, являющимися членами арифметической прогрессии с разностью равной 0,5 или с шагом 0,5.

Для записи макроса “арифметическая прогрессия ” выполните следующие действия:

- введите в ячейку A1 число 1000 и выделите ячейки A1:C1;

- выполните команду Сервис - Запись макроса - Начать запись. Появится окно диалога “Запись макроса”;

- введите в поле ввода Имя макроса название записываемого макроса Прогрессия и нажмите кнопку Параметры. Окно диалога “Запись макроса” расширяется;

- перейдите на латинский регистр и введите букву p для клавиши быстрого вызова макроса;

- нажмите кнопку OK в окне диалога “Запись макроса”, которое после этого закроется. Начиная с этого момента, все ваши действия будут записываться в макрос. В строке состояния появится слово “Запись”;

- выполните команду Правка - Заполнить - Прогрессия. Появится окно диалога «Прогрессия»;

- установите флажок по строкам. Это означает, что прогрессия будет строится в выделенной строке;

- установите флажок арифметическая. Это означает, что ячейки в строке будет заполняться числами, являющимися членами арифметической прогрессии;

- в окне “Шаг” введите число 0,5 — разность арифметической прогрессии;

- нажмите кнопку OK, чтобы выйти из окна диалога “Прогрессия”. Теперь нужно закончить запись макроса;

- нажмите кнопку Остановить макрос.

Результаты работы макроса “арифметическая прогрессия”. Он запускается нажатием Ctrl+p. Перед этим нужно ввести первый член прогрессии и выделить ячейки в строке, где будут размещаться члены арифметической прогрессии с разностью 0,5. После нажатия клавиш Ctrl+p ячейки A1:G1 заполнятся числами 1000 1000,5 1001 1001,5 1002 1002,5 1003. Если первая ячейка среди выделенных, не содержит число, то заполнение числами не произойдет, и ячейки останутся без изменения.

Контрольные вопросы.

1. Дать понятие о макросе. Для чего он используется?

2. Дать понятие модуля. Какие типы модулей представлены в Microsoft Excel?

3. Какие настройки необходимо предварительно выполнить для настройки макросов в Microsoft Excel

4. Как осуществить запись макрокоманды?

5. Какова последовательность действий при создании макроса с помощью макрорекордера?

6. Какие требования налагаются при создании имени макроса?

7. Какие символы можно использовать для задания сочетания клавиш при запуске макроса?

8. Как запустить макрос на выполнение?

9. Какие два режима записи, отличающиеся разными видами ссылок Вам известны? Чем они отличаются друг от друга?

10. Как осуществить выбор нужного режима ссылок?

11. Как назначить макрос графическому объекту?

12. Как активизировать графический объект, к которому назначен макрос?

13. Как назначить макрос рисунку?

14. Как удалить существующий макрос?

15. Какие действия необходимо предпринять для переименования макроса?

Литература[5, 8].