2 семестр / vba_2002

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

Резюме

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

Вследующем главе речь пойдет о втором типе процедур VBA — функциях.

Часть III. VisualBasicforApplications

239

Создание функций

VВА позволяет создавать два типа структ>р: процедуры Sub и процедуры функции. Процедуры Sub были описаны в предыдущей главе. В данной — рассматриваются

функции.

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

Процедуры и функции: сравнение

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

Процедуры функций универсальны и используются в двух ситуациях,

•Как часть выражения в процедуре VBA.

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

няются функции Excel и встроенные функции VBA.

Назначение пользовательских функций

Несомненно, вам знакомы функции Excel. Даже начинающие пользователи знают, как работать с самыми популярными функциями в формулах рабочего листа — СУММ, СРЗНАЧ и ЕСЛИ. Excel содержит более 300 встроенных функций, а также дополнительные функции, доступные после установки надстройки Пакет анализа. Если их вам недостаточно, то можете создать специальные функции с помощью VBA.

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

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

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

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

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

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

Простой пример функции

Чтобы сразу же перейти к делу, рассмотрим пример процедуры функции VBA.

Пользовательская функция

Ниже приведена пользовательская функция, определенная в модуле VBA. Эта функция имеет название R e v e r s e и принимает один аргумент. Функция переставляет символы аргумента в обратном порядке и возвращает результат как строковое значение.

Function Reverse(InString) As String

1 Возвращает символы аргумента в обратном порядке Dim i as Integer, StringLength as Integer Reverse = ""

StringLength = Len(InString)

For i = StringLength To 1 Step -1 Reverse = Reverse & Mid(InString, i, 1)

Next i

End Function

Как работает эта функция, рассказано далее в разделе "Анализ пользовательской функции".

При создании пользовательских функций, которые использованы в формуле рабочего листа, убедитесь, что код вводится в обычном модуле VBA. Если вы поместите пользовательские функции в модуле листа Лист или модуле Эта Кик га, они не будут выполняться в формулах

Часть III VisualBasic forApplications

241

Использование функции на рабочем листе

При вводе формулы, в которой используется функция Reverse, Excel выполняет программу для получения конечного значения. Эгу функцию можно использовать в формуле: =Reverse(A1)

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

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

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

=Reverse(Reverse(Al) )

Использование функции в процедуре VBA

Следующая процедура VBA, определенная в том же модуле, что и пользовательская функция Reverse, сначала отображает окно для ввода текста пользователем. Затем процедура использует встроенную функцию VBA МздВох для отображения данных, введенных пользователем, но уже после их обработки функцией Reverse (рис. 10.2). Первоначальные данные отображаются в заголовке окна сообщения,

Sub Reverselt{)

Dim Userlnput as String

Userlnput = InputBox("Введите текст:") MsgBox Reverse(Userlnput), , Userlnput 2nd Sub

В примере, показанном на рис. 10.2, в ответ на функцию InputBox была введена строка Профессиональное программирование на VBA в Excel. Функция MsgBox отображает текст-перевертыш.

Рис.10.1.Использование	Рис.10.2. Использованиепользова-
пользовательскойфункции	тельскойфункциивпроцедуреVBA
в формулерабочего листа

Анализ пользовательской функции

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

242	Глава10.Созданиефункций

Приведем текст функция

Function Reverse(InString) As String

' Возвращает символы аргумента в обратном порядке Dim i as Integer, StringLength as Integer Reverse = ""

StringLength = Len(InString)

For i = StringLength To 1 Step -1 Reverse = Reverse & Mid(InString, i, 1) Next i

End Function

Обратите внимание, что процедура начинается с ключевого слова Function, а не Sub, после которого указывается название функции (Reverse). Эта специальная функция использует только один аргумент (InString), заключенный в скобки. As S t r i n g , определяет тип данных значения, которое возвращает функция. (Excel использует по умолчанию тип данных Variant, если тип данных не определен).

Вторая строка— простой комментарий (необязательный), который описывает выполняемые функцией действия. После комментария приведен оператор Dim с двумя переменными (i и StringLength), применяемыми в процедуре.

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

Далее функция VBA Len определяет длину введенной строки и присваивает значение переменной StringLength .

Следующие три инструкции составляют цикл For-Next. Процедура циклически и в обратном порядке просматривает каждый символ введенного текста и создает на их основе новую строку. Обратите внимание, что значение шага Step в цикле For-Next — отрицательное число, поэтому итерации цикла выполняются в обратном порядке. Инструкция цикла использует функцию VBA Mid, которая возвращает один символ из строки, введенной пользователем. По окончании цикла переменная Reverse будет хранить все символы введенной строки, переставленные в обратном порядке. Эта строка — значение, которое возвращает функция,

Процедура заканчивается оператором End Function .

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

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

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

Процедура функции

Процедура пользовательской функции имеет много общего с процедурой. (Детально о процедурах рассказано в главе 9.)

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

243

Объявление функции

Для объявления функции используется следующий синтаксис:

[Public | Private] [Static] Function имя ([список^аргументов]) [As тип] [инструкции] [имя = выражение]

[Exit Function] [инструкции]

[имя = выражение]

End Function

Public	(Необязательное ключевое слово) указывающее, что функция доступна
	для других процедур во всех остальных модулях активных проектов VBА
Privat e	(Необязательное ключевое слово) указывающее, что функция дос-
	тупна только для других процедур в текущем модуле
Static	(Необязательное ключевое слово) указывающее, что значения пере-
	менных, объявленных в процедуре функции, сохраняются между вы-
	зовами функции
Function	(Обязательное) ключевое слово, которое обозначает начало процеду-
	ры, возвращающей значение или другие данные
имя	(Обязательное) ключевое слово, представляющее произвольное на-
	звание процедуры функции. При именовании функций используются
	те же правила, что и при задании имен переменным. По окончании
	выполнения функции результат присваивается ее названию

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

	ны в скобки. Для разделения пар аргументов используется запятая
тип	(Необязательный) тип данных, который возвращает функция
инструкции	(Необязательные) корректные инструкции VBA
Exit Function	(Необязательный) оператор, вызывающий немедленный выход из
	процедуры функции до ее завершения
End Function	(Обязательное) ключевое слово, обозначающее конец процедуры
	функции

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

Создание пользовательской функции начните с создания модуля VBA (можно также использовать существующий модуль). Введите ключевое слово Function, после которого укажите название функции и список ее аргументов (если они есть) в скобках. Вы также можете объявить тип данных значения, которое иозвращает функция, используя ключевое слово As (это не обязательно, но рекомендуется). Вставьте код VBA, выполняющий необходимые действия, и проверьте, что необходимое значение присваивается переменной соответствующей названию процедуры минимум один раз в теле процедуры функции. Функция заканчивается оператором End Function.

Имена функций подчиняются тем же правилам, что и имена переменных. Если вы плани-
руете использовать функцию в формуле рабочего листа, убедитесь, что название не имеет
форму адреса ячейки (например, в формуле не будет выполняться функция с названием J21).
244	Глава 10. Создание функций

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

Область действия функции

В главе9 была рассмотрена концепция области действия процедуры ( P u b l i c или P r i v a t e ) . Это же относится и к функциям: область действия функции определяет, может ли эта функция вызываться процедурами в других модулях или рабочих листах.

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

•Если область действия функции не задана, то по умолчанию подразумевается РиЫ 1с.

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

только в процедуре VBA, необходимо объявить ее как P r i v a t e , чтобы пользователи не пытались применять ее в формуле,

• Если в программе VBA необходимо вызвать функцию, которая определена в другой рабочей книге, задайте ссылку на другую рабочую книгу, воспользовавшись командой VBE Tools^References.

Выполнение функций

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

•В результате вызова из другой процедуры.

•При использовании в формуле рабочего листа.

Вызов из процедуры

Пользовательские функции можно вызывать из процедуры точно так же, как и встроенные функции. Например, после определения функции с названием SuinArray в код вводится оператор, показанный ниже:

T o t a l = SumArray(MyArray)

Этот оператор выполняет функцию SumArray с аргументом МуАггау, возвращает результат функции и присваивает его переменной T o t a l .

Кроме того, можно использовать метод Run объекта A p p l i c a t i o n . Например: T o t a l = A p p l i c a t i o n . R u n ("SumArray", "МуАггау")

Первый аргумент метода Run — имя функции. Дополнительные аргументы представляют аргумент (аргументы) функции. Аргументами метода Run могут быть строки (как показано выше), числа или переменные.

Использование в формуле рабочего листа

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

Вы вправе выбрать один из трех способов.
ЧастьIII. VisualBasicforApplications	245

Указать перед названием функции ссылку на файл. Например, если вы решили использовать функцию с названием CountNames, определенную в открытой рабочей книге Myf unc . x l s , то обратитесь к следующей ссылке:

=My£unc.xls!CountNames(Al:AlOOO)

Если вы вставите функцию с помощью команды Вставка1 *Функция, то ссылка на рабочую книгу будет добавлена автоматически.

Установить ссылку на рабочую книгу. Это можно сделать с помощью команды VBE Tools1 ^ References. Если функция определена в ссылаемой рабочей книге, то не следует указывать имя рабочего листа. Даже если зависимая рабочая книга определена по ссылке, то в диалоговом окне Мастер функции указывается рабочая книга, содержащая функцию (хотя это не обязательно).

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

Изобретаем колесо

Большинство встроенных функций Excel в VBA создать невозможно. Однако некоторые функции можно дублировать.

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

Function UpCase(InString As String] As String

1 Преобразовывает символы аргумента в верхний регистр

Dim StringLength As Integer Dim i As Integer

Dim ASCIIVal As Integer

Dim CharVal As Integer

StringLength = Len(InString) UpCase = InString

For i = 1 To StringLength

ASCIIVal = Asc(Mid{InString, i, 1)) CharVal = 0

If ASCIIVal >= 97 And ASCIIVal <••= 122 Then CharVal = -32

Mid(UpCase, i, 1) = Chr(ASCIIVal + CharVal) End If

Next i

End Function

Итак, мне было интересно, чем пользовательская функция отличается от встроенной функции, поэтому я создал рабочий лист, вызывающий функцию 10000 раз с аргументом длиной в 26 символов. Вычисления продолжались 13 секунд. Затем пользовательская функция была заменена на встроенную функцию Excel ПРОПИСН. Вновь проведем тест и убедимся, что вычисления выполнены меньше, чем за секунду.

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

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

246	Глава10.Созданиефункций

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

Аргументы функций

Всегда помните о следующих особенностях аргументов процедур функций.

•Аргументы могут представляться переменными (в том числе и массивами), константами, символьными данными или выражениями.

•Некоторые функции не имеют аргументов.

•Определенные функции имеют строго заданное число обязательных аргументов {от 1 до 60).

•Некоторые функции имеют как обязательные, так и необязательные аргументы.

Если формула содержит пользовательскую функцию рабочего листа и возвращает #ЗНАЧ!, то в функции есть ошибка. Ошибка может вызываться логическими ошибками в программе, передачей в функцию некорректных аргументов или выполнением ошибочного действия (например, попытка изменить формат ячейки). См. раздел "Отладка функций" далее в этой главе.

Примеры функций

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

Все примеры функций из этого раздела можно найти на Web-узле издательства.

Функция без аргументов

Как и процедуры, пользовательские функции не обязательно должны иметь аргументы. Например, в Excel есть несколько встроенных функций, не использующих аргументы, в том числе функции СЛЧИС () и СЕГОДНЯ (). Несложно создать аналогичные функции.

Ниже приведен пример простой функции, не использующей аргументов. Следующая функция возвращает свойство UserName объекта Application . Это имя отображается в диалоговом окне Excel Параметры вкладки Общие, и хранится в системном реестре Windows.

Function User()

' Возвращает имя зарегистрированного пользователя User = Application.UserName

End Function

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

=User()

Часть III. Visual Basic forApplications

247

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

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

В следующем примере вызывается функция User, и в качестве аргумента оператора MsgBox используется значение, которое она возвращает. Оператор конкатенации (&) объединяет текстовую строку с результатом функции User,

Sub ShowUser(}

MsgBox "Ваше имя — " & User О End Sub

Еще одна функция без аргументов

Функция Excel СЛЧИС () служит для быстрого заполнения диапазона ячеек значениями. Однако при этом случайные числа изменяются при каждом пересчете формул на рабочем листе. Поэтому обычно приходится преобразовывать формулы в значения с помощью команлы Правка^Специальная вставка с активным параметром Значения.

Вы можете создать пользовательскую функцию, возвращающую случайные числа, которые не будут изменяться при пересчете формул. Для этого используем встроенную функцию VBA Rnd (}, которая возвращает случайное число в диапазоне от 0 до 1. Эта функция выгля-

дит следующим образом:
Function StaticRand()
'	Возвращает		случайное число,	которое
1	не	изменяется при пересчете		формул
	StaticRand =		Rnd()
End		Function

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

=UEflOE(StaticRand() *1000)

Значения, полученные с ее помощью, никогда не изменяются, в отличие от сгенерированных встроенной функцией СЛЧИС () -

Управление пересчетом функций

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

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

A p p l i c a t i o n . V o l a t i l e True

Метод V o l a t i l e объекта Application имеет один аргумент (True или False). Если функция отмечена как v o l a t i l e (то есть изменяемая), она пересчитывается всякий раз, когда изменяется ячейка рабочего листа.

Например, пользовательскую функцию StaticRand можно изменить с помощью метода V o l a t i l e так, чтобы она стала аналогичной функции Excel СЛЧИС ().

248	Глава10.Созданиефункций