Процедуры Sub и функции Function

Разбираемся, чем Sub отличается от Function, как передавать параметры и возвращать результат — и как из макроса-простыни сделать код, который через месяц ещё можно прочитать.

Процедура — именованный кусок кода, который можно вызвать по имени. Sub что-то делает (красит, копирует, чистит), Function что-то считает и возвращает значение.

Зачем дробить код

Первый рабочий макрос почти всегда выглядит одинаково: одна Sub на 80 строк, которая чистит лист, тянет данные, считает итоги и красит шапку. Работает — и ладно. Проблемы приходят позже: нужно поменять только форматирование шапки, а вы полчаса ищете, где оно начинается; нужно посчитать НДС ещё и в другом отчёте — и вы копируете формулу, а потом ставку меняют, и в одном месте вы её поправили, а в другом забыли.

Процедуры решают ровно это: каждый кусок работы получает имя, живёт отдельно и переиспользуется. Хороший ориентир — процедура помещается на экран без прокрутки.

Sub: делает работу

Option Explicit

Sub OchistitOtchet()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Отчёт")

    ws.Range("A2:F100000").ClearContents
    ws.Range("A2:F100000").Interior.ColorIndex = xlNone
End Sub

Sub ничего не возвращает — она меняет мир вокруг: пишет в ячейки, красит, сохраняет файл. Именно Sub без параметров видна в списке макросов Excel (Alt+F8) и может быть назначена кнопке на листе.

Function: считает и возвращает

Функция получает данные, что-то с ними делает и отдаёт результат. Возврат в VBA выглядит непривычно: никакого return нет — вы присваиваете значение имени функции.

Function SummaSNDS(bezNDS As Double) As Double
    SummaSNDS = bezNDS * 1.2
End Function

Разбор: bezNDS As Double — параметр, входные данные. As Double после скобок — тип того, что функция вернёт. Строка SummaSNDS = ... и есть возврат значения. Если её забыть, функция вернёт ноль — молча, без ошибки. Это ошибка №1 у новичков.

Вызвать функцию можно из другой процедуры:

Sub ProstavitSNDS()
    Dim i As Long
    Dim lastRow As Long
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Счета")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    For i = 2 To lastRow
        ws.Cells(i, 3).Value = SummaSNDS(ws.Cells(i, 2).Value)
    Next i
End Sub

А можно — прямо с листа, как обычную формулу Excel. Это называется UDF (User Defined Function, пользовательская функция), и это одна из самых недооценённых возможностей VBA:

=SummaSNDS(B2)

Ваша функция появится в списке формул наравне с СУММ и ВПР. Условие одно: она должна лежать в стандартном модуле (Insert → Module), а не в модуле листа или книги.

Важное ограничение UDF: функция, вызванная из ячейки, не может менять лист — ни писать в другие ячейки, ни красить, ни удалять строки. Excel вызывает её в режиме пересчёта, где изменения запрещены; попытка просто ничего не сделает или вернёт #ЗНАЧ!. Нужно менять лист — это работа для Sub, запускаемой кнопкой.

Параметры: ByVal и ByRef

Параметр — это «вход» процедуры. Передавать значения параметрами гораздо надёжнее, чем через глобальные переменные: видно, что процедура берёт и что отдаёт.

Но есть тонкость, которая в VBA работает не так, как ожидают. По умолчанию параметр передаётся по ссылке (ByRef): процедура получает не копию, а саму переменную и может её изменить.

Sub Demo()
    Dim x As Long
    x = 10

    Udvoit x
    MsgBox x        ' покажет 20 — переменная снаружи изменилась!
End Sub

Sub Udvoit(ByRef chislo As Long)
    chislo = chislo * 2
End Sub

Иногда это ровно то, что нужно. Гораздо чаще — источник неприятных сюрпризов: вспомогательная процедура «испортила» переменную вызывающего кода. Практическое правило: пишите ByVal, если не собираетесь менять аргумент. С ByVal процедура получает копию, и внешняя переменная в безопасности.

Function Nadbavka(ByVal oklad As Double, ByVal stazhLet As Long) As Double
    Dim koef As Double

    Select Case stazhLet
        Case Is >= 10
            koef = 0.2
        Case 5 To 9
            koef = 0.1
        Case Else
            koef = 0
    End Select

    Nadbavka = oklad * koef
End Function

Необязательные параметры

Function OkruglitDeneg(ByVal summa As Double, Optional ByVal znakov As Long = 2) As Double
    OkruglitDeneg = Application.WorksheetFunction.Round(summa, znakov)
End Function

Optional со значением по умолчанию: OkruglitDeneg(1234.5678) округлит до 2 знаков, OkruglitDeneg(1234.5678, 0) — до целых. И маленькая ловушка из практики: встроенный VBA-Round использует «банковское» округление (0.5 округляется к чётному, Round(2.5, 0) даёт 2). Если нужно привычное школьное округление — берите Application.WorksheetFunction.Round, как в примере.

Как вызывать: со скобками или без

Тут VBA расставил грабли. Для Sub:

Udvoit x            ' правильно: без скобок
Call Udvoit(x)      ' правильно: со словом Call — скобки обязательны
Udvoit (x)          ' ЛОВУШКА: передаётся КОПИЯ, ByRef не сработает

Лишние скобки вокруг единственного аргумента заставляют VBA сначала вычислить выражение и передать его значение. Именно поэтому Udvoit (x) тихо не меняет x — и человек полдня ищет несуществующую ошибку. Для Function, наоборот, скобки обязательны, когда вы забираете результат: y = Nadbavka(50000, 7).

Разбиваем монолит: как это выглядит на практике

Было — одна процедура на всё, «простыня»:

Sub ObnovitOtchetPloho()
    ' 80 строк: чистка, загрузка, расчёты, оформление, сохранение — всё вперемешку
    Worksheets("Отчёт").Range("A2:F100000").ClearContents
    ' ...
    ' ...
End Sub

Стало — «оглавление» вверху и понятные куски внизу:

Option Explicit

Public Sub ObnovitOtchet()          ' точка входа: видна в Alt+F8, вешается на кнопку
    Application.ScreenUpdating = False

    OchistitOtchet
    ZagruzitDannye
    RasschitatItogi
    OformitShapku

    Application.ScreenUpdating = True
    MsgBox "Отчёт обновлён: " & Format(Now, "dd.mm.yyyy hh:nn")
End Sub

Private Sub OchistitOtchet()
    ' ... 6 строк ...
End Sub

Private Sub ZagruzitDannye()
    ' ... 15 строк ...
End Sub

Private Sub RasschitatItogi()
    ' ... 12 строк ...
End Sub

Private Sub OformitShapku()
    ' ... 8 строк ...
End Sub

Верхняя процедура читается как содержание книги — по ней сразу видно, что происходит. Слово Private у вспомогательных процедур делает важную вещь: они исчезают из списка макросов (Alt+F8). Пользователь видит одну кнопку «Обновить отчёт», а не десяток непонятных имён, половину которых опасно запускать по отдельности.

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

Когда одна процедура вызывает другую, VBA запоминает точку возврата (кладёт её на так называемый стек вызовов), передаёт управление вниз, а по достижении End Sub возвращается ровно в то место, откуда ушёл, и продолжает со следующей строки. Локальные переменные вызванной процедуры создаются заново при каждом вызове и уничтожаются на выходе — именно поэтому Dim i As Long в двух разных процедурах никак друг другу не мешают.

Exit Sub и Exit Function позволяют выйти досрочно — очень удобно для «стражей» в начале:

Private Sub ZagruzitDannye()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Источник")

    If ws.Range("A2").Value = "" Then
        MsgBox "Лист «Источник» пуст — загружать нечего."
        Exit Sub                    ' дальше идти незачем
    End If

    ' ... основная работа ...
End Sub

Есть ещё одна тонкость про Function: тип возврата тоже стоит указывать явно. Function Nadbavka(...) без As Double вернёт Variant — работать будет, но вы теряете и проверку типов, и немного скорости.

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

• Function ничего не присваивает своему имени. Вернётся 0 или пустая строка — без единого предупреждения. Проверьте, что строка ИмяФункции = ... точно есть.
• Ждут Sub в списке Alt+F8, а она с параметрами. Процедуры с параметрами и Private-процедуры в списке макросов не показываются — это по замыслу. Точка входа должна быть Public Sub без параметров.
• Лишние скобки при вызове Sub. Udvoit (x) передаёт копию, и ByRef перестаёт работать. Пишите Udvoit x или Call Udvoit(x).
• UDF пытается менять лист. Функция из ячейки не может писать в другие ячейки и красить. Для этого нужна Sub.
• Забыли ByVal. Процедура «неожиданно» поменяла переменную вызывающего кода. По умолчанию всё передаётся ByRef.
• Обмен данными через Public-переменные вместо параметров. Кажется быстрее, но через месяц невозможно понять, кто что перезаписал. Параметры и возвращаемые значения — честный контракт.
• Имя функции совпадает со встроенным (Left, Round, Date). VBA запутается, и вы вместе с ним. Давайте свои, узнаваемые имена.

Итоги

• Sub выполняет действие и ничего не возвращает; Function возвращает значение — присваиванием своему имени (return в VBA нет).
• Function в стандартном модуле работает и как формула на листе (UDF), но менять лист из неё нельзя.
• Параметры по умолчанию передаются ByRef — пишите ByVal, если не собираетесь менять аргумент.
• Optional задаёт необязательный параметр со значением по умолчанию.
• Вызов Sub: ИмяSub аргумент или Call ИмяSub(аргумент). Лишние скобки без Call ломают ByRef.
• Одна процедура — одна понятная задача. Точка входа Public, вспомогательные — Private, чтобы не мусорить в Alt+F8.
• Exit Sub / Exit Function — досрочный выход, отличный способ отсечь «нечего делать» в самом начале.