Хитрости »
Основные понятия (22)Здесь собраны статьи, в которых разъясняются базовые понятия работы в Excel и VBA, а так же проблемы, с которыми сталкивается большинство начинающих
Сводные таблицы и анализ данных (5) Раздел поможет изучить сводные таблицы и научиться их использовать "на полную"
Графики и диаграммы (4) Раздел поможет научиться создавать диаграммы и графики в Excel, в том числе нестандартные
Работа с VB проектом (10) С помощью статей раздела вы научитесь создавать процедуры программно и выполнять различные операции с объектами самого VBA
Power BI и Power Query (5) Здесь собраны статьи, раскрывающие различные возможности мощнейшего инструмента для визуализаций бизнесс-процессов Power BI и надстройки для Excel Power Query
Условное форматирование (5) Этот раздел поможет поближе познакомиться с Условным форматированием на примерах различных ситуаций
Списки и диапазоны (5) Статьи, посвященные работе не только с выпадающими списками, но и с диапазонами и хитростями их применения в рабочих файлах
Макросы(VBA процедуры) (59) Статьи раздела направлены на изучение VBA с детальным разбором кодов. Множество статей с примерами кодов под всевозможные ситуации с комментариями и пояснениями
Разное (34) Собраны статьи, которые не подходят ни под одну из представленных выше категорий или входят сразу в несколько. Но эти статье не менее полезные!

Что такое функция пользователя(UDF)?

 

Функция пользователя(UDF) - или в дословном переводе Функция, Определенная Пользователем, т.к. в оригинале она звучит как: User Defined Function. Так же их называют пользовательские функции.
Такие функции вызываются через Мастер функций -категория Определенные пользователем (User Defined):

Так что же это за функции такие? Функция пользователя это функция, написанная при помощи языка Visual Basic for Application (VBA) и вызываемая как любая другая функция с листа. Но т.к. эти функции пишутся самостоятельно - можно создать любую функцию, которая будет делать то, что ни одна стандартная функция делать не умеет. Естественно, теперь возникает вопрос как написать такую функцию. Для написания UDF понадобятся хотя бы базовые знания языка VBA. Я в статье опишу лишь принципы создания таких функций и после прочтения вы сможете создать простейшую функцию. Но это никак не означает, что я научу создавать функции на все случаи жизни, ибо это сводится к обучению самому языку программирования. Именно поэтому в статье рассмотрим лишь основные моменты и то, как уже написанные функции применить к своей книге.

Самое главное, что необходимо усвоить - функция пользователя:

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

Как создать функцию пользователя

Предполагается, что Вы уже обладаете начальными навыками написания процедур в VBA и умеете создавать эти самые процедуры, хотя бы самые простые.
Однако прежде чем читать дальше советую ознакомиться так же со статьей: Что такое модуль? Какие бывают модули?
Основные моменты, которые следует помнить при создании функции пользователя:

  • в отличие от процедуры(Sub) функция всегда начинается именно со слова Function, а не Sub;
  • в теле функции всегда должно быть присвоение ей значения, иначе функция не вернет необходимый результат;
  • функция должна располагаться в стандартном модуле или в модуле книги, если Вы планируете вызывать её непосредственно с листа Excel
  • функции пользователя "привязаны" к той книге, в которой созданы и по умолчанию не будут работать в других (для этого надо будет всегда указывать имя книги с функцией). Чтобы созданные функции работали удобно и без проблем в любой книге необходимо книгу с функциями сохранить как надстройку: Как создать свою надстройку?

В приведенном выше коде я упростил стандартную функцию СУММ (SUM) до двух аргументов. Записанная на лист функция будет иметь такой вид: =Mysum(A1;A2), где:
A1 - первый аргумент(vArg1), ссылка на ячейку или число
A2 - второй аргумент(vArg2), ссылка на ячейку или число
Функция вернет #ЗНАЧ! (#VALUE!), если в качестве одного из аргументов передано не числовое значение.
 
Еще одна простая функция, которая вернет строку до первого пробела:

Text - ссылка на ячейку или непосредственно текст, первое слово из которого надо извлечь. Если переданный текст не будет содержать пробелов или это будет число - функция вернет весь текст. Если ячейка будет пустая - функция вернет пусто и не выдаст ошибки.
Эту функцию можно записать и намного короче:

Но в таком виде функция вернет значение ошибки #ЗНАЧ!(#VALUE!), если ячейка с текстом будет пустой. Вдаваться в подробности не буду. Могу лишь написать, что функция VBA Split разбивает указанный текст на отдельные части, используя для разбиения указанный разделитель. И создает из разбитых частей одномерный массив с нижней границей, равной нулю. А функция выше просто возвращает первый элемент этого массива.

 
Как вставить уже имеющийся код функции к себе в книгу

Для начала необходимо создать стандартный модуль. Затем в этот модуль вставить через копирование(или записать вручную) имеющийся код. Все, теперь функция доступна из диспетчера функций в категории Определенные пользователем(User defined), так же можно будет вводить эту функцию напрямую в ячейки той книги, в которой содержится код функции. Чтобы функция заработала очень важно разрешить макросы. Иначе результатом будет ошибка #ИМЯ!(#NAME!)

Если Вы используете версию Excel 2007 и выше, то книгу необходимо будет сохранить с поддержкой макросов: Меню -Сохранить как -Книга Excel с поддержкой макросов.

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


Статья помогла? Сделай твит, поделись ссылкой с друзьями!

Поиск по меткам

Access Multex Outlook Power BI Power Query и Power BI VBA работа в редакторе VBA управление кодами Бесплатные надстройки Дата и время Диаграммы и графики Записки Защита Защита данных Интернет Картинки и объекты Листы и книги Макросы и VBA Настройка Поиск данных Почта Программы Работа с приложениями Работа с файлами Разработка приложений Сводные таблицы Списки Тренинги и вебинары Финансовые Форматирование Формулы и функции Функции Excel Функции VBA Ячейки и диапазоны акции MulTEx вебинар ссылки статьи тренинг
Обсуждение: оставлено 4 коммент.
  1. Ольга:

    Большое спасибо! Очень полезная информация!

  2. Олег:

    Здравствуйте. Все сделал как написано выше, функция работает! Но при создании нового документа этой функции в списке НЕТ. Я что-то упустил или так и должно быть?

    • Димон:

      Так и должно быть, в принципе.
      Модули с функциями привязаны к документу, в котором они созданы.
      Если хотите использовать функции из документа А в документе Б, то просто скопируйте их в Б.
      Так же, может быть, можно просто не закрывать документ А - когда он открыт, его модули доступны.

  3. Станислава:

    Дмитрий, добрый день!
    Извините, если вопрос не подходит в рубрику, но подскажите, пожалуйста! Есть ли возможность функцией записать "подбор параметра"? Если есть, как это сделать?

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

Логин
Наши партнеры
Перейти
Перейти
Счетчики
Анализ сайта

Яндекс.Метрика
© 2016 Excel для всех  Войти