Создание пользовательской функции (UDF) в Excel VBA (полное руководство)

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

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

В этом руководстве я расскажу все о создании и использовании пользовательских функций в VBA.

Если вы заинтересованы в изучении VBA простым способом, ознакомьтесь с моими Онлайн-обучение по Excel VBA.

Что такое функциональная процедура в VBA?

Функциональная процедура - это код VBA, который выполняет вычисления и возвращает значение (или массив значений).

Используя процедуру Function, вы можете создать функцию, которую можно использовать на листе (как и любую обычную функцию Excel, такую ​​как SUM или VLOOKUP).

Когда вы создали процедуру Function с помощью VBA, вы можете использовать ее тремя способами:

1. Как формула на листе, где она может принимать аргументы в качестве входных данных и возвращать значение или массив значений.
2. Как часть кода подпрограммы VBA или кода другой функции.
3. В условном форматировании.

Хотя на листе уже есть более 450 встроенных функций Excel, вам может потребоваться настраиваемая функция, если:

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

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

Функция Vs. Подпрограмма в VBA

«Подпрограмма» позволяет вам выполнять набор кода, в то время как «Функция» возвращает значение (или массив значений).

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

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

С помощью настраиваемой функции вы можете использовать ее в отдельном столбце, и она может возвращать ИСТИНА, если значение в ячейке отрицательное, и ЛОЖЬ, если оно положительное. С помощью функции вы не можете изменить свойства объекта. Это означает, что вы не можете изменить цвет ячейки с помощью самой функции (однако вы можете сделать это, используя условное форматирование с помощью пользовательской функции).

Когда вы создаете определяемую пользователем функцию (UDF) с помощью VBA, вы можете использовать эту функцию на листе, как и любую другую функцию. Подробнее об этом я расскажу в разделе «Различные способы использования пользовательской функции в Excel».

Создание простой пользовательской функции в VBA

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

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

Функция GetNumeric (CellRef As String) as Long Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If IsNumeric (Mid (CellRef, i, 1)) Then Result = Result & Mid (CellRef, i, 1) Далее я GetNumeric = Результат Конечная функция

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

Ниже показано, как эта функция - GetNumeric - можно использовать в Excel.

Теперь, прежде чем я расскажу вам, как эта функция создается в VBA и как она работает, вам следует знать несколько вещей:

• Когда вы создаете функцию в VBA, она становится доступной во всей книге, как и любая другая обычная функция.
• Когда вы вводите имя функции, за которым следует знак равенства, Excel покажет вам имя функции в списке соответствующих функций. В приведенном выше примере, когда я ввел = Get, Excel показал мне список с моей настраиваемой функцией.

Я считаю, что это хороший пример, когда вы можете использовать VBA для создания простой в использовании функции в Excel. Вы можете сделать то же самое с формулой (как показано в этом руководстве), но это становится сложным и трудным для понимания. С этой UDF вам нужно передать только один аргумент, и вы получите результат.

Анатомия пользовательской функции в VBA

В приведенном выше разделе я дал вам код и показал, как функция UDF работает на листе.

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

Функция GetNumeric (CellRef As String) as Long 'Эта функция извлекает числовую часть из строки Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If IsNumeric (Mid (CellRef, i, 1)) Then Result = Результат и середина (CellRef, i, 1) Далее i GetNumeric = Результат Конечная функция

Первая строка кода начинается со слова - Функция.

Это слово сообщает VBA, что наш код является функцией (а не подпрограммой). За словом Function следует название функции - GetNumeric. Это имя, которое мы будем использовать на листе для использования этой функции.

• Имя функции не может содержать пробелов. Кроме того, вы не можете назвать функцию, если она противоречит имени ссылки на ячейку. Например, вы не можете назвать функцию ABC123, поскольку она также относится к ячейке на листе Excel.
• Вы не должны давать своей функции то же имя, что и существующая функция. Если вы сделаете это, Excel предпочтет встроенную функцию.
• Вы можете использовать подчеркивание, если хотите разделять слова. Например, Get_Numeric - допустимое имя.

За именем функции следуют аргументы в скобках. Это аргументы, которые потребуются нашей функции от пользователя. Это точно так же, как аргументы, которые нам нужно передать встроенным функциям Excel. Например, в функции СЧЁТЕСЛИ есть два аргумента (диапазон и критерий)

В скобках необходимо указать аргументы.

В нашем примере аргумент только один - CellRef.

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

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

Обратите внимание, что функция указана как тип данных «Строка». Это сообщит VBA, что результатом формулы будет тип данных String.

Хотя я могу использовать здесь числовой тип данных (например, Long или Double), это ограничит диапазон чисел, которые он может возвращать. Если у меня есть длинная строка из 20 чисел, которую мне нужно извлечь из общей строки, объявление функции как Long или Double приведет к ошибке (так как число будет вне допустимого диапазона). Поэтому я сохранил тип выходных данных функции как String.

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

Третья строка кода объявляет переменную StringLength как целочисленный тип данных. Это переменная, в которой мы храним значение длины строки, которая анализируется формулой.

Четвертая строка объявляет переменную Result как тип данных String. Это переменная, в которой мы будем извлекать числа из буквенно-цифровой строки.

Пятая строка присваивает длину строки во входном аргументе переменной StringLength. Обратите внимание, что «CellRef» относится к аргументу, который будет задан пользователем при использовании формулы на рабочем листе (или использовании ее в VBA - что мы увидим позже в этом руководстве).

Шестая, седьмая и восьмая строки являются частью цикла For Next. Цикл выполняется столько раз, сколько символов присутствует во входном аргументе. Этот номер задается функцией LEN и присваивается переменной StringLength.

Таким образом, цикл идет от «1 до Stringlength».

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

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

Последняя строка кода - End Function. Это обязательная строка кода, которая сообщает VBA, что здесь заканчивается код функции.

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

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

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

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

Создание функции в VBA без аргументов

На листе Excel у нас есть несколько функций, которые не принимают аргументов (например, RAND, TODAY, NOW).

Эти функции не зависят от каких-либо входных аргументов. Например, функция СЕГОДНЯ вернет текущую дату, а функция СЛЧИС вернет случайное число от 0 до 1.

Вы можете создать такую ​​же функцию и в VBA.

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

Функция WorkbookName () As String WorkbookName = ThisWorkbook.Name Конечная функция

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

Эта функция присваивает функции значение ThisWorkbook.Name, которое возвращается, когда функция используется на рабочем листе.

Если файл был сохранен, он возвращает имя с расширением файла, иначе он просто дает имя.

Однако у вышесказанного есть одна проблема.

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

При желании вы можете принудительно выполнить пересчет с помощью сочетания клавиш - Control + Alt + F9.

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

В приведенном ниже коде функция пересчитывается при каждом изменении рабочего листа (как и другие аналогичные функции рабочего листа, такие как функция СЕГОДНЯ или СЛУЧАЙ).

Функция WorkbookName () As String Application.Volatile True WorkbookName = ThisWorkbook.Name Конечная функция

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

Создание функции в VBA с одним аргументом

В одном из разделов выше мы уже видели, как создать функцию, которая принимает только один аргумент (функция GetNumeric, описанная выше).

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

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

Функция ConvertToUpperCase (CellRef As Range) ConvertToUpperCase = UCase (CellRef) Конечная функция

Эта функция использует функцию UCase в VBA для изменения значения переменной CellRef. Затем он присваивает значение функции ConvertToUpperCase.

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

Создание функции в VBA с несколькими аргументами

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

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

Функция GetDataBeforeDelimiter (CellRef As Range, Delim As String) as String Dim Result As String Dim DelimPosition As Integer DelimPosition = InStr (1, CellRef, Delim, vbBinaryCompare) - 1 Result = Left (CellRef, DelimPosition) GetDataBeforeDelimiter = Result End Function

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

Обратите внимание, что для каждого аргумента вы можете указать тип данных. В приведенном выше примере CellRef был объявлен как тип данных диапазона, а Delim был объявлен как тип данных String. Если вы не укажете какой-либо тип данных, VBA считает, что это вариантный тип данных.

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

Затем он проверяет положение разделителя с помощью функции INSTR в VBA. Эта позиция затем используется для извлечения всех символов перед разделителем (с помощью функции LEFT).

Наконец, он присваивает результат функции.

Эта формула далека от совершенства. Например, если вы введете разделитель, которого нет в тексте, это приведет к ошибке. Теперь вы можете использовать функцию ЕСЛИОШИБКА на листе, чтобы избавиться от ошибок, или вы можете использовать приведенный ниже код, который возвращает весь текст, когда не может найти разделитель.

Функция GetDataBeforeDelimiter (CellRef As Range, Delim As String) as String Dim Result As String Dim DelimPosition As Integer DelimPosition = InStr (1, CellRef, Delim, vbBinaryCompare) - 1 If DelimPosition <0 Then DelimPosition = Len (CellRef) Result = Left ( CellRef, DelimPosition) GetDataBeforeDelimiter = Результат Конечная функция

Мы можем дополнительно оптимизировать эту функцию.

Если вы введете текст (из которого вы хотите извлечь часть перед разделителем) непосредственно в функции, это приведет к ошибке. Давай… попробуй!

Это происходит, поскольку мы указали CellRef как тип данных диапазона.

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

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

Следующий код сделает это:

Функция GetDataBeforeDelimiter (CellRef, Delim) As String Dim Result As String Dim DelimPosition As Integer DelimPosition = InStr (1, CellRef, Delim, vbBinaryCompare) - 1 Если DelimPosition <0, то DelimPosition = Len (CellRef) Result = Left (CellRef, DelimPosition) GetDataBeforeDelimiter = Результат Конечная функция

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

В Excel есть много функций, для которых некоторые аргументы необязательны.

Например, легендарная функция ВПР имеет 3 обязательных аргумента и один необязательный аргумент.

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

Но необязательные аргументы не бесполезны. Они позволяют вам выбирать из множества вариантов.

Например, в функции VLOOKUP, если вы не укажете четвертый аргумент, VLOOKUP выполнит приблизительный поиск, а если вы укажете последний аргумент как FALSE (или 0), то будет выполнено точное совпадение.

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

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

Функция только с необязательным аргументом

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

Но мы можем создать его с помощью VBA.

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

Функция CurrDate (необязательный fmt в качестве варианта) Dim Result If IsMissing (fmt) Then CurrDate = Format (Date, "dd-mm-yyyy") Else CurrDate = Format (Date, "dd mmmm, yyyy") End If End Function

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

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

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

Функция CurrDate (необязательный fmt в качестве варианта) Dim Result If IsMissing (fmt) Then CurrDate = Format (Date, "dd-mm-yyyy") ElseIf fmt = 1 Then CurrDate = Format (Date, "dd mmmm, yyyy") Else CurrDate = CVErr (xlErrValue) Конец, если Конец функции

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

Функция с обязательными и необязательными аргументами

Мы уже видели код, извлекающий числовую часть из строки.

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

Приведенный ниже код создает функцию, которая извлекает текстовую часть из строки. Если необязательный аргумент ИСТИНА, он дает результат в верхнем регистре, а если необязательный аргумент ЛОЖЬ или опущен, он дает результат как есть.

Функция GetText (CellRef As Range, Optional TextCase = False) As String Dim StringLength As Integer Dim Result As String StringLength = Len (CellRef) For i = 1 To StringLength If Not (IsNumeric (Mid (CellRef, i, 1))) Then Result = Result & Mid (CellRef, i, 1) Next i Если TextCase = True Then Result = UCase (Result) GetText = Result End Function

Обратите внимание, что в приведенном выше коде мы инициализировали значение TextCase как False (смотрите в скобках в первой строке).

Таким образом мы убедились, что необязательный аргумент начинается со значения по умолчанию, которое равно FALSE. Если пользователь указывает значение как ИСТИНА, функция возвращает текст в верхнем регистре, а если пользователь указывает необязательный аргумент как ЛОЖЬ или опускает его, то возвращается текст как есть.

Создание функции в VBA с массивом в качестве аргумента

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

Вы также можете создать функцию, которая может принимать массив в качестве аргумента. В функциях листа Excel есть много функций, которые принимают аргументы массива, такие как СУММ, ВПР, СУММЕСЛИ, СЧЁТЕСЛИ и т. Д.

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

Функция AddEven (CellRef as Range) Тусклая ячейка как диапазон для каждой ячейки в CellRef Если IsNumeric (Cell.Value), то, если Cell.Value Mod 2 = 0, то результат = результат + Cell.Value, конец, если конец, если следующая ячейка, AddEven = результат, конец Функция

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

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

В приведенном выше коде я указал аргумент CellRef как Range (который может принимать массив в качестве входных данных). Здесь также можно использовать вариантный тип данных.

В коде есть цикл For Each, который просматривает каждую ячейку и проверяет, есть ли число нет. Если это не так, ничего не происходит, и он переходит в следующую ячейку. Если это число, он проверяет, четное оно или нет (с помощью функции MOD).

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

Создание функции с неопределенным числом аргументов

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

Примером такой функции рабочего листа является функция СУММ. Вы можете указать ему несколько аргументов (например, этот):

= СУММ (A1; A2: A4; B1: B20)

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

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

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

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

Функция AddArguments (ParamArray arglist () как вариант) Для каждого аргумента в списке аргументов AddArguments = AddArguments + arg Next arg End Function

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

Обратите внимание, что в качестве аргумента можно использовать только одно значение, ссылку на ячейку, логическое значение или выражение. Вы не можете указать массив в качестве аргумента. Например, если один из ваших аргументов - D8: D10, эта формула выдаст вам ошибку.

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

Функция AddArguments (ParamArray arglist () как вариант) Для каждого аргумента в списке аргументов Для каждой ячейки в аргументе AddArguments = AddArguments + Cell Next Cell Next arg End Function

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

Цель здесь - показать вам, как работает ParamArray, чтобы вы могли разрешить неопределенное количество аргументов в функции. Если вам нужна лучшая функция, чем та, которая создана в приведенном выше коде, используйте функцию СУММ на листе.

Создание функции, возвращающей массив

До сих пор мы видели функции, возвращающие одно значение.

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

Формулы массива также доступны в виде встроенных функций на листах Excel. Если вы знакомы с формулами массива в Excel, вы должны знать, что они вводятся с помощью Control + Shift + Enter (а не только Enter). Вы можете узнать больше о формулах массива здесь. Если вы не знаете о формулах массива, не волнуйтесь, продолжайте читать.

Давайте создадим формулу, которая возвращает массив из трех чисел (1,2,3).

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

Функция ThreeNumbers () как вариант Dim NumberValue (от 1 до 3) NumberValue (1) = 1 NumberValue (2) = 2 NumberValue (3) = 3 ThreeNumbers = NumberValue Конечная функция

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

Переменная NumberValue объявлена ​​как массив из 3 элементов. Он содержит три значения и назначает их функции «Три числа».

Вы можете использовать эту функцию на листе, введя функцию и нажав клавиши Control + Shift + Enter (удерживая клавиши Control и Shift, затем нажмите Enter).

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

= МАКС (Три числа ())

Используйте указанную выше функцию с помощью Control + Shift + Enter. Вы заметите, что результат теперь равен 3, так как это наибольшие значения в массиве, возвращаемом функцией Max, которая получает три числа в результате нашей пользовательской функции - ThreeNumbers.

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

Функция Месяцы () как вариант Dim MonthName (от 1 до 12) MonthName (1) = «Январь» Имя месяца (2) = «Февраль» Имя месяца (3) = «Март» Имя месяца (4) = «Апрель» Имя месяца (5) = «May» MonthName (6) = «июнь» MonthName (7) = «июль» MonthName (8) = «август» MonthName (9) = «сентябрь» MonthName (10) = «октябрь» MonthName (11) = «ноябрь» "MonthName (12) =" декабрь "Месяцы = MonthName Окончание функции

Теперь, когда вы вводите функцию = Месяцы () в листе Excel и используете Control + Shift + Enter, она вернет весь массив названий месяцев. Обратите внимание, что в ячейке отображается только январь, поскольку это первое значение в массиве. Это не означает, что массив возвращает только одно значение.

Чтобы показать вам, что он возвращает все значения, сделайте следующее: выберите ячейку с формулой, перейдите к строке формул, выберите всю формулу и нажмите F9. Это покажет вам все значения, которые возвращает функция.

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

= ИНДЕКС (Месяцы (); СТРОКА ())

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

Таким образом, тот же код, в котором мы создаем функцию «Месяцы», станет короче, как показано ниже:

Функция Месяцы () как вариант Месяцы = массив («январь», «февраль», «март», «апрель», «май», «июнь», _ «июль», «август», «сентябрь», «октябрь» , «Ноябрь», «декабрь») Завершение функции

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

Обратите внимание, что все созданные выше функции возвращают горизонтальный массив значений. Это означает, что если вы выберете 12 горизонтальных ячеек (скажем, A1: L1) и введете формулу = Месяцы () в ячейку A1, она даст вам все названия месяцев.

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

Вы можете сделать это, используя формулу ТРАНСПОРТ на рабочем листе.

Просто выберите 12 вертикальных ячеек (смежных) и введите формулу ниже.

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

Функция может иметь две области видимости - Общественные или Частный.

• А Публичная сфера означает, что функция доступна для всех листов в книге, а также для всех процедур (подпрограмм и функций) во всех модулях книги. Это полезно, когда вы хотите вызвать функцию из подпрограммы (мы увидим, как это делается в следующем разделе).
• А Частная сфера означает, что функция доступна только в том модуле, в котором она существует. Вы не можете использовать его в других модулях. Вы также не увидите его в списке функций на листе. Например, если имя вашей функции - «Месяцы ()», и вы вводите функцию в Excel (после знака =), она не покажет вам имя функции. Однако вы все равно можете использовать его, если введете имя формулы.

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

Ниже приведена функция, которая является частной функцией:

Частная функция WorkbookName () As String WorkbookName = ThisWorkbook.Name Конечная функция

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

Приведенный ниже код сделает эту функцию общедоступной. Это также будет отображаться на листе.

Функция WorkbookName () As String WorkbookName = ThisWorkbook.Name Конечная функция

Различные способы использования пользовательской функции в Excel

Создав пользовательскую функцию в VBA, вы можете использовать ее по-разному.

Давайте сначала рассмотрим, как использовать функции на листе.

Использование UDF в рабочих листах

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

Все, что вам нужно сделать, это ввести имя функции, и оно появится в intellisense.

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

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

• Перейдите на вкладку "Данные".
• Щелкните опцию «Вставить функцию».
• В диалоговом окне «Вставить функцию» выберите категорию «Определено пользователем». Этот параметр отображается только в том случае, если у вас есть функция в редакторе VB (и эта функция является общедоступной).
• Выберите функцию из списка всех общедоступных функций, определяемых пользователем.
• Щелкните кнопку ОК.

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

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

= ВЕРХНИЙ (Имя книги ())

Использование пользовательских функций в процедурах и функциях VBA

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

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

Ниже представлена ​​функция, которая возвращает имя книги.

Функция WorkbookName () As String WorkbookName = ThisWorkbook.Name Конечная функция

Приведенная ниже процедура вызывает функцию, а затем отображает имя в окне сообщения.

Sub ShowWorkbookName () MsgBox WorkbookName End Sub

Вы также можете вызвать функцию из другой функции.

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

Функция WorkbookName () As String WorkbookName = ThisWorkbook.Name Конечная функция

Функция WorkbookNameinUpper () WorkbookNameinUpper = UCase (WorkbookName) Конечная функция

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

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

Есть несколько способов сделать это:

1. Создание надстройки
2. Функция сохранения в личной книге макросов
3. Ссылка на функцию из другой книги.

Создание надстройки

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

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

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

• Перейдите на вкладку «Файл» и нажмите «Сохранить как».
• В диалоговом окне «Сохранить как» измените тип «Сохранить как» на .xlam. Имя, которое вы назначаете файлу, будет именем вашей надстройки. В этом примере файл сохраняется под именем GetNumeric.
  • Вы заметите, что путь к файлу, в который он сохраняется, автоматически изменяется. Вы можете использовать значение по умолчанию или изменить его, если хотите.
• Откройте новую книгу Excel и перейдите на вкладку Разработчик.
• Щелкните параметр Надстройки Excel.
• В диалоговом окне «Надстройки» найдите сохраненный файл и нажмите «ОК».

Теперь надстройка активирована.

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

Сохранение функции в личной книге макросов

Личная книга макросов - это скрытая книга в вашей системе, которая открывается всякий раз, когда вы открываете приложение Excel.

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

По умолчанию в вашем Excel нет личной книги макросов. Вам необходимо создать его, записав макрос и сохранив его в личной книге макросов.

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

Ссылка на функцию из другой книги

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

Предположим, у вас есть рабочая тетрадь с названием ‘Рабочая тетрадь с формулой », и у него есть функция с именем ‘GetNumeric ’.

Чтобы использовать эту функцию в другой книге (пока Рабочая тетрадь с формулой открыто), вы можете использовать следующую формулу:

= ’Рабочая тетрадь с формулой’! GetNumeric (A1)

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

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

Использование оператора функции выхода VBA

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

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

Функция GetNumericFirstThree (CellRef As Range) As Long Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If J = 3 Then Exit Function If IsNumeric (Mid (CellRef, i, 1)) Then J = J + 1 Result = Result & Mid (CellRef, i, 1) GetNumericFirstThree = Result End If Next i End Function

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

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

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

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

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

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

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

Установите точку останова, чтобы можно было проходить каждую строку по очереди. Чтобы установить точку останова, выберите нужную строку и нажмите F9 или щелкните серую вертикальную область слева от строк кода. Любой из этих методов вставит точку останова (вы увидите красную точку в серой области).

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

Отладка пользовательской функции с помощью Debug.Print в коде

Вы можете использовать оператор Debug.Print в своем коде, чтобы получить значения указанных переменных / аргументов в непосредственном окне.

Например, в приведенном ниже коде я использовал Debug.Print, чтобы получить значение двух переменных - «j» и «Result».

Функция GetNumericFirstThree (CellRef As Range) As Long Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If J = 3 Then Exit Function If IsNumeric (Mid (CellRef, i, 1)) Then J = J + 1 Result = Result & Mid (CellRef, i, 1) Debug.Print J, Result GetNumericFirstThree = Result End If Next i End Function

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

Встроенные функции Excel против. Функция, определяемая пользователем VBA

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

• Встроенные функции намного быстрее, чем функции VBA.
• Когда вы создаете отчет / информационную панель с помощью функций VBA и отправляете его клиенту / коллеге, им не нужно беспокоиться о том, включены ли макросы или нет. В некоторых случаях клиенты пугаются, увидев предупреждение на желтой полосе (которое просто просит их включить макросы).
• Благодаря встроенным функциям Excel вам не нужно беспокоиться о расширениях файлов. Если у вас есть макросы или пользовательские функции в книге, вам необходимо сохранить ее в формате .xlsm.

Хотя есть много веских причин использовать встроенные функции Excel, в некоторых случаях лучше использовать пользовательскую функцию.

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

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

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

Ниже приведены инструкции по размещению кода функции GetNumeric в книге.

1. Перейдите на вкладку Разработчик.
2. Выберите вариант Visual Basic. Это откроет редактор VB в бэкэнде.
3. На панели Project Explorer в редакторе VB щелкните правой кнопкой мыши любой объект книги, в которую вы хотите вставить код. Если вы не видите Project Explorer, перейдите на вкладку View и нажмите Project Explorer.
4. Перейдите во вкладку "Вставить" и нажмите "Модуль". Это вставит объект модуля для вашей книги.
5. Скопируйте и вставьте код в окно модуля.

Вам также могут понравиться следующие руководства по Excel VBA:

• Работа с ячейками и диапазонами в Excel VBA.
• Работа с листами в Excel VBA.
• Работа с книгами с использованием VBA.
• Как использовать циклы в Excel VBA.
• События Excel VBA - простое (и полное) руководство
• Использование операторов IF Then Else в VBA.
• Как записать макрос в Excel.
• Как запустить макрос в Excel.
• Как отсортировать данные в Excel с помощью VBA (пошаговое руководство).
• Функция Excel VBA InStr - объяснение с примерами.