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

•    Применение пользовательских функций

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

•    Ключевые слова VBA в пользовательских функциях

•    Документирование макросов и пользовательских функций

•    с необязательными аргументами

•    Доступ к пользовательским функциям*

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

Пользовательские  функции,  как  и  макросы,  создаются  при  помощи  языка  программирования Visual Basic for Applications (VBA). Однако между первыми и вторыми имеется два    важных    различия.    Во-первых,    это    разные    типы    процедур.    Процедуры    в пользовательских    функциях   называются   процедурами-функциями.   Они   начинаются инструкцией Function (вместо Sub), а заканчиваются инструкцией End Function (вместо End Sub).  Во-вторых,  пользовательские  функции  вместо  воспроизведения  каких-либо действий выполняют вычисления. В них вы не встретите инструкций по выделению или форматированию    ячеек.    В    этой    главе    вы    научитесь    создавать    и    применять пользовательские функции.

Предположим, что фирма предлагает торговую скидку 10%, если  заказано больше 100 саженцев одного сорта. Лист на рис. 32.1 представляет собой бланк заказа, в котором для каждого сорта указываются количество, цена, скидка и окончательная стоимость.

Рис. 32.1. В столбце F мы собираемся вычислить скидку для каждого  заказанного сорта саженцев

Чтобы  создать  пользовательскую  функцию,  в  данном  случае  для  вычисления  скидки,

выполните следующие действия:

1.   Нажмите клавиши Alt+F11, чтобы запустить редактор VBA, и затем в меню редактора выберите  команду  Insert  ?  Module  (Вставка  ?  Модуль).  Excel  откроет  пустой модуль, как показано на рис. 32.2.

2.   Введите   в   окно   редактора   код,   приведенный   ниже.   Для   получения   отступов используйте клавишу Tab.

Function Скидка(количество, цена) If количество >= 100 Then

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

Else

Скидка = 0

End If

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

Если вы сделали отступ, редактор автоматически расположит  следующую строку с таким же отступом. Для того чтобы вернуть текст влево, нажмите клавиши Shift+Tab.

Рис. 32.2. Команда Insert ? Module добавляет модуль в текущую книгу

Применение пользовательских функций

Теперь вы вправе использовать новую функцию Скидка. Переключитесь на рабочий лист, представленный ранее на рис. 32.1, с помощью клавиш Alt+F11. Выделите ячейку F9 и введите выражение =Скидка(С9;D9).  Excel вычислит и возвратит значение скидки для заданных аргументов, а именно $950,00.

Поскольку при создании функции вы указали в первой строке ее кода, что функция будет иметь два аргумента — Количество и Цена, при вызове вам необходимо задавать оба этих аргумента.  Так,  в  описанном  примере  в   качестве  аргумента  Количество  выступает содержимое ячейки С9, а в  качестве аргумента Цена — значение ячейки D9. Теперь.вы можете    скопировать    формулу    из    ячейки    F9    в    диапазон    F10:F15.    Результат продемонстрирован на рис. 32.3.

Рис.  32.3.  На  этом  рабочем  листе  показаны  значения,  возвращаемые  пользовательской функцией Скидка

Как работает пользовательская функция

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

В следующем фрагменте текста функции инструкция If выполняет  проверку значения аргумента количество:

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

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

Else

Скидка = 0

End If

Если число заказанных саженцев больше или равно 100, Excel выполняет инструкцию

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

Результат вычисления сохраняется в переменной Скидка. Инструкция VBA, сохраняющая значение   в   некоторой   переменной,   часто    называется   инструкцией   присваивания, поскольку   она   вычисляет   выражение   справа   от   знака   равенства   и   присваивает вычисленное  значение переменной, находящейся слева от знака равенства. Так как  имя Скидка совпадает с именем самой функции, значение данной переменной возвращается формулой, вызвавшей функцию.

Далее, если количество заказанных саженцев оказывается меньше 100,  Excel выполняет инструкцию, присваивающую переменной Скидка значение 0:

Скидка  = 0

Наконец, следующая инструкция округляет значение скидки до двух десятичных знаков:

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

Обратите  внимание,  VBA,  в  отличие  от  Excel,  не  имеет  функции  ОКРУГЛ (Round). Следовательно, чтобы здесь использовать  округление,  вы должны дать указание VBA применить метод (функцию) Round объекта Application (то есть Excel), добавив ключевое слово Application, перед именем Round. Такой синтаксис имеет силу для доступа к любой функции Excel из модуля VBA.

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

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

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

Ключевые слова VBA

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

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

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

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

Документирование макросов и пользовательских функций

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

Рис. 32.4. Текст функции Скидка с комментариями

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

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

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

Источник: Эффективная работа: Microsoft Office Excel 2003 / М. Додж, К. Стинсон — СПб.: Питер, 2005. — 1088 с: ил.

Похожие посты:

Вы можете оставить комментарий, или ссылку на Ваш сайт.

Оставить комментарий