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. Евгений

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

    2. Николай

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

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

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

      1. Евгений

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

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

      1. Евгений

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

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

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

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

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

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

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

    5. Александр

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

      1. Евгений

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

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

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

    6. Александр

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

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

      1. Евгений

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

    7. Александр

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

      1. Евгений

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

        А мой пример:

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

    8. Александр

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

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

      1. Евгений

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

        1. Александр

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

            1. Александр

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

              1. Евгений

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

    9. Александр

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

    10. Александр

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

      1. Евгений

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

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

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

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

      1. Евгений

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

        1. Александр

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

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

    13. Александр

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

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

      и на строчке 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, а так как она отключена, ячейка при пересчете возвращает значение #ИМЯ?.

        2. Александр

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

          1. Александр

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

            1. Евгений

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

    14. Александр

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

      1. Евгений

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

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

    15. Александр

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

    16. Александр

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

        1. Александр

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

    Добавить комментарий

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