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

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

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

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

Использовать их можно:

• непосредственно на листе Эксель
• как часть макроса или другой функции в редакторе VBA
• как правило условного форматирования

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

Как создать

Давайте попробуем составить простую функцию Excel для сложения двух чисел. Откройте редактор кода сочетанием клавиш «Alt+F11». Затем создайте дополнительный модуль к книге и вставьте приведенный ниже код.

Function СЛОЖИТЬ(number1 As Long, number2 As Long) As Long
    Dim result As Long
    result = number1 + number2
    СЛОЖИТЬ = result
End Function

Сохраните книгу сочетанием клавиш Ctrl+S. Теперь функцию СЛОЖИТЬ() можно использовать на листе. Чтобы проверить ее работу, вернитесь на лист и попробуйте написать =СЛОЖИТЬ(1;2) в ячейку.

=СЛОЖИТЬ(1;2)

Как видите, все работает.

Синтаксис

Давайте разберем из чего синтаксически состоит код.

Вместо команды Sub у макроса, функция определяется командой Function.

Далее идет имя. Оно может быть задано цифрами, кириллическими или латинскими буквами, символом подчеркивания. Нельзя использовать пробелы. Также имя пользовательской функции не должно дублировать имя стандартной, поскольку в противном случае она просто не будет работать.

После имени объявляются аргументы. У аргументов при необходимости можно задать тип данных. Типы аргументов соответствуют стандартным типам данных VBA. При этом функция может быть вообще без аргументов. Также аргументы могут быть обязательными и необязательными. Эти примеры мы разберем позже.

После аргументов можно объявить тип данных возвращаемого значения.

Далее идет непосредственно блок вычислений - в нем мы можем объявлять переменные, проводить операции и вычисления с аргументами. Перед завершающей командой End Function нужно обязательно указать возвращаемое значение:

Function ИМЯФУНКЦИИ()
    ' тут наши вычисления
    ИМЯФУНКЦИИ = 2
End Function

Аргументы

Как правило, задается несколько аргументов, однако их может не быть совсем — Вы наверняка знаете, что в Эксель есть и стандартные функции без аргументов, типа СЕГОДНЯ() или СЛЧИС(). Например, мы можем получить имя текущего листа - для этого не требуется никаких аргументов.

Function ИМЯЛИСТА()
    ИМЯЛИСТА = Application.ActiveSheet.Name
End Function

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

В нашем примере сложения добавим возможность вывести единицы измерения после числа, если аргумент единиц измерения задан.

Function СЛОЖИТЬЕД(number1 As Long, number2 As Long, Optional ed As String)
    Dim count As Long
    count = number1 + number2 ' Складываем

    If Len(ed) > 0 Then
        result = count & ed ' Если единицы заданы, добавляем их
    Else
        result = count ' Иначе возвращаем просто число
    End If

  СЛОЖИТЬЕД = result
End Function

Обратите внимание, как задан необязательный аргумент — перед именем указано ключевое слово Optional и расположен он после обязательных, это важно.

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

Function СНДС(price As Long, Optional nds As Long = 20) As Long
    Dim result As Long
    result = price + price * nds / 100
    СНДС = result
End Function

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

Примеры

Вот несколько полезных функций, которых изначально нет в Эксель. Их можно легко реализовать и стандартными функциями, это просто для примера.

Рассчитать процент от числа

Function ПРЦН(num1 As Long, num2 As Long) As Double
    Dim result As Double
    result = num1 / num2 * 100
    ПРЦН = result
End Function

Рассчитать маржу товара

Function МАРЖА(price As Long, sebes As Long, Optional dop As Long = 0, Optional logist As Long = 0, Optional nalog As long = 0) As Double
    Dim result As Double
    result = price - sebes - dop - logist - (price * nalog / 100)
    МАРЖА = result
End Function

Рассчитать маржинальность товара

Function МАРЖИНАЛЬНОСТЬ(price As Long, sebes As Long, Optional dop As Long = 0, Optional logist As Long = 0, Optional nalog As long = 0) As Double
    Dim result As Double
    result = (price - sebes - dop - logist - (price * nalog / 100)) / price * 100
    МАРЖИНАЛЬНОСТЬ = result
End Function