Создание определенных пользователем функций для Excel

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

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

Следует помнить о том, что ни VBA, ни Excel не отображают сообщение об ошибке, если в качестве UDF используется функция, в которой нарушены правила для определенных пользователем функций. Такая функция просто не может возвращать результат. Например, при попытке вставить значение в рабочий лист Excel, используя функцию-процедуру, нарушающую правила для определенных пользователем функций, ячейка с такой функцией отображает Excel-сообщение об ошибке #VALUE!, обозначающее, что функция или формула для этой ячейки не может возвращать допустимый результат.

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

Объявление типа функции имеет еще один результат: если функция-процедура заканчивается без выполнения оператора присваивания функции, VBA возвращает строку нулевой длины для функций типа Stringи 0 –для функций-процедур, возвращающих численный тип.

Пример 39. Программа демонстрирует функцию LenTrim,модифицированную так, что она всегда возвращает значение типа Long.

Dim tStr As String

Dim test As Long

Sub stroka()

tstr = InputBox("Введите тестируемое выражение", "Запрос задачи")

test = LenTrim(tStr)

gg = MsgBox(test, vbOKOnly, "Ответ")

End Sub

Function LenTrim(tStr) As Long

'возвращает длину tStr без ведущих и хвостовых пробелов

LenTrim = Len(Trim(tStr))

End Function