В начало

Приложение. Ответы

24-й час. Выполнение процедур

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

В этом часе будут рассмотрены следующие вопросы.

• Использование процедур Auto_Open и Auto_Close.
• Процедуры, выполняемые при наступлении определенных событий.
• Создание надстроек Excel.

Автоматическое выполнение процедур

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

Процедуру Auto_Open можно использовать для решения следующих задач:

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

Процедура, выполняемая при закрытии рабочей книги, называется Auto_Close. Вот типичные задачи, выполняемые этой процедурой:

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

Можно использовать обе или только одну из этих процедур.

Имена этих процедур не чувствительны к применяемому регистру, т.е. их можно писать как строчными, так и прописными буквами.

Если рабочая книга открывается из процедуры VBA, то процедуры Auto не выполняются. В этом случае для выполнения этих процедур используется метод RunAutoMacros.

Приведем примеры процедур Auto_Open и Auto_Close. Откройте новую рабочую книгу, вставьте модуль и в этом модуле создайте процедуру Auto_Open, код которой показан в листинге 24.1.

Листинг 24.1. Пример процедуры Auto Open

1: Sub Auto_Open()

2:  Range("A1").Value = "Текущая дата:"

3:

4:  Range("B1").FormulaR1C1 = "=NOW()"

5:  Columns("A:B").EntireColumn.AutoFit

6:

7:  With ActiveWindow

8:   .DisplayHorizontalScrollBar = False

9:   .DisplayVerticalScrollBar = False

10:  End With

11:  With Application

12:   .DisplayFormulaBar = False

13:   .DisplayStatusBar = False

14:  End With

15: End Sub

Эта процедура вводит в рабочий лист текущую дату и время и удаляет из окна Excel полосы прокрутки, строку формул и строку состояния. Если надо восстановить внешний вид окна Excel после закрытия этой рабочей книги, создайте еще процедуру Auto_Close, код которой приведен в листинге 24.2.

Листинг 24.2. Пример процедуры Auto Close

1: Sub Auto_Close()

2:

3:  With ActiveWindow

4:   .DisplayHorizontalScrollBar = True

5:   .DisplayVerticalScrollBar = True

6:  End With

7:  With Application

8:   .DisplayFormulaBar = True

9:   .DisplayStatusBar = True

10:  End With

11: End Sub

Сохраните эту рабочую книгу пол именем Час24. Для тестирования процедур закройте рабочую книгу, а затем откройте ее снова. Вы увидите текущую дату в ячейке В1, а также заметите отсутствие полос прокрутки, строки формул и строки состояния (рис. 24.1). Закройте эту рабочую книгу и откройте новую, чтобы убедиться в том, что привычный вид окна Excel восстановлен.

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

Процедуры, выполняемые при наступлении событий

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

• OnAction - выполняется, если указанный объект становится активным, например на нем щелкнули кнопкой мыши.
• OnCalculate - выполняется после пересчета рабочего листа.
• OnData - выполняется, когда получены данные из приложения, отличного от Excel.
• OnDoubleСlick - выполняется, если на указанном объекте дважды щелкнули кнопкой мыши.
• OnEntry - выполняется, когда пользователь нажал клавишу <Enter> (или переместил табличный курсор в другую ячейку) после ввода данных в ячейку.
• OnKey - выполняется, если нажата указанная комбинация клавиш.
• OnRepeat - выполняется, если возвращена отмененная команда.
• OnSheetActivate - выполняется, когда делается активным (текущим) указанный рабочий лист.
• OnSheetDeactivate - выполняется, если пользователь переходит из указанного рабочего листа в любой другой.
• OnTime - выполняется в указанное время, если в это время загружена программа Excel и открыта книга, содержащая процедуру OnTime.
• OnUndo - выполняется при отмене ранее выполненной команды.
• OnWindow - выполняется, когда пользователь переключается в указанное окно либо когда активизируется или открывается окно приложения Excel.

Многие из приведенных здесь событий в справочной системе VBA помечены как hidden (скрытый). Это указывает на то, что они не отображены в окне просмотра объектов, но не означает, что они не доступны и их нельзя использовать.

Обычно Оn-свойства объектов устанавливаются в процедуре Auto_Open рабочей книги. Например, можно предусмотреть сохранение рабочей книги при наступлении события OnData либо проверить корректность введенных данных после события OnEntry. Продемонстрируем на небольшом примере, как "'работают" события.

1. Откройте новую рабочую книгу, которая должна иметь не менее двух рабочих листов.
2. Создайте процедуру Auto_Open со следующим кодом:

Worksheets("Лист1").OnSheetActivate= "Попытка"

3. Создайте новую процедуру с именем попытка со строкой кода

MsgBox "Вы вернулись в Лист1!"

4. Сохраните рабочую книгу под именем Событие и закройте ее.
5. Откройте рабочую книгу Событие и перейдите в рабочий лист Лист2.
6. Теперь перейдите в Лист1 - отобразится окно сообщения.
7. Щелкните на кнопке ОК для закрытия окна сообщения.

Для следующего примера в процедуру Auto_Open введите строку кода

Worksheets("Лист1").OnEntry = "ПроверкаА1"

Теперь создайте процедуру ПроверкаА1, код которой приведен в листинге 24.3.

Листинг 24.3. Процедура ПроверкаА1

1: Sub ПроверкаА1()

2:  If Range("A1").Value < 5 Then

3:   MsgBox "Значение должно быть не меньше 5"

4:   Range("A1").Clear

5:   Range("A1").Activate

6:  End If

7: End Sub

Перейдите в Лист1 и выберите ячейку А1. Введите число 4 и нажмите <Enter>. Появится окно с сообщением, что число должно быть не меньше 5. Щелкните на кнопке ОК для закрытия окна сообщения.

Создание надстроек Excel

В этом часе вы уже познакомились с двумя способами автоматического выполнения процедур (при открытии или закрытии рабочих книг и в ответ на наступление определенных событий). Но предположим, что необходима автоматическая загрузка определенных процедур при открытии самой программы Excel. Перечисленные выше способы автоматического выполнения процедур в этом случае не подходят. Выход есть и в данной ситуации: надо создать необходимые процедуры и сохранить рабочую книгу их содержащую, как надстройку. Рабочая книга-надстройка содержит информацию, созданную пользователем, например специальную структуру и оформление рабочего листа или процедуры. Книгу-надстройку нельзя отрыть (как обычную рабочую книгу) и изменить, но она может содержать средства настройки, которыми пользователь может управлять. Обычно надстройки содержат следующее:

• функции, созданные пользователем;
• пользовательские диалоговые окна;
• пользовательские меню;
• пользовательские панели инструментов.

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

1. Откройте новую рабочую книгу и создайте функцию Комиссионные.
2. Введите код этой функции

Function Комиссионные(х As Variant)

 Комиссионные = х * 0.3

End Function

3. Сохраните рабочую книгу под именем МояФункция.
4. Выполните команду Файл > Сохранить как. В диалоговом окне Сохранение документа в раскрывающемся списке Тип файла выберите Надстройка Microsoft Excel. Автоматически откроется папка Addins. Щелкните на кнопке Сохранить для создания новой надстройки.
5. Закройте все открытые рабочие книги.
6. Откройте новую рабочую книгу.
7. Выполните команду Сервис > Надстройки, откроется диалоговое окно Надстройки.
8. Щелкните на кнопке Обзор. В открывшемся диалоговом окне Обзор, отображающем содержимое папки Addins, выберите файл МояФункция и щелкните на кнопке ОК. Щелкните на кнопке ОК в диалоговом окне Надстройки.
9. Если вы сейчас откроете меню Окно, то увидите, что открыта только одна(текущая) рабочая книга.
10. В ячейку А1 введите число 100. В ячейку В1 введите формулу =комиссионные(А1) и нажмите клавишу <Enter>. Комиссионные будут вычислены.

Надеюсь, этот маленький пример ясно показал основную идею создания и использования надстроек.

Резюме

В этом часе вы получили тот уровень знаний о VBA, который необходим для автоматизации выполнения процедур. Вы узнали, что процедура Auto_Open автоматически выполняется при открытии рабочей книги, a Auto_Close - при закрытии. Вы также познакомились с процедурами, выполняемыми при наступлении определенных событий. Наконец, вы узнали, что такое надстройки Excel, как их создавать и использовать.

Вопросы и ответы

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

Ответ. Нет, в данном случае имя процедуры "работает" как спусковой крючок для автоматического выполнения процедуры.

Вопрос. Как сделать, чтобы процедура, сохраненная в книге-надстройке, была доступна всегда?

Ответ. Сохраните ее в личной книге макросов.

Практикум

С помощью тестов и упражнений вы проверите, насколько хорошо усвоили изложенный материал. Ответы на вопросы смотрите в Приложении.

Тесты

1. Если необходимо выполнение определенной процедуры при импортировании данных с помощью MS Query, какое событие необходимо использовать?
2. Истинно или ложно следующее утверждение: в рабочей книге может быть несколько процедур Auto_Open?
3. Назовите не менее двух свойств событий, которые можно назначить для выполнения процедур проверки корректности вводимых данных.
4. Истинно или ложно следующее утверждение: вы должны при использовании надстройки каждый раз загружать ее вручную?
5. Предположим, что при закрытии рабочей книги необходимо сохранить внешний файл Word. Как это сделать?
6. Сформулируйте задачи, которые могут выполнять надстройки.

Упражнение

Откройте новую рабочую книгу и создайте процедуру Auto_Open, которая должна выполнять следующие операции:

• ввести текст Дата:, Имя:, Организация: в ячейки A1, A2 и A3 соответственно;
• с помощью функции Now в ячейку В1 отобразить системное время компьютера;
• использовать свойство UserName объекта Application для вставки имени пользователя в ячейку В2;
• использовать свойство OrganizationName объекта Application для вставки названия организации в ячейку B3;
• подогнать размеры столбцов А и В так, чтобы они полностью отображали введенные данные.

Протестируйте процедуру.