Что такое функция пользователя(UDF)?
Если потребовалось заиметь в Excel функцию, которой там еще нет, но она очень нужна или её применение значительно упростило бы жизнь при выполнение определенных задач, то самое время посмотреть в сторону функций пользователя.
Основные ограничения функций пользователя
Самое главное, что необходимо усвоить — это определенные ограничения, накладываемые на функцию пользователя(UDF), вызываемую с листа:
Предполагается, что Вы уже обладаете начальными навыками написания процедур в VBA и умеете создавать эти самые процедуры, хотя бы самые простые.
Однако прежде чем читать дальше советую ознакомиться так же со статьей: Что такое модуль? Какие бывают модули?
Основные моменты, которые следует помнить при создании функции пользователя:
- в отличие от процедуры ( Sub ) функция всегда начинается именно со слова Function , а не Sub ;
- в теле функции всегда должно быть присвоение ей значения, иначе функция не вернет необходимый результат;
- функция должна располагаться в стандартном модуле или в модуле книги, если Вы планируете вызывать её непосредственно с листа Excel
- функции пользователя «привязаны» к той книге, в которой созданы и по умолчанию не будут работать в других (для этого надо будет всегда указывать имя книги с функцией). Чтобы созданные функции работали удобно и без проблем в любой книге необходимо книгу с функциями сохранить как надстройку: Как создать свою надстройку?
Самая простая функция пользователя может выглядеть так:
Function ТекущаяДата() ‘присваиваем функции значение, чтобы она вернула его на лист(обязательно!) ТекущаяДата = Date ‘ТекущаяДата — имя функции и именно ему необходимо передать результат End Function
Функция будет работать отлично, даже если передать одно или два числа. Но это только в том случае, если для аргументов у нас заданы строгие типы данных — в примере это Double. Если тип не задан — получим ошибку #ЗНАЧ! (#VALUE!) :
Передав меньше аргументов в функцию =DivideFiveArgs( A1 ; A4 ) мы получим ошибку #ЗНАЧ! (#VALUE!) , которую вызовет деление на ноль внутри кода на третьем аргументе.
А передав меньше аргументов в функцию умножения =MultipleFiveArgs( A1 ; A4 ) ) — получим в качестве результата 0, т.к. на третьем аргументе умножим общую сумму на аргумент, который равен 0.
И для большего кругозора еще одна простая функция, но которая работает уже с текстом и вернет строку до первого пробела:
Text — ссылка на ячейку или непосредственно текст, первое слово из которого надо извлечь. Если переданный текст не будет содержать пробелов или это будет число — функция вернет весь текст. Если ячейка будет пустая — функция вернет пусто и не выдаст ошибки.
Эту функцию можно записать и намного короче:
Function ТекстДоПервогоПробела(Текст As String) As String ТекстДоПервогоПробела = Split(Текст, » «)(0) End Function
Function ТекстДоУказанногоСимвола(Текст As String, Optional Разделитель As String = » «) As String ТекстДоУказанногоСимвола = Split(Текст, Разделитель)(0) End Function
В данном примере если вызвать функцию так:
=ТекстДоУказанногоСимвола( A1 )
то функция будет использовать в качестве разделителя пробел( Optional Разделитель As String = » « ). Или можно задать символ разделения напрямую в функции и это может быть как пробел, так и любой другой символ:
=ТекстДоУказанногоСимвола(A1; «;» )
Но такая функция может выдать ошибку, если в качестве любого аргумента будет передана не одна единственная ячейка или значение — а диапазон ячеек( A1:A4 ) или массив( ). В этом случае внутри функции обязательно придется определять тип данных внутри ParamArray . Сделать это можно следующим образом:
Так же можно применить ParamArray , чтобы указывать «неограниченное» количество аргументов для сцепления значений из ячеек в одну строку с указанным разделителем:
Пример вызова такой функции с листа(первым обязательно передается разделитель, а далее уже что объединять — любой тип данных):
=ОбъединитьВсеСРазделителем(«; «; A1:A4 ; C1 ;»Привет»;)
Если Вы используете версию Excel 2007 и выше, то книгу необходимо будет сохранить с поддержкой макросов: Меню -Сохранить как -Книга Excel с поддержкой макросов.
Function ТекущаяДатаВремя() ТекущаяДатаВремя = Now ‘Now — возвращает текущие дату и время End Function
Function ТекущаяДатаВремя() Application.Volatile True ТекущаяДатаВремя = Now ‘Now — возвращает текущие дату и время End Function
Function ТекущаяДатаВремя(Optional ДиапазонОбновления As Range = Nothing) ТекущаяДатаВремя = Now End Function
тогда при любом изменении в ячейках аргумента ДиапазонОбновления функция будет пересчитана. При этом использовать хоть как-то сам этот аргумент внутри функции совершенно необязательно. Выглядеть запись такой функции будет так:
=ТекущаяДатаВремя( E:E )
при любом изменении в столбце E функция будет пересчитана.
Создание макросов и пользовательских функций на VBA — Блог SF Education
- в отличие от процедуры ( Sub ) функция всегда начинается именно со слова Function , а не Sub ;
- в теле функции всегда должно быть присвоение ей значения, иначе функция не вернет необходимый результат;
- функция должна располагаться в стандартном модуле или в модуле книги, если Вы планируете вызывать её непосредственно с листа Excel
- функции пользователя «привязаны» к той книге, в которой созданы и по умолчанию не будут работать в других (для этого надо будет всегда указывать имя книги с функцией). Чтобы созданные функции работали удобно и без проблем в любой книге необходимо книгу с функциями сохранить как надстройку: Как создать свою надстройку?
В следующем примере переменной день присваивается 03/17/63-: День = DateAdd («m», 34, |05/17/60#) Возвращает значение типа Variant (Date) , содержащее значение времени, соответствующее указанным часу, минуте и секунде.
