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

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

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

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

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

· на вкладке Разработчик кнопкой Редактор Visual Basic открывается окно VBA;

· выполняется команда Вставка / Модуль, задается имя модуля;

· с помощью команды Вид / Программа отображается окно для ввода инструкций; вводится инструкция VB для пользовательской функции;

· выполняется команда Отладка / Компилировать;

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

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

1. Пользовательские функции начинаются инструкцией Function и заканчиваются инструкцией End Function.

2. После инструкции Function следует ввести имя функции и далее, в круглых скобках, через запятую аргументы функции (разрешается использовать до 25 аргументов).

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

4. Инструкция присваивания обозначается знаком равенства.

5. Комментарий в VBA начинается с апострофа или ключевого слова Rem.

6. Последовательность If…Else…End if является управляющей конструкцией, которая служит для проверки условий и позволяет изменить поведение процедуры в зависимости от результатов проверки. Ключевые слова управляющей конструкции должны стоять в начале строки.

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

Ниже приведен пример простой пользовательской функции Скидка для вычисления торговой скидки 10%, если количество заказов на некоторый товар больше 500. С помощью встроенной функции Round результат расчета скидки округляется с точностью до 2 десятичных знаков.

Function Скидка (количество, цена)

If количество >= 500 Then

Скидка = количество * цена * 0.1

Else

Скидка = 0

End If

Скидка = Application.Round(Скидка, 2)