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

В теме 5.2 мы уже видели слово Function — так помечали заготовки вроде Function Четность(n As Integer) As String. Но там функция была лишь примером синтаксиса. Здесь разберём главное: как написать СВОЮ функцию, которую Excel начнёт понимать как обычную формулу листа, — набираешь =МояФункция(A1) в ячейке точно так же, как =СУММ или =СРЗНАЧ, только считает она по твоим правилам. А во второй половине темы — что делать, когда код повёл себя не так, как ожидалось: как остановить его на нужной строке и заглянуть внутрь, что там происходит.

К концу темы ты сможешь:
— написать свою функцию на VBA с аргументами и вызвать её как формулу в ячейке;
— объяснить, чем Public отличается от Private и почему только Public-функция из обычного модуля видна в списке функций листа;
— поставить точку останова и пройти код по шагам, чтобы увидеть, где он повёл себя не так;
— читать значения переменных через окно Locals и проверять выражения через Immediate.

Вспомни: в теме 5.2 мы разобрали разницу между Sub (выполняет действие) и Function (возвращает значение). Дальше без этого никак — пользовательская функция это Function, доведённая до состояния «работает в ячейке листа».

Содержание

Зачем писать свою функцию

Иногда расчёт в ячейке разрастается в длинную формулу с кучей вложенных ЕСЛИ, которую страшно трогать и невозможно скопировать без ошибки. Например, расчёт скидки: до 5 единиц — без скидки, от 5 до 20 — 5%, больше 20 — 10%. Такую логику можно один раз описать в коде и дальше вызывать одним коротким именем в любой ячейке — вместо того чтобы копировать длинную формулу по всей таблице и бояться, что где-то собьётся скобка.

Синтаксис: Function, один и несколько аргументов, возврат значения

Функция начинается с Function, дальше — имя, в скобках аргументы, после As — тип того, что функция вернёт. Внутри функции результат отдают наружу необычно: не через отдельное слово вроде «вернуть», а присваивая значение имени самой функции.

Function Удвоить(n As Double) As Double
    Удвоить = n * 2
End Function

Строка Удвоить = n * 2 — это и есть возврат результата: что присвоено имени функции, то и увидит ячейка. Вызывают функцию в ячейке как обычную формулу: =Удвоить(A1) подставит значение из A1 вместо аргумента n и вернёт результат.

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

Function ЦенаСоСкидкой(cena As Double, kolichestvo As Integer) As Double
    If kolichestvo > 20 Then
        ЦенаСоСкидкой = cena * 0.9
    ElseIf kolichestvo >= 5 Then
        ЦенаСоСкидкой = cena * 0.95
    Else
        ЦенаСоСкидкой = cena
    End If
End Function

В ячейке эта функция вызывается так: =ЦенаСоСкидкой(A2;B2) — Excel сам подставит значения из A2 и B2 на место cena и kolichestvo по порядку.

ВЫЗОВ ПОЛЬЗОВАТЕЛЬСКОЙ ФУНКЦИИ ИЗ ЯЧЕЙКИ

   Ячейка листа:  =ЦенаСоСкидкой(A2;B2)
                          |         |
                          v         v
   Код VBA:  Function ЦенаСоСкидкой(cena As Double, kolichestvo As Integer) As Double
                             ^                              ^
                        первый аргумент              второй аргумент
                        (значение из A2)              (значение из B2)

   Результат = то, что присвоено имени ЦенаСоСкидкой внутри функции.

Public и Private: кто виден в ячейке, а кто только в коде

Если не написать перед Function ничего особенного, функция становится Public по умолчанию — это и значит «видна снаружи». Private Function, наоборот, работает только внутри своего модуля кода: из другого модуля её не вызвать, а в списке функций на вкладке «Формулы» (Мастер функций) она не появится. Правило на практике простое: свои функции для использования в ячейках делай Public (или просто не пиши ничего — это то же самое), а вспомогательные функции, которые нужны только другому коду и не должны мозолить глаза в списке формул листа, — Private.

Важное условие, не только про Public/Private. Функция станет доступна в ячейке листа, только если она написана в обычном (стандартном) модуле — том, что создаётся через «Вставка → Module» в редакторе VBA. Функция, написанная внутри объекта листа (Лист1) или книги (ЭтаКнига), в ячейке не появится и в формуле даст ошибку #ИМЯ?, даже если она Public.

ГДЕ ЛЕЖИТ ФУНКЦИЯ          ВИДНА ЛИ В ЯЧЕЙКЕ ЛИСТА КАК =ИмяФункции(...)
─────────────────────────────────────────────────────────────────────
Public, обычный модуль      ДА — работает как обычная формула
Private, обычный модуль     НЕТ — доступна только коду в этом же модуле
Public, объект листа        НЕТ — Excel вернёт #ИМЯ?, даже если Public
(Лист1) или книги

Отладка: что делать, когда код повёл себя не так

Функция написана, но результат неверный — а формула ведь не показывает, что происходило внутри неё по шагам. Для этого в редакторе VBA есть отладчик: он умеет остановить выполнение кода на нужной строке и дать заглянуть внутрь, что в этот момент лежит в переменных.

Точка останова ставится клавишей F9 — поставь курсор на строку кода и нажми F9 (или щёлкни на серой полосе слева от строки). Когда выполнение дойдёт до этой строки, код остановится, а сама строка подсветится. Дальше жмут F8 — «Пошагово», это выполняет код по одной строке за раз, и видно, какая строка выполняется прямо сейчас.

ОСТАНОВКА КОДА НА ТОЧКЕ ОСТАНОВА

   Function ЦенаСоСкидкой(cena As Double, kolichestvo As Integer) As Double
       If kolichestvo > 20 Then
   ●       ЦенаСоСкидкой = cena * 0.9      <- точка останова (F9), код встал здесь
       ElseIf kolichestvo >= 5 Then
           ЦенаСоСкидкой = cena * 0.95
       Else
           ЦенаСоСкидкой = cena
       End If
   End Function

   F8 (Step Into) — выполнить ЭТУ строку и остановиться на следующей.

Пока код стоит на точке останова, доступны три окна-помощника (включаются через меню «Вид»). Окно Locals (Переменные) само показывает текущие значения ВСЕХ переменных активной процедуры — ничего печатать не нужно, оно обновляется на каждом шаге. Окно Watch делает то же самое, но только для переменных, которые ты сам добавил в список, — удобно, если переменных много, а следить нужно за одной-двумя. Immediate (окно немедленного выполнения) — это что-то вроде мини-консоли: пока код стоит на паузе, туда можно напечатать любое выражение и сразу увидеть результат, или заранее вставить в код команду Debug.Print, и её вывод появится именно здесь.

ОКНО         ЧТО ПОКАЗЫВАЕТ                         КОГДА ОБНОВЛЯЕТСЯ
─────────────────────────────────────────────────────────────────────────
Locals       ВСЕ переменные активной процедуры       само, на каждом шаге
Watch        только переменные, добавленные вручную   само, на каждом шаге
Immediate    результат введённого выражения           когда сам напечатал
             или вывод Debug.Print                    команду или строку кода

Проверь себя

Контрольный вопрос. Чем Function отличается от Sub по тому, что она умеет?

АFunction возвращает значение, которое можно использовать дальше — например, в ячейке или в другой формуле

БFunction выполняется быстрее, чем Sub

ВFunction нельзя вызвать из другого кода

Гразницы нет, это просто два разных слова для одного и того же

Подсказка: Sub выполняет действие. Function делает то же самое, но ещё и отдаёт результат наружу.

Онлайн-проверка ответа появится позже

Контрольный вопрос. Как в VBA передают результат наружу из функции?

Априсваивают значение имени самой функции

Бпишут ключевое слово Return перед значением

Врезультат печатают через Debug.Print

Грезультат передают через MsgBox

Подсказка: В примере урока результат отдавался строкой вида «ИмяФункции = …».

Онлайн-проверка ответа появится позже

Задание. В ячейке написали формулу =Удвоить(5). Какое число она вернёт? Впиши число.

Исходный код для этого задания:
Function Удвоить(n As Double) As Double
    Удвоить = n * 2
End Function
Подсказка: Функция умножает переданное число на 2.

✅ Готово, если: ты ввёл(а) верный ответ, и онлайн-проверка его приняла.

Онлайн-проверка ответа появится позже

Контрольный вопрос. Функцию объявили без ключевого слова Private (просто Function ИмяФункции(…) …). Какая у неё область видимости по умолчанию?

АPublic

БPrivate

Ву функций в VBA не бывает области видимости

Гзависит от того, какой Excel установлен

Подсказка: Если ничего не написано, действует правило «по умолчанию».

Онлайн-проверка ответа появится позже

Контрольный вопрос. Какую функцию можно вызвать прямо из ячейки листа как обычную формулу — при условии, что она написана в обычном (стандартном) модуле?

АPublic

БPrivate

Влюбую, область видимости здесь ни при чём

Гни одну, функции листа пишутся не в VBA

Подсказка: Одна из двух областей видимости специально закрывает доступ снаружи модуля.

Онлайн-проверка ответа появится позже

Контрольный вопрос. Функция написана внутри объекта листа (Лист1), а не в обычном модуле, и объявлена как Public. Что произойдёт при попытке вызвать её из ячейки?

АExcel не найдёт такую функцию и вернёт #ИМЯ? — место написания важнее, чем Public

Бфункция сработает как обычно, Public решает всё

Вфункция сработает, но только на этом же листе

ГExcel сам предложит перенести функцию в обычный модуль

Подсказка: В уроке было отдельное условие: видимость в ячейке требует ОБОИХ факторов сразу — и Public, и правильного места.

Онлайн-проверка ответа появится позже

Задание. В ячейке написали =ЦенаСоСкидкой(1000;12). Какое число вернёт функция? Впиши число.

Исходный код для этого задания:
Function ЦенаСоСкидкой(cena As Double, kolichestvo As Integer) As Double
    If kolichestvo > 20 Then
        ЦенаСоСкидкой = cena * 0.9
    ElseIf kolichestvo >= 5 Then
        ЦенаСоСкидкой = cena * 0.95
    Else
        ЦенаСоСкидкой = cena
    End If
End Function
Подсказка: 12 попадает в диапазон «от 5 до 20» — какая скидка действует в этом случае?

✅ Готово, если: ты ввёл(а) верный ответ, и онлайн-проверка его приняла.

Онлайн-проверка ответа появится позже

Контрольный вопрос. Отметь все верные утверждения о пользовательских функциях в Excel (не менее двух вариантов).

Выберите все верные варианты.

АPublic-функция из обычного модуля видна в ячейке как обычная формула

Бфункция может принимать несколько аргументов

Вфункция обязательно должна начинаться со слова Sub

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

Дрезультат функции возвращается присваиванием значения её имени

Подсказка: Два утверждения путают функцию с процедурой Sub или описывают несуществующее ограничение.

Онлайн-проверка ответа появится позже

Контрольный вопрос. Что делает клавиша F9, если поставить курсор на строку кода в редакторе VBA и нажать её?

Аставит (или снимает) точку останова на этой строке

Бзапускает весь код от начала до конца

Ввыполняет только эту одну строку и останавливается

Готкрывает окно Immediate

Подсказка: Это про подготовку остановки, а не про сам запуск кода.

Онлайн-проверка ответа появится позже

Контрольный вопрос. Какая клавиша выполняет код по одной строке за раз, подсвечивая ту, что выполняется прямо сейчас?

АF8

БF9

ВF5

ГF4

Подсказка: F9 только ставит место остановки. Дальше нужна другая клавиша, чтобы идти по коду шаг за шагом.

Онлайн-проверка ответа появится позже

Контрольный вопрос. Код стоит на точке останова. В какое окно можно напечатать выражение и сразу увидеть его результат — или заранее вставить в код команду Debug.Print, чтобы увидеть её вывод?

АImmediate (окно немедленного выполнения)

БLocals (окно переменных)

ВProject Explorer

Гстрока формул

Подсказка: Это окно ведёт себя как мини-консоль внутри редактора кода.

Онлайн-проверка ответа появится позже

Контрольный вопрос. Какое окно отладчика само, без каких-либо команд от тебя, показывает текущие значения ВСЕХ переменных активной процедуры?

АLocals (окно переменных)

БImmediate (окно немедленного выполнения)

ВWatch

ГProject Explorer

Подсказка: Одно из трёх окон обновляется само по всем переменным сразу — ничего в него добавлять не нужно.

Онлайн-проверка ответа появится позже

Задание. Нужно следить только за одной конкретной переменной, не отвлекаясь на остальные, которых в процедуре много. Какое окно отладчика для этого предназначено? Впиши одно слово: Locals, Immediate или Watch.

Подсказка: Locals показывает вообще всё сразу. Нужно окно, куда переменные добавляют по одной, вручную.

✅ Готово, если: ты ввёл(а) верный ответ, и онлайн-проверка его приняла.

Онлайн-проверка ответа появится позже

Итог

Итог. Пользовательская функция — это Function, которую можно вызвать прямо в ячейке листа как обычную формулу: =ИмяФункции(аргументы). Результат возвращают, присваивая значение имени самой функции. Видна в ячейке только функция, у которой ОБА условия выполнены разом: она Public (или без пометки — это то же самое) И написана в обычном (стандартном) модуле, а не в объекте листа/книги. Когда код ведёт себя не так, как ожидалось: F9 ставит точку останова, F8 проводит по коду шаг за шагом, окно Locals само показывает все переменные, Watch — только выбранные, а Immediate позволяет напечатать выражение или увидеть вывод Debug.Print прямо во время остановки.

Практика (необязательная)

Задание для практики. Напиши свою функцию ОценкаСтажа(stazh As Integer) As String: если лет опыта меньше 1 — «новичок», от 1 до 3 — «специалист», больше 3 — «эксперт». Сохрани в обычном модуле как Public, проверь в ячейке на нескольких значениях. Затем поставь точку останова на первой строке If, нажми F8 несколько раз и последи в окне Locals, как ведёт себя переменная stazh на каждом шаге.

← Назад · ↑ В начало урока · ⌂ В начало курса · Вперёд: Массивы и коллекции →