Пользовательские функции с необязательными аргументами

Некоторые    встроенные    функции    позволяют    опускать    определенные    аргументы. Например, в функции ПС (PV) необязательными являются аргументы тип и бс (будущая стоимость),  Excel  все  равно  вычислит  результат.  Этот  подход  применим  и  к  вашим собственным функциям.

Допустим, вы хотите создать простую пользовательскую функцию с именем Треугольник, которая   по   теореме   Пифагора   определяет   длину   любой   стороны   прямоугольного треугольника на основании  известных значений двух других сторон. Теорема Пифагора выражается уравнением а2 + b2 = с2, где а и b являются катетами, а с — гипотенузой. Если известны  значения  любых  двух  переменных,  то  исходя  из  данного  равенства  всегда можно определить третье неизвестное.

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

Function TpeyronbHHK(Optional short1, Optional short2, _ Optional longside) If Not (IsMissing(short1))

And Not (IsMissing(short2)) Then

Треугольник = Sqr(short1^2 + short2^2) Else If Not (IsMissing(short1))

And Not (IsMissing(longside)) Then

Треугольник = Sqr(longside^2 – short1^2)

Else

If Not  (IsMissing(short2))

And Not  (IsMissingClongside))  Then

Треугольник = Sqr(longside^2 short2^2)

Else

End If End If End If

Треугольник = "Необходимо задать два аргумента."

End Function

Первая   инструкция   содержит   имя   пользовательской   функции   и    необязательные аргументы shortl, short2 и longside. Дальше находится  ряд инструкций If, в которых с помощью   встроенной   в   VBA   функции   IsMissing  выясняется,   какая   именно   пара аргументов была задана. Отсюда узнается, какая сторона требует вычислений. Например, следующая инструкция проверяет наличие аргументов shortl и short2:

If Not   (IsMissing(short1))

And  Not   (IsMissing(short2))  Then Треугопьник  = Sqr(short1^2  +  short2^2)

Функция IsMissing возвращает значение True (истина), если аргумент  не был задан. Соответственно,   если   заранее   известны   катеты   shortl   и   short2,   Excel   извлекает квадратный корень из суммы квадратов их сторон и возвращает длину гипотенузы.

Если  задано  меньше  двух  аргументов,  то  ни  одна  из  инструкций  If  не  возвращает значение  True,  и  в  этом  случае  выполняется  инструкция,  присваивающая  результату текстовое значение:

Треугольник =  "Необходимо задать два аргумента."

Теперь  посмотрим,  как  функция  работает  на  практике.  Формула  =Треугольник(;4;5)

возвратит   значение   3   —   длину   отсутствующего   катета.   Соответственно   формула

=Треугольник(3;;5)  вернет   значение   4   —   длину   другого   отсутствующего   катета. Наконец, на выходе последней формулы  =Треугольник(3;4;) мы увидим число 5 — длину гипотенузы.

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

У  первой  проблемы  есть  простое  решение:  сразу  после  инструкции  Function ввести дополнительный блок If…End If:

If Not(IsMissing(short1)) And Not(IsMissing(sshort2)) And Not(IsMissing(longside)) Then

Triangle = "Введите ровно два аргумента!" Exit Function

End If

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

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

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

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

Пользовательская функция работоспособна только в том случае, когда  открыта рабочая книга, содержащая модуль, в котором находится эта  функция. Если рабочая книга не открыта,  при  вызове  функции  Excel  возвратит  ошибку  #ИМЯ!.  Более  того,  если  вы используете функцию в другой книге, при обращении к функции необходимо указывать также  имя  рабочей  книги,  в  которой  она  находится.  Например,  при  вызове  функции Скидка, хранимой в личной книге макросов Personals.xls, из другой книги, вы должны написать =personal.xls!Cкидка(), а не просто =Скидка().

Вместо набора с клавиатуры имен функций (что нередко приводит к ошибкам) вы можете выбирать пользовательские функции при помощи  Мастера функций. Диалоговое окно Мастера функций открывается  командой Вставка ? Функция (Insert ? Function). В раскрывающемся    списке   Категория   (Category)   выберите   пункт    Определенные пользователем  (User  Defined).   После   этого   имена   доступных   пользовательских функций отобразятся в соседнем списке.

Простейший способ сделать пользовательские функции легкодоступными —включить их в отдельную рабочую книгу и затем сохранить эту книгу как надстройку Excel (.XLA) в папке XLStart. (Папка XLStart находится  там же, где и все файлы Excel. При запуске Excel  автоматически  открываются  все  хранимые  в  ней  документы.)  Для  того  чтобы сохранить рабочую книгу в виде файла .XLА, выберите команду Файл  ? Сохранить (File ? Save) и в списке Тип файла (File Of Type) укажите тип Надстройка Microsoft Excel (Microsoft Excel Add-in).

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

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

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

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

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