Перейти к содержимому
Наше приложение «Дешевые авиабилеты» в AppGallery >>

VBA Excel. Пользовательская функция (синтаксис, компоненты)

    Создание пользовательской функции в VBA Excel, ее синтаксис и компоненты. Описание пользовательской функции и ее аргументов. Метод Application.MacroOptions.


    Пользовательская функция — это процедура VBA, которая производит заданные вычисления и возвращает полученный результат. Используется для вставки в ячейки рабочего листа Excel или для вызова из других процедур.

    Объявление пользовательской функции

    Синтаксис функции

    Компоненты функции

    • Static — необязательное ключевое слово, указывающее на то, что значения переменных, объявленных в функции, сохраняются между ее вызовами.
    • Имя — обязательный компонент, имя пользовательской функции.
    • СписокАргументов — необязательный компонент, одна или более переменных, представляющих аргументы, которые передаются в функцию. Аргументы заключаются в скобки и разделяются между собой запятыми.
    • Операторы — необязательный компонент, блок операторов (инструкций).
    • Имя = выражение — необязательный* компонент, присвоение имени функции значения выражения или переменной. Обычно, значение присваивается функции непосредственно перед выходом из нее.
    • Exit Function — необязательный компонент, принудительный выход из функции, если ей уже присвоено окончательное значение.

    *Один из компонентов Имя = выражение следует считать обязательным, так как если не присвоить функции значения, смысл ее использования теряется.

    Видимость функции

    Видимость пользовательской функции определяется необязательными ключевыми словами Public и Private, которые могут быть указаны перед оператором Function (или Static, в случае его использования).

    Ключевое слово Public указывает на то, что функция будет доступна для вызова из других процедур во всех модулях открытых книг Excel. Функция, объявленная как Public, отображается в диалоговом окне Мастера функций.

    Ключевое слово Private указывает на то, что функция будет доступна для вызова из других процедур только в пределах программного модуля, в котором она находится. Функция, объявленная как Private, не отображается в диалоговом окне Мастера функций, но ее можно ввести в ячейку вручную.

    Если ключевое слово Public или Private не указано, функция считается по умолчанию объявленной, как Public.

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

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

    Для примера мы рассмотрим простейшую пользовательскую функцию, которой в следующем параграфе добавим описание. Называется функция «Деление», объявлена с типом данных Variant, так как ее возвращаемое значение может быть и числом, и текстом. Аргументы функции — Делимое и Делитель — тоже объявлены как Variant, так как в ячейках Excel могут быть числовые значения разных типов, и функция IsNumeric тоже проверяет разные типы данных и требует, чтобы ее аргументы были объявлены как Variant.

    Эта функция выполняет деление значений двух ячеек рабочего листа Excel. Перед делением проверяются два блока условий:

    • Если делимое или делитель не являются числом, функция возвращает значение: «Ошибка: Делимое и Делитель должны быть числами!», и производится принудительный выход из функции оператором Exit Function.
    • Если делитель равен нулю, функция возвращает значение: «Ошибка: деление на ноль!», и производится принудительный выход из функции оператором Exit Function.

    Если проверяемые условия не выполняются (возвращают значение False) производится деление чисел и функция возвращает частное (результат деления).

    Вы можете скопировать к себе в стандартный модуль эту функцию и она станет доступна в разделе «Определенные пользователем» Мастера функций. Попробуйте вставить функцию «Деление» в ячейку рабочего листа с помощью Мастера и поэкспериментируйте с ней.

    Практического смысла функция «Деление» не имеет, но она хорошо демонстрирует как объявляются, создаются и работают пользовательские функции в VBA Excel. А еще она поможет продемонстрировать, как добавлять к функциям и аргументам описания. С полноценной пользовательской функцией вы можете ознакомиться здесь.

    Добавление описания функции

    В списке функций, выводимом Мастером, невозможно добавить или отредактировать их описание. Список макросов позволяет добавлять процедурам описание, но в нем нет функций. Проблема решается следующим образом:

    • Запустите Мастер функций, посмотрите, как отображается имя нужной функции и закройте его.
    • Откройте список макросов и в поле «Имя макроса» впишите имя пользовательской функции.
    • Нажмите кнопку «Параметры» и в открывшемся окне добавьте или отредактируйте описание.
    • Нажмите кнопку «OK», затем в окне списка макросов — «Отмена». Описание готово!

    Добавление описания на примере функции «Деление»:

    Добавление описания пользовательской функции в окне «Параметры макроса»
    Добавление описания пользовательской функции

    Описание функции «Деление» в диалоговом окне Мастера функций «Аргументы функции»:

    Описание пользовательской функции в диалоговом окне «Аргументы функции»
    Описание пользовательской функции в окне «Аргументы функции»

    С помощью окна «Список макросов» можно добавить описание самой функции, а ее аргументам нельзя. Но это можно сделать, используя метод Application.MacroOptions.

    Метод Application.MacroOptions

    Метод Application.MacroOptions позволяет добавить пользовательской функции описание, назначить сочетание клавиш, указать категорию, добавить описания аргументов и добавить или изменить другие параметры. Давайте рассмотрим возможности этого метода, используемые чаще всего.

    Пример кода с методом Application.MacroOptions:

    • ИмяПодпрограммы — любое уникальное имя, подходящее для наименования процедур.
    • ИмяФункции — имя функции, параметры которой добавляются или изменяются.
    • Описание функции — описание функции, которое добавляется или изменяется.
    • Название категории — название категории в которую будет помещена функция. Если параметр Category отсутствует, пользовательская функция будет записана в раздел по умолчанию — «Определенные пользователем». Если указанное Название категории соответствует одному из названий стандартного списка, функция будет записана в него. Если такого Названия категории нет в списке, будет создан новый раздел с этим названием и функция будет помещена в него.
    • «Описание 1», «Описание 2», «Описание 3», … — описания аргументов в том порядке, как они расположены в объявлении пользовательской функции.

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

    Сейчас с помощью метода Application.MacroOptions попробуем изменить описание пользовательской функции «Деление» и добавить описания аргументов.

    После однократного запуска этой подпрограммы получаем следующий результат:

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

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

    Содержание рубрики VBA Excel по тематическим разделам со ссылками на все статьи.

    80 комментариев для “VBA Excel. Пользовательская функция (синтаксис, компоненты)”

    1. Полезная статья, но у Вас там ошибка в функции лишнее „As Variant“

    2. Евгений

      Асхаб, в строке
      Function Деление(Делимое As Variant, Делитель As Variant) As Variant
      объявляются типы данных двух аргументов и возвращаемого значения функции.
      Хотя, так тоже будет работать:
      Function Деление(Делимое, Делитель)
      типы данных Variant применятся по умолчанию (протестировано в Excel 2016).

    3. Николай

      Сделаю ремарку для тех, кто попал сразу на эту страницу, поиском: запись вида

      Application.MacroOptions _
      Macro:=»Деление», _ и т.д. с символом подчеркивания означает перенос, а на самом деле вся запись делается в одну строку. Иначе возникает синтаксическая ошибка.

      Или я чего то еще не знаю?! :-)))

      1. Евгений

        Привет, Николай!
        Скопируйте в стандартный модуль код функции «Деление» и код процедуры «ИзменениеОписания». Запустите однократно код «ИзменениеОписания». Вы увидите, что все работает, и мастер функций начнет открывать функцию «Деление» с описанием.

    4. Как сделать в функции с неопределенным количеством аргументов, чтобы в окне где в поля ставятся значения эти аргументы именовались с порядковыми номерами? Как в функции СЦЕПИТЬ текст1 текст2 текст3 и т.д.

      1. Евгений

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

        не получится. Но есть возможность, если максимальное количество аргументов не велико, объявить их все сразу как необязательные:

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

    5. > Метод Application.MacroOptions не работает в Личной книге макросов, но и здесь можно найти решение.

      Почему же не работает? А так?
      Application.MacroOptions _
      Macro:=»PERSONAL.XLSB!Деление», _

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

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

    6. Александр

      Добрый день,
      у меня если создаю функцию нескольких переменных не позволяет определять в экселе после = переменные функции. Почему-то не воспринимает запятую как разделитель переменных. Например, а1=(A1, .. дальше ругается на запятую.
      Как быть в этом случае?

      1. Евгений

        Добрый день, Александр!
        В формулах на рабочем листе в качестве разделителя используется точка с запятой.

        Пример функции в программном модуле:

        В ячейку она записывается так:

    7. Александр

      вот добавляю простейшую функцию с двумя переменными

      при вызове функции =F1(N10;N11) пишет #ССЫЛКА!

      1. Евгений

        Так не будет работать. F1 – это ключевое слово, обозначающее адрес ячейки. Замените его на Ф1 – все заработает.

    8. Александр

      Я пробовал разные варианты. С одной переменной функции нормально вызываются и работают. С двумя и больше почему-то все время выдают ошибку.

      1. Евгений

        Попробуйте, все-таки, не использовать в названиях функций сочетания букв и цифр, обозначающих ячейки.

        А мой пример:

        тоже вызывает ошибку?

    9. Александр

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

      Если s cсылается на диапазон с формулами, то возникают ошибки. и если заменяешь Set c=s.value тоже дает ошибку.

      1. Евгений

        Добрый день, Александр!
        У вас уже есть переменная s, которую вы дублируете переменной c. Возможно из-за этого возникают ошибки. Попробуйте переменную c заменить на s.

        1. Александр

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

          1. Евгений

            Переменная диапазона (as Range) содержит ссылку на присвоенный ей диапазон, и любые действия с этой переменной приводят к тем же действиям с диапазоном на рабочем листе.

            1. Александр

              спасибо,
              а если диапазон еще не присвоен переменной, то ее можно использовать или только в цикле For Each?

              1. Евгений

                Если диапазон переменной диапазона (As Range) еще не присвоен, она содержит значение Nothing и какое-либо ее использование не представляется возможным.

    10. Александр

      Получается, что пока переменная типа Range объявлена, но конкретный диапазон не присвоен, ее использовать нельзя. А какое-то относительное задание диапазона возможно?
      И еще такой вопрос, а само значение функции можно в самой функции использовать? Там, например, присваивать каким-то диапазонам или передавать в другие ячейки? Я вот попробовал, чтобы значение функции вывелось в ячейке, где я вставляю свою пользовательскую функцию, и скопировалось в соседнюю, но ничего не получилось.

    11. Александр

      Я правильно понимаю, что если задана пользовательская функция, то она вставляет значение только в ту ячейку, куда она записывается. Другие ячейки при этом не могут редактироваться и выдается ошибка?

      1. Евгений

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

    12. Добрый день,
      я сделал такую простую пользовательскую функцию

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

      Вот в произвольную ячейку вставляю значение и выдает 0. При этом программа почему-то внутри функции неправильно определяет длину строки диапазона rr.
      C чем это может быть связано?

      1. Евгений

        Переменные myR1 и myR2 существуют только в процедуре, их нет на рабочем листе, поэтому формула не работает. Можно сделать так:

        1. Александр

          Так это получается, что нужно каждый раз так ссылаться на адрес и даже если это обычные переменные? Или это только для объектов типа Range такое?

          1. Евгений

            В обычных переменных должен содержаться адрес.

    13. А как вставлять так, чтобы вставлялся в комментарии как часть программного кода?

      1. Евгений

        Код можно вставлять так:
        <pre class="lang:vb decode:true " >
        Здесь код VBA
        </pre>

    14. Александр

      Добрый день, у меня возникла еще такая проблема. Я сделал функцию, как вы советовали.

      Она работает в процедуре. Но программа доходит до такого цикла:

      и на строчке Cells(i + 4, j).Value = i перескакивает на выполнение функции и зацикливается. Тут вроде нет никаких общих обозначений, но почему-то происходит такой переход. Что это может быть? Может какие-то обозначения нельзя использовать?

      1. Евгений

        Привет, Александр!
        Такое может быть, если ячейка Cells(i + 4, j) является аргументом функции Pairs, размещенной на рабочем листе.

        1. Александр

          Я убрал пока пользовательскую функцию. Но мне выдает теперь ошибку
          Error 2029 в строке23 If Cells(w + 4, q + n + 6 + i).Value + 1 = m Then
          А что это может быть за ошибка?

          1. Евгений

            Возможно, в ячейке Cells(w + 4, q + n + 6 + i) находится формула =Pairs, а так как она отключена, ячейка при пересчете возвращает значение #ИМЯ?.

            1. Александр

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

        2. Александр

          Я разобрался кажется, что происходит.
          Программа когда вставляет в ячейку Cells(i + 4, j).Value = i где i=1 пишет, что вставила значение True.
          А как можно избавиться от этого? и гарантированно вставлять в ячейку число, а не логическую переменную?

            1. Александр

              а ок. Спасибо! Значит ошибка еще где-то.

          1. Александр

            Я даже попробовал сделать такое преобразование. Все равно выдает True.

            1. Евгений

              выдает результат сравнения Cells(i + 4, j).Value с i, то есть True или False.

    15. Александр

      Я еще обнаружил такое, что при присвоении значения переменной из массива, значение не присваивается.
      В том коде, что я пересылал строка21 w = Arr(ai) для ai=2 Arr(ai)=3,но w почему-то равно 1.
      Это с чем может быть связано?

      1. Евгений

        Может быть два варианта:

        Проверить можно так:

    16. Александр

      Arr (1 to n) as Integer
      x = Arr(i)
      а вот это правильный способ присвоения переменной значения из массива? Тут нет каких-то скрытых нюансов?

      1. Евгений

        i не должен выходить за пределы 1 to n, других нюансов нет.

    17. Александр

      Если я задаю прямо
      Arr (1 to n) as Integer
      Arr(1) обозначает первый элемент или второй в массиве?

        1. Александр

          Спасибо, не нашел пока ошибки. Перепишу заново, по-другому процедуру.
          А скажите, индекс в цикле For i=1 to n всегда пробегая все значения после завершения цикла имеет значение i=n+1?

    Обсуждение закрыто.