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

Вероятно, одним из самых крутых преимуществ изучения VBA является возможность создавать свои собственные функции.

В Excel более 450 функций, некоторые из них очень полезны в повседневной работе. Но Excel дает вам возможность создать собственную функцию с помощью VBA. Да, ты прав. USER‌ DEFINED Функция, сокращенно UDF, или вы также можете назвать ее пользовательской функцией VBA.

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

Я рад сообщить вам, что это ПОЛНОЕ РУКОВОДСТВО, которое поможет вам создать свою первую пользовательскую функцию с помощью VBA, и кроме того, я поделился некоторыми примерами ПОЛЬЗОВАТЕЛЬСКИХ ФУНКЦИЙ, которые помогут вам вдохновиться.

1. Здесь я буду использовать слова «пользовательская функция», «пользовательская функция» и «UDF» взаимозаменяемо. Так что оставайтесь со мной, в ближайшие несколько минут вы станете рок-звездой VBA.
2. Чтобы создать код для пользовательской функции VBA, вам необходимо его написать, вы не можете записать его с помощью средства записи макросов .

Почему вам следует создать собственную функцию Excel

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

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

1. Когда для этого нет функции

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

2. Замените сложную формулу

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

3. Если вы не хотите использовать SUB Routine

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

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

Хорошо, тогда смотри. Я разделил весь процесс на три этапа:

1. Объявите свою процедуру как функцию
2. Определите его аргументы и их тип данных
3. Добавьте код для расчета желаемого значения

Но позвольте мне дать вам:

Вам нужно создать функцию, которая может возвращать название дня из значения даты. Итак, у нас есть функция, которая возвращает номер дня недели, но не имя. Ты понимаешь, что я говорю? Да?

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

1. Прежде всего, откройте редактор Visual Basic с помощью сочетания клавиш ALT + F11 или перейдите на вкладку «Разработчик » и просто нажмите кнопку «Visual Basic».
   
2. Следующее, что нужно сделать, это вставить модуль, поэтому щелкните правой кнопкой мыши окно проекта VBA, затем перейдите к вставке и нажмите «Модуль». (ВНИМАНИЕ: необходимо вводить ПОЛЬЗОВАТЕЛЬСКУЮ ФУНКЦИЮ только в стандартных модулях. Модули «Лист» и «Эта рабочая книга» представляют собой модуль особого типа, и если вы вводите пользовательскую функцию в этих двух модулях, Excel не распознает, что вы создаете пользовательскую функцию) .
   
3. Третье — определить имя функции, и здесь я использую «myDayName». Итак, вам нужно написать «Функция mydayName». Почему функция перед именем? Когда вы создаете функцию VBA, использование слова «Функция» указывает Excel, что этот код следует рассматривать как функцию (обязательно прочтите область действия UDF перед сообщением).
4. После этого вам нужно определить аргументы для вашей функции. Поэтому вставьте скобки и напишите «InputDate As Date». Здесь InputDate — это имя аргумента, а дата — его тип данных. Всегда лучше определить тип данных для аргумента.
   
5. Теперь закройте скобки и напишите «Как строка». Здесь вы определяете тип данных результата, возвращаемого функцией, и, поскольку вы хотите, чтобы название дня было текстовым, его тип данных должен быть «Строка». Если вы хотите, чтобы результатом было нечто иное, чем строка, убедитесь, что вы соответствующим образом установили тип данных. (myDayName(InputDate As Date) как строковая функция).
   
6. В конце нажмите ENTER. На этом этапе задано имя вашей функции, ее аргумент, тип данных аргумента и тип данных функции, и в вашем модуле есть что-то вроде ниже:
   
7. Теперь в «Функция» и «Конечная функция» вам нужно определить расчет или можно сказать работу этой UDF. В Excel есть функция рабочего листа под названием «Текст», и мы здесь используем ее. И для этого вам нужно написать код ниже и с помощью этого кода определить значение, которое должна возвращать функция. myDayName = WorksheetFunction.Text(InputDate, «дддддд»)
   
8. Теперь закройте редактор VB, вернитесь к рабочему листу и в ячейке B2 введите «=myDayName(A2)». Нажмите Enter, и вы получите название дня.
   

Поздравляем! Вы только что создали свою первую пользовательскую функцию. Это время истинной Радости. Не так ли? Напишите «Радость» в разделе комментариев.

Как работает эта функция и возвращаемое значение в ячейке

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

• Вы вводите его в ячейку как функцию и указываете входное значение.
• Excel запускает код функции и использует указанное вами значение.
• У вас есть результат в ячейке.

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

Поскольку вы указали «InputDate» в качестве аргумента функции и когда вы вводите функцию в ячейку и указываете дату, VBA принимает это значение даты и передает его текстовой функции, которую вы использовали в коде.

А в примере, который я упомянул выше, дата, указанная в ячейке A1, — 01 января 2019 года.

После этого функция ТЕКСТ преобразует эту дату в день, используя код формата «дддддд», который вы уже упомянули в коде функции. И этот день, возвращаемый функцией ТЕКСТ, присваивается «myDayName».

Итак, если результатом функции ТЕКСТ является вторник, это значение будет присвоено «myDayName».

И здесь работа функции подходит к концу. «myDayName» — это имя функции, поэтому любое значение, присвоенное «myDayName», будет значением результата, и функция, которую вы вставили в рабочий лист, вернет его в ячейку.

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

Как улучшить UDF навсегда

Что ж, вы знаете, как создать собственную функцию VBA.

СЕЙЧАС…

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

Цель…

Что делать, если указанное вами значение не является датой? Что делать, если ячейка, на которую вы ссылаетесь, пуста? Могут быть и другие возможности, но я уверен, что вы меня поняли.

ВЕРНО? Итак, давайте попробуем улучшить эту пользовательскую функцию, которая может решить вышеуказанные проблемы. ХОРОШИЙ. Во-первых, вам нужно изменить тип данных аргумента и использовать:

 InputDate As Variant

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

 If InputDate = "" Then myDayName = ""

Это сделает функцию пустой, если ячейка, на которую вы ссылаетесь, пуста.

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

Код будет:

 If IsDate(InputDate) = False Then myDateName = ""

Здесь я использую пробел для обоих условий, чтобы при наличии больших данных вы могли легко отфильтровать значения, в которых входное значение недопустимо. Итак, после добавления приведенных выше условий код будет выглядеть так:

 Function myDayName(InputDate As Variant) As String If InputDate = "" Then myDayName = "" Else If IsDate(InputDate) = False Then myDateName = "" Else myDayName = WorksheetFunction.Text(InputDate, "dddddd") End If End If End Function

А вот как это работает сейчас: я уверен, что вы еще можете внести изменения в эту функцию, но я уверен, что вы четко понимаете мою точку зрения.

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

На данный момент вы примерно знаете, как создать функцию VBA в Excel. Но как только оно у вас появится, вам нужно знать, как его использовать. И в этой части поста я поделюсь с вами, как и где его можно использовать. Итак, приступим.

1. Просто в электронной таблице

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

Вы также можете ввести пользовательскую функцию из библиотеки функций. Перейдите на вкладку «Формула» ➜ «Вставить функцию» ➜ «Определяется пользователем».

Из этого списка вы можете выбрать UDF, который хотите вставить.

2. Использование других подпроцедур и функций

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

 Sub todayDay() MsgBox "Today is " & myDayName(Date) End Sub

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

3. Доступ к функциям из другой книги

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

• Сначала вам необходимо сохранить файл (в котором находится код пользовательской функции) как надстройку.
• Для этого перейдите на вкладку «Файл» ➜ «Сохранить как» ➜ «Надстройки Excel (.xalm).

• После этого дважды щелкните надстройку и установите ее.

Вот и все. Теперь вы можете использовать все функции VBA в любой книге.

Различные способы создания пользовательской функции VBA [продвинутый уровень]

К этому моменту вы знаете, как создать пользовательскую функцию в VBA. Но дело в том, что когда мы используем встроенные функции, они имеют разные типы аргументов.

Итак, в этом разделе этого руководства вы узнаете, как создать UDF с различными типами аргументов.

• Без каких-либо аргументов
• С одним аргументом
• С несколькими аргументами
• Использование массива в качестве аргумента

… Движение вперед.

1. Без всяких аргументов

Помните такие функции, как СЕЙЧАС и СЕГОДНЯ, где не нужно вводить аргумент?

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

Давайте создадим пользовательскую функцию, которая сможет возвращать местоположение текущего файла. И вот код:

 Function myPath() As String Dim myLocation As String Dim myName As String myLocation = ActiveWorkbook.FullName myName = ActiveWorkbook.Name If myLocation = myName Then myPath = "File is not saved yet." Else myPath = myLocation End If End Function

Эта функция возвращает путь к месту, где хранится текущий файл, и если книга нигде не сохранена, она отобразит сообщение «Файл еще не сохранен».

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

Основное правило создания функции без аргументов — это код, в котором не нужно ничего вводить.

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

И в этой функции у вас то же самое.

ActiveWorkbook.FullName возвращает расположение файла, а ActiveWorkbook.Name возвращает имя. Вам не нужно ничего вводить.

2. С одним аргументом

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

 Function giveMeURL(rng As Range) As String On Error Resume Next giveMeURL = rng.Hyperlinks(1).Address End Function

Теперь в этой функции у вас есть только один аргумент.

Когда вы вводите это в ячейку, а затем указываете ячейку, в которой есть гиперссылка, она возвращает URL-адрес гиперссылки. Теперь в этой функции основную работу выполняют:

 rng.Hyperlinks(1).Address

Но номер — это то, что вам нужно указать. Скажите «Легко» в разделе комментариев, если создание UDF кажется вам простым.

3. С несколькими аргументами

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

Давайте рассмотрим пример: вы хотите удалить отдельные буквы из текстовой строки и сохранить остальную часть.

Итак, у вас есть такие функции, как RIGHT и LEN, которые вы собираетесь использовать в этой пользовательской функции. Но здесь нам это не нужно. Все, что нам нужно, это пользовательская функция с использованием VBA.

Итак, вот функция:

 Function removeFirstC(rng As String, cnt As Long) As String removeFirstC = Right(rng, Len(rng) - cnt) End Functio

Хорошо, тогда смотри:

В этой функции у вас есть два аргумента:

• rng: В этом аргументе вы должны указать ячейку, из которой вы хотите удалить первый символ текста.
• cnt: А в аргументе нужно указать количество удаляемых символов (если вы хотите удалить из текста несколько символов).

Когда вы вводите его в ячейку, он работает следующим образом:

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

Если вы подумаете о функции, которую мы только что создали в приведенном выше примере, где у вас есть два разных аргумента, то оба необходимы. И если вы пропустите что-либо из этого, вы получите подобную ошибку.

Теперь, если подумать логически, созданная нами функция — это удаление первого символа. Но здесь нужно указать количество удаляемых символов. Я считаю, что этот аргумент должен быть необязательным и принимать его по умолчанию.

Что вы думаете?

Скажите «Да» в разделе комментариев, если вы согласны со мной в этом.

Хорошо, тогда смотри. Чтобы сделать аргумент необязательным, просто добавьте перед ним «Необязательный». Просто так:

Но важно, чтобы ваш код работал со значением этого аргумента или без него. Итак, наш новый код для той же функции будет выглядеть так: Теперь в коде, если вы игнорируете указание второго аргумента.

4. Используйте массив в качестве аргумента

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

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

 Function addNumbers(CellRef As Range) Dim Cell As Range For Each Cell In CellRef If IsNumeric(Cell.Value) = True Then Result = Result + Cell.Value End If Next Cell addNumbers = Result End Function

В приведенном выше коде функции мы использовали целочисленный диапазон A1:A10 вместо одного значения или ссылки на ячейку.

Используя цикл FOR EACH, он проверит каждую ячейку в диапазоне и суммирует значение, если ячейка содержит число.

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

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

1. Аудитория

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

Но функция по умолчанию является общедоступной, если вы не сделаете ее частной. Во всех рассмотренных нами примерах все они являются общедоступными.

2. Частный

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

Допустим, если у вас есть UDF в «Модуле1», вы можете использовать его только в процедурах, которые есть в «Модуле1». И он не появится в списке функций рабочего листа (когда вы используете знак = и пытаетесь ввести имя), но вы все равно можете использовать его, введя его имя и указав аргументы.

Ограничения определяемой пользователем функции [UDF]

UDF очень полезны. Но они ограничены в определенных ситуациях. Вот несколько вещей, которые я хочу, чтобы вы обратили внимание и помнили при создании пользовательской функции в VBA.

• Вы не можете редактировать, удалять или форматировать ячейки и диапазоны с помощью настраиваемой функции.
• Также невозможно перемещать, переименовывать, удалять или добавлять листы в книгу.
• Измените значение другой ячейки.
• Он также не может изменить параметры среды.

Есть ли разница между встроенной функцией и функцией, определяемой пользователем?

Я рад, что ты спросил. Что ж, чтобы ответить на этот вопрос, я хочу поделиться некоторыми моментами, которые, по моему мнению, важны для вас.

1. Медленнее, чем встроенные. Если вы сравните скорость встроенных функций и функций VBA, вы обнаружите, что первые быстрее. Причина этого в том, что встроенные функции написаны на C++ или FORTRAN.
2. Сложность обмена файлами. Мы часто обмениваемся файлами по электронной почте и через облако. Если вы используете какую-либо пользовательскую функцию, вам необходимо поделиться этим файлом в формате «xlam», чтобы кто-то другой также мог использовать вашу пользовательскую функцию.

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

Заключение

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

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

Но теперь скажи мне одну вещь.

UDF полезны, как вы думаете?

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

Связанные руководства

• Список функций VBA – объяснено на примерах
• Как запустить макрос в Excel
• Как использовать оператор IF в VBA
• Как работать с диапазонами и ячейками в VBA
• Добавьте новый лист, используя код VBA