Аргументы функции на VBA

Создание функции без каких-либо аргументов

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

Function НазваниеЛиста() As String
    НазваниеЛиста = ActiveSheet.Name
End Function

Как видите скобки мы оставили пустые, указали наименование функции НазваниеЛиста и задали тип возвращаемых значение As String (Текстовое значение).

Единственный нюанс данной функции состоит в том, что она автоматически не изменит значение, если Вы переименуете лист. К сожалению Excel запускает процесс пересчета когда меняется какой либо аргумент, но тут их нет и функций "не знает" когда пересчитывать значение. Принудительно можно запустить пересчет книги с помощью сочетания клавиш Ctrl + Alt + F9. Чтобы заставить формулу пересчитываться всякий раз, когда происходит изменение на листе, нужно указать еще одно строку кода.

Function НазваниеЛиста() As String
    Application.Volatile True
    НазваниеЛиста = ActiveSheet.Name
End Function

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

Создание функции с одним аргументом

В предыдущей статье Создаем свою первую функцию в Excel мы достаточно подробно рассказали о создании функции с одним аргументом. Поэтому приведем еще один пример простой но полезной функции и перейдем к следующему пункту.

Наверняка вы сталкивались с тем, что необходимо написать огромную формулу, проверяющую несколько условий с помощью функций ЕСЛИ. Получалось что-то вроде нескольких вложенных функций ЕСЛИ. Давайте попробуем решить эту задачу путем создания пользовательской функции. Например нам необходимо рассчитать значение премии в зависти от процента выполненного плана. Если план выполнен менее чем 50% нам ничего не платят. Если от 50 до 75 процентов, то дают премию 5 000 рублей. Если от 75 до 90% - 15 000 рублей. От 90 до 100% - 25 000 рублей. И если более 100%, то 50 000 тысяч. Давайте писать код:

Function Премия(Процент As Double) As Double
    Dim res As Double
    Select Case Процент
        Case Is < 0.5
            res = 0
        Case Is < 0.75
            res = 5000
        Case Is < 0.9
            res = 15000
        Case Is < 1
            res = 25000
        Case Else
            res = 50000
    End Select
    Премия = res
End Function

Смысл кода думаю ясен, конструкция Select Case проверяет условие и выдает результат если условие истинно.

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

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

Создание функции с несколькими аргументами

Сейчас научимся создавать функции с несколькими переменными и заодно создадим достаточно полезную для работы функцию. Наверняка у всех была похожая задача. Нужно из ячейки, которая содержит ФИО извлечь, к примеру только имя или отчество. Можно обойтись набором стандартных функций, но сложновато. Подобно функциям листа, вы можете создавать функции в VBA, которые принимают несколько аргументов.

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

Function Слово(Текст As String, Номер As Long) As String
      Dim splt As Variant
      splt = Split(Текст, " ")
      Слово = splt(Номер - 1)
End Function

Когда вам необходимо использовать более одного аргумента в функции, то нужно их все перечислить в скобках через запятую. Обратите внимание, что для каждого аргумента вы можете указать тип данных. В приведенном выше примере переменная Текст была объявлена As String (как текст), а переменная Номер объявлена как Long (целое число). Если вы не укажете какой-либо тип данных, VBA считает, что тип данных Variant - этот тип переменных сам изменяет свой тип в зависимости от значения, но потребляет больше ресурсов.

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

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

Пример функции с двумя переменными

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

В Excel есть много функций, в которых некоторые аргументы являются необязательными.

Например, всем известная функция ВПР. У нее 3 обязательных аргумента и один необязательный. Он обозначается в квадратных скобках - [интервальный_просмотр].

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

Но это не означает, что необязательные аргументы бесполезны. Они призваны облегчить функцию и использовать значения по умолчанию.

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

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

Function ТолькоТекст(Ячейка As Range, Optional Прописные As Boolean = False) As String
    Dim i As Long
    Dim result As String
     
    For i = 1 To Len(Ячейка)
        If Not IsNumeric(Mid(Ячейка, i, 1)) Then result = result & Mid(Ячейка, i, 1)
    Next
    
    If Прописные Then result = UCase(result)
    
    ТолькоТекст = result
End Function

Тут хочу обратить внимание на первую строку, а именно на:

Optional Прописные As Boolean = False

Optional обозначает, что переменная необязательная, далее идет наименование аргумента - Прописные, далее при необходимости указывается тип данных. В нашем случае это логическая переменная Boolean. А далее? после знака равно, указывается значение по умолчанию. Если при использовании функции второй аргумент не будет указан, то переменной присвоется значение по умолчанию, в нашем случае False.

Далее в коде вы увидите строку:

If Прописные Then result = UCase(result)

Она как раз и проверяет значение необязательного аргумента. Если в качестве аргумента указано значение ИСТИНА (или 1), то функция вернет текст прописными буквами.

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

Скачать