ПРЕДЛАГАЮ КОЛЛЕГАМ

О.А. Житкова, Т.И. Панфилова
Москва

VBA в приложении к Excel, Word и Power Point

Продолжение. Начало в № 1

Занятие № 2. Создание макросов и их применение в Excel

На этом занятии мы рассмотрим следующие вопросы:

• Понятие макроса.
• Запись простых макросов.
• Выполнение макросов.
• Редактирование макросов.
• Назначение макроса графическим изображениям.
• Удаление макросов из списка макросов.
• Ограниченность макросов.

Понятие макроса

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

MacroRecorder — это стандартное средство записи макросов в Excel, посредством которого можно записывать последовательность действий пользователя и получать соответствующий код (программу) на VBA. Программа, созданная с помощью MacroRecorder, называется макросом.

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

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

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

Запись макросов в приложении Excel

Для работы с макросами в приложении Excel имеется специальная панель.

Рис. 1. Панель для работы с макросами

Для вызова этой панели необходимо выполнить последовательно команды меню: “Вид | Панель инструментов | Visual Basic”.

Для записи макроса необходимо:

1. Нажать на кнопку “Запись макроса” на панели Visual Basic.

2. В диалоговом окне “Запись макроса” присвоить имя макросу. (В имени макроса первым символом должна быть буква, не допускается использование пробелов.)

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

4. Нажать кнопку “Остановить запись”.

Пример

Рассмотрим процедуру записи последовательности действий на следующем примере: определим максимальное значение в выделенном диапазоне ячеек.

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

Это действие в Excel можно выполнить с помощью стандартной функции МАКС, но мы продемонстрируем на этом примере, как сохранить последовательность действий пользователя и на их основе создать макрос.

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

1. Откройте новую книгу.

2. Создайте таблицу по приведенному образцу.

3. В ячейке D10 наберите “Максимальная масса в граммах”. Курсор после набора текста может находиться в любой ячейке, кроме ячейки, в которой мы хотим получить результат. На панели Visual Basic нажмите на кнопку “Запись макроса”. Появится диалоговое окно “Запись макроса”:

Рис. 2. Окно “Запись макроса”

Введите вместо названия “Макрос1” название макроса “Алмаз” и нажмите “ОК”. На экране появится панель инструментов “Остановить запись”. Теперь все производимые нами действия будут записываться до тех пор, пока не будет нажата эта кнопка. Не выполняйте никаких лишних действий. Все ваши действия фиксируются и записываются.

Установите курсор в ячейку E10. В этой ячейке должен появиться результат.

4. Выполните команду “Вставка | Функция”. Появится диалоговое окно “Мастер функций”. В окне “Категории функций” выберите “Статистические”. Затем выберите функцию МАКС. Появится диалоговое окно, в первой строке которого необходимо указать диапазон, в котором мы будем искать максимальное значение, — E2:E9. Нажмите “ОК”.

5. Щелкните на кнопке “Остановить запись” на панели Visual Basic. Запись макроса завершена.

Выполнение макросов

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

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

Рис. 3. Диалоговое окно функции МАКС

Затем:

1. Установите курсор в любую ячейку листа.

2. Выполните команду “Сервис | Макрос | Макросы”. Появится диалоговое окно, показанное на рис. 4:

Рис. 4. Диалоговое окно “Макрос”

3. Выделите макрос “Алмаз” и щелкните по кнопке “Выполнить”. В ячейке E10 появится результат.

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

Во время записи макроса Excel запоминает ваши действия и преобразует их в код VBA. Можно просмотреть полученный код и отредактировать его, если в этом есть необходимость. Для просмотра созданного макроса выполните команду “Сервис | Макрос | Макросы”. Появится уже знакомое диалоговое окно “Макрос”.

Выделим макрос “Алмаз” и щелкнем по кнопке “Изменить”. Откроется окно редактора.

Рис. 5. Использование редактора Visual Basic для просмотра
и редактирования кода VBA

При записи макроса выполнялись всего два действия. Сначала мы установили курсор в ячейку E10. На языке VBA этому действию соответствует строка:

Range("E10").Select

Затем мы вызвали функцию МАКС и в качестве аргумента указали диапазон ячеек E2:E9:

ActiveCell.FormulaR1C1 = "=MAX(R[-8]C:R[-1]C)"

Полученный код можно редактировать непосредственно в редакторе Visual Basic. Изменим размер шрифта в ячейке E10 на 16:

Range("E10").Font.Size = 16

Можно также изменить цвет шрифта. Например, изменим цвет текста в ячейке на красный:

Range("E10").Font.ColorIndex = 3.

Значения, которые может принять свойство Font.ColorIndex, изменяются от 1 до 56.

После внесенных изменений текст макроса стал следующим:

Sub Алмаз()

' Алмаз Макрос

Range("E10").Select

ActiveCell.FormulaR1C1 = "=MAX(R[-8]C:R[-1]C)"

Range("E10").Font.Size = 16

Range("E10").Font.ColorIndex = 3

End Sub

Закройте окно редактора, вернитесь на лист Excel, удалите содержимое ячейки E10 и запустите макрос на выполнение.

Назначение макроса графическим изображениям

Разрабатывая приложение, надо думать о том, чтобы создать легкий и удобный интерфейс для выполнения задач автоматизации. Запуск макросов на выполнение посредством команд меню или кнопок на панели Visual Basic — не слишком удобный механизм для пользователей.

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

Используя панель “Рисование”, нарисуем на листе любую автофигуру. Щелкнем правой кнопкой мыши на изображении и в открывшемся контекстном меню выберем команду “Назначить макрос”. Отобразится диалоговое окно “Назначить макрос объекту”. Затем необходимо выбрать макрос “Алмаз” и щелкнуть вне графического изображения, чтобы снять выделение с объекта.

Теперь макрос “Алмаз” можно запустить посредством щелчка на автофигуре.

Рис. 6. Автофигура и кнопка для запуска макроса

Запустить макрос на выполнение можно и другим способом, использовав командную кнопку. Вставить командую кнопку непосредственно на рабочий лист Excel можно с помощью панели инструментов “Формы”. Затем необходимо назначить ей наш макрос.

Удаление макросов из списка макросов

Для того чтобы удалить макрос, надо:

1. Выполнить команду “Сервис | Макрос | Макросы”. Появится уже знакомое диалоговое окно.

2. Выделить макрос, подлежащий удалению, и щелкнуть по кнопке “Удалить”.

3. Подтвердить выполнение операции в специальном окне, которое появится.

Ограниченность макросов

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

Эти ограничения приводят к необходимости создания программ на VBA.

Продолжение следует