Специальные процедуры и функции языка VBA

План Типы специальных процедур языка VBA и их характеристика Технология создания и использования процедур- функций Технология создания и использования процедур- подпрограмм

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

Процедура-подпрограмма Sub выполняет действие или набор действий, но не возвращает значение. Вместо того чтобы включать одну и ту же программу Visual Basic в каждую процедуру, удобно один раз создать процедуру-подпрограмму, а затем вызывать ее по мере необходимости из других процедур. Процедуры-подпрограммы задаются с помощью инструкции Sub. В общем виде инструкция Sub имеет следующий синтаксис: [Private | Public] [Static] Sub имя [(список Аргументов)] [инструкции] [Exit Sub] [инструкции] End Sub

Процедура-функция Function (часто такие процедуры называют просто функциями) возвращает значение, полученное в результате расчетов. Процедуры-функции, могут использоваться в выражениях или в аргументах инструкций. Процедуры-функции задаются с помощью инструкции Function. В общем виде инструкция Function имеет следующий синтаксис: [Public | Private] [Static] Function имя [(список Аргументов)] [As тип] [инструкции] [имя = выражение] [Exit Function] [инструкции] [имя = выражение] End Function

Синтаксис инструкций Sub или Function содержит следующие элементы: Элемент Описание Public Необязательный. Указывает, что процедура Function или Sub доступна для всех других процедур во всех модулях. Private Необязательный. Указывает, что процедура Function или Sub доступна для других процедур только того модуля, в котором она описана. Static Необязательный. Указывает, что локальные переменные процедуры Function сохраняются в промежутках времени между вызовами этой процедуры. Атрибут Static не действует на переменные, описанные вне процедуры Function или Sub, даже если они используются в этой процедуре. имя Обязательный. Имя процедуры Function или Sub, удовлетворяющее стандартным правилам именования переменных.

список Аргументов Необязательный. Список переменных, представляющий аргументы, которые передаются в процедуру Function или Sub при ее вызове. Имена переменных разделяются запятой. тип Необязательный. Тип данных значения, возвращаемого процедурой Function, поддерживаются типы Byte, Boolean, Integer, Long, Currency, Single, Double, Date, String (за исключением строк фиксированной длины), Object, Variant или любой определяемый пользователем тип. Невозможен возврат массивов любого типа, однако допускается возврат значения типа Variant, содержащего массив. Примечание: используется только процедурой Function инструкции Необязательный. Любая группа инструкций, выполняемых внутри процедуры Function или Sub. выражение Необязательный. Возвращаемое значение процедуры Function. Примечание: используется только процедурой Function.

В синтаксисе процедур Function и Sub приняты следующие соглашения: 1. Если процедуры Function и Sub, не описанные явно с помощью ключевых слов Public или Private, то по умолчанию они являются общими и могут быть вызваны из любой процедуры любого модуля. 2. Если не используется ключевое слово Static, значения локальных переменных не сохраняются между вызовами. 3. Все выполняемые команды должны содержаться в процедурах. 4. Не допускается определение процедуры Function или Sub внутри другой процедуры Function, Sub или Property. 5. Допускается использование переменных двух категорий: явно описанные внутри процедуры Function или Sub и не описанные внутри процедуры. 6. Переменные, которые явно описаны в процедуре (с помощью ключевого слова Dim или эквивалентного ему), всегда являются локальными для этой процедуры. 7. Переменные, которые используются, но явно не описаны в процедуре, также являются локальными, если они явно не описаны на более высоком уровне.

8. В процедуре допускается использование переменной, которая явно не описана в этой процедуре, но при этом может возникать конфликт имен, если на уровне модуля был определен элемент с тем же самым именем. 9. Если в процедуре содержится ссылка на неописанную переменную, имя которой совпадает с именем другой процедуры, константы или переменной, то подразумевается, что процедура ссылается на это имя на уровне модуля. Чтобы избежать возникновения конфликтов такого рода, следует пользоваться явным описанием переменных. Для включения обязательного явного описания переменных используется инструкция Option Explicit. 10. Инструкция Exit Function приводит к немедленному выходу из процедуры Function. Выполнение программы продолжается с инструкции, следующей за инструкцией, в которой была вызвана процедура Function. В любом месте процедуры Function возможно наличие любого числа инструкций Exit Function. 11. Инструкция Exit Sub приводит к немедленному выходу из процедуры Sub. Выполнение программы продолжается с инструкции, следующей за инструкцией, содержащей вызов процедуры Sub. В любом месте процедуры Sub допускается наличие любого числа инструкций Exit Sub.

Отличие процедур Sub и Function: И процедура Sub, и процедура Function является самостоятельной процедурой, которая может получать аргументы, выполнять последовательность инструкций и изменять значения своих аргументов. Основное отличие состоит в том, что процедура Function может применяться в правой части выражения, как и любая другая встроенная функция, например, Sqr, Cos или Chr, а процедура Sub не может применяться в выражении.

Вызов процедур Sub и Function: Процедура Function вызывается в выражении по своему имени, за которым следует список аргументов в скобках. Для возврата значения из функции следует присвоить значение имени функции. Любое число таких инструкций присвоения может находиться в любом месте процедуры. Если имени процедуры не присваивается никакого значения, процедура возвращает значение по умолчанию: числовая функция возвращает нулевое значение, строковая функция возвращает значение пустой строки (""), а функция типа Variant возвращает значение Empty. Процедура Sub вызывается по своему имени, за которым следует список аргументов без скобках. Чтобы вызвать процедуру Sub из другой процедуры, следует указать имя этой процедуры и значения для всех требуемых аргументов. Может быть использована инструкция Call, но не обязательно, однако если она все же используется, аргументы должны быть заключены в скобки.

Правила использования процедур Sub или Function: для передачи необходимых данных в эти процедуры используются аргументы, которые должны быть описаны при создании процедуры Function или Sub. Аргумент - это константа, переменная или выражение, передаваемое в процедуру и служащие источником данных. при вызове процедуры Function или Sub, имеющей аргументы, в вызывающей процедуре необходимо указать значения аргументов. В инструкциях Sub или Function аргумент список Аргументов имеет следующий синтаксис и элементы: [Optional] [ByVal | ByRef] [ParamArray] имя Переменной[( )] [As тип] [= по Умолчанию]

Элемент Описание Optional Необязательный. Указывает, что этот аргумент необязателен. При использовании этого элемента все последующие аргументы, которые содержит список Аргументов, также должны быть необязательными и быть описаны с помощью ключевого слова Optional. Не допускается использование ключевого слова Optional для любого из аргументов, если используется ключевое слово ParamArray. ByVal Необязательный. Указывает, что этот аргумент передается по значению. ByRef Необязательный. Указывает, что этот аргумент передается по ссылке. Описание ByRef используется в Visual Basic по умолчанию. ParamArray Необязательный. Используется только в качестве последнего элемента в списке список Аргументов для указания, что конечным аргументом является описанный как Optional массив типа Variant. Ключевое слово ParamArray позволяет задавать произвольное количество аргументов. Оно не может быть использовано со словами ByVal, ByRef или Optional.

имя Переменной Обязательный. Имя переменной, удовлетворяющее стандартным правилам именования переменных. тип Необязательный. Тип данных аргумента, передаваемого в процедуру; допускаются типы Byte, Boolean, Integer, Long, Currency, Single, Double, Date, String (только троки переменной длины), Object, Variant. Если отсутствует ключевое слово Optional, допускается указание определяемого пользователем типа или объектного типа. по УмолчаниюНеобязательный. Любая константа или выражение, дающее константу. Используется только вместе с параметром Optional. Если указан тип Object, единственным значением по умолчанию может быть значение Nothing.

2. Технология создания и использования процедур- функций Исходя из синтаксиса, процедура Function представляет собой последовательность инструкций языка Visual Basic, ограниченных инструкциями Function и End Function. Процедура Function может получать аргументы, например, такие как константы, переменные, или выражения, передаваемые ей вызывающей процедурой. Если процедура Function не имеет аргументов, ее инструкция Function должна содержать пустые скобки. Возврат значения осуществляется путем его присвоения имени функции в одной или нескольких инструкциях процедуры.

Для создания процедуры-функции необходимо: 1. В окне модуля объявить функцию, введя инструкцию Function. 2. Задать имя функции и вслед за именем функции в круглых скобках объявить аргументы функции. Например, инструкция описания функции SumArray определяет аргумент k целого типа и возвращает значение типа Double : Function SumArray(k As Integer) As Double 3. Вслед за этой инструкцией ввести программный код на языке VBA, выполняющий операции, которые должна выполнять функция. 4. Завершить программный код процедуры инструкцией End Function.

Пример 1: необходимо создать процедуру-функцию с именем Celsius, которая пересчитывает градусы Фаренгейта в градусы Цельсия. Процедура-функция будет иметь следующий вид: Function Celsius(fDegrees) Celsius = (fDegrees - 32) * 5 / 9 End Function Тогда процедура, из которой вызывается функция, может быть, например, такой: Sub Main() Dim temp temp = InputBox("Введите температуру в градусах Фаренгейта.") MsgBox "Температура равна: " & Celsius(temp) & "градусов Цельсия End Sub В этом примере, когда функция вызывается процедурой Main, переменная temp, содержащая значение аргумента, передается функции в переменную fDegrees. Результат вычислений возвращается вызывающей процедуре и выводится в окно сообщения.

Пример 2: создать процедуру-функцию, которая определяет возможность извлечения квадратного корня числа, определяет квадратный корень и возвращает полученное значение в вызывающую процедуру. Процедура-функция будет иметь следующий вид: Function CalculateSquareRoot(NumberArg As Double) As Double If NumberArg < 0 Then ' Анализирует аргумент. Exit Function ' Возврат в вызывающую процедуру ' если число отрицательное. Else CalculateSquareRoot = Sqr(NumberArg) ' Возвращает квадратный ' корень. End If End Function

Тогда процедура, из которой вызывается функция, может быть, например, такой: Sub Main1() Dim KvKoren As Double KvKoren = InputBox("Введите число для извлечения квадр. _ корня") MsgBox "Квадратный корень числа " & KvKoren & "равен: " & _ CalculateSquareRoot(KvKoren) End Sub

Передача аргументов в процедуру-функцию Аргументы в процедуру-функцию могут передаваться по ссылке (ByRef) или по значению (ByVal). По умолчанию аргументы передаются в процедуры по ссылке. Изменения аргумента в процедуре распространяются на значение исходной переменной. Передача аргумента по ссылке является более эффективной, так как все аргументы, передаваемые по ссылке, требуют одинакового времени для передачи и одинакового количества памяти (4 байта) для каждой процедуры, независимо от типа данных аргумента. Если включить ключевое слово ByVal в описание процедуры, то аргумент будет передан по значению. При передаче аргумента по значению создается копия исходной переменной. Изменения аргумента в процедуре не распространяются на значение исходной переменной. Аргументы, переданные по значению, занимают от 2 до 16 байт на процедуру, в зависимости от типа данных аргументов. Типы данных большего размера требуют на передачу немного больше времени. По этой причине обычно не следует передавать по значению типы данных String и Variant.

Пример 3: создать процедуру-функцию для расчета факториала числа, в которую число передается по значению. Процедура-функция будет иметь следующий вид: Function Factorial (ByVal MyVar As Integer)' Описание функции. MyVar = MyVar - 1 If MyVar = 0 Then Factorial = 1 Exit Function End If Factorial = Factorial(MyVar) * (MyVar + 1) End Function

Тогда процедура, из которой вызывается функция, может быть, например, такой: Sub Main2() Dim S As Double S = InputBox("Введите число для определения факториала") MsgBox "Факториал числа " & S & "равен: " & Factorial(S) End Sub В этом случае в окне сообщений будет выведено число, например, 120 (факториал числа 5). Без ключевого слова ByVal в описании функции инструкция MsgBox выводит на экран 1 и 0. Причина заключается в том, что MyVar ссылается затем на переменную S, которая уменьшается на 1 до тех пор, пока не станет равной 0. Примечание: поскольку ByVal создает копию аргумента, она позволяет передавать в функцию Factorial значение типа Variant. Невозможна передача по ссылке значений типа Variant если процедура, описывающая аргумент, использует другой тип данных.

3. Технология создания и использования процедур- подпрограмм Процедура Sub - это процедура, выполняющая операции. В отличие от процедуры-функции Function, процедура-подпрограмма Sub не возвращает значение. Описание процедуры Sub начинается с зарезервированного слова Sub и завершается инструкцией End Sub. Для создания специальной процедуры Sub необходимо выполнить следующие действия: 1. В окне модуля объявить функцию, введя инструкцию Sub. 2. Задать имя процедуры и вслед за именем процедуры в круглых скобках объявить аргументы процедуры. Например, следующая инструкция описания процедуры Sub с именем ShowEvent указывает строковый аргумент EventName: Sub ShowEvent(EventName As String) 3. Вслед за этой инструкцией ввести программный код на языке VBA, выполняющий операции, которые должна выполнять процедура. 4. Завершить программный код процедурой End Sub

Пример 1: создать процедуру расчета площади треугольника и вывода результата расчета на печать. Процедура-подпрограмма будет иметь следующий вид: Sub ComputeArea(Length, TheWidth) Dim Area As Double' Объявляет локальную переменную. If Length = 0 Or TheWidth = 0 Then ' Если хотя бы один из аргументов равен 0 Exit Sub' Немедленно завершает процедуру Sub End If Area = Length * TheWidth' Вычисляет площадь прямоугольника. Debug.Print Area' Выводит полученное значение в окно отладки. End Sub Тогда процедура, которая вызывает эту подпрограмму может быть, например, такой: Sub MainPodpr() L=InputBox("Введите высоту треугольника") T=InputBox("Введите длину основания треугольника") ComputeArea L,T End Sub

Пример 2: вызов и выполнение нескольких процедур В этом примере процедура Sub Main вызывает процедуру Sub MultiBeep, передавая значение 56 для ее аргумента. По окончании работы MultiBeep управление возвращается к Main, и Main вызывает процедуру Sub Message. Message показывает окно сообщения, когда пользователь выбирает мышью OK, управление возвращается к Main, и Main завершается. Sub MainPros2() MultiBeep 56 Message End Sub Sub MultiBeep(numbeeps) For counter = 1 To numbeeps Beep Next counter End Sub Sub Message() MsgBox "Пора сделать перерыв!" End Sub

Пример 3: Вызов процедуры Sub с несколькими аргументами. Процедура должна определить приемлема ли цена дома для покупки. В качестве исходных значений вводится договорная цена и государственная оценка дома. Sub Main() HouseCalc 99800, Call HouseCalc(380950, 49500) End Sub Sub HouseCalc(price As Single, wage As Single) If 2.5 * wage <= 0.8 * price Then MsgBox "Этот дом слишком дорогой." Else MsgBox "Цена этого дома приемлема." End If End Sub В этом примере показывает два способа вызова процедуры Sub с несколькими аргументами. Когда процедура HouseCalc вызывается во второй раз, аргументы заключаются в скобки, поскольку используется инструкция Call.