2 семестр / vba_2002

Работа

с процедурами VBA

Процедура содержит группу операторов VBA, которые выполняют поставленную задачу. Большая часть кода VBA содержится в процедурах. Данная глава сосредоточена

на процедурах, которые выполняют задачи, но не возвращают дискретные значения.

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

О процедурах

Процедура — это последовательность операторов VBA, расположенная в модуле VBA, доступ к которому вы получаете с помощью VBE. Модуль может включать любое количество процедур.

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

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

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

Объявление процедуры

При объявлении процедуры с использованием ключевого слова Sub необходимо придерживаться следующего синтаксиса.

[Private | Public]{Static]	Sub имя ([список_аргументов])
[инструкции]	4
[Exit Sub]
[инструкции]

End Sub

P r i v a t e (необязательное ключевое слово) — указывает на то, что процедура доступна только для других процедур в том же модуле.

P u b l i c (необязательное ключевое слово) — указывает на то, что процедура доступна для всех остальных процедур во всех модулях рабочей книги. При использовании в модуле, содержащем оператор O p t i o n P r i v a t e Module, процедура будет не доступна за пределами проекта.

S t a t i c (необязательное ключевое слово) — указывает на то, что переменные процедуры сохраняются после окончания процедуры.

Sub (обязательное ключевое слово} — обозначает начало процедуры. имя — любое корректное название процедуры.

список_аргумэнтов — представляет заключенный в скобки список переменных, содержащих аргументы, которые передаются it процедуру. Для разделения аргументов используется запятая. Если процедура не использует аргументы, то необходимо включить в объявление процедуры пустые скобки.

инструкции (необязательные) — корректные инструкции VBA.

E x i t	Sub (необязательный оператор) — вызывает немедленный выход из процедуры до
ее формального завершения.
End	Sub (обязательный оператор) — указывает на окончание процедуры.

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

Название процедуры

Каждая процедура должна иметь название. Правила именования процедур, в основном, такие же, как при именовании переменных. В идеале название процедуры должно описывать, что выполняют операторы, содержащиеся в процедуре. Хорошее правило— использование названия, включающего глагол и существительное (например, ProcessDate — обработать данные, PrintReport - распечатать отчет, sort _ Array - сортировать массив или checkFilenaine - проверить имя файла). Избегайте незначимых названий (Dolt, Update, Fix и т.п.).

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

210	Глава 9. Работа с процедурами VBA

Область действия процедуры

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

Процедуры Public

По умолчанию все процедуры имеют область действия P u b l i c — т.е. могут вызываться другими процедурами в любом модуле рабочей книги. Ключевое слово P u b l i c использовать необязательно, но программисты часто включают его для ясности. Обе приведенные ниже процедуры имеют область действия Public .

Sub	F i r s t ( )
'	...[код процедуры]..
End Sub

Public Sub Second О

...[код процедуры]..

End Sub

Процедуры Private

Процедуры P r i v a t e могут вызываться другими процедурами в этом же модуле, но не процедурами другнх модулей.

При выборе команды Сервис^Макрос^Макросы в Excel в диалоговом окне Макрос отображаются только Public-процедуры. Следовательно, при наличии процедур, которые должны вызываться только процедурами в этом же модуле, необходимо убедиться, что процедура имеетобластьдействия Private—таким образом вы предотвратите запуск этой процедуры из диалогового окна Макрос.

В следующем примере объявляется процедура P r i v a t e с названием MySub:

Private Sub MySubO

. .. [здесь идет код].. End Sub

Вы можете назначить всем процедурам модуля область действия Private — дажетем, которыеобъявлены с использованием ключевого слова Public. Для этого включите следующий оператор перед первым оператором sub:

Option Private Module

Введя указанный оператор в модуле, вы можете опустить ключевое слово P r i - vate в объявлениях процедур.

Функция записи макросов Excel обычно создает новые процедуры с названием Макрос1, Какрос2 и т.д. Все эти процедуры имеют область действия Public, и не имеют аргументов.

Выполнение процедуры

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

•С помощью команды Rurr^Run Sub/UserForm (в VBE). Альтернатива— нажать <F5>. Excel выполняет процедуру, в которой находится курсор. Этот метод не срабатывает, если процедура имеет один или более аргументов.

Часть №. Visual Basic forApplications

211

Из диалогового окна Макрос программы Excel (которое можно открыть, выбрав команду Сервис^Макрос^Макросы). Другой способ отобразить диалоговое окно Макрос — нажать <Alt+F8>.

С помощью комбинации клавиши <Ctrl> и присвоенной процедуре клавиши (если процедуре присвоена комбинация клавиш).

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

Из другой процедуры.

С помощью кнопки на панели инструментов. Из специально разработанного меню.

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

Из окна Immediate редактора Visual Basic. Просто введите название процедуры, включите необходимые аргументы, и нажмите <Enter>.

В Excel 5 и Excel 95 можно легко связать макрос с новым элементом меню Tools (Сераис). Начиная с Excel 97, эта возможность уже не поддерживается.

Рассмотренные методы запуска процедур подробно обсуждаются в следующих разделах.

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

Выполнение процедуры с помощью команды Run^Run Sub/UserForm

Команда меню Run^Run Sub/UserForm в VBE используется преимущественно для тестирования процедуры в процессе ее разработки. Пользователь вряд ли станет активизировать редактор Visual Basic, чтобы запустить процедуру. Выберите команду Rurr^Run Sub/UserForm (или нажмите клавишу <F5>) в VBE. чтобы выполнить текущую процедуру (другими словами, процедуру, в которой расположен курсор),

Если курсор во время выбора команды Run^Run Sub/UserForm находится не в одной из процедур, то VBE отображает диалоговое окно Макрос, в котором можно выбрать процедуру для выполнения.

Выполнение процедуры в диалоговом окне Макрос

При выборе команды Сервис^Макрос^Макросы в Excel отображается диалоговое окно Макрос — рис. 9.1 (кроме того, это диалоговое окно можно открыть с помощью комбинации клавиш <Alt+F8>). В диалоговом окне Макрос перечислены все созданные вами процедуры. Используйте раскрывающийся список Находится в, чтобы фильтровать отображаемые макросы по области действия (например, показать только макросы активной рабочей книги).

212	Глава 9. Работа с процедурами VBA

В диалоговом окне Макрос не отображаются процедуры функций. В нем также не представлены процедуры, объявленные с ключевым словом P r i v a t e , процедуры, требующие аргументов или процедуры надстроек.

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

Рис. 9.1. В диалоговом окне Макрос перечисленывседоступныепроцедуры

Выполнение процедуры с помощью комбинации клавиш

Любок процедуре, не имеющей аргументов, можно присвоить комбинацию <Со-1+специать- ная клавишах Например, если вы присвоите процедуре с названием U p d a t e комбинацию клавиш <Ctrl+U>, то при нажатии <CtrI+U> она будет выполняться.

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

1.Запустите Excel и выберите команду Сервис^Макрос^Макросы.

2.Выберите необходимую процедуру из списка в диалоговом окне Макрос.

3.Щелкните на кнопке Параметры, чтобы отобразить диалоговое окно Параметры макроса, показанное на рис. 9.2.

4.Введите символ в текстовое поле Ctrl+.

Символ, который вы вводите в текстовое поле Ctrl +, чувствителен к регистру. Если вы введете s в нижнем регистре, то присваивается комбинация клавиш <Ctrl+S>. Если вы введете 5 в верхнем регистре, то присваивается комбинация клавиш <Ctrl+Shift+S>.

5.Добавьте описание (но желанию). После введения описание будет отображаться в нижней части диалогового окна Макрос при выборе процедуры из списка.

6.Щелкните на кнопке ОК, чтобы закрыть диалоговое окно Параметры макроса, затем щелкните на кнопке Закрыть, чтобы закрыть диалоговое окно Макрос.

Рис. 9.2. В диалоговом окне

Параметры макроса можноприсвоитьпроцедуреKO.U

бинациюклавиш и(необязательно)описание

Часть HI. Visual Basic forApplications

213

Если вы присваиваете процедуре одну из предопределенных комбинаций клавиш Excel, то ваша команда имеет приоритет над предопределенной комбинацией клавиш. Например, комбинация клавиш <Ctrl+S> в Excel соответствует операции сохранения рабочей книги. Но если вы присвоите эту же комбинацию процедуре, то при нажатии <Ctrl+S> активная книга больше сохраняться не будет.

ВExcel не используются следующие комбинации <GVc\+xnaeuwa>: E, J, L, M, Q и Т.

ВExcel практически не используются комбинации клавиш < С t г I+S h if t+клав иша>, поэтому свободно используйте любые комбинации, кроме F, О и Р.

Выполнение процедуры из пользовательского меню

Как описано в главе 23, Excel предоставляет два способа создания пользовательских меню: с помощью команды Вид^Панели инструментов^Настройка или в результате создания программы VBA. Предпочтительнее использовать второй метод, но для связывания макроса с новым элементом меню можно применять оба метода.

В Ехсе) 5 и Excel 95 поддерживаются редактор меню, который не используется в Excel 97 и более современных версиях программы.

Ниже приведены действия, которые следует выполнить для отображения в меню нового элемента и связывания этого элемента с макросом. Предполагается, что новый элемент добавляется в меню Данные: название нового элемента — Открыть файл заказчика, а процедура называется O p e n C u s t o m e r F i l e .

1.Выберите команду Вид^Панели инструментов^Настройка. Excel отображает диалоговое окно Настройка.

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

2.Щелкните на вкладке Команды в диалоговом окне Настройка.

3.Прокрутите вниз список Категории и выберите опцию Макросы.

4.Перетащите из списка Команды первый элемент с названием Настраиваемая команда меню в нижнюю часть меню Данные. Расположите его после элемента Обновить данные. Меню Данные отображается при щелчке на нем.

5.Щелкните правой кнопкой мыши на новом элементе меню (с названием Настраиваемая команда меню), чтобы отобразить контекстное меню.

6.Введите в текстовое поле Имя новое название элемента меню: & Открыть файл заказчика, как показано на рис. 9.3.

7.Щелкните на команде контекстного меню Назначить макрос.

8.В диалоговом окне Назначить макрос выберите процедуру O p e n C u s t o m e r F i l e из списка макросов.

9.Щелкните на кнопке ОК, чтобы закрыть диалоговое окно Назначить макрос, а затем — на кнопке Закрыть, чтобы закрыть диалоговое окно Настройка.

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

214	Глава 9. Работа с процедурами VBA

В главе 23 рассказано об использовании VBA для создания элементов меню, которыеотображаются,еслиоткрытатолькоопределеннаярабочаякнига.

Выполнение процедуры из другой процедуры

Один из самых популярных способов выполнить процедуру— вызвать ее из другой процедуры. Для этого существует три способа.

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

Ш Ввести ключевое слово C a l l , после него — название процедуры и ее аргументы (если они есть), заключенные в скобки и разделенные запятыми.

•Использовать метод Run объекта A p p l i c a t i o n . Этот метод можно применять для выполнения других процедур VBA или макросов XLM. Метод Run полезен, когда выполняется процедура, название которой присвоено переменной. Тогда переменную можно передать в метод Run как аргумент.

Вследующем примере показан первый метод. В данном случае процедура MySub выполняет некоторые операторы (не показанные в примере), запускает процедуру Update Sheet и затем выполняет остальные операторы:

Sub MySubO

. . . [ к о д ] . . .

UpdateSheet

. - . [ к о д ] . . .

End Sub

Sub UpdateSheet()

...[код]...

End Sub

Далее представлен второй метод. Ключевое слово C a l l вызывает процедуру Update, имеющую один аргумент; вызывающая процедура передает аргумент в вызываемую процедуру. Аргументы процедур рассматриваются далее в этой главе.

Часть ///. Visual Basic for Applications

2 5

Sub MySub()

MonthNum = InputBox("Введите номер месяца; ") Call UpdateSheetfMonthNum)

1 ...[код]...

End Sub

Sub UpdateSheet(MonthSeq)

...[кол]...

End Sub

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

В следующем примере используется метсд Run, выполняющий процедуру U p d a t e S h e e t и передающий MonthNum в качестве аргумента;

Sub MySub()

MonthKum = InputBox("Введите номер месяца: ") Result e Application.Run("UpdateSheet", MonthNum)

. . . [ к о д ] . . .

End Sub

Sub UpdateSheet(MonthSeq}

...[код] .. .

End Sub

Возможно, оптимальной причиной использования метода Run является присвоение названия процедуры переменной. Существует только один способ выполнить процедуру гаким образом (см. следующий пример). Процедура Main использует функцию VBA WeekDay для определения дня недели (целое число от 1 до 7, начиная с воскресенья). Переменной SubToCall присваивается строка, содержащая название процедуры. Затем метод Run вызывает соответствующую процедуру (Weekend или Daily) .

Sub Main()

Select		Case	Weekday(Now)
	Case 1: SubToCall = "Weekend"
	Case 7: SubToCall - "Weekend"
	Case	Else:	SubToCall =		"Daily'
End Select
	Application.Run SubToCall
End	Sub
Sub	Weekend()
MsgBox		"Сегодня		выходной"
1	Код,	который выполняется			в	выходной
End	Sub
Sub Daily
MsgBox		"Сегодня		будний день"
'	Код,	который		выполняется	в	будний день
End	Sub

Вызов процедуры из другого модуля

Если VBA не может найти вызываемую процедуру в текущем модуле, он ищет процедуры P u b l i c в других, модулях этого же проекта.

При вызове процедуры P r i v a t e из другой процедуры обе они должны находиться в од-

ном .модуле,
216	Глава 9. Работа с процедурами VBA

Следует отметить, что в одном модуле не может быть двух процедур с одинаковыми названиями, не допускается также использование процедуры с идентичными именами в разных модулях. Можно указать VBA выполнить неоднозначно названную процедуру, т.е. другую процедуру с тем же названием, но в другом модуле. Для этого введите перед названием процедуры название модуля и точку. Предположим, вы создали процедуры с названием MyEub в двух модулях: Modulel и Module2. Если требуется, чтобы процедура MySub в Module2 вызывала MySub из Modulel, то используйте один из следующих операторов:

Modulel.MySub

C a l l Modulel.MySub

Если вы не укажете разницу между процедурами с одинаковыми названиями, будет отображено сообщение об ошибке: Ambiguous Name D e t e c t e d (Обнаружено неоднозначное имя).

Вызов процедуры в другой рабочей книге

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

Чтобы добавить ссылку на другую рабочую книгу, выберите в VBE команду Toolso References. Будет отображено диалоговое окно References, как показано на рис. 9.4, в котором перечислены все существующие ссылки во всех открытых рабочих книгах. Выставьте флажок, соответствующий той рабочей книге, на которую вы планируете добавить ссылку, и щелкните на кнопке ОК. После установки ссылки процедуры в этой рабочей книге можно вызывать так, если бы они находились в текущей рабочей книге, к которой принадлежит вызывающая процедура.

Рис. 9.4. В диалоговом окне References можно установить ссылку на другую рабочуюкнигу

Рабочая книга, на которую ссылаются, не обязательно должна быть открыта — она рассматривается как отдельная библиотека объектов. Используйте кнопку Browse в диалоговом окне References, чтобы установить ссылку на закрытую рабочую книгу. Названия книг, которые указаны в списке ссылок, перечислены по названиям проектов VBE. По умолчанию каждый проект обычно наделен именем VBAProjecr. Следовательно, в списке может содержаться несколько одинаково названных, элементов, Чтобы отличить проект, юмешпге его название в окне Properties в VBE. Список ссылок, который отображается в диалоговом окне References, включает также библиотеки объектов и элементы управления ActiveX, зарегистрированные в вашей системе. Рабочие книги Excel 2002 всегда включают ссылки на библиотеки объектов:

•Visual Basic for Applications;

•Microsoft Excel 10.0 Object Library;

Часть III. Visual Basic for Applications

217

•OLE Automation;

•Microsoft Office 10.0 Object Library;

•Microsoft Forms 2.0 Object Library (необязательно; включается в случае, если в проекте присутствует пользовательская форма UserForm).

ВExcel 2002 любые дополнительные ссылки, которые вы добавите, включаются в структуру проекта в окне Project Explorer редактора VBE. Эти ссылки перечислены в узле с названием References,

Кпримеру, если вы установили ссылку на рабочую книгу, которая содержит процедуру YourSub, то можете использовать один из следующих операторов для вызова YourSub:

YourSub

Call YourSub

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

MyProj ееt.MyModule.MySub

Также можно использовать ключевое слово Call: Call MyProject.MyModule.MySub

Существует еще один способ вызвать процедуру в другой открытой рабочей книге — использовать метод Run объекта Application. Этот метод не требует установки ссылки. Оператор, который выполняет процедуру Consolidate, расположенную в рабочей книге

с названием budget macros . xls, представлен далее: Application.Run "'budget macros.xls'!Consolidate"

Причины вызова других процедур

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

Во-первых, это делает программу более понятной. Чем проще программа, тем легче ее анализировать и изменять. Меньшие процедуры можно быстрее понять и отладить. Рассмотрим процедуру, которая только вызывает другие процедуры и больше не выполняет никаких функций. Вы разберетесь в ней достаточно быстро:

Sub Mainf(

Call GetUserOptions Call ProcessData Call Cleanup

Call CloseltDown End Sub

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

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

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

218	Глава 9. Работа с процедурами VBA