Пользовательские функции (UDF)

Своя функция прямо в формуле листа: пишешь =VatAmount(A2) — и Excel считает по твоему коду, как по родному СУММ.

UDF (User Defined Function, пользовательская функция) — процедура Function в обычном модуле VBA, которую можно вызвать прямо из ячейки, как встроенную формулу. Она получает аргументы, что-то считает и возвращает одно значение в свою ячейку — и больше ничего в книге изменить не может.

Зачем нужны UDF, если есть макросы

Макрос — это глагол: «собери отчёт», «удали дубли», «разошли письма». Его запускают кнопкой, он что-то делает с книгой. UDF — существительное: «сумма по цвету», «НДС из суммы», «телефон в нормальном виде». Её не запускают — её вызывают из формулы, и она живёт в ячейке, пересчитываясь автоматически при изменении данных.

Классический признак того, что нужна именно UDF: в вашем файле есть формула-монстр на три экрана из вложенных ЕСЛИ, ПОДСТАВИТЬ и ПСТР, которую никто, включая автора, уже не понимает. Такую логику лучше перенести в код: там она читается, комментируется и правится в одном месте, а в ячейке остаётся человеческое =CleanPhone(B2).

Первая функция за две минуты

Куда класть код: Alt+F11 → Insert → Module. Именно обычный модуль. Это не придирка: функцию, написанную в модуле листа (Лист1) или в ThisWorkbook, Excel из формулы не увидит — в ячейке будет #ИМЯ?, и человек полдня будет искать опечатку там, где её нет.

Option Explicit

' НДС «в том числе»: сколько налога сидит внутри суммы
Function VatAmount(ByVal amount As Double, Optional ByVal rate As Double = 0.2) As Double
    VatAmount = Round(amount * rate / (1 + rate), 2)
End Function

Разбор по частям. Function вместо Sub — этим отличается функция от макроса. As Double в конце заголовка — тип возвращаемого значения. Возврат делается присваиванием имени самой функции: VatAmount = .... Никакого return в VBA нет — это первое, обо что спотыкаются те, кто видел другие языки. Optional ... = 0.2 — необязательный аргумент со значением по умолчанию: не передали ставку, значит 20%.

Теперь в ячейке:

=VatAmount(A2)          → 30 000,00   при A2 = 180000
=VatAmount(A2; 0,1)     → 16 363,64   ставка 10%

Обрати внимание на разделитель аргументов: в русской локали это точка с запятой, в английской — запятая. В коде VBA всегда запятая, в ячейке — как настроено у Excel. Ещё приятная мелочь: функция сразу появляется в мастере формул, в категории «Определённые пользователем».

Функции, которые действительно экономят время

Чистка телефонов

' Оставляет только цифры и приводит к виду +7 (999) 123-45-67
Function CleanPhone(ByVal src As String) As String

    Dim i As Long
    Dim ch As String
    Dim digits As String

    For i = 1 To Len(src)
        ch = Mid$(src, i, 1)
        If ch >= "0" And ch <= "9" Then digits = digits & ch
    Next i

    If Len(digits) = 11 And Left$(digits, 1) = "8" Then
        digits = "7" & Mid$(digits, 2)          ' 8… → 7…
    End If

    If Len(digits) <> 11 Then
        CleanPhone = "?"                        ' не похоже на номер
        Exit Function
    End If

    CleanPhone = "+" & Left$(digits, 1) & _
                 " (" & Mid$(digits, 2, 3) & ") " & _
                 Mid$(digits, 5, 3) & "-" & _
                 Mid$(digits, 8, 2) & "-" & _
                 Mid$(digits, 10, 2)

End Function

Результат:

B2: 8 (999) 123 45 67    =CleanPhone(B2)  → +7 (999) 123-45-67
B3: 79991234567          =CleanPhone(B3)  → +7 (999) 123-45-67
B4: доб. 205             =CleanPhone(B4)  → ?

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

Сумма по цвету заливки

Формулой это не делается вообще: у Excel нет функции, которая видит цвет ячейки. А у VBA есть.

' target — что суммируем, sample — ячейка-образец с нужным цветом
Function SumByColor(ByVal target As Range, ByVal sample As Range) As Double

    Dim c As Range
    Dim wantedColor As Long
    Dim total As Double

    wantedColor = sample.Cells(1, 1).Interior.Color

    For Each c In target.Cells
        If c.Interior.Color = wantedColor Then
            If IsNumeric(c.Value) And Not IsEmpty(c.Value) Then
                total = total + c.Value
            End If
        End If
    Next c

    SumByColor = total

End Function

Вызов: =SumByColor(E2:E100; H1) — просуммирует те ячейки диапазона E, чей цвет совпадает с цветом H1.

И тут же — честное предупреждение, которое обычно не пишут. Изменение цвета ячейки не считается изменением данных, и Excel не пересчитает функцию. Ты перекрасил ячейку — итог остался старым. Придётся жать Ctrl+Alt+F9 (полный пересчёт книги). Добавление Application.Volatile первой строкой заставит функцию пересчитываться при любом пересчёте листа, но и это не спасает от перекраски — Excel просто не запускает пересчёт в ответ на смену формата. Такая функция удобна, но её ограничения надо понимать заранее.

Склейка значений по условию

' Собирает через запятую все значения, у которых в колонке-критерии совпадение
Function JoinIf(ByVal criteria As Range, ByVal criterion As String, _
                ByVal values As Range, Optional ByVal sep As String = ", ") As String

    Dim i As Long
    Dim result As String

    For i = 1 To criteria.Cells.Count
        If CStr(criteria.Cells(i, 1).Value) = criterion Then
            If Len(result) > 0 Then result = result & sep
            result = result & CStr(values.Cells(i, 1).Value)
        End If
    Next i

    JoinIf = result

End Function

Вызов =JoinIf(B2:B100; "Иванов"; C2:C100) вернёт «Ноутбук, Клавиатура, Монитор» — все товары Иванова в одной ячейке.

Как это работает

Когда Excel пересчитывает лист, он видит в ячейке имя, которого нет среди встроенных функций, и ищет его среди Function в открытых модулях VBA. Нашёл — передаёт аргументы, ждёт результат, кладёт его в ячейку. Всё это происходит внутри цикла пересчёта, и вот отсюда растут все ограничения UDF.

Главное: UDF не может менять книгу

Excel запрещает функции трогать что-либо, кроме собственной ячейки. Не «не рекомендует» — физически блокирует. Логика простая: пересчёт может запуститься в любой момент, много раз, в непредсказуемом порядке; если бы функции разрешили писать в соседние ячейки, каждый пересчёт менял бы данные и порождал новый пересчёт.

Function BadUdf(ByVal x As Double) As Double

    Range("Z1").Value = "привет"              ' не сработает: ячейка не изменится
    Range("A1").Interior.Color = vbRed        ' и это не сработает
    Columns("A").AutoFit                      ' и это тоже
    Worksheets.Add                            ' и это

    BadUdf = x * 2                            ' а вот это отработает

End Function

Самое неприятное — Excel не ругается. Он молча игнорирует запрещённые действия (иногда возвращая #ЗНАЧ!), и человек часами гадает, почему «код правильный, а ничего не происходит». Если нужно менять другие ячейки, форматы, листы — это не UDF, это макрос. Пиши Sub и вешай на кнопку.

Что ещё нельзя и о чём стоит знать

• MsgBox внутри UDF — формально работает, практически подвешивает Excel: окно всплывает при каждом пересчёте каждой ячейки с этой формулой. Для отладки используй Debug.Print и окно Immediate (Ctrl+G).
• ActiveCell и Selection — не то, что ты думаешь: во время пересчёта активна может быть любая ячейка. Если функции нужно знать, откуда её вызвали, есть Application.Caller.
• Скорость. UDF на порядок медленнее встроенных функций. Десять тысяч ячеек с SumByColor по диапазону в тысячу строк — это десять миллионов проверок цвета при каждом пересчёте. Для тяжёлых расчётов лучше макрос, который посчитает один раз и запишет значения.
• Application.Volatile — заставляет функцию пересчитываться при любом изменении в книге. Иногда необходимо (например, функция зависит от Now), но замедляет файл. Без крайней нужды не ставь.
• Живёт только в своей книге. Отправишь коллеге файл без макросов (.xlsx) — формулы превратятся в #ИМЯ?. Сохраняй как .xlsm, а для функций «на все файлы» делай надстройку .xlam.

Как вернуть из UDF нормальную ошибку Excel

Если аргументы кривые, правильный ответ — не ноль и не текст «ошибка», а настоящая ошибка Excel. Для этого функция должна возвращать Variant, а внутри — CVErr:

Function SafeDivide(ByVal a As Variant, ByVal b As Variant) As Variant

    If Not IsNumeric(a) Or Not IsNumeric(b) Then
        SafeDivide = CVErr(xlErrValue)        ' #ЗНАЧ!
        Exit Function
    End If

    If b = 0 Then
        SafeDivide = CVErr(xlErrDiv0)         ' #ДЕЛ/0!
        Exit Function
    End If

    SafeDivide = a / b

End Function

Теперь =ЕСЛИОШИБКА(SafeDivide(A2;B2); 0) сработает как с любой встроенной формулой — твоя функция ведёт себя как родная.

Частые ошибки

Итоги

• UDF — это Function в обычном модуле; результат возвращается присваиванием имени функции, return в VBA нет.
• UDF считает и возвращает одно значение в свою ячейку — менять другие ячейки, форматы и листы она физически не может, причём молча.
• Нужно что-то изменить в книге — это макрос (Sub), а не функция.
• Всё, от чего зависит результат, передавай аргументами — иначе Excel не узнает, когда пересчитывать.
• Optional задаёт необязательный аргумент со значением по умолчанию.
• Ошибки возвращай через CVErr(xlErrValue) из функции типа Variant — так формула подружится с ЕСЛИОШИБКА.
• UDF медленнее встроенных функций: для тяжёлых расчётов по десяткам тысяч ячеек бери макрос.
• Книгу с UDF сохраняй как .xlsm; чтобы функции работали во всех файлах — собери надстройку .xlam.