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

Содержание

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

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

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

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

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

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

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

Содержание

  1. Зачем вам создавать пользовательскую функцию Excel?
  2. Как создать свою первую пользовательскую функцию в Excel
  3. Как эта функция работает и возвращает значение в ячейке
  4. Как улучшить пользовательскую функцию?
  5. Как использовать пользовательскую функцию VBA
  6. Различные способы создания пользовательской функции VBA
  7. Область действия определяемой пользователем функции
  8. Ограничения пользовательской функции
  9. Заключение

Зачем вам создавать пользовательскую функцию 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. Но…

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

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

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

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

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

InputDate As Variant

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

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

Создаем свою первую функцию в Excel




Возможности Excel »


Макросы »
Создание функции

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

Скачать

Что такое функция в VBA?

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

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

  2. Как часть вашей подпрограммы VBA. В процедуре Sub или внутри других функций.

  3. В правилах условного форматирования.

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

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

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

В чем отличие процедуры (Sub) от функции (Function)?

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

Для демонстрации приведем пример. Например есть ряд чисел от 1 до 100 и необходимо отделить четные от нечетных.

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

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

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

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


Function Цифры(Текст As String) As Long
    Dim i As Long
    Dim result As String
    
    For i = 1 To Len(Текст)
        If IsNumeric(Mid(Текст, i, 1)) Then result = result & Mid(Текст, i, 1)
    Next
    
    Цифры = CLng(result)
End Function

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

Теперь посмотрим как функция работает, попробуем использовать ее на листе:

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

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

  • Когда вы ввели знак «=» и начинаете вводить имя функции, то Excel выводит все совпадения и показывает не только встроенные функции, но и пользовательские.

Разбираем функцию пошагово

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


Function Цифры(Текст As String) As Long

Слово Function говорит о начале функции, далее идет ее название, в нашем случае Цифры.

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

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

  • Вы можете использовать символ подчеркивания, если хотите разделить слова. Например, Сумма_Прописью является допустимым именем.

После названия в круглых скобках  описываются аргументы функции. По аналогии со встроенными функциями Excel. В нашем случае используется единственный аргумент Текст. После названия аргумента мы указали As String, это означает, что наш аргумент — текстовое значение или ссылка на ячейку, содержащее текстовое значение.  Если вы не укажете тип данных, VBA рассмотрит его как Variant (что означает, что вы можете использовать любой тип данных, VBA его определит самостоятельно).

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

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


Dim i As Long
Dim result As String

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

Задача функции — пройти по всем символам переменной Текст и сохранить только те, что являются цифрами. Поэтому начнем цикл с 1 и до последнего символа.


For i = 1 To Len(Текст)

Len — функция, которая определяет количество символов.

Основная строка функции — это проверка является ли очередной символ текста цифрой и если да — то сохранение его в переменной result


If IsNumeric(Mid(Текст, i, 1)) Then result = result & Mid(Текст, i, 1)

Для этого нам потребуется функция IsNumeric — она возвращает True если текст — число и False в противном случае.

Функция Mid берет из аргумента Текст i-ый символ (значение 1, указывает что функция Mid берет только 1 символ)/

Функция Next — закрывает цикл For тут все понятно.


Цифры = CLng(result)

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

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

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

Скачать

Рекомендуем к прочтению

Как записать макрос в Excel? Пошаговая инструкция

Аргументы функции на VBA

Функции VBA и массивы значений

Настраиваем редактор кода VBE

Как работать с Excel через VBA или объектная модель Excel

Комментарии:

Please enable JavaScript to view the comments powered by Disqus. comments powered by Disqus

Что такое функция Excel // Глоссарий Excel // PerfectXL

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

Список всех функций Excel

Но Excel предлагает сотни других предустановленных формул:

13 ABS

ACCRINT

ACCRINTM
ACOS
ACOSH
ACOT
ACOTH
ADDRESS
AGGREGATE
AMORDEGRC
AMORLINC
AND
ARABIC
AREAS
ASC
ASIN
ASINH
ATAN
ATAN2
ATANH
AVEDEV
AVERAGE
AVERAGEA
AVERAGEIF
AVERAGEIFS
BAHTTEXT
BASE
BESSELI
BESSELJ
BESSELK
BESSELY
BETA.DIST
BETA.INV
BETADIST
BETAINV
BIN2DEC
BIN2HEX
BIN2OCT
BINOM.DIST
BINOM.DIST .RANGE
BINOM.INV
BINOMDIST
BITAND
BITLSHIFT
BITOR
BITRSHIFT
BITXOR
CALL
CEILING
CEILING.MATH
CEILING 3 AR1 90 CH1AR3 900 CH1AR3

13 CHIDIST

CHIINV
CHISQ. DIST
CHISQ.DIST.RT
CHISQ.INV
CHISQ.INV.RT
CHISQ.TEST
CHITEST
CHOOSE
CLEAN
CODE
COLUMN
COLUMNS
COMBIN
COMBINA
COMPLEX
CONCATENATE
CONFIDENCE
Доверие. Норм
Доверие. Т
Преобразование
COREL
COS
COSH
COT
COTH
COUNT
COUNTA
COUNTBLANK
COUTIF
COUNTIFS
CoupdayBS
COPTANCS
CUPDAY
COUPDAY
COUPDAY
COUPDANC
COUPDANC
COUPDANC
COUPDANC
COUPDANC
COUPDANC.0013 COUPNUM
COUPPCD
COVAR
COVARIANCE.P
COVARIANCE.S
CRITBINOM
CSC
CSCH
CUBEKPIMEMBER
CUBEMEMBER
CUBEMEMBERPROPERTY
CUBERANKEDMEMBER
CUBESET
CUBESETCOUNT
CUBEVALUE
CUMIPMT
CUMPRINC
DATE
DATEVALUE
DAVERAGE
DAY
DAYS
DAYS360
DB
DBCS
DCOUNT
DCOUNTA
DDB
DEC2BIN
DEC2HEX
DEC2OCT
DECIMAL
ГРАДУСЫ
DELTA
DEVSQ
DGET
DISC
DMAX
DMIN
DOLLAR
DOLLARDE
DOLLARFR
DPRODUCT
DSTDEV
DSTDEVP
DSUM
DURATION
DVAR
DVARP
EDATE
EFFECT
ENCODEURL
EOMONTH
ERF
ERF. PRECISE
ERFC
ERFC.PRECISE
ERROR .TYPE
EUROCONVERT
EVEN
EXACT
EXP
EXPON.DIST
EXPONDIST
F.DIST
F.DIST.RT
F.INV
F.INV.RT
F.TEST 90DOCT

FACT

3 FDIST
FILTERXML
FIND, FINDB
FINV
Fisher
Fisherinv
Фиксированная
Пол
Пол. Мат
Пол.
FORECAST
Formulagext

FERIED
FERIED
.
GAMMAINV
GAMMALN
GAMMALN.PRECISE
GAUSS
GCD
GEOMEAN
GESTEP
GETPIVOTDATA
GROWTH
HARMEAN
HEX2BIN
HEX2DEC
HEX2OCT
HLOOKUP
HOUR
HYPERLINK
HYPGEOM.DIST
HYPGEOMDIST
IF
IFERROR
IFNA
IMABS
IMAGINARY
IMARGUMENT
IMCONJUGATE
IMCOS
IMCOSH
IMCOT
IMCSC
IMCSCH
IMDIV
IMEXP
IMLN
IMLOG10
IMLOG2
IMPOWER
IMPRODUCT
IMREAL
IMSEC
IMSECH
IMSIN
IMSINH
IMSQRT
IMSUB
IMSUM
IMTAN
ИНДЕКС
НЕПРЯМОЙ
ИНФОРМАЦИЯ
INT
INTERCEPT
INTRATE 90913 IPMT0013 IRR
ISBLANK
ISERR
ISERROR
ISEVEN
ISFORMULA
ISLOGICAL
ISNA
ISNONTEXT
ISNUMBER
ISO. CEILING
ISODD
ISOWEEKNUM
ISPMT
ISREF
ISTEXT
KURT
LARGE
LCM
LEFT, LEFTB
LEN, LENB
LINEST
LN
log
log10
logest
loginv
lognorm.dist
lognorm.inv
lognormdist
Lookup
Нижний
Match
Max
Maxa
Mdetererm
МД МАЙСКИЙ.0013 MEDIAN
MID, MIDB
MIN
MINA
MINUTE
MINVERSE
MIRR
MMULT
MOD
MODE
MODE.MULT
MODE.SNGL
MONTH
MROUND
MULTINOMIAL
MUNIT
N
NA
NEGBINOM.DIST
NEGBINOMDIST
NETWORKDAYS
NetworkDays.intl
NOMINAL
NORM.DIST
NORM.INV
NORM.S.DIST
NORM.S.INV
Normdist
Norminv
NORMSDIST
NORMSINV
НЕ
СЕЙЧАС
NPER
NPVV
Номерв.0013 OCT2BIN
OCT2DEC
OCT2HEX
ODD
ODDFPRICE
ODDFYIELD
ODDLPRICE
ODDLYIELD
OFFSET
OR
PDURATION
PEARSON
PERCENTILE
PERCENTILE. EXC
PERCENTILE.INC
PERCENTRANK
PERCENTRANK.EXC
PERCENTRANK.INC
PERMUT
PERMUTATIONA
PHI 100013 PV
Квартиль
QUARTILE.EXC
Квартиль.inc
Коэффициент
Радианы
Рэнд
Ранд -Между
Рейнг
Рейнг. AVG
Rank.eq
.
ROUND
ROUNDDOWN
ROUNDUP
ROW
ROWS
RRI
RSQ
RTD
SEARCH, SEARCHB
SEC
SECH
SECOND
SERIESSUM
SHEET
SHEETS
SIGN
SIN
SINH
SKEW
SKEW.P
SLN
SLOPE
SMALL
SQL.REQUEST
SQRT
SQRTPI
STANDARDIZE
STDEV
STDEV.P
STDEV.S
STDEVA
STDEVP
STDEVPA
STEYX
SUBSTITUTE
SUBTOTAL
SUM
SUMIF
SUMIFS
SUMPRODUCT
SUMSQ
SUMX2MY2
SUMX2PY2
SUMXMY2
SYD
T
T.DIST
T.DIST.2T
T.DIST.RT
T.INV
T.INV.2T
T.TEST
TANBILL
TANBILLESTEST

T. BILLEST

T.BILLEST

T.BILLEST
T.BILLEST
T.BILLEST
T.Bill.
ТБИЛЛИЭЛД
TDIST
TEXT
TIME
TIMEVALUE
TINV
TODAY
TRANSPOSE
TREND
TRIM
TRIMMEAN
TRUE
TRUNC
TTEST
TYPE
UNICHAR
UNICODE
UPPER
VALUE
VAR
VAR.P
VAR.S
VARA
VARP
VARPA
VDB
Vlookup
Webservice
Weekday
Weeknum
Weibull
Weibull.dist
Workday
Workday.intl
XIRR
XNPV
XOR

GONFRAC

Год.0013 YIELDMAT
Z.TEST
ZTEST

Риск уязвимых функций

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

Переведенные функции Excel

Сообщения в блогах для лучшего использования Excel:

Функция VBA — вызов, возвращаемое значение и параметры

В этой статье

  • Создание функций
    • Один аргумент
    • Несколько аргументов
    • Дополнительные аргументы
    • Значение аргумента по умолчанию
    • ByVal и ByRef
  • Выход из функции
  • Использование функции из листа Excel

    • Это руководство научит вас создавать и использовать функции с параметрами и без них в VBA

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

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

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

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

     Функция GetValue() как целое число
   ПолучитьЗначение = 50
End Function

    Если бы вы запускали функцию, она всегда возвращала бы значение 50.

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

     Функция GetRange() как диапазон
  Установить GetRange = Диапазон ("A1: G4")
End Function

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

    Вызов функции из подпроцедуры

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

    Всегда будет возвращаться значение 50.

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

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

    Создание функций

    Один аргумент

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

     Функция ConvertKilosToPounds (dblKilo as Double) as Double
   ConvertKiloToPounds = дблкило*2,2
Завершить функцию

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

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

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

    Несколько аргументов

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

     Функция CalculateDayDiff(Date1 as Date, Date2 as Date) as Double
   ВычислитьDayDiff = Date2-Date1
End Function

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

    Необязательные аргументы

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

     Функция CalculateDayDiff(Date1 as Date, необязательно Date2 as Date) as Double
'проверить вторую дату и, если ее нет, сделать Date2 равной сегодняшней дате. 
   Если Дата2=0, то Дата2 = Дата
'рассчитать разницу
   ВычислитьDayDiff = Date2-Date1
End Function

    Кодирование VBA стало проще

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

    Узнать больше

    Значение аргумента по умолчанию

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

     Функция CalculateDayDiff(Date1 as Date, необязательная Date2 as Date="02/06/2020") as Double
'рассчитать разницу
   ВычислитьDayDiff = Date2-Date1
Конечная функция

    ByVal и ByRef

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

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

     Функция GetValue(ByRef intA As Integer) Как Integer
   интервал = интервал * 4
   ПолучитьЗначение = intA
End Function

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

     Функция GetValue(intA As Integer) Как Integer
   интервал = интервал * 4
   ПолучитьЗначение = intA
End Function

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

     Подтестовые значения()
   Dim intVal как целое число
'заполнить переменную значением 10
   Интервал = 10
'запустить функцию GetValue и показать значение в непосредственном окне
   Debug. Print GetValue (intVal)
'показать значение переменной intVal в непосредственном окне
   Debug.Print intVal
End Sub

    Обратите внимание, что окна отладки оба раза показывают значение 40. Когда вы передаете в функцию переменную IntVal, в функцию передается значение 10, которое умножается на 4. Использование ключевого слова ByRef (или полное его отсутствие) ИСПРАВЛЯЕТ значение переменной IntVal. Это проявляется, когда вы сначала показываете результат функции в непосредственном окне (40), а затем значение переменной IntVal в окне отладки (тоже 40).

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

     Функция GetValue(ByVal intA As Integer) Как Integer
интервал = интервал * 4
ПолучитьЗначение = intA
End Function

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

    Exit Function

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

     Функция FindNumber(strSearch As String) Как целое число
   Dim i как целое число
'перебрать каждую букву в строке
   Для i = 1 To Len(strSearch)
   'если буква числовая, вернуть значение функции
      Если IsNumeric(Mid(strSearch, i, 1)) Тогда
         FindNumber= Mid(strSearch, i, 1)
   'затем выйдите из функции
         Выход из функции
      Конец, если
   Следующий
   НайтиЧисло= 0
End Function

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

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

     Sub CheckForNumber()
   Dim NumIs как целое число
'передать текстовую строку в функцию поиска числа
   NumIs = FindNumber("Верхний этаж, 8 Оук Лейн, Техас")
'показать результат в ближайшем окне
   Debug.Print NumIs
End Sub

    Программирование VBA | Генератор кода работает на вас!

    Использование функции из листа Excel

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

    Нажмите на fx , чтобы открыть диалоговое окно «Вставить функцию».

    Выберите Определяется пользователем из списка категорий

    Выберите нужную функцию из доступных Пользовательские функции (UDF).

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

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

     Частная функция CalculateDayDiff(Date1 as Date, Date2 as Date) as Double
   ВычислитьDayDiff = Date2-Date1
Завершить функцию

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

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

    Если вы объявили второй аргумент как Необязательный , вы можете опустить его как на листе Excel, так и в коде VBA.

    Читайте также: