В excel нет функции определено пользователем

ПРИМЕР:

Function VAT(value As Integer) As Integer
 VAT = value * 1.18
End Function

Эта функция возвращает стоимость с НДС.

Для того что бы её применить, необходимо поместить код в стандартный модуль (Alt+F11 — Insert — Module):

После этого функция станет доступной в категории Определённые пользователем:

Далее, её применение ничем не отличается от применения стандартных функций.

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

О том, как добавить краткое описание функций определённых пользователем, смотрите в презентации, которую предоставил пользователь Астроном

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

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

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

1. Открыть редактор языка VBA с помощью комбинации клавиш ALT+F11.
2. В открывшемся окне выбрать пункт Insert и подпункт Module, как показано на рисунке:



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



4. При необходимости можно изменить название модуля.
5. В отличие от макросов, код которых должен находиться между операторами Sub и End Sub, пользовательские функции обозначают операторами Function и End Function соответственно. В состав пользовательской функции входят название (произвольное имя, отражающее ее суть), список параметров (аргументов) с объявлением их типов, если они требуются (некоторые могут не принимать аргументов), тип возвращаемого значения, тело функции (код, отражающий логику ее работы), а также оператор End Function. Пример простой пользовательской функции, возвращающей названия дня недели в зависимости от указанного номера, представлен на рисунке ниже:



6. После ввода представленного выше кода необходимо нажать комбинацию клавиш Ctrl+S или специальный значок в левом верхнем углу редактора кода для сохранения.
7. Чтобы воспользоваться созданной функцией, необходимо вернуться к табличному редактору Excel, установить курсор в любую ячейку и ввести название пользовательской функции после символа «=»:

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

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

1. Создайте новый макрос (нажмите комбинацию клавиш Alt+F8), в появившемся окне введите произвольное название нового макроса, нажмите кнопку Создать:



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



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



4. В качестве «Macro» должна быть передана текстовая строка с названием пользовательской функции, в качестве «Description» — переменная типа String с текстом описания возвращаемого значения, в качестве «ArgumentDescriptions» — массив переменных типа String с текстами описаний аргументов пользовательской функции.
5. Для создания описания пользовательской функции достаточно один раз выполнить созданный выше модуль. Теперь при вызове пользовательской функции (или SHIFT+F3) отображается описание возвращаемого результата и переменной:

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



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

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

Вид исходной таблицы данных:

Каждому работнику полагается 24 выходных дня с выплатой S=N*24/(365-n), где:

• N – суммарная зарплата за год;
• n – число праздничных дней в году.

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

Код примера:


Public Function Otpusknye(summZp As Long, holidays As Long) As Long
If IsNumeric(holidays) = False Or IsNumeric(summZp) = False Then
    Otpusknye = "Введены нечисловые данные"
    Exit Function
ElseIf holidays <= 0 Or summZp <= 0 Then
    Otpusknye = "Отрицательное число или 0"
    Exit Function
Else
    Otpusknye = summZp * 24 / (365 - holidays)
End If
End Function


Сохраним функцию и выполним расчет с ее использованием:

=Otpusknye(B3;C3)

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

Калькулятор расчета калорий в Excel

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

Вид исходной таблицы данных:

Для расчета используем формулу Миффлина — Сан Жеора, которую запишем в коде пользовательской функции с учетом пола участника. Код примера:


Public Function CaloriesPerDay(sex As String, age As Integer, weight As Integer, height As Integer) As Integer
If sex = "женский" Then
    CaloriesPerDay = 10 * weight + 6.25 * height - 5 * age - 161
ElseIf sex = "мужской" Then
    CaloriesPerDay = 10 * weight + 6.25 * height - 5 * age + 5
Else: CaloriesPerDay = 0
End If
End Function


Проверки корректности введенных данных упущены для упрощения кода. Если пол не определен, функция вернет результат 0 (нуль).

Пример расчета для первого участника:

=CaloriesPerDay(B3;C3;D3;E3)

В результате использования автозаполнения получим следующие результаты:

Пользовательская функция для решения квадратных уравнений в Excel

Пример 3. Создать функцию, которая возвращает результаты решения квадратных уравнений для указанных в ячейках коэффициентах a, b и c уравнения типа ax2+bx+c=0.

Вид исходной таблицы:

Для решения создадим следующую пользовательскую функцию:

Код примера:


Public Function SquareEquation(a As Integer, b As Integer, c As Integer) As String
Dim answer1 As String
Dim answer2 As String
If a = 0 Then
    answer1 = "Единственный корень - "
    SquareEquation = answer1 & "(" & -c / b & ")"
ElseIf c = 0 Then
    answer1 = "Единственный корень - "
    SquareEquation = answer1 & "(" & -b / a & ")"
ElseIf b = 0 And c < 0 Then
    answer1 = "Единственный корень - "
    SquareEquation = answer1 & "(" & Sqr(a / c) & ")"
ElseIf b ^ 2 - 4 * a * c >= 0 Then
    answer1 = "Первый корень - "
    answer2 = "Второй корень - "
    SquareEquation = answer1 & "(" & (-b + Sqr(b ^ 2 - 4 * a * c)) / (2 * a) & ")" & "; " & _
         answer2 & "(" & (-b - Sqr(b ^ 2 - 4 * a * c)) / (2 * a) & ")"
Else:
    SquareEquation = "Решений нет"
End If
End Function


Найдем корни первого уравнения:

=SquareEquation(A3;B3;C3)

Выполним расчеты для остальных уравнений. Полученные результаты:

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

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

Хитрости »

1 Май 2011              133682 просмотров

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

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

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

• Основные ограничения функций пользователя
• Как создать функцию пользователя
• Аргументы функции пользователя
• Необязательные аргументы функции пользователя
• Динамическое количество аргументов в функции пользователя(ParamArray)
• Создание формулы массива из UDF или ввод формулы сразу в несколько ячеек
• Как добавить уже созданную функцию в свою книгу
• Обновление расчетов функции пользователя UDF(автопересчет)

1. UDF не может изменять значения других ячеек (с небольшими недокументированными исключениями)
2. UDF не может изменять форматы ячеек либо присваивать форматы (с небольшими недокументированными исключениями)
3. UDF не может изменять так называемые объекты окружения самого Excel. Например, сменить стиль ссылок или параметры вычислений формул, вид курсора и т.п.
4. UDF будет некорректно работать с такими методами как FindNext, SpecialCells, CurrentRegion, CurrentArray, Select, ShowPrecedents и ShowDependents(выделение зависимостей ячеек), Application.GoTo и т.п. Хотя методы вроде Range.End(xlUp), Range.End(xlDown), обычный Find(без FindNext) проблем не вызывают.Подробнее про работу этих методов из UDF можно узнать из статьи: Глюк работы в UDF методов SpecialCells и FindNext
5. UDF может возвращать результат только в ту ячейку, в которой записана сама функция
6. для работы функции пользователя(UDF) обязательно должны быть разрешены макросы

Предполагается, что Вы уже обладаете начальными навыками написания процедур в VBA и умеете создавать эти самые процедуры, хотя бы самые простые.
Т.к. функции пользователя создаются в редакторе VBA, то необходимо сначала перейти в редактор: сочетанием клавиш Alt+F11 или через вкладку Разработчик(Developer) —Visual Basic.
Однако прежде чем читать дальше советую ознакомиться так же со статьей: Что такое модуль? Какие бывают модули?
Основные моменты, которые следует помнить при создании функции пользователя:

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

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

Function ТекущаяДата()
    'присваиваем функции значение, чтобы она вернула его на лист(обязательно!)
    ТекущаяДата = Date 'ТекущаяДата - имя функции и именно ему необходимо передать результат
End Function

Эта функция делает одно — возвращает в ячейку, в которую записана, текущую дату. В ячейке эта функция будет выглядеть так:
=ТекущаяДата()
К записи пользовательских функций в ячейку предъявляются такие же требования, как и к встроенным функциям. Это касается так же и скобок на конце функции, у которой нет аргументов. И так же это означает, что в функцию могут быть переданы наши собственные аргументы

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

Function MySum(vArg1 As Double, vArg2 As Double)
    Dim dblSum as Double
    'получаем сумму двух аргументов
    dblSum = vArg1 + vArg2
    'присваиваем функции значение, чтобы она вернула его на лист(обязательно!)
    MySum = dblSum 'MySum имя функции и именно ему необходимо передать результат
End Function

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

Необязательные аргументы функции пользователя

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

Optional

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

Function SumFiveArgs(arg1 As Double, Optional arg2 As Double, Optional arg3 As Double, Optional arg4 As Double, Optional arg5 As Double)
    Dim dblSum As Double
    dblSum = arg1
    dblSum = dblSum + arg2
    dblSum = dblSum + arg3
    dblSum = dblSum + arg4
    dblSum = dblSum + arg5
    SumFiveArgs = dblSum
End Function

Функция будет работать отлично, даже если передать одно или два числа. Но это только в том случае, если для аргументов у нас заданы строгие типы данных — в примере это Double. Если тип не задан — получим ошибку #ЗНАЧ! (#VALUE!):

Function SumFiveArgs(arg1 As Double, Optional arg2, Optional arg3, Optional arg4, Optional arg5)
    Dim dblSum As Double
    dblSum = arg1
    dblSum = dblSum + arg2
    dblSum = dblSum + arg3
    dblSum = dblSum + arg4
    dblSum = dblSum + arg5
    SumFiveArgs = dblSum
End Function

Можно, конечно, всегда задавать тип данных, как в первом примере. Но стоит учитывать, что для числовых типов данных(Double, Integer, Long) значение по умолчанию будет всегда 0, даже если мы аргумент не передали в функцию(для типа String значение по умолчанию нулевая строка — «»). Это нам не мешает произвести операцию сложения и вычитания. Но операция внутри функции может быть умножением или делением и в этом случае мы получим ошибку или неверный результат:

'функция деления аргументов между собой
Function DivideFiveArgs(arg1 As Double, Optional arg2 As Double, Optional arg3 As Double, Optional arg4 As Double, Optional arg5 As Double)
    Dim dblSum As Double
    dblSum = arg1
    dblSum = dblSum / arg2
    dblSum = dblSum / arg3 'уже здесь получим ошибку "на ноль делить нельзя"
    dblSum = dblSum / arg4
    dblSum = dblSum / arg5
    DivideFiveArgs = dblSum
End Function

'функция перемножения аргументов между собой
Function MultipleFiveArgs(arg1 As Double, Optional arg2 As Double, Optional arg3 As Double, Optional arg4 As Double, Optional arg5 As Double)
    Dim dblSum As Double
    dblSum = arg1
    dblSum = dblSum * arg2
    dblSum = dblSum * arg3 'здесь arg3 равен нулю, значит далее сумма будет тоже равна нулю
    dblSum = dblSum * arg4
    dblSum = dblSum * arg5
    MultipleFiveArgs = dblSum
End Function

Передав меньше аргументов в функцию =DivideFiveArgs(A1;A4) мы получим ошибку #ЗНАЧ!(#VALUE!), которую вызовет деление на ноль внутри кода на третьем аргументе.
А передав меньше аргументов в функцию умножения =MultipleFiveArgs(A1;A4)) — получим в качестве результата 0, т.к. на третьем аргументе умножим общую сумму на аргумент, который равен 0.

Проверять каждый аргумент на равенство нулю(If arg2 = 0 Then) тоже будет неверно — вдруг какой-либо реально переданный аргумент будет действительно равен 0? Будет неверный результат функции. Поэтому, чтобы функции выше заработали правильно — нужна проверка на отсутствие в аргументе значения.
Тут надо знать, что если тип аргумента не указан и сам аргумент в функцию не был передан — то ему назначается особый тип — Missing. Который и дает понять, что аргумент просто не передавался в функцию(Missing в переводе можно представить как «пропущен»). И в VBA для таких случаев есть специальная функция — IsMissing. Тогда можно более гибко манипулировать аргументами(на примере функции с умножением):

Function MultipleFiveArgs(arg1 As Double, Optional arg2, Optional arg3, Optional arg4, Optional arg5)
    Dim dblSum As Double
    dblSum = arg1
    'проверяем, что аргумент передан(NOT IsMISSING)
    If Not IsMissing(arg2) Then
        dblSum = dblSum * arg2
    End If
    If Not IsMissing(arg3) Then
        dblSum = dblSum * arg3
    End If
    If Not IsMissing(arg4) Then
        dblSum = dblSum * arg4
    End If
    If Not IsMissing(arg5) Then
        dblSum = dblSum * arg5
    End If
    MultipleFiveArgs = dblSum
End Function

Как видно — теперь Optional можно использовать вполне эффективно. Но надо помнить одно правило: аргументы, заданные в функции с ключевым Optional должны быть заданы самими последними. Т.е. после них не может идти никаких других обязательных аргументов(без ключевого Optional). Впрочем, в этом случае VBA сообщит нам об этом ошибкой «Expected: Optional», что означает: Ожидался не обязательный аргумент.

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

Function ТекстДоПервогоПробела(Текст As String) As String
    Dim i As Long
    Dim Result As String 'переменная для результата
    i = InStr(1, Текст, " ", 1) 'ищем позицию первого пробела в переданном тексте
    'если пробел есть и он не первый символ в строке
    If i > 1 Then
        Result = Mid(Текст, 1, i - 1) 'получаем текст до первого пробела
    Else
    'если пробела нет - возвращаем всю строку
        Result = Текст
    End If
    'присваиваем результат функции для возврата его на лист
    ТекстДоПервогоПробела = Result
End Function

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

Function ТекстДоПервогоПробела(Текст As String) As String
    ТекстДоПервогоПробела = Split(Текст, " ")(0)
End Function

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

Function ТекстДоУказанногоСимвола(Текст As String, Optional Разделитель As String = " ") As String
    ТекстДоУказанногоСимвола = Split(Текст, Разделитель)(0)
End Function

В данном примере если вызвать функцию так:
=ТекстДоУказанногоСимвола(A1)
то функция будет использовать в качестве разделителя пробел(Optional Разделитель As String = » «). Или можно задать символ разделения напрямую в функции и это может быть как пробел, так и любой другой символ:
=ТекстДоУказанногоСимвола(A1;»;»)

Function SumMultiple(ParamArray args())
    Dim dblSum As Double, arg
    On Error Resume Next
    For Each arg In args
        dblSum = dblSum + arg
    Next
    SumMultiple = dblSum
End Function

Но такая функция может выдать ошибку, если в качестве любого аргумента будет передана не одна единственная ячейка или значение — а диапазон ячеек(A1:A4) или массив({10;20;30}). В этом случае внутри функции обязательно придется определять тип данных внутри ParamArray. Сделать это можно следующим образом:

Function SumMultiple_DiffTypes(ParamArray args())
    Dim dblSum As Double, arg, rc As Range, x
    On Error Resume Next
    For Each arg In args
        Select Case TypeName(arg)
        Case "Range"                     'это диапазон
            'цикл по всем ячейкам
            For Each rc In arg.Cells
                'проверяем, что в ячейке числовой тип данных
                If IsNumeric(rc.Value) Then
                    dblSum = dblSum + rc.Value
                End If
            Next
        Case "Variant()"                 'это произвольный массив({10;20;30})
            'цикл по всем ячейкам
            For Each x In arg
                'проверяем, что это числовой тип данных
                If IsNumeric(x) Then
                    dblSum = dblSum + x
                End If
            Next
        Case "Double", "Long", "Integer" 'это любой числовой тип
            'суммируем
            dblSum = dblSum + arg
        'все остальные типы игнорируем
        End Select
    Next
    SumMultiple_DiffTypes = dblSum
End Function

И в такую функцию может быть передан любой из наиболее распространенных типов данных:
=SumMultiple_DiffTypes({10;20;30};A1:A4;10;C1)
Но и у ParamArray есть недостаток: он не может использоваться одновременно с необязательными аргументами(Optional). Вместе с ParamArray могут быть использованы только обязательные аргументы и они должны обязательно идти ДО ParamArray. Если хоть один будет указан после, то получим ошибку компилятора: «Expected: )». Т.е. ожидалась завершающая скобка функции.

Так же можно применить ParamArray, чтобы указывать «неограниченное» количество аргументов для сцепления значений из ячеек в одну строку с указанным разделителем:

Function ОбъединитьВсеСРазделителем(Разделитель As String, ParamArray Значения()) As String
    Dim result As String, arg, x, rc As Range
    For Each arg In Значения
        Select Case TypeName(arg)
        Case "Range"                     'это диапазон
            'цикл по всем ячейкам
            For Each rc In arg.Cells
                If result = "" Then
                    result = rc.Value
                Else
                    result = result & Разделитель & rc.Value
                End If
            Next
        Case "Variant()"                 'это произвольный массив({"а";"б";"в"})
            'цикл по всем ячейкам
            For Each x In arg
                If result = "" Then
                    result = x
                Else
                    result = result & Разделитель & x
                End If
            Next
        Case Else 'это любой другой тип
            'суммируем
            If result = "" Then
                result = arg
            Else
                result = result & Разделитель & arg
            End If
        End Select
    Next
    ОбъединитьВсеСРазделителем = result
End Function

Пример вызова такой функции с листа(первым обязательно передается разделитель, а далее уже что объединять — любой тип данных):
=ОбъединитьВсеСРазделителем(«; «;A1:A4;C1;»Привет»;{«а»;»б»;»в»})

Создание формулы массива из UDF или ввод формулы сразу в несколько ячеек

Иногда бывает необходимо делать вычисления таким образом, чтобы они возвращались сразу в несколько ячеек. А порой без этого вообще не обойтись. Например, если расчет значения для следующей ячейки напрямую зависит от полученного на предыдущей итерации и видеть надо одновременно значения всех итераций. Например, вывести в несколько строк и столбцов числа от 6 с шагом 2. Стандартными формулами это довольно непросто сделать — ведь придется как-то определять сколько ячеек в каждом столбце и какое последнее число в каждом из столбцов. Через функцию пользователя, созданную как формула массива(подробнее про формулы массива), это сделать проще.
Ниже представлена функция

WriteNumbers

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

'---------------------------------------------------------------------------------------
' Author : Щербаков Дмитрий(The_Prist)
'          Профессиональная разработка приложений для MS Office любой сложности
'          Проведение тренингов по MS Excel
'          https://www.excel-vba.ru
'          info@excel-vba.ru
' Purpose: Функция записывает в ячейки числа от первого заданного(Число) с заданным шагом(Шаг)
'          Вводится сразу в несколько ячеек и ввод завершается сочетанием клавиш Ctrl+Shift+Enter
'---------------------------------------------------------------------------------------
Function WriteNumbers(Число As Double, Шаг As Double)
    Dim aNumbers() 'массив для записи результата
    Dim rResRange As Range
    Dim lr As Long, lc As Long, dblNum As Double
 
    'задаем начальное значение числа - оно равно Шаг
    dblNum = Число
    'определяем кол-во выделенных ячеек, в которые надо вернуть результат
    Set rResRange = Application.Caller
    ReDim aNumbers(1 To rResRange.Rows.Count, 1 To rResRange.Columns.Count)
    'создаем массив результирующих чисел для всех выделенных ячеек
    For lc = 1 To rResRange.Columns.Count
        For lr = 1 To rResRange.Rows.Count
            aNumbers(lr, lc) = dblNum
            dblNum = dblNum + Шаг
        Next
    Next
    'возвращаем результат
    WriteNumbers = aNumbers
End Function

Чтобы правильно применить приведенную UDF, необходимо

• выделить несколько ячеек(например, A1:F10)
• в строку формул ввести нашу UDF: =WriteNumbers(6;2)
• завершить ввод формулы сразу тремя клавишами Ctrl+Shift+Enter

Главное, на что надо обратить внимание — это тип переменной, которая используется для записи результата: aNumbers(). Она обязательно должна быть задана как массив, если мы хотим, чтобы UDF возвращала результат сразу во всем выделенные ячейки и работала как привычная формула массива. В зависимости от решаемой задачи, массив может быть как одномерным горизонтальным или вертикальным, так и многомерным.
В примере выше итоговый массив определяется автоматически при помощи Application.Caller(подробнее про Caller в статье Кто вызвал функцию или процедуру?). Рекомендую всегда делать именно так, чтобы корректно задавать итоговый массив, как бы он ни был задан. Возможно, для написания правильно работающей UDF подобного плана, надо будет чуть более углубленно изучать работу с массивами.

Как добавить уже созданную функцию в свою книгу

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

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

Function ТекущаяДатаВремя()
    ТекущаяДатаВремя = Now 'Now - возвращает текущие дату и время
End Function

Если записать её в таком виде, то после записи в ячейку:
=ТекущаяДатаВремя()
при первой записи будут показаны текущие дата и время. Чтобы эксперимент был более наглядным, лучше перейти в Формат ячеек и выставить для ячейки с функцией формат «ДД.ММ.ГГ ч:мм:сс;@». С небольшим интервалом времени понажимайте клавишу F9, чтобы вызвать пересчет книги. Тогда наглядно будет видно, что при пересчете значение функции не изменяется — секунды «застынут» на том месте, где были при начальном вводе функции. Выделите ячейку с функцией — нажмите F2-Enter. Только тогда значение будет пересчитано. А теперь чуть изменим функцию — добавим ключевой параметр пересчета — Application.Volatile:

Function ТекущаяДатаВремя()
    Application.Volatile True
    ТекущаяДатаВремя = Now 'Now - возвращает текущие дату и время
End Function

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

Function ТекущаяДатаВремя(Optional ДиапазонОбновления As Range = Nothing)
    ТекущаяДатаВремя = Now
End Function

тогда при любом изменении в ячейках аргумента ДиапазонОбновления функция будет пересчитана. При этом использовать хоть как-то сам этот аргумент внутри функции совершенно необязательно. Выглядеть запись такой функции будет так:
=ТекущаяДатаВремя(E:E)
при любом изменении в столбце E функция будет пересчитана.

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

• Как оставить в ячейке только цифры или только текст?
• Как получить текст примечания в ячейку?
• Как скопировать картинку из примечания?
• Как получить адрес гиперссылки из ячейки
• Сцепить много ячеек с указанным разделителем
• Как сцепить несколько значений в одну ячейку по критерию? СцепитьЕсли
• Сравнение текста по части предложения

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

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

    Видеоуроки

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



Access
apple watch
Multex
Power Query и Power BI
VBA управление кодами
Бесплатные надстройки
Дата и время
Записки
ИП
Надстройки
Печать
Политика Конфиденциальности
Почта
Программы
Работа с приложениями
Разработка приложений
Росстат
Тренинги и вебинары
Финансовые
Форматирование
Функции Excel
акции MulTEx
ссылки
статистика

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

Но… Excel дает вам возможность создавать пользовательские функции с помощью VBA.

Да — да, вы не ослышались. Пользовательская функция.

И я могу сказать со 100%-ой уверенностью, что каждый начинающий пользователь VBA хочет научиться создавать пользовательские функции. Не так ли? Кивните, если вы один из них.

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

… так что оставайтесь со мной, вы станете рок-звездой VBA в ближайшие пару минут.

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

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

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

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

1. Когда нет подходящей функции

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

Например, подсчет слов в ячейке, в этом случае пользовательская функция — идеальное решение.

Function MyWordCount(rng As Range) As Integer

MyWordCount = UBound(Split(rng.Value, " "), 1) + 1

End Function

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

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

Пользовательская функция может решить эту проблему.

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

3. Когда вы не хотите использовать SUB

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

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

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

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

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

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

Позвольте мне привести вам простой пример.

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

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

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

• Прежде всего, откройте редактор VB с помощью сочетания клавиш ALT + F11 или перейдите на вкладку «Разработчик» и нажмите кнопку «Visual Basic».

• Вставка модуля: щелкните правой кнопкой мыши в окне проекта VBA, затем перейдите к Insert и после этого нажмите «Module».

• Следующим шагом является определение имени для функции, здесь я использую «myDayName». Поэтому вы должны написать «Function mydayName».

Почему «Function» перед именем?

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

• После этого вам нужно определить аргументы для пользовательской функции. Поэтому вставьте начальные скобки и напишите «InputDate As Date».

• Здесь InputDate — имя аргумента, а date — его тип данных. Всегда лучше определить тип данных для аргумента.

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

• В конце нажмите ENTER.

На этом этапе имя вашей функции, ее аргумент, тип данных аргумента и тип данных функции определены, и у вы должны видеть что-то вроде этого в вашем модуле:

Теперь между «Function» и «End Function» вам нужно сделать расчет.

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

myDayName = WorksheetFunction.Text (InputDate, "dddddd")

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

• Теперь закройте редактор VB и вернитесь к рабочему листу.

• В ячейке B2 введите «= myDayName (A2)», нажмите Enter, и у вы увидите название дня недели.

Поздравляю! Вы только что создали свою первую пользовательскую функцию!

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

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

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

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

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

Давайте чуть медленнее:

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

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

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

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

Вы уже знаете, как создать пользовательскую функцию VBA. Но…

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

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

Могут быть и другие проблемы, но я уверен, что вы поняли, о чем я.

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

Прежде всего, вам нужно изменить тип данных аргумента и использовать:

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

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

If InputDate = "" Then
myDayName = ""

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

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

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

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

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

Функция myDayName (InputDate As Variant) в виде строки

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. На рабочем листе

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

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

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

Из списка можно выбрать функцию, которую хотите вставить.

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

Вы также можете использовать функцию в других функциях или в процедуре «Sub».

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

Sub todayDay()
MsgBox "Сегодня " & myDayName(Date)
End Sub

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

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

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

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

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

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

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

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

1. Без каких-либо аргументов

Помните есть такие функции, как СЕЙЧАС и СЕГОДНЯ, в которых вам не нужно вводить какие-либо аргументы? Вы можете создать пользовательскую функцию, в которой вам тоже не нужно вводить аргумент. Сделаем это на примере:

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

Вот код:

Function myPath() As String
Dim myLocation As String
Dim myName As String
myLocation = ActiveWorkbook.FullName
myName = ActiveWorkbook.Name
If myLocation = myName Then
myPath = "Файл еще не сохранен."
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

rng ​​- это то, что вам нужно указать.

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

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

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

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

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

Итак, посмотрим:

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

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

Когда вы вводите ее в ячейку, она работает примерно так:

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

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

Но можно сделать этот аргумент необязательным, чтобы он принимал значение по умолчанию. Чтобы сделать аргумент необязательным, вам просто нужно добавить «Optional» перед ним.

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, он проверяет каждую ячейку диапазона и суммирует значение, если в ячейке есть число.

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

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

1. Public

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

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

2. Private

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

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

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

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

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

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

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

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

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

Заключение

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

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

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

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

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

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

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

Если вы заинтересованы в изучении VBA простым способом, ознакомьтесь с моими Онлайн-обучение по Excel 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.

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

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

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

Вы можете использовать пользовательскую функцию, как и любую другую функцию в 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.

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

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

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

Личная книга макросов — это скрытая книга в вашей системе, которая открывается всякий раз, когда вы открываете приложение 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 — объяснение с примерами.