Теоретическая часть. Создание пользовательских функций

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

Лабораторная работа №2

VBA Excel

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

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

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

Чтобы создать функцию необходимо:

1. Запустить редактор Visual Basic

2. В окне Project щелкнуть правой кнопкой мыши на папке Modules

3. Из контекстного меню выбрать Insert/Module

4. В окне Code записать код функции

5. Сохранить результат

Общая форма записи:

Function имя(список формальных аргументов)

Операторы

End Function

Имя функции подчиняется тем же правилам записи имен, что и имя переменной в VBA.

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

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

Имя = выражение

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

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

Вычислить сумму налога на имущество по следующей схеме: если стоимость имущества меньше 850 минимальных зарплат (мин. зар.), то налога нет, если до 1700 мин. зар., то 5% от суммы, превышающей 850 мин. зар., иначе 10% от суммы, превышающей 850 мин. зар. (пример учебный).

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

Function nalog(cost, salary_min)

'вычисление налога на имущество

k = cost / salary_min

If k <= 850 Then

nalog = 0

ElseIf k <= 1700 Then

nalog = (cost - 850 * salary_min) * 0.05

Else

nalog = (cost - 850 * salary_min) * 0.1

End If

End Function

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

Рис. 1. Обращение к функции

Как видно на рисунке 1, аргументы функции отображаются так, как они записаны в функции. Для облегчения понимания функции вы можете давать имена переменным по-русски. Краткий поясняющий текст, который будет отображаться при вызове функции, можно записать в окне свойств функции. Для этого, находясь на листе модуля, нажмите F2, откроется окно просмотра объектов. В списке библиотек выберите «VBA Project», выделите имя функции и в контекстном меню укажите пункт «Properties». Заполните поле «Descriptions».

У многих встроенных функций Excel, кроме обязательных аргументов, которые должны указываться при каждом обращении к функции, имеются так называемые необязательные аргументы. В окне ввода аргументов функции их имена отображаются серым цветом (например, для функции ППЛАТ это аргументы БС и ТИП). Если значение такого аргумента не указывается, то в вычислениях используется значение, установленное по умолчанию.

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

В тексте функции до использования необязательного аргумента надо проверить, был ли он задан. Чтобы определить, указано значение аргумента, или нет, используется функция IsMissing(аргумент). Данная функция возвращает значение «Истина», если аргумент не указан, т.е. должно использоваться значение по умолчанию. Например, в рассмотренном примере можно ввести необязательные аргументы для значений кратности минимальным зарплатам и процентных ставок. Для примера введем необязательные аргументы для процентных ставок. По умолчанию будут использованы значения, указанные в условии задачи. Внесем необходимые изменения в код (выделены в тексте):

Function nalog(cost, salary_min, Optional rate_min, Optional rate_max)

If IsMissing(rate_min) Then rate_min = 0.05

If IsMissing(rate_max) Then rate_max = 0.1

k = cost / alary_min

If k <= 850 Then

nalog = 0

ElseIf k <= 1700 Then

nalog = (cost – 850 * salary_min) * rate_min

Else

nalog = (cost – 850 * salary_min) * rate_max

End If

End Function

Функция IsMissing предназначена только для проверки необязательных аргументов типа Variant. Для необязательных аргументов любого другого типа VBA выполняет инициализацию значений как для новых переменных и функция IsMissing всегда возвращает значение False. В этом случае необходимо выполнять непосредственную проверку значения аргумента.

Дата добавления: 2014-12-07