2 семестр / vba_2002

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

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

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

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

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

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

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

4.Перетащите из списка Команды второй элемент с названием Настраиваемая кнопка на требуемую панель инструментов.

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

6.Введите в текстовое поле Имя название кнопки. Это текст "подсказки", которая появляется на экране, когда указатель мыши наведен на кнопку. Описанный шаг необязателен; если вы его пропустите, в подсказке будет отображаться Настраиваемая кнопка.

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

8.Выберите процедуру из списка макросов.

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

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

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

Пользовательские панели инструментов рассматриваются в главе 23.

Выполнение процедуры по щелчку на объекте

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

•Панель инструментов Рисование.

•Панель инструментов Формы.

•Панель инструментов Элементы управления.

Часть III. Visual Basic forApplications

219

Кроме того, макросы можно связывать с рисунками, добавляемыми на рабочий лист с помощью команды Вставка^Рисунок. Щелкните правой кнопкой на рисунке и иыполните команду Назначить макрос.

Панель инструментов Элементы управления содержит те же элементы управления ActiveX, которые применяются в пользовательской форме UserForm. Панель инструментов Формы, включенная из соображений совместимости, содержит аналогичные элементы управления (не являющиеся элементами управления ActiveX). Элементы управления на панели инструментов Формы разработаны для Ехсе! 5 и Excel 95. Однако их можно применять и в более поздних версиях (в некоторых случаях их использовать лучше). Изложенный далее материал относится к элементу управления Кнопка на панели инструментов Формы. Информацию об использовании элементов управления ActiveX на рабочих листах вы найдете в главе 13.

Чтобы связать процедуру с объектом B u t t o n (Кнопка), который находится на панели инструментов Формы, выполните следующие действия.

1.Убедитесь, что на экране отображается панель инструментов Формы.

2.Щелкните на опции Кнопка на панели инструментов Формы.

3.Перетащите элемент Кнопка на рабочий лист.

Если при перетаскивании элемента нажать <Alt>, то кнопка будет выравниваться вдоль линий сетки рабочего листа. Если нажать <Shift>, то кнопка будет иметь идеально квадратную форму.

Excel сразу же отображает окно Назначить макрос. Выберите макрос, который необходимо назначить кнопке, и щелкните на кнопке ОК.

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

Выполнение процедуры по событию

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

•Имеют специальные названия, состоящие из имени объекта, символа подчеркивания

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

•Хранятся в окне кода для определенного объекта.

Процедурам обработки событий посвящена глава 19.

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

Процедуру можно также выполнить, введя ее название в окне Immediate программы VBE. Если это окно в данный момент не отображено, нажмите <Ctrl+G>. Окно Immediate

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

выполняет операторы VBA, которые вы вводите в его области. Чтобы выполнить процедуру, достаточно ввести название процедуры в окне Immediate и нажать <Enter>.

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

Sub ChangeCase() MyString = "Это тест"

MyString = UCase(MySbring) Debug.Print MyString

End Sub

На рис. 9.5 показан результат выполнения процедуры ChangcCase в окне Immediate. Оператор Debug. P r i n t немедленно отобразит его.

Рис. 9.5. выполнениепроцедурыпривводеееназваниявокне immediate

Передача аргументов в процедуры

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

•переменная;

•константа;

•массив;

•объект.

Что касается аргументов, процедуры подобны функциям Excel в следующем:

•процедура может не принимать аргументы;

•процедура может иметь фиксированное количество аргументов;

•процедура может иметь неопределенное количество аргументов;

•не все аргументы процедуры являются обязательными:

•все аргументы могут быть необязательными.

Например, существует несколько функций Excel, не имеющих аргументов (например. СЛЧИС). Другие функции (такие, как СЧЕТЕСЛИ) принимают два аргумента. Функции еще одной группы (например, СУММ) могут иметь неограниченное число аргументов (до 30). Некоторые функции

Часть HI. VisualBasic forApplications

221

Excel имеют необязательные аргументы. Например, функция ПЛТ может иметь пять аргументов (три обязательных и два необязательных).

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

В примере, приведенном далее, отображается две процедуры. Процедура Main вызывает процедуру P r o c e s s F i l e трижды (оператор C a l l задается в цикле F o r - N e x t ) . Однако пе-

ред вызовом	P r o c e s s F i l e создается	трехэлементный массив.	Внутри цикла каждый эле-
мент массива	становится аргументом	вызываемой процедуры.	Процедура P r o c e s s F i l e

принимает один аргумент (с названием T h e F i l e ) . Обратите внимание, что аргумент указывается в скобках в операторе Sub. После выполнения P r o c e s s F i l e программа продолжается с оператора, указанного после оператора C a l l .

Sub Main

File(l) = "deptl.xls"

File(2) = "dept2.xls"

File(3) = "dept3.xls"

.For,i = 1 To 3

Call ProcessFile{File(i))

Next i

End Sub

Sub ProcessFile(TheFile)

Workbooks.Open FileName:=TheFile

...[код]...

End Sub

Конечно, вы всегда можете передавать в процедуру символы (не переменные). Например:

Sub Main

Call ProcessFile("budget.xls") End Sub

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

Следующий пример подтверждает высказанную концепцию. Аргумент процедуры P r o c e s s передается по ссылке (по умолчанию). После того, как процедура Main присваивает переменной MyValue значение 10, она вызывает процедуру P r o c e s s и передает MyValue в качестве аргумента. Процедура P r o c e s s умножает значение своего аргумента (с названием YourValue) на 10. По окончании процедуры P r o c e s s возобновляется выполнение процедуры Main, a функция MsgBox отображает MyValue : 100.

Sub Main

MyValue = 1 0

Call Process(MyValue) MsgBox MyValue

End Sub

Sub Process(YourValue)

YourValue = YourValue * 10

End Sub

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

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

передавались по значению, а не по ссылке. Для этого добавьте перед аргументом ключевое слово ByVal. Тогда вызываемая процедура будет управлять копией переданных данных, а не самими данными. В следующей процедуре, например, изменения, которые происходят с YourValue в процедуре Process, не влияют на значение переменной MyValue в процедуре Main. В результате функция MsgBox отображает 10, а не 100.

Sub Process(ByVal YourValue} YourValue = YourValue * 10

End Sub

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

Аргументы процедуры могут быть самыми разными и передаваться и по значению, и по ссылке. Все аргументы, перед которыми указано ключевое слово ByVal, передаются по значению; остальные — по ссылке.

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

Так как в предыдущих примерах не был объявлен тип данных ни для одного аргумента, то все аргументы имеют тип данных Variant. Но процедура, использующая аргументы, может определять типы данных непосредственно в списке аргументов. Ниже представлен оператор Sub для процедуры с двумя аргументами, имеющими: различные типы данных. Первый аргумент объявлен как Integer, второй — как string:

Sub Process(Iterations As Integer, TheFile As String)

При передаче аргументов в процедуру важно, чтобы данные, которые передаются в качестве аргументов, имели тип данных аргументов. Например, в предыдущем примере при вызове Process и передаче в качестве первого аргумента переменной типа String отображаетсясообшениеобошибке:ByRefargumenttypemismatch.

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

Сравнение переменных Public и передачи аргументов в процедуру

В главе 8 отмечалось, что переменная, объявленная как Public (вверху модуля), доступна для всех процедур текущего модуля. В некоторых случаях лучше обратиться к переменной Public, а не передавать переменную в качестве аргумента при вызове другой процедуры, Например, приведенная ниже процедура передает значение переменной MonthVal в процедуру ProcessMonth:

Sub	MySub()
Dim		MonthVal as Integer
1	...[код]
MonthVal = 4
Call		ProcessMonth{MonthVal)
•	...[код]
End Sub
ЧастьIII. VisualBasicforApplications			223

Альтернативный метод будет выглядеть следующим образом: Public MonthVal as Integer

Sub	MySub()
1	. . . [ к о д ]

MonthVal = 4

Call ProcessMonth

1 ...[кол]

End Sub

Во втором фрагменте по причине того, что MonthVal является переменной Public, процедура ProcessMonth имеет к ней доступ, поэтому необходимость в аргументах для этой процедуры отпадает.

Обработка ошибок

Вы, конечно же, догадываетесь, что при выполнении процедуры VBA могут происходить ошибки: синтаксические (которые необходимо исправить перед тем, как выполнять процедуру) и ошибки выполнения (они происходят в процессе выполнения процедуры). В этом разделе рассматривается вторая группа ошибок.

Чтобы процедуры обработки ошибок выполнялись, следует отключить параметр Break on All Errors. B VBE выберите Toofs^Qptions, а затем щелкните на вкладке General диалогового окна Options. Если выбран переключатель Break on All Errors, то VBA игнорирует процедуры обработки ошибок. Поэтому обычно используется параметр Break on Unhandled Errors.

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

Все коды и описание ошибок VBA приведены в приложении В.

Перехват ошибок

Чтобы указать программе, что должна произойти при возникновении ошибки, используется оператор On E r r o r . Вы вправе выбрачь один из двух вариантов.

• Проигнорировать ошибку и посолить VBA продолжить выполнение программы. После этого можно проанализировать объект E r r . чтобы узнать, какая ошибка произошла, и при необходимости принять меры по ее предотвращению.

•[Терейти к специальному разделу кода для обработки ошибок, чтобы выполнить необходимые действия. Этот раздел вводится в конце процедуры и обозначается специальной меткой.

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

On E r r o r Resume Next

224	Глава ft Работа с процедурами VBA

Некоторые ошибки несущественны, и их можно запросто игнорировать. Однако сначала следует определить, какая ошибка произошла. При возникновении ошибки можно использовать объект E r r для определения ее номера. Чтобы отобразить текст значения E r r . V a l u e {по умолчанию используется Err), обратитесь к функции Error . Например, представленный далее оператор отображает ту же информацию, что и обычное диалоговое окно со сведениями об ошибке Visual Basic (содержит номер ошибки и ее описание):

MsgBox "Ошибка" & Err & ": " & Error(Err)

На рис. 9.6 показано сообщение об ошибке VBA, а на рис. 9,7 отображена та же ошибка, что и в окне сообщения, но уже в Excel. Конечно, вы можете сделать сообщение об ошибке более значимым для конечных пользователей, используя описательный текст.

Рис. 9.6. Сообщений об ошибках, используемые в	Рис. 9.7. Для отображения кода
VBA,невсегдадружественныдляпользователя	ошибки и описания молено ис-
	пользоватьокносообщения

Ссылка на Err эквивалентна обращению к свойству Number объекта Err. Следовательно,дваприведенныхнижеоператораидентичны:

MsgBox Err MsgBox Err.Number

Оператор On E r r o r также применяется для определения места в процедуре, к которому должна перейти программа в случае ошибки. Чтобы обозначить это место, используется метка::

On Error GoTo ErrorHandler

Примеры обработки ошибок

В первом примере демонстрируется ошибка, которую можно спокойно проигнорировать. Метод S p e c i a l C e l l s выделяет ячейку, которая соответствует заданному критерию. (Действие, эквивалентное выбору команды Правка^Перейти и щелчку на кнопке Выделить, чтобы выделить, например, все ячейки, содержащие формулы.)

В приведенном далее примере метод S p e c i a l C e l l s выделяет все ячейки в текущем диапазоне, которые содержат формулу, возвращающую число. Если ни одна ячейка в диапазоне не соответствует критерию, VBA генерирует сообщение об ошибке. С помощью оператора On E r r o r Resume Next мы предотвращаем появление сообщения об ошибке:

Sub SelectFormulas()

On Error Resume Next Selection.SpecialCells{xlFormulas, xlNumbers).Select On Error GoTo 0

End Sub
Обратите внимание, что оператор On E r r o r GoTo	0 восстанавливает нормальную об-
работку ошибок перед выходом из процедуры.
Часть III. VisualBasic forApplications	225

Следующая процедура использует дополнительный оператор для определения результата: произошла ли ошибка.

Sub SelectFormulas2(} On Error Resume Next

Selection.SpecialCells{xlFormulas, xlNumbers).Select

If Err <> 0 Then MsgBox "He Оыло найдено ячеек с формулами." On Error GoTo 0

End Sub

Если значение E r r не равно нулю, то произошла ошибка, и в диалоговом окне отображается сообщение для пользователя.

Далее продемонстрируем обработку ошибки в результате перехода к метке:

Sub ErrorDemo(}

On Error GoTo Handler

Selection.Value = 123 Exit Sub

Handler:

MsgBox "Невозможно присвоить значение выделенному диапазону." End Sub

В процедуре производится попытка присвоить значение текущему выделенному объекту. Если происходит ошибка (например, не выделен диапазон ячеек или лист защищен), то опе-

ратор присвоения выдает ошибку. Оператор On	E r r o r	задаст переход к метке H a n d l e r в
случае ошибки. Обратите внимание, что перед	меткой	используется оператор E x i t Sub.

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

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

Sub CheckForFileO FileName = "BUDGET.XLS" FileExists = False

1Цикл по всем рабочим книгам For Each book In Workbooks

If UCase{book.Name) = FileName Then FileExists = True

End If Next book

1 Отображение соответствующего сообщения

If FileExists Then _

MsgBox FileName & " открыт." Else „ MsgBox FileName & " не открыт."

End Sub

В этой процедуре в цикле For Each - Next просматриваются все объекты коллекции Workbooks. Если книга открыта, переменная F i l e E x i s t s получает значение True . В результате отображается сообщение, указывающее пользователю, открыта ли рабочая книга.

Предыдущую процедуру можно переписать так, что для определения "открытости" будет использоваться метод обработки ошибок. В следующем примере оператор On E r r o r Resume Next указывает VBA игнорировать любые ошибки. В инструкции мы обратимся к рабочей книге, присвоив ей переменную объекта (с помощью ключевого слова Set) . Если рабочая книга закрыта, происходит ошибка. В структуре I f - T h e n - E l s e проверяется значение свойства E r r и отображается соответствующее сообщение:

226	Глава ft Работа с процедурами VBA

Sub CheckForFile{)

Dim x as Workbook

FileName = "BUDGET.XLS"

On Error Resume Next

Set x = Workbooks(FileName)

If Err = 0 Then

MsgBox FileNAme & " открыт."

Else

MsgBox FileNAme & " не открыт."

End If

On Error GoTo 0

End Sub

В главе 11 представлены дополнительные примеры кодов, в которых выполняется обработка ошибок.

Реальный пример

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

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

Готовое приложение можно найти на Web-узле издательства.

Цель

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

Требования к проекту

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

Список требований для разрабатываемого приложения приведен далее.

1.Приложение должно сортировать листы (т.е. рабочие листы и листы диаграмм) активной книги по названиям в возрастающем алфавитном порядке.

2.Приложение должно выполняться.

ЧастьIII. VisualBasicforApplications

227

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

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

5.В приложении не должны отображаться сообщения об ошибках VBA.

Исходные данные

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

•В Excel отсутствует команда сортировки листов. Следовательно, отпадает вариант записи макроса для упорядочивания листов в алфавитном порядке.

•Лист можно легко переместить, перетащив его за ярлык вкладки.

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

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

•Узнайте названия листов. Информацию получите с помощью VBA.

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

•Благодаря диалоговому окну Параметры макроса можно легко назначить для макроса комбинацию клавиш.

•Если макрос сохранен в личной книге макросов, он всегда доступен.

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

•Если разработать программу правильно, то VBA не будет отображать сообщения об ошибках.

Примечание: не будем принимать желаемое за действительное...

Подход

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

1.Идентифицировать активную рабочую книгу.

2.Получить список названий всех листов в рабочей книге.

3.Посчитать листы.

4.Отсортировать их (определенным образом).

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

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