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

Функция пользователя(UDF) - или в дословном переводе Функция, Определенная Пользователем, т.к. в оригинале она звучит как: User Defined Function. Так же их называют пользовательские функции.
Такие функции вызываются через Мастер функций -категория Определенные пользователем (User Defined):

Так что же это за функции такие? Функция пользователя это функция, написанная при помощи языка Visual Basic for Application (VBA) и вызываемая как любая другая функция с листа. Но т.к. эти функции пишутся самостоятельно - можно создать любую функцию, которая будет делать то, что ни одна стандартная функция делать не умеет. Естественно, теперь возникает вопрос как написать такую функцию. Для написания UDF понадобятся хотя бы базовые знания языка VBA. Я в статье опишу лишь принципы создания таких функций и после прочтения вы сможете создать простейшую функцию. Но это никак не означает, что я научу создавать функции на все случаи жизни, ибо это сводится к обучению самому языку программирования. В статье же рассмотрим основные принципы создания, некоторые нюансы и как уже написанные функции использовать в своей книге.


 
Основные ограничения функций пользователя

Самое главное, что необходимо усвоить - это определенные ограничения, накладываемые на функцию пользователя(UDF), вызываемую с листа:
  1. UDF не может изменять значения других ячеек (с небольшими недокументированными исключениями). Т.е. фактически UDF может возвращать значение только в ту ячейку, в которой записана - это ограничение Excel и все обходные методы использовать не рекомендуются во избежание непредвиденных результатов.
  2. UDF не может изменять форматы ячеек либо присваивать форматы (с небольшими недокументированными исключениями)
  3. UDF не может изменять так называемые объекты окружения самого Excel. Например, сменить стиль ссылок или параметры вычислений формул, вид курсора и т.п.
  4. UDF будет некорректно работать с такими методами как FindNext, SpecialCells, CurrentRegion, CurrentArray, Select, ShowPrecedents и ShowDependents(выделение зависимостей ячеек), Application.GoTo и т.п. Хотя методы вроде Range.End(xlUp), Range.End(xlDown), обычный Find(без FindNext) проблем не вызывают.Подробнее про работу этих методов из UDF можно узнать из статьи: Глюк работы в UDF методов SpecialCells и FindNext
  5. для работы функции пользователя(UDF) обязательно должны быть разрешены макросы


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

Предполагается, что Вы уже обладаете начальными навыками написания процедур в VBA и умеете создавать эти самые процедуры, хотя бы самые простые.
Т.к. функции пользователя создаются в редакторе VBA, то необходимо сначала перейти в редактор: сочетанием клавиш Alt+F11 или через вкладку Разработчик(Developer) -Visual Basic.
Однако прежде чем читать дальше советую ознакомиться так же со статьей: Что такое модуль? Какие бывают модули?

Основные моменты, которые следует помнить при создании функции пользователя:
  • в отличие от процедуры(Sub) функция всегда начинается именно со слова Function, а не Sub;
  • в теле функции всегда должно быть присвоение ей значения, иначе функция не вернет необходимый результат;
  • функция должна располагаться в стандартном модуле или в модуле книги, если Вы планируете вызывать её непосредственно с листа Excel
  • функции пользователя "привязаны" к той книге, в которой созданы и по умолчанию не будут работать в других (для этого надо будет всегда указывать имя книги с функцией). Чтобы созданные функции работали удобно и без проблем в любой книге необходимо книгу с функциями сохранить как надстройку: Как создать свою надстройку?

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

Function ТекущаяДата()
    'присваиваем функции значение, чтобы она вернула его на лист(обязательно!)
    ТекущаяДата = Date 'ТекущаяДата - имя функции и именно ему необходимо передать результат
End Function

Эта функция делает одно - возвращает в ячейку, в которую записана, текущую дату. В ячейке листа Excel эта функция будет выглядеть так:
=ТекущаяДата()
Особое внимание обращаю на то, что в конце функции(если точнее после того как все необходимые вычисления сделаны) обязательно нужно сделать присвоение значения:
ТекущаяДата = Date
Иначе функции вернет 0 или пустую ячейку. Т.е. надо записать имя самой функции(ТекущаяДата) и после знака равно указать то значение, которое необходимо записать в ячейку с функцией в качестве результата.
Если кто-то уже работал с другими языками программирования(вроде С), то это аналог return
К записи пользовательских функций в ячейку предъявляются такие же требования, как и к встроенным функциям. Это касается так же и скобок на конце функции, у которой нет аргументов. И так же это означает, что в функцию могут быть переданы наши собственные аргументы


 
Аргументы функции пользователя

Function MySum(vArg1 As Double, vArg2 As Double)
    Dim dblSum as Double
    'получаем сумму двух аргументов
    dblSum = vArg1 + vArg2
    'присваиваем функции значение, чтобы она вернула его на лист(обязательно!)
    MySum = dblSum 'MySum имя функции и именно ему необходимо передать результат
End Function

В приведенном выше коде я упростил стандартную функцию СУММ(SUM) до двух аргументов. Записанная на лист функция будет иметь такой вид:
=Mysum(A1;A2)
где:
A1 - первый аргумент(vArg1), ссылка на ячейку или число
A2 - второй аргумент(vArg2), ссылка на ячейку или число
Функция вернет #ЗНАЧ!(#VALUE!), если в качестве одного из аргументов передано не числовое значение.


 
Необязательные аргументы функции пользователя
Однако иногда бывает неизвестно, сколько аргументов будет передано в функцию: 1, 2 или 10. Для этого можно использовать ключевой параметр Optional перед аргументом, который укажет функции, что этот аргумент является не обязательным, т.е. указывать его в функции при вызове этой функции не обязательно. На примере приведенной выше функции мы можем сделать обязательным только один параметр, а еще 4 необязательными:

Function SumFiveArgs(arg1 As Double, Optional arg2 As Double, Optional arg3 As Double, Optional arg4 As Double, Optional arg5 As Double)
    Dim dblSum As Double
    dblSum = arg1
    dblSum = dblSum + arg2
    dblSum = dblSum + arg3
    dblSum = dblSum + arg4
    dblSum = dblSum + arg5
    SumFiveArgs = dblSum
End Function

Функция будет работать отлично, даже если передать одно или два числа. Но это только в том случае, если для аргументов у нас заданы строгие типы данных - в примере это Double. Если тип не задан - получим ошибку #ЗНАЧ! (#VALUE!):

Function SumFiveArgs(arg1 As Double, Optional arg2, Optional arg3, Optional arg4, Optional arg5)
    Dim dblSum As Double
    dblSum = arg1
    dblSum = dblSum + arg2
    dblSum = dblSum + arg3
    dblSum = dblSum + arg4
    dblSum = dblSum + arg5
    SumFiveArgs = dblSum
End Function

Можно, конечно, всегда задавать тип данных, как в первом примере. Но стоит учитывать, что для числовых типов данных(Double, Integer, Long) значение по умолчанию будет всегда 0, даже если мы аргумент не передали в функцию(для типа String значение по умолчанию нулевая строка - ""). Это нам не мешает произвести операцию сложения и вычитания. Но операция внутри функции может быть умножением или делением и в этом случае мы получим ошибку или неверный результат:

'функция деления аргументов между собой
Function DivideFiveArgs(arg1 As Double, Optional arg2 As Double, Optional arg3 As Double, Optional arg4 As Double, Optional arg5 As Double)
    Dim dblSum As Double
    dblSum = arg1
    dblSum = dblSum / arg2
    dblSum = dblSum / arg3 'уже здесь получим ошибку "на ноль делить нельзя"
    dblSum = dblSum / arg4
    dblSum = dblSum / arg5
    DivideFiveArgs = dblSum
End Function
'функция перемножения аргументов между собой
Function MultipleFiveArgs(arg1 As Double, Optional arg2 As Double, Optional arg3 As Double, Optional arg4 As Double, Optional arg5 As Double)
    Dim dblSum As Double
    dblSum = arg1
    dblSum = dblSum * arg2
    dblSum = dblSum * arg3 'здесь arg3 равен нулю, значит далее сумма будет тоже равна нулю
    dblSum = dblSum * arg4
    dblSum = dblSum * arg5
    MultipleFiveArgs = dblSum
End Function

Передав меньше аргументов в функцию =DivideFiveArgs(A1;A4) мы получим ошибку #ЗНАЧ!(#VALUE!), которую вызовет деление на ноль внутри кода на третьем аргументе.
А передав меньше аргументов в функцию умножения =MultipleFiveArgs(A1;A4) - получим в качестве результата 0, т.к. на третьем аргументе умножим общую сумму на аргумент, который равен 0.

Проверять каждый аргумент на равенство нулю(If arg2 = 0 Then) тоже будет неверно - вдруг какой-либо реально переданный аргумент будет действительно равен 0? Будет неверный результат функции. Поэтому, чтобы функции выше заработали правильно - нужна проверка на отсутствие в аргументе значения.
Тут надо знать, что если тип аргумента не указан и сам аргумент в функцию не был передан - то ему назначается особый тип - Missing. Который и дает понять, что аргумент просто не передавался в функцию(Missing в переводе можно представить как "пропущен"). И в VBA для таких случаев есть специальная функция - IsMissing. Тогда можно более гибко манипулировать аргументами(на примере функции с умножением):

Function MultipleFiveArgs(arg1 As Double, Optional arg2, Optional arg3, Optional arg4, Optional arg5)
    Dim dblSum As Double
    dblSum = arg1
    'проверяем, что аргумент передан(NOT IsMISSING)
    If Not IsMissing(arg2) Then
        dblSum = dblSum * arg2
    End If
    If Not IsMissing(arg3) Then
        dblSum = dblSum * arg3
    End If
    If Not IsMissing(arg4) Then
        dblSum = dblSum * arg4
    End If
    If Not IsMissing(arg5) Then
        dblSum = dblSum * arg5
    End If
    MultipleFiveArgs = dblSum
End Function

Как видно - теперь Optional можно использовать вполне эффективно. Но надо помнить одно правило: аргументы, заданные в функции с ключевым Optional должны быть заданы самими последними. Т.е. после них не может идти никаких других обязательных аргументов(без ключевого Optional). Впрочем, в этом случае VBA сообщит нам об этом ошибкой "Expected: Optional", что означает: Ожидался не обязательный аргумент.

 
И для большего кругозора еще одна простая функция, но которая работает уже с текстом и вернет строку до первого пробела:

Function ТекстДоПервогоПробела(Текст As String) As String
    Dim i As Long
    Dim Result As String 'переменная для результата
    i = InStr(1, Текст, " ", 1) 'ищем позицию первого пробела в переданном тексте
    'если пробел есть и он не первый символ в строке
    If i > 1 Then
        Result = Mid(Текст, 1, i - 1) 'получаем текст до первого пробела
    Else
    'если пробела нет - возвращаем всю строку
        Result = Текст
    End If
    'присваиваем результат функции для возврата его на лист
    ТекстДоПервогоПробела = Result
End Function

Text - ссылка на ячейку или непосредственно текст, первое слово из которого надо извлечь. Если переданный текст не будет содержать пробелов или это будет число - функция вернет весь текст. Если ячейка будет пустая - функция вернет пусто и не выдаст ошибки.
Эту функцию можно записать и намного короче:

Function ТекстДоПервогоПробела(Текст As String) As String
    ТекстДоПервогоПробела = Split(Текст, " ")(0)
End Function

Но в таком виде функция вернет значение ошибки #ЗНАЧ!(#VALUE!), если ячейка с текстом будет пустой. Вдаваться в подробности не буду. Могу лишь написать, что функция VBA Split разбивает указанный текст на отдельные части, используя для разбиения указанный разделитель. И создает из разбитых частей одномерный массив с нижней границей, равной нулю. А функция выше просто возвращает первый элемент этого массива.
Обе функции можно дополнить не обязательным аргументом - разделитель слов. И сделать его по умолчанию пробелом. Значение по умолчанию в данном случае задается сразу при объявлении аргумента. Выглядеть это будет так:

Function ТекстДоУказанногоСимвола(Текст As String, Optional Разделитель As String = " ") As String
    ТекстДоУказанногоСимвола = Split(Текст, Разделитель)(0)
End Function

В данном примере если вызвать функцию так:
=ТекстДоУказанногоСимвола(A1)
то функция будет использовать в качестве разделителя пробел(Optional Разделитель As String = " "). Или можно задать символ разделения напрямую в функции и это может быть как пробел, так и любой другой символ:
=ТекстДоУказанногоСимвола(A1;";")


 
Динамическое количество аргументов в функции пользователя(ParamArray)
Сразу после некоторого использования Optional напрашивается вопрос: а если заранее неизвестно сколько аргументов будет передано? Может их будет передано 50? Или 70? Что, все перечислять? В принципе, можно сделать и так. Но можно и иначе. В VBA предусмотрен очень интересный тип данных - ParamArray. Он представляет собой динамический массив, размер которого зависит от количества переданных аргументов. На примере суммирования данных функция будет выглядеть следующим образом:

Function SumMultiple(ParamArray args())
    Dim dblSum As Double, arg
    On Error Resume Next
    For Each arg In args
        dblSum = dblSum + arg
    Next
    SumMultiple = dblSum
End Function

Но такая функция может выдать ошибку, если в качестве любого аргумента будет передана не одна единственная ячейка или значение - а диапазон ячеек(A1:A4) или массив({10;20;30}). В этом случае внутри функции обязательно придется определять тип данных внутри ParamArray. Сделать это можно следующим образом:

Function SumMultiple_DiffTypes(ParamArray args())
    Dim dblSum As Double, arg, rc As Range, x
    On Error Resume Next
    For Each arg In args
        Select Case TypeName(arg)
        Case "Range"                     'это диапазон
            'цикл по всем ячейкам
            For Each rc In arg.Cells
                'проверяем, что в ячейке числовой тип данных
                If IsNumeric(rc.Value) Then
                    dblSum = dblSum + rc.Value
                End If
            Next
        Case "Variant()"                 'это произвольный массив({10;20;30})
            'цикл по всем ячейкам
            For Each x In arg
                'проверяем, что это числовой тип данных
                If IsNumeric(x) Then
                    dblSum = dblSum + x
                End If
            Next
        Case "Double", "Long", "Integer" 'это любой числовой тип
            'суммируем
            dblSum = dblSum + arg
        'все остальные типы игнорируем
        End Select
    Next
    SumMultiple_DiffTypes = dblSum
End Function

И в такую функцию может быть передан любой из наиболее распространенных типов данных:
=SumMultiple_DiffTypes({10;20;30};A1:A4;10;C1)
Но и у ParamArray есть недостаток: он не может использоваться одновременно с необязательными аргументами(Optional). Вместе с ParamArray могут быть использованы только обязательные аргументы и они должны обязательно идти ДО ParamArray. Если хоть один будет указан после, то получим ошибку компилятора: "Expected: )". Т.е. ожидалась завершающая скобка функции.

Так же можно применить ParamArray, чтобы указывать "неограниченное" количество аргументов для сцепления значений из ячеек в одну строку с указанным разделителем:

Function ОбъединитьВсеСРазделителем(Разделитель As String, ParamArray Значения()) As String
    Dim result As String, arg, x, rc As Range
    For Each arg In Значения
        Select Case TypeName(arg)
        Case "Range"                     'это диапазон
            'цикл по всем ячейкам
            For Each rc In arg.Cells
                If result = "" Then
                    result = rc.Value
                Else
                    result = result & Разделитель & rc.Value
                End If
            Next
        Case "Variant()"                 'это произвольный массив({"а";"б";"в"})
            'цикл по всем ячейкам
            For Each x In arg
                If result = "" Then
                    result = x
                Else
                    result = result & Разделитель & x
                End If
            Next
        Case Else 'это любой другой тип
            'суммируем
            If result = "" Then
                result = arg
            Else
                result = result & Разделитель & arg
            End If
        End Select
    Next
    ОбъединитьВсеСРазделителем = result
End Function

Пример вызова такой функции с листа(первым обязательно передается разделитель, а далее уже что объединять - любой тип данных):
=ОбъединитьВсеСРазделителем("; ";A1:A4;C1;"Привет";{"а";"б";"в"})


 
Создание формулы массива из UDF или ввод формулы сразу в несколько ячеек
Иногда бывает необходимо делать вычисления таким образом, чтобы они возвращались сразу в несколько ячеек. А порой без этого вообще не обойтись. Например, если расчет значения для следующей ячейки напрямую зависит от полученного на предыдущей итерации и видеть надо одновременно значения всех итераций. Например, вывести в несколько строк и столбцов числа от 6 с шагом 2. Стандартными формулами это довольно непросто сделать - ведь придется как-то определять сколько ячеек в каждом столбце и какое последнее число в каждом из столбцов. Через функцию пользователя, созданную как формула массива(подробнее про формулы массива), это сделать проще.
Ниже представлена функция WriteNumbers, которая первым аргументом(Число) принимает произвольное число, с которого начать отсчет, а вторым(Шаг) задается шаг, на который надо увеличивать это число при каждой итерации.

'---------------------------------------------------------------------------------------
' Author : Щербаков Дмитрий(The_Prist)
'          Профессиональная разработка приложений для MS Office любой сложности
'          Проведение тренингов по MS Excel
'          https://www.excel-vba.ru
'          info@excel-vba.ru
' Purpose: Функция записывает в ячейки числа от первого заданного(Число) с заданным шагом(Шаг)
'          Вводится сразу в несколько ячеек и ввод завершается сочетанием клавиш Ctrl+Shift+Enter
'---------------------------------------------------------------------------------------
Function WriteNumbers(Число As Double, Шаг As Double)
    Dim aNumbers() 'массив для записи результата
    Dim rResRange As Range
    Dim lr As Long, lc As Long, dblNum As Double
 
    'задаем начальное значение числа - оно равно Шаг
    dblNum = Число
    'определяем кол-во выделенных ячеек, в которые надо вернуть результат
    Set rResRange = Application.Caller
    ReDim aNumbers(1 To rResRange.Rows.Count, 1 To rResRange.Columns.Count)
    'создаем массив результирующих чисел для всех выделенных ячеек
    For lc = 1 To rResRange.Columns.Count
        For lr = 1 To rResRange.Rows.Count
            aNumbers(lr, lc) = dblNum
            dblNum = dblNum + Шаг
        Next
    Next
    'возвращаем результат
    WriteNumbers = aNumbers
End Function

Чтобы правильно применить приведенную UDF, необходимо

  • выделить несколько ячеек(например, A1:F10)
  • в строку формул ввести нашу UDF: =WriteNumbers(6;2)
  • завершить ввод формулы сразу тремя клавишами Ctrl+Shift+Enter

Главное, на что надо обратить внимание - это тип переменной, которая используется для записи результата: aNumbers(). Она обязательно должна быть задана как массив, если мы хотим, чтобы UDF возвращала результат сразу во все выделенные ячейки и работала как привычная формула массива. В зависимости от решаемой задачи, массив может быть как одномерным горизонтальным или вертикальным, так и многомерным.
В примере выше итоговый массив определяется автоматически при помощи Application.Caller(подробнее про Caller в статье Кто вызвал функцию или процедуру?). Рекомендую всегда делать именно так, чтобы корректно задавать итоговый массив, как бы он ни был задан. Возможно, для написания правильно работающей UDF подобного плана, надо будет чуть более углубленно изучать работу с массивами.


 
Как добавить уже созданную функцию в свою книгу

Для начала необходимо создать стандартный модуль(Insert -Module). Затем в этот модуль вставить весь текст функции(код). Все, теперь функция доступна из диспетчера функций в категории Определенные пользователем(User defined), так же можно будет вводить эту функцию напрямую в ячейки той книги, в которой содержится код функции. Чтобы функция заработала очень важно разрешить макросы. Иначе результатом будет ошибка #ИМЯ!(#NAME!)
GIF-ка с инструкцией, как вставить функцию к себе в книгу на примере функции ТекстДоПервогоПробела из этой статьи:
Как вставить UDF к себе в книгу

Если Вы используете версию Excel 2007 и выше, то книгу необходимо будет сохранить с поддержкой макросов: Меню -Сохранить как -Книга Excel с поддержкой макросов.


 
Обновление расчетов функции пользователя UDF(автопересчет)
По умолчанию функции пользователя не пересчитываются вместе с пересчетом листа или по нажатию F9(Shift+F9). Чтобы функция пользователя пересчиталась, как правило необходимо либо изменить значение любого аргумента функции(например, изменить значение участвующей в расчетах ячейки) или имитировать редактирование самой функции последовательным нажатием клавиш F2-Enter. Это не всегда удобно и часто возникает вопрос: как заставить функцию пересчитываться при любом изменении листа и при пересчете листа/книги клавишами(F9 или Shift+F9). Между тем делается это довольно просто и при этом сделать можно для каждой отдельной функции. На примере простой функции записи даты-времени в ячейку:

Function ТекущаяДатаВремя()
    ТекущаяДатаВремя = Now 'Now - возвращает текущие дату и время
End Function

Если записать её в таком виде, то после записи в ячейку:
=ТекущаяДатаВремя()
при первой записи будут показаны текущие дата и время. Чтобы эксперимент был более наглядным, лучше перейти в Формат ячеек и выставить для ячейки с функцией формат "ДД.ММ.ГГ ч:мм:сс;@". С небольшим интервалом времени понажимайте клавишу F9, чтобы вызвать пересчет книги. Тогда наглядно будет видно, что при пересчете значение функции не изменяется - секунды "застынут" на том месте, где были при начальном вводе функции. Выделите ячейку с функцией - нажмите F2-Enter. Только тогда значение будет пересчитано. А теперь чуть изменим функцию - добавим ключевой параметр пересчета - Application.Volatile:

Function ТекущаяДатаВремя()
    Application.Volatile True
    ТекущаяДатаВремя = Now 'Now - возвращает текущие дату и время
End Function

Теперь при каждом пересчете листа и при любом изменении на листе функция будет пересчитываться. В некоторых случаях это делать просто необходимо(например, если применяется функция получения имени листа или книги).
Но стоит всегда учитывать тот факт, что не всегда такой автопересчет полезен. Если функция пользователя использует "тяжелые" расчеты и выполняется долго - добавление автопересчета может значительно затормозить работу с файлом. Поэтому применять параметр следует с осторожностью.
Если надо, чтобы функция пересчитывалась только при изменениях в конкретном диапазоне/ячейках, можно просто сделать необязательные параметры:

Function ТекущаяДатаВремя(Optional ДиапазонОбновления As Range = Nothing)
    ТекущаяДатаВремя = Now
End Function

тогда при любом изменении в ячейках аргумента ДиапазонОбновления функция будет пересчитана. При этом использовать хоть как-то сам этот аргумент внутри функции совершенно необязательно. Выглядеть запись такой функции будет так:
=ТекущаяДатаВремя(E:E)
при любом изменении в столбце E функция будет пересчитана.

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

Чтобы использовать функции пользователя более удобно, их лучше размещать в специальных файлах - надстройках: Как создать свою надстройку?

Loading

4 комментария

  2. Здравствуйте. Все сделал как написано выше, функция работает! Но при создании нового документа этой функции в списке НЕТ. Я что-то упустил или так и должно быть?

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

  3. Дмитрий, добрый день!
    Извините, если вопрос не подходит в рубрику, но подскажите, пожалуйста! Есть ли возможность функцией записать "подбор параметра"? Если есть, как это сделать?

Добавить комментарий

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.