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

Вопросы: l l l Понятие функции-процедуры VBA Использование функций типа UDF в рабочих листах Excel Управление передачей аргументов (по ссылке и по значению). 2

Функция-процедура – это особый вид процедуры VBA, возвращающей результат. l Функции-процедуры с некоторыми ограничениями на их действия называются определенными пользователем функциями (или сокращенно UDF – user-defined functions), и только их может использовать Excel в формуле ячейки рабочего листа. 3

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

l l Например, нельзя использовать функциюпроцедуру как определенную пользователем функцию в Excel, если она выделяет ячейки или изменяет каким-то образом текущий рабочий лист. Кроме того, UDF не может устанавливать свойства объекта или использовать методы объекта: в большинстве случаев установка свойств объекта или использование его методов приводят к изменениям в среде Excel. 5

Функция пользователя: l l l не может изменять значения других ячеек; не может изменять форматы ячеек либо присваивать форматы; может возвращать результат только в ту ячейку, в которой записана сама функция. 6

Общий синтаксис для процедурыфункции выглядит следующим образом: Function <имя_функции> ([<список_аргументов>]) [As <тип_данных>] <инструкции VBA> [<имя> = <выражение>] End Function 7

Где: Function - ключевое слово, объявляющее начало функции; <имя_функции > - имя создаваемой функции, придуманное пользователем При написании имен функций необходимо соблюдать те же правила, что и при написании имен других идентификаторов в VBA: они должны начинаться с буквы, не могут содержать пробелов или каких-либо символов арифметических, логических операторов или операторов отношения и не могут дублировать ключевые слова VBA; <список_аргументов> - список аргументов данной функции, необязательный элемент. 8

<тип_данных >– любой тип возвращаемого значения функции. Если только не определяется иначе, результат, который возвращает функция-процедура, имеет тип Variant; <инструкции> - это последовательность команд, выполняемых при нахождении значения функции. В совокупности они образуют тело функции. Необязательный элемент синтаксиса <имя> = <выражение> представляет присваивание функции, которое указывает VBA, какое значение должна возвращать функция; End Function - ключевые слова, заканчивающие функцию. 9

Особенности применения: 1. В списке аргументов, название каждого аргумента отделяется запятой. Список аргументов используется для передачи информации. Правила написания имен аргументов те же, что и для переменных, точно так же можно указывать их тип данных. Имена аргументов имеют такую же область определения, как и переменные, объявленные в функции локально, т. е. переменные-аргументы недоступны за пределами процедуры-функции. Если список аргументов пуст, тем не менее, круглые скобки следует сохранять; 10

Особенности применения: 2. 3. Необязательный оператор <имя> = <выражение> предназначен для возврата значения функции в точку вызова. Сам оператор носит название присвоение результата функции. Если он опущен, то функция не будет возвращать результат в точку вызова. Впрочем, это не совсем так: числовые функции возвращают значение нуль, строковые – строку длины нуль, в типе Variant возвращается значение Empty; инструкция As <тип данных> определяет тип возвращаемого результата. VBA следит за тем, чтобы тип результата функции был соответствующий указанному. Если она опущена, то процедура-функция возвратит результат работы в типе Variant. 11

Совместимость типов l VBA запрещает присваивание несовместимого типа результату функции в любой функции-процедуре, которая имеет объявленный тип данных для ее результата. l Если, например, вы ошибочно написали оператор присваивания функции так, что присваивается, допустим, тип Integer результату функции с объявленным типом String, то VBA отображает ошибку несовпадения типа. 12

Совместимость типов l l В случае присваивания типа данных, который не является тем же, что объявленный возвращаемый тип для функции-процедуры, но является совместимым, VBA преобразует значение типа, определенного для функции при возврате результата функции. Например, если присваивается тип Double функции, результат которой был объявлен как Long, то VBA не выдает никакой ошибки, а просто преобразует Double в тип Long (при возвращении результата функции). 13

Пример 1 Написать пользовательскую функцию для вычисления дискриминанта 14

Вызов пользовательской функции в MS EXCEL l ВставкаФункция, Категория –Определенные пользователем 15

Вызов пользовательской функции в MS EXCEL 16

Вызов пользовательской функции в MS EXCEL 17

Передача аргументов l l Существуют два способа для передачи информации в функцию-процедуру: по ссылке и по значению. По умолчанию VBA передает функции все аргументы по ссылке. При передаче данных функции по ссылке на самом деле передается только адрес памяти, который ссылается на исходные данные, определенные в списке аргументов функции во время ее вызова. Это означает, что если функция изменит значение в любом из аргументов, то исходные данные также изменяются. При передаче аргумента по значению VBA делает копию исходных данных и передает эту копию функции. Если функция изменяет значение в аргументе, передаваемом по значению, изменяется только копия данных, а исходные данные не изменяются. 18

Передача аргументов l Передача данных по ссылке позволяет изменить исходные данные, переданные функции через аргумент; а передача с помощью значения не позволяет изменять значение исходных данных. l Для того, чтобы явно определить метод передачи, используются ключевые слова: By. Val – (By value) метод по значению; By. Ref – (By reference) метод по ссылке. l l 19

Пример 2 20

Передача аргументов l l l Т. к. передача по ссылке позволяет функции изменять значение исходных данных ее аргументов, аргументы, передаваемые по ссылке, могут получить нежелательные побочные значения. В листинге, представленном выше, показано, что до применения функции Argument строковая переменная s 1 содержала текстовую строку в нижнем регистре, а после применения функции она уже содержит строку в верхнем регистре. Для предупреждения таких побочных результатов необходимо, чтобы функция работала с копией значения аргумента, а не с исходными данными. 21

Пример 3 22

Использование оператора Exit Для того чтобы функция прекратила выполнение, используется форма VBAоператора Exit. Оператор Exit имеет следующий синтаксис: Exit Function используется для окончания функции. 23

Пример 4 Напишите процедуру на языке VBA, которая вычисляет значение функции y(x) = cos(sin 2 x) + cos(sin 5 x). Вычисление cos(sin kx) оформить в виде функции. 24

Результат выполнения: 25

СПАСИБО ЗА ВНИМАНИЕ!