Телевизор 

Встроенные функции Visual Basic. VBA Excel. Пользовательская функция (синтаксис, компоненты) Передача аргументов по значению и по ссылке

Таблица 6.1. Встроенные математические функции
Функция Описание
Abs Абсолютное значение
Atn Арктангенс
Cos Косинус числа
Exp Возвращает число e (2.718282), возведенное в степень аргумента функции.
Fix Отбрасывает дробную часть числа и возвращает целую. В результате для положительных чисел получается число меньшее, чем входное (Fix(2.5) возвратит 2), для отрицательных - большее (Fix(-2.5) возвратит -2)
Int Отбрасывает дробную часть числа и возвращает целую. Для положительных получается число меньшее введенного (Int(2.5) возвратит 2), для отрицательных - так же меньшее (Int(-2.5) возвратит -3).
Log Возвращает натуральный логарифм числа
Rnd Возвращает случайное число типа Single, причем, это число находится между 0 и 1. Для инициализации генератора случайных чисел используйте директиву Randomize - ее надо вызвать до вызова Rnd .
Sgn Функция предназначена для определения знака числа. Если число положительное - она возвращает 1. Для нуля функция возвратит 0, для отрицательного числа -1.
Sin Синус
Sqr Квадратный корень
Tan Тангенс

Давайте рассмотрим пример. Добавим в документ Microsoft Word кнопку, назовем ее cmd_Calc , надпишем ее как Вычисления и добавим следующий код (листинг 6.1 .), иллюстрирующий работу рассмотренных функций.

Dim dblNumber As Double "Переменная, используемая в вычислениях Dim varResult "Переменная типа Variant dblNumber = Val(InputBox("Введите число")) "Вычисляем абсолютное значение введенного числа "Сначала присвоим результат переменной varResult "Далее - выведем подписанный результат в окне "сообщения, воспользуемся знаком "+" для "конкатенации строк, в других случаях "будем вызывать функции непосредственно "в MsgBox"e "Обратите внимание на то, что мы конвертируем "числовые значения в строки с помощью функции Str varResult = Abs(dblNumber) MsgBox ("Абсолютное значение " + _ Str(dblNumber) + " равняется " + Str(varResult)) "Арктангенс MsgBox ("Арктангенс " + _ Str(dblNumber) + " равняется " + _ Str(Atn(dblNumber))) "Косинус MsgBox ("Косинус " + _ Str(dblNumber) + " равняется " + _ Str(Cos(dblNumber))) "e в степени введенного числа MsgBox ("Число e в степени " + _ Str(dblNumber) + " равняется " + _ Str(Exp(dblNumber))) "Функция Fix MsgBox ("Результат работы функции Fiх для " + _ Str(dblNumber) + " равняется " + _ Str(Fix(dblNumber))) "Функция Int MsgBox ("Результат работы функции Int для " + _ Str(dblNumber) + " равняется " + _ Str(Int(dblNumber))) "Натуральный логарифм MsgBox ("Натуральный логарифм " + _ Str(dblNumber) + " равняется " + _ Str(Log(dblNumber))) "Получим несколько случайных чисел "первое число - от 0 до 1 "второе - от 0 до 10. "Третье - от 25 до 100 "Четвертое - целое от 0 до 34 Randomize MsgBox ("Группа случайных чисел: " + _ Str(Rnd()) + ", " + _ Str(Rnd() * 10) + ", " + _ Str(Rnd() * 75 + 25) + ", " + _ Str(Int(Rnd() * 34))) "Функция Sgn MsgBox ("Результат работы Sgn для " + _ Str(dblNumber) + " равняется " + _ Str(Sgn(dblNumber))) "Cинус MsgBox ("Синус " + _ Str(dblNumber) + " равняется " + _ Str(Sin(dblNumber))) "Квадратный корень MsgBox ("Квадратный корень " + _ Str(dblNumber) + " равняется " + _ Str(Sqr(dblNumber))) "Тангенс MsgBox ("Тангенс " + _ Str(dblNumber) + " равняется " + _ Str(Tan(dblNumber))) Листинг 6.1. Обработчик события Click кнопки cmd_Calc

Обратите внимание на алгоритм получения случайного числа, находящегося в определенном диапазоне, с помощью функции Rnd . Предположим, нам нужно получить случайное число от 15 до 40 . Получим, для начала, число от 0 до 40 . Очевидно, что для этого нам понадобится такой вызов: Rnd ()*40 .

Чтобы "поднять" уровень наименьшего случайного числа, возвращаемого выражением, до 15 , сделаем следующее.

Во-первых, вычислим разность 40 и 15 - у нас получится 25 . Значит, чтобы получить случайное число от 0 до 25 , можно использовать вызов Rnd ()*25 .

Во-вторых, прибавим к полученному случайному числу 15 . Теперь выражение для получения случайного числа от 15 до 40 выглядит так: Rnd ()*25+15 .

Проверим это высказывание на правильность. Функция Rnd , как известно, возвращает случайные числа от 0 до 1 . Если функция возвратит 0 - результат вычисления выражения будет равен 15 (0*25+15) . Если функция возвратит 1 - результат будет равен 40 (25*1+15) . Промежуточные значения Rnd дадут искомые случайные числа между 15 и 40 .

Обработчик нажатия кнопки cmd_Len (листинг 6.2 .) будет содержать решение следующей задачи: сообщить пользователю длину введенного текста. Очевидно, для решения этой задачи нам понадобится функция Len .

"Переменная для хранения входной строки Dim str_InpStr As String "Переменная для хранения найденной длины строки Dim lng_StrLen As Long str_InpStr = InputBox("Введите строку") "Вычисляем длину строки lng_StrLen = Len(str_InpStr) MsgBox ("Длина введенной строки: _" + _ str_InpStr + "_ равняется " + Str(lng_StrLen) + _ " символам") Листинг 6.2. Пример использования функции Len

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


Рис. 6.2.

Теперь займемся конверсией символов - функциями LCase и UCase (листинг 6. 3.).

"Переменная для хранения входной строки Dim str_InpStr As String "Переменная для хранения измененной строки Dim str_NewStr As String str_InpStr = InputBox("Введите текст") "В str_NewStr окажется введеная строка "в которой все прописные буквы заменены строчными str_NewStr = LCase(str_InpStr) MsgBox ("Измененная строка: " + str_NewStr) "Теперь в str_NewStr будет та же строка "в которой все буквы стали прописными str_NewStr = UCase(str_InpStr) MsgBox ("Измененная строка: " + str_NewStr) Листинг 6.3. Пример использования функций LCase и UCase

На очереди - вырезание символов - функции Mid , LTrim , Rtrim , Left , Right (листинг 6.4 .) Среди этих функций наиболее мощной является Mid - используя ее, можно делать со строками очень много всего.

"Переменная для хранения входной строки Dim str_InpStr As String "Переменная для хранения вырезанных символов Dim str_NewStr As String "Зададим строку, с которой удобно будет работать str_InpStr = " Здравствуйте, Александр " "Функции удаления пробелов "При выводе строки ставим перед ее началом "и концом символ "_" для того чтобы "лучше было видно наличие "или отсутствие пробелов MsgBox ("Мы работаем с такой строкой: " + _ "_" + str_InpStr + "_") "LTrim - присваиваем результаты работы "переменной и выводим информацию в MsgBox str_NewStr = LTrim(str_InpStr) MsgBox ("Результат работы LTrim: " + _ "_" + str_NewStr + "_") "RTrim MsgBox ("Результат работы RTrim: " + _ "_" + RTrim(str_InpStr) + "_") "Trim MsgBox ("Результат работы Trim: " + _ "_" + Trim(str_InpStr) + "_") "Left - вырезаем из строки str_InpStr 12 "символов предварительно очистив ее "от начальных пробелов str_NewStr = Left(LTrim(str_InpStr), 12) MsgBox ("Первые 12 символов слева: " + _ str_NewStr) "Right - аналогично Вырезаем 9 символов справа str_NewStr = Right(RTrim(str_InpStr), 9) MsgBox ("Первые 9 символов справа: " + _ str_NewStr) "Функция Mid - для начала выведем по одному символу "со второй и пятнадцатой позиции строки "преварительно очищенной от лишних пробелов "в начале и в конце str_NewStr = Mid(Trim(str_InpStr), 2, 1) MsgBox ("Второй символ введенной строки: " + _ str_NewStr) str_NewStr = Mid(Trim(str_InpStr), 15, 1) MsgBox ("Пятнадцатый символ введенной строки: " + _ str_NewStr) "Выведем 5 символов, начиная с 15 символа str_NewStr = Mid(Trim(str_InpStr), 15, 5) MsgBox ("Пять символов строки с 15-й позиции: " + _ str_NewStr) Листинг 6.4. Пример использования функций для вырезания символов

Теперь рассмотрим примеры работы функций

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

Математические операторы

Основные математические операторы VBA перечислены в таблице ниже.

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

Строковые операторы

Основной строковый оператор в Excel VBA – это оператор конкатенации & (слияние):

Операторы сравнения

Операторы сравнения используются для сравнения двух чисел или строк и возвращают логическое значение типа Boolean (True или False). Основные операторы сравнения Excel VBA перечислены в этой таблице:

Логические операторы

Логические операторы, как и операторы сравнения, возвращают логическое значение типа Boolean (True или False). Основные логические операторы Excel VBA перечислены в таблице ниже:

В приведённой выше таблице перечислены не все логические операторы, доступные в VBA. Полный список логических операторов можно найти на сайте Visual Basic Developer Center .

Встроенные функции

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

Функция Действие
Abs Возвращает абсолютную величину заданного числа.
  • Abs(-20) возвращает значение 20;
  • Abs(20) возвращает значение 20.
Chr Возвращает символ ANSI, соответствующий числовому значению параметра.
  • Chr(10) возвращает перенос строки;
  • Chr(97) возвращает символ a .
Date Возвращает текущую системную дату.
DateAdd Добавляет определённый временной интервал к заданной дате. Синтаксис функции:

DateAdd(интервал , число , дата )

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

Аргумент интервал может принимать одно из следующих значений:

  • DateAdd(“d”, 32, “01/01/2015”) добавляет 32 дня к дате 01/01/2015 и, таким образом, возвращает дату 02/02/2015.
  • DateAdd(“ww”, 36, “01/01/2015”) добавляет 36 недель к дате 01/01/2015 и возвращает дату 09/09/2015.
DateDiff Вычисляет количество определённых временных интервалов между двумя заданными датами.
  • DateDiff(“d”, “01/01/2015”, “02/02/2015”) вычисляет количество дней между датами 01/01/2015 и 02/02/2015, возвращает результат 32.
  • DateDiff(“ww”, “01/01/2015”, “03/03/2016”) вычисляет количество недель между датами 01/01/2015 и 03/03/2016, возвращает результат 61.
Day Возвращает целое число, соответствующее дню месяца в заданной дате.

Пример: Day(“29/01/2015”) возвращает число 29.
Hour Возвращает целое число, соответствующее количеству часов в заданном времени.

Пример: Hour(“22:45:00”) возвращает число 22.
InStr Принимает в качестве аргументов целое число и две строки. Возвращает позицию вхождения второй строки внутри первой, начиная поиск с позиции, заданной целым числом.
  • InStr(1, “Вот искомое слово”, “слово”) возвращает число 13.
  • InStr(14, “Вот искомое слово, а вот еще искомое слово”, “слово”) возвращает число 38.

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

Пример: Int(5.79) возвращает результат 5.
Isdate Возвращает True , если заданное значение является датой, или False – если датой не является.
  • IsDate(“01/01/2015”) возвращает True ;
  • IsDate(100) возвращает False .
IsError Возвращает True , если заданное значение является ошибкой, или False – если ошибкой не является.
IsMissing В качестве аргумента функции передаётся имя необязательного аргумента процедуры. IsMissing возвращает True , если для рассматриваемого аргумента процедуры не передано значение.
IsNumeric Возвращает True , если заданное значение может быть рассмотрено как число, в противном случае возвращает False .
Left Возвращает заданное количество символов от начала переданной строки. Синтаксис функции вот такой:

Left(строка , длина )

где строка – это исходная строка, а длина – количество возвращаемых символов, считая от начала строки.

  • Left(“абвгдежзиклмн”, 4) возвращает строку “абвг”;
  • Left(“абвгдежзиклмн”, 1) возвращает строку “а”.
Len Возвращает количество символов в строке.

Пример: Len(“абвгдеж”) возвращает число 7.
Month Возвращает целое число, соответствующее месяцу в заданной дате.

Пример: Month(“29/01/2015”) возвращает значение 1.
Mid Возвращает заданное количество символов из середины переданной строки. Синтаксис функции:

Mid(строка , начало , длина )

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

  • Mid(“абвгдежзиклмн”, 4, 5) возвращает строку “гдежз”;
  • Mid(“абвгдежзиклмн”, 10, 2) возвращает строку “кл”.
Minute Возвращает целое число, соответствующее количеству минут в заданном времени.Пример: Minute(“22:45:15”) возвращает значение 45.
Now Возвращает текущую системную дату и время.
Right Возвращает заданное количество символов от конца переданной строки. Синтаксис функции:

Right(строка , длина )

Где строка – это исходная строка, а длина – это количество символов, которые надо извлечь, считая от конца заданной строки.

  • Right(“абвгдежзиклмн”, 4) возвращает строку “клмн”;
  • Right(“абвгдежзиклмн”, 1) возвращает строку “н”.
Second Возвращает целое число, соответствующее количеству секунд в заданном времени.

Пример: Second(“22:45:15”) возвращает значение 15.
Sqr Возвращает квадратный корень числовой величины, переданной в аргументе.
  • Sqr(4) возвращает значение 2;
  • Sqr(16) возвращает значение 4.
Time Возвращает текущее системное время.
Ubound Возвращает верхний индекс измерения заданного массива.

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

Практически весь программный код модулей VBA содержится в процедурах двух типов Sub (подпрограммы) и Function (функции). Основная задача процедуры-функции Function - это вычисление некоторого значения и возвращение его в точку вызова процедуры-функции.

Синтаксис процедуры-функции Function:

Function Имя_Функции(аргументы As) As

Имя_Функции = Возвращаемое_Значение

End Function

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

Пример 1

Например, самая простая процедура-функция Function:

Function F1(x As Currency) As Currency

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

Пример 2

Например, Function F1(x) можно использовать в процедуре MySub (), присвоив переменной "у" имя F1(x).

Dim y As Single ‘Объявление переменной y

y = F1 (9) ‘Определяем F1 (x) для значения x=9

Debug.Print y ‘Вывод значений в окне Immediate

Function F1 (x As Single) As Single

F1 = x ^ 10 ‘Возвращаемое значение х в степени 10

Здесь Function F1(x)=$x^{10}$ для значения $ x=9$ возвращает в вызывающую процедуру MySub () значение $3,486785E+09$. Если для возвращаемого функцией значения или переменной, которая используется в процедуре VBA, не объявлен тип данных, то по умолчанию будет задан тип данных Variant.

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

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

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

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

Sub MyDate ()

Dim TD ‘Объявление переменной TD

TD = Now ‘Определяем текущую системную дату и время

Debug.Print TD ‘Вывод значений в окне Immediate

Для вызова функции, требующей ввода одного или нескольких аргументов, необходимо ввести в правой части оператора присваивания ее имя с заключенными в скобках параметрами (значениями аргументов). Например, для вызова встроенной Function Log (N) с одной переменной N в процедуре типа Sub переменной Log_N присвоено имя функции Log (50) со значением аргумента равного 50.

Sub Натуральныйлогарифм ()

Dim LogN ‘Объявление переменной LogN

Debug.Print LogN ‘Вывод значений в окне Immediate

Замечание 1

Здесь встроенная функция Log (N) для значения аргумента равного 50 возвращает значение 3,91202300542815 в точку вызова Log (50) вызывающей процедуры "Sub Натуральный_логарифм ()". Функцию можно вызвать как с помощью отдельного оператора VBA, так и поместив ее имя со списком значений аргументов (параметров) в формулу или выражение в программе на VBA. В VBA для сокращения записи используется механизм вложения функций, который позволяет указывать вызов одной функции в качестве аргумента для другой функции. В этом случае возвращаемое первой функцией значение используется в качестве аргумента для следующей функции.

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

  • математические;
  • функции проверки типов;
  • функции обработки строк;
  • функция форматирования;
  • функции преобразования форматов;
  • функции даты и времени.

Математические функции

К математическим функциям относят:

  • Abs (x) - модуль аргумента $x$;
  • Cos(x) - косинус аргумента $x$;
  • Exp(x) - возведение основания натурального логарифма в степень $x$;
  • Log(x) - логарифм натуральный аргумента $x$;
  • Rnd - случайное число из интервала ;
  • Sin(x) - синус аргумента $x$;
  • Sqr(x) - квадратный корень из $x$;
  • Atn(x) - арктангенс от $x$;
  • Tan(x) – тангенс от $x$;
  • Sgn(x) – знак $x$.

Fix(x) и Int(x) обе функции отбрасывают дробную часть числа и возвращают целое значение. Разница между этими функциями состоит для отрицательных значений аргумента. Int(x) возвращает ближайшее отрицательное целое число, меньшее или равное х, а Fix(x) – ближайшее отрицательное целое число, большее или равное х.

Функции проверки типов

Приведем функции, определяющие каким типом является переменная:

  • IsArray(х) – функция проверяет является ли переменная массивом;
  • IsDate(х) определяет является ли переменная датой;
  • IsError(х) определяет является ли переменная кодом ошибки;
  • IsNull(х) определяет является ли переменная пустым значением;
  • IsNumeric(х) определяет является ли переменная числовым значением;
  • IsObject(х) определяет является ли переменная объектом.

Функция форматирования

Функция форматирования возвращает значение типа Variant (String), содержащее выражение, оформленное согласно синтаксиса функции:

Format(Выражение[,Формат [,Первый день недели[,Первая Неделя Года]]]),где:

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

Если к имени функции добавляется знак $, то функция возвращает значение типа String., то функция возвращает значение типа String.

При создании собственного числового формата можно использовать следующие символы :

  • 0 – резервирует позицию цифрового разряда. Отображает цифру или нуль. Если у форматируемого числа есть какая-нибудь цифра в этой позиции разряда, где в строке формата находится 0, функция отображает эту цифру, если нет, то в этой позиции отображается нуль;
  • "#" - действие данного символа аналогично действию 0 с той лишь разницей, что незначащие нули не отображаются;
  • . – резервирует позицию десятичного разделителя, определяет, сколько разрядов необходимо отображать слева и справа от десятичной точки;
  • % - резервирует процентное отображение числа;
  • . – разделяет сотни от тысяч.

Функции преобразования форматов

К ним относят:

  • Val(строка) – возвращает числа, содержащиеся в строке, как числовое значение соответствующего типа.
  • Str(число) – возвращает значение типа Variant (String), являющееся строковым представлением числа.

Кроме функций Val и Str имеется ряд функций по преобразованию типов выражений.

Функции обработки строк

Среди множества функций можно выделить следующие:

  • Chr(код) – преобразует ASCII – код в строку. Например, Chr(10) осуществляет переход на новую строку, Chr(13) – возврат каретки;
  • Mid(string, pos[,length]) – возвращает подстроку строки, содержащую указанное число символов, где String – строковое выражение, из которого извлекается подстрока;
  • Pos – позиция символа в строке String, с которого начинается нужная подстрока;
  • Length – число возвращаемых символов подстроки;
  • Len(строка) – возвращает число символов строки.

Функции времени и даты

Возвращают значение типа Variant, содержащее системную дату, текущее время и т. д. Например функция Date возвращает значение, содержащее системную дату.

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

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

Определение функции

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

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

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

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

Синтаксис

Добавьте кнопку и добавьте следующую функцию.

Function Functionname(parameter-list) statement 1 statement 2 statement 3 ....... statement n End Function

Пример

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

Function find_area(Length As Double, Optional Width As Variant) If IsMissing(Width) Then find_area = Length * Length Else find_area = Length * Width End If End Function

Вызов функции

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

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

Создание пользовательской функции в VBA Excel, ее синтаксис и компоненты. Описание пользовательской функции и ее аргументов. Метод Application.MacroOptions.

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

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

Синтаксис функции

Function Имя ([СписокАргументов]) [Операторы] [Имя = выражение] [Операторы] [Имя = выражение] End Function

Компоненты функции

  • Static - необязательное ключевое слово, указывающее на то, что значения переменных, объявленных в функции, сохраняются между ее вызовами.
  • Имя - обязательный компонент, имя пользовательской функции.
  • СписокАргументов - необязательный компонент, одна или более переменных, представляющих аргументы, которые передаются в функцию. Аргументы заключаются в скобки и разделяются между собой запятыми.
  • Операторы - необязательный компонент, блок операторов (инструкций).
  • Имя = выражение - необязательный* компонент, присвоение имени функции значения выражения или переменной. Обычно, значение присваивается функции непосредственно перед выходом из нее.
  • Exit Function - необязательный компонент, принудительный выход из функции, если ей уже присвоено окончательное значение.

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

Видимость функции

Видимость пользовательской функции определяется необязательными ключевыми словами Public и Private, которые могут быть указаны перед оператором Function (или Static, в случае его использования).

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

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

Если ключевое слово Public или Private не указано, функция считается по умолчанию объявленной, как Public.

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

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

Для примера мы рассмотрим простейшую пользовательскую функцию, которой в следующем параграфе добавим описание. Называется функция «Деление», объявлена с типом данных Variant, так как ее возвращаемое значение может быть и числом, и текстом. Аргументы функции - Делимое и Делитель - тоже объявлены как Variant, так как в ячейках Excel могут быть числовые значения разных типов, и функция IsNumeric тоже проверяет разные типы данных и требует, чтобы ее аргументы были объявлены как Variant.

Function Деление(Делимое As Variant, Делитель As Variant) As Variant If IsNumeric(Делимое) = False Or IsNumeric(Делитель) = False Then Деление = "Ошибка: Делимое и Делитель должны быть числами!" Exit Function ElseIf Делитель = 0 Then Деление = "Ошибка: деление на ноль!" Exit Function Else Деление = Делимое / Делитель End If End Function

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

  • Если делимое или делитель не являются числом, функция возвращает значение: «Ошибка: Делимое и Делитель должны быть числами!», и производится принудительный выход из функции оператором Exit Function.
  • Если делитель равен нулю, функция возвращает значение: «Ошибка: деление на ноль!», и производится принудительный выход из функции оператором Exit Function.

Если проверяемые условия не выполняются (возвращают значение False) производится деление чисел и функция возвращает частное (результат деления).

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

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

Добавление описания функции

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

  • Запустите Мастер функций, посмотрите, как отображается имя нужной функции и закройте его.
  • Откройте и в поле «Имя макроса» впишите имя пользовательской функции.
  • Нажмите кнопку «Параметры» и в открывшемся окне добавьте или отредактируйте описание.
  • Нажмите кнопку «OK», затем в окне списка макросов - «Отмена». Описание готово!

Добавление описания на примере функции «Деление»:

Описание функции «Деление» в диалоговом окне Мастера функций «Аргументы функции»:


С помощью окна «Список макросов» можно добавить описание самой функции, а ее аргументам нельзя. Но это можно сделать, используя метод Application.MacroOptions.

Метод Application.MacroOptions

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

Пример кода с методом Application.MacroOptions:

Sub ИмяПодпрограммы() Application.MacroOptions _ Macro:="ИмяФункции", _ Description:="Описание функции", _ Category:="Название категории", _ ArgumentDescriptions:=Array("Описание 1", "Описание 2", "Описание 3", ...) End Sub

  • ИмяПодпрограммы - любое уникальное имя, подходящее для наименования процедур.
  • ИмяФункции - имя функции, параметры которой добавляются или изменяются.
  • Описание функции - описание функции, которое добавляется или изменяется.
  • Название категории - название категории в которую будет помещена функция. Если параметр Category отсутствует, пользовательская функция будет записана в раздел по умолчанию - «Определенные пользователем». Если указанное Название категории соответствует одному из названий стандартного списка, функция будет записана в него. Если такого Названия категории нет в списке, будет создан новый раздел с этим названием и функция будет помещена в него.
  • "Описание 1", "Описание 2", "Описание 3", ... - описания аргументов в том порядке, как они расположены в объявлении пользовательской функции.

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

Сейчас с помощью метода Application.MacroOptions попробуем изменить описание пользовательской функции «Деление» и добавить описания аргументов.

Sub ИзменениеОписания() Application.MacroOptions _ Macro:="Деление", _ Description:="Описание функции Деление изменено методом Application.MacroOptions", _ ArgumentDescriptions:=Array("- любое числовое значение", "- числовое значение, кроме нуля") End Sub

После однократного запуска этой подпрограммы получаем следующий результат:


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