Пользовательские функции в VBA для Excel. Основы VBA: встроенные и пользовательские функции Основные функции vba

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

Значение всегда присваивается названию функции минимум один раз и, как правило, тогда, когда функция завершила выполнение. Создание пользовательской функции начните с создания модуля VBA (можно также использовать существующий модуль). Введите ключевое слово Function, после которого укажите название функции и список ее аргументов (если они есть) в скобках. Вы также можете объявить тип данных значения, которое возвращает функция, используя ключевое слово As (это делать необязательно, но рекомендуется). Вставьте код VBA, выполняющий требуемые действия, и убедитесь, что необходимое значение присваивается переменной процедуры, соответствующей названию функции, минимум один раз в теле функции. Функция заканчивается оператором End Function.

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

Функцию можно вызвать одним из следующих способов:

• вызвать ее из другой процедуры;
• включить ее в формулу рабочего листа;
• включить в формулу условного форматирования;
• вызвать ее в окне отладки VBE (Immediate ). Этот метод обычно применяется на этапе тестирования (рис. 3).

Рис. 3. Вызов функции в окне отладки

В отличие от процедур, функции не отображаются в диалоговом окне Макрос (меню Разработчик –> Код –> Макросы ; или Alt+F8).

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

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

Функции без аргументов

В Excel есть несколько встроенных функций, не имеющих аргументов, например, СЛЧИС, СЕГОДНЯ, ТДАТА. Несложно создать аналогичные пользовательские функции. Например:

Function User()
" Возвращает имя пользователя
User = Application.UserName
End Function

При вводе формулы =User() ячейка возвращает имя текущего пользователя (рис. 4). Обратите внимание: при использовании функции без аргумента в формуле рабочего листа необходимо указать пустые скобки.

Рис. 4. Формула =User() возвращает имя текущего пользователя

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

Application.Volatile True

Метод Volatile объекта Application имеет один аргумент (True или False). Если функция выделена как volatile (изменяемая), она пересчитывается всякий раз, когда изменяется любая ячейка листа. При использовании аргумента False метода Volatile функция пересчитывается только тогда, когда в результате пересчета изменяется один из ее аргументов.

В Excel есть встроенная функция СЛЧИС. Но мне не слишком понравилось, что случайные числа изменяются при каждом пересчете рабочего листа. Поэтому я разработал функцию, которая возвращает случайные числа, не изменяющиеся при пересчете формул. Для этого была использована встроенная функция VBA Rnd:

Function StaticRand()
" Возвращает случайное число, не изменяемое при пересчете формул
StaticRand = Rnd()
End Function

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

Функция с одним аргументом

Рис. 5. Таблица комиссионных

Существует несколько способов вычислить комиссионные. Например, с помощью следующей формулы (если объем продаж поместить в ячейку D1):

ЕСЛИ(И(D1>=0;D1<=9999,99);D1*0,08;ЕСЛИ(И(D1>=10000;D1<=19999,99);D1*0,105; ЕСЛИ(И(D1>=20000;D1<=39999,99);D1*0,12;ЕСЛИ(D1>=40000;D1*0,14))))

Эта формула неудачна по нескольким причинам. Во-первых, она сложна, ее нелегко набрать, и в дальнейшем редактировать. Во-вторых, значения строго определены в формуле, из-за чего ее сложно изменять. Гораздо лучше использовать ВПР (рис. 6).

Рис. 6. Использование функции ВПР для вычисления комиссионных

Еще лучше (тогда не нужно использовать таблицу соответствия) создать пользовательскую функцию:

Function Commission(Sales)
Const Tier1 = 0.08
Const Tier2 = 0.105
Const Tier3 = 0.12
Const Tier4 = 0.14
" Вычисление комиссионных с продаж
Select Case Sales
Case 0 To 9999.99: Commission = Sales * Tier1
Case 10000 To 19999.99: Commission = Sales * Tier2
Case 20000 To 39999.99: Commission = Sales * Tier3
Case Is >= 40000: Commission = Sales * Tier4
End Select
End Function

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

Commission(В2)

Function DoubleCell()
DoubleCell = Range(" Al ") * 2
End Function

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

Function DoubleCell(cell)
DoubleCell = cell * 2
End Function

Функция с двумя аргументами

Представим, что менеджер, о котором речь шла выше, внедряет новую политику, разработанную для уменьшения текучести кадров: общая сумма комиссионных, подлежащих выплате, увеличивается на 1% за каждый год, который служащий проработал в компании. Изменим пользовательскую функцию Commission так, чтобы она принимала два аргумента. Новый аргумент представляет количество лет, отработанных сотрудником в компании. Назовем эту новую функцию Commission2:

Function Commission2(Sales, Years) As Single
" Вычисление комиссионных с продаж на основе
" длительности стажа
Commission2 = Commission(Sales) + _
(Commission(Sales) * Years / 100)
End Function

Функция с аргументом в виде массива

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

Function SumArray(List) As Double
Dim Item As Variant
SumArray = 0
For Each Item In List
If WorksheetFunction.IsNumber(Item) Then _
SumArray = SumArray + Item
Next Item
End Function

Функция Excel ЕЧИСЛО проверяет, является ли каждый элемент числом, прежде чем добавить его к общему целому. Добавление этого простого оператора проверки данных устраняет ошибки несоответствия типов при попытке выполнить арифметическую операцию над строкой.

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

Многие встроенные функции Excel имеют необязательные аргументы. Пример - функция ЛЕВСИМВ, возвращающая символы с левого края строки. Она имеет следующий синтаксис:

ЛЕВСИМВ(текст, кол_символов )

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

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

Function User2(Optional Uppercase As Variant)
If IsMissing(Uppercase) Then Uppercase = False
User2 = Application.UserName
If Uppercase Then User2 = UCase(User2)
End Function

Если аргумент равен False или опущен, то имя пользователя возвращается без каких-либо изменений. Если же аргумент функции True, то имя пользователя возвращается в символах верхнего регистра (с помощью VBA-функции Ucase). Обратите внимание на первый оператор функции - он содержит VBA-функцию IsMissing, которая определяет наличие аргумента. Если аргумент отсутствует, оператор присваивает переменной Uppercase значение False (задано по умолчанию).

Функция VBA, возвращающая массив

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

Функция MonthNames - простой пример применения функции Array в пользовательской функции.

Function MonthNames()
MonthNames = Array(" Январь " , " Февраль " , " Март " , _
" Апрель " , " Май " , " Июнь " , " Июль " , " Август " , _
" Сентябрь " , " Октябрь " , " Ноябрь " , " Декабрь "
End Function

Функция MonthNames возвращает горизонтальный массив названий месяцев. На рабочем листе выделите 12 ячеек, введите формулу =MonthNames() и нажмите . Если необходимо сгенерировать вертикальный массив названий месяцев, выделите вертикальный диапазон, введите формулу =ТРАНСП(MonthNames()) и нажмите .

Функция, возвращающая значение ошибки

• xlErrDivO (для ошибки #ДЕЛ/0!);
• xlErrNA (для ошибки #Н/Д);
• xlErrName (для ошибки #ИМЯ?);
• xlErrNull (для ошибки #ПУСТО!);
• xlErrNum (для ошибки #ЧИСЛО!);
• xlErrRef (для ошибки #ССЫЛ!);
• xlErrValue (для ошибки #ЗНАЧ!).

Ниже приведена преобразованная функция RemoveVowels (см. пример в начале). Конструкция If-Then применяется для выполнения альтернативного действия в случае, когда аргумент не является текстовым. Эта функция вызывает функцию Excel ЕТЕКСТ, которая определяет, содержит ли аргумент текст. Если ячейка содержит текст, то функция возвращает нормальный результат. Если же ячейка содержит не текст (или пуста), то функция возвращает ошибку #ЗНАЧ!

Function RemoveVowels3(txt) As Variant
" Удаляет все гласные буквы из аргумента Txt
" Возвращает ошибку #ЗНАЧ!, если аргумент — не строка
Dim i As Long
RemoveVowels3 = " "
If Application.WorksheetFunction.IsText(txt) Then
For i = 1 To Len(txt)
If Not UCase(Mid(txt, i, 1)) Like " " Then
RemoveVowels3 = RemoveVowels3 & Mid(txt, i, 1)
End If
Next i
Else
RemoveVowels3 = CVErr(xlErrValue)
End If
End Function

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

Функция с неопределенным количеством аргументов

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

Function SimpleSum(ParamArray arglist() As Variant) As Double
Dim cell As Range
Dim arg As Variant
For Each arg In arglist
For Each cell In arg
SimpleSum = SimpleSum + cell
Next cell
Next arg
End Function

Отладка функций

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

• Поместите в важных местах функцию MsgBox, чтобы контролировать значения отдельных переменных.
• Протестируйте функцию, вызвав ее из процедуры, а не в формуле рабочего листа. Ошибки в процессе выполнения отображаются обычным образом.
• Определите точку остановки в функции и просмотрите функцию пошагово. При этом можно воспользоваться всеми стандартными инструментами отладки. Чтобы добавить точку остановки, поместите курсор в операторе, в котором вы решили приостановить выполнение, и выберите команду Debug –> Toggle Breakpoint (Отладка –> Точка остановки ) или нажмите .
• Используйте в программе один или несколько временных операторов Print (Отладка, Печать), чтобы отобразить значения в окне Immediate редактора VBA. Например, чтобы проконтролировать циклически изменяемое значение, используйте следующий метод:

Рис. 7. Используйте окно отладки для отображения результатов при выполнении функции

В данном случае значения двух переменных, Ch и i, выводятся в окне отладки (Immediate ) всякий раз, когда в программе встречается оператор Debug.Print. Встаньте курсором в любое место процедуры Test() и нажмите F5. На рис. 7 показан результат для случая, когда функция принимает аргумент TusconArizona.

Использование метода MacroOptions

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

• добавить описание функции (начиная с версии Excel 2010;
• указать категорию функции;
• добавить описание аргументов функции.

Sub DescribeFunction()
Dim FuncName As String
Dim FuncDesc As String
Dim FuncCat As Long
Dim Arg1Desc As String, Arg2Desc As String
FuncName = " Draw "
FuncDesc = " Содержимое случайной ячейки диапазона "
FuncCat = 5 " Ссылки и массивы
Arg1Desc = " Диапазон, который содержит значения "
Arg2Desc = " (не обязательный) Если False или отсутствует, _
функция Rnd не пересчитывается. "
Arg2Desc = Arg2Desc & " Если True, функция Rnd пересчитывается "
Arg2Desc = Arg2Desc & " при любом изменении на листе. "
Application.MacroOptions _
Macro:=FuncName, _
Description:=FuncDesc, _
Category:=FuncCat, _
ArgumentDescriptions:=Array(Arg1Desc, Arg2Desc)
End Sub

На рис. 8 показаны диалоговые окна Мастер функций и Аргументы функции после выполнения процедуры DescribeFunction().

Рис. 8. Вид диалоговых окон Мастер функций и Аргументы функции для пользовательской функции

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

Если вы не укажете категорию функции с помощью метода MacroOptions, пользовательская функция рабочего листа появится в категории Определенные пользователем диалогового окна Мастер функций . В таблице (рис. 9) перечислены номера категорий, которые можно использовать в качестве значений аргумента Category метода MacroOptions. Обратите внимание, что некоторые из этих категорий (от 10 до 13) обычно не отображаются в диалоговом окне Мастер функций . Если же отнести одну из пользовательских функций в подобную категорию, она появится в диалоговом окне.

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

При желании можно сохранить часто используемые пользовательские функции в файле надстройки. Основное преимущество такого подхода заключается в следующем: функции могут быть применены в формулах без спецификатора имени файла. Предположим, у вас есть пользовательская функция ZapSpaces; она хранится в файле Myfuncs.xlsm. Чтобы применить ее в формуле другой рабочей книги (отличной от Myfuncs.xlsm), необходимо ввести следующую формулу: =Myfuncs.xlsm!ZapSpaces(А1:С12).

Если вы создадите надстройку на основе файла Myfuncs.xlsm и эта надстройка будет загружена в текущем сеансе работы Excel, то ссылку на файл можно пропустить, введя следующую формулу: =ZapSpaces(А1:С12). Создание надстроек будет рассмотрено отдельно.

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

Использование функций Windows API

VBA может заимствовать методы из других файлов, которые не имеют ничего общего с Excel или VBA, например, файлы DLL (Dynamic Link Library - динамически подключаемая библиотека), которые используются Windows и другими программами. В результате в VBA появляется возможность выполнять операции, которые без заимствованных методов находятся за пределами возможностей языка.

Windows API (Application Programming Interface - интерфейс прикладного программирования) представляет собой набор функций, доступных программистам в среде Windows. При вызове функции Windows из VBA вы обращаетесь к Windows API. Многие ресурсы Windows, используемые программистами Windows, можно получить из файлов DLL, в которых хранятся программы и функции, подсоединяемые в процессе выполнения программы, а не во время компиляции.

Прежде чем использовать функцию Windows API, ее необходимо объявить вверху программного модуля. Если программный модуль - это не стандартный модуль VBA (т.е. модуль для UserForm , Лист или ЭтаКнига ), то API-функцию необходимо объявить, как Private.

Объявление API-функции имеет некоторую сложность - функция должна объявляться максимально точно. Оператор объявления указывает VBA следующее:

• какую API-функцию вы используете;
• в какой библиотеке расположена API-функция;
• аргументы API-функции.

После объявления API-функцию можно использовать в программе VBA.

Рассмотрим пример API-функции, которая отображает имя папки Windows (с помощью стандартных операторов VBA эту задачу порой выполнить невозможно). Для начала объявим API-функцию:

Declare PtrSafe Function GetWindowsDirectoryA Lib " kernel32 " _
(ByVal lpBuffer As String, ByVal nSize As Long) As Long

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

Следующий пример отображает результат в окне сообщения:

Sub ShowWindowsDir()
Dim WinPath As String * 255
Dim WinDir As String
WinPath = Space(255)
WinDir = Left(WinPath, GetWindowsDirectoryA _
(WinPath, Len(WinPath)))
MsgBox WinDir, vbInformation, " Windows Directory "
End Sub

В процессе выполнения процедуры ShowWindowsDir отображается окно сообщения с указанием расположения папки Windows.

Иногда требуется создать оболочку (wrapper) для API-функций. Другими словами, вы создадите собственную функцию, использующую API-функцию. Такой подход существенно упрощает использование API-функции. Ниже приведен пример такой функции VBA:

Function WindowsDir() As String
" Название папки Windows
Dim WinPath As String * 255
WinPath = Space(255)
WindowsDir = Left(WinPath, GetWindowsDirectoryA _
(WinPath, Len(WinPath)))
End Function

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

Внимание! Не удивляйтесь сбоям в системе при использовании в VBA функций Windows API. Заранее сохраните свою работу перед тестированием.

Определение состояния клавиши

Предположим, вы написали макрос VBA, который будет выполняться с помощью кнопки на панели инструментов. Необходимо, чтобы этот макрос выполнялся по-другому, если пользователь после щелчка на кнопке удерживает клавишу . Чтобы узнать о нажатии клавиши , можно использовать API-функцию GetKeyState. Функция GetKeyState сообщает о том, нажата ли конкретная клавиша. Функция имеет один аргумент, nVirtKey, который представляет код интересующей вас клавиши.

Ниже приведена программа, которая выявляет, что при выполнении процедуры обработки события Button_Click была нажата клавиша . Обратите внимание, что для определения состояния клавиши используется константа (принимающая шестнадцатеричное значение), которая затем применяется как аргумент функции GetKeyState. Если GetKeyState возвращает значение меньше 0, это означает, что клавиша нажата; в противном случае клавиша не нажата. Аналогичную проверку можно устроить для клавиш Ctrl и Alt (рис. 10).

Рис. 10. Проверка нажатия клавиш Shift, Ctrl и Alt

Код функции VBA можно найти в приложенном Excel-файле

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

В текстовом файле содержатся объявления и константы Windows API. Можно открыть этот файл в текстовом редакторе и скопировать соответствующие объявления в модуль VBA.

По материалам книги . – М: Диалектика, 2013. – С. 287–323.

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

Помимо встроенных функций, 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 или для вызова из других процедур.

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

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

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

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

Большинство функций рабочего листа Microsoft Excel можно использовать в коде Visual Basic (список этих функций: List of Worksheet Functions Available to Visual Basic)
Часть функций Excel в Visual Basic просто не нужны. Например, вместо функции Concatenate (СЦЕПИТЬ) в коде Visual Basic для объединения текстов используется оператор & .

Вызов функции рабочего листа из Visual Basic

В Visual Basic функции рабочего листа (worksheet) Microsoft Excel доступны через объект WorksheetFunction .

Например, здесь используется функция Min для определения наименьшего значения в диапазоне ячеек:

Sub UseFunction() Dim myRange As Range Set myRange = Worksheets("Sheet1").Range("A1:C10") answer = Application.WorksheetFunction.Min(myRange) MsgBox answer End Sub

Здесь сначала объявляется переменная myRange как объект типа Range , и затем ей назначается диапазон ячеек A1:C10 на листе Sheet1. Переменной answer присваивается результат применения функции Min к myRange . Полученное значение answer отображается в окне сообщения.

Диапазон ячеек необходимо объявить как объект Range !

Например, в формуле в ячейке таблицы Excel
=MATCH(9,A1:A10,0)
а в коде Visual Basic —

Sub FindFirst() myVar = Application.WorksheetFunction.Match(9, Worksheets(1).Range("A1:A10"), 0) MsgBox myVar End Sub

Функции Visual Basic (без префикса WorksheetFunction ) могут иметь такое же имя, как и функция Excel, но работать иначе. Например, Application.WorksheetFunction.Log и Log возвращают разные значения.

Вставка функции рабочего листа в ячейку

Чтобы вставить функцию в ячейку, нужно присвоить ее свойству Formula соответствующего объекта Range .

Например:

Sub InsertFormula() Worksheets("Sheet1").Range("A1:B3").Formula = "=RAND()" End Sub

результат функции RAND (она возвращает случайное число) присваивается свойству Formula диапазона ячеек A1:B3 на листе Sheet1в активной книге Excel.

(Using Microsoft Excel Worksheet Functions in Visual Basic Office 2003 )

Функция написанная на 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.