Функция, определённая пользователем, называемая также процедурой-функцией, – это особый вид процедуры VBA, возвращающей результат. Пользовательские процедуры-функции, как и встроенные функции VBA, могут иметь необязательные и именованные аргументы. Для записи процедуры-функции нельзя использовать макрорекордер, хотя можно редактировать записанный рекордером макрос и превращать его в процедуру-функцию.
Формат процедуры-функции:
Function Name ([ Arglist ]) [As Type]
‘ VBA Statements
[ Name = expression ]
End Function
Function – ключевое слово, объявляющее начало функции.
Name – имя функции. Имена функций следуют тем же правилам, что и имена других идентификаторов VBA.
Arglist – список аргументов данной функции, необязательный элемент.
Type – любой тип возвращаемого значения функции. Если тип не определен, результат, который возвращает функция-процедура, имеет тип Variant.
Name = expression – присваивание функции выражения, значение которого возвращает функция, необязательный элемент. Тем не менее, всегда следует включать оператор присваивания в функции-процедуры.
End Function – ключевые слова, заканчивающие функцию.
Даже если функция не имеет аргументов (например, Now, Date) в объявлении функции необходимо использовать круглые скобки.
Основное различие между процедурой-функцией и другими процедурами, помимо того, что функции возвращают значение, а процедуры - нет, состоит в том, что в процедуре-функции используются ключевые слова Function и End Function.
Обычно функция предназначается для выполнения вычисления и для возвращения результата. При объявлении функции-процедуры указывается имя каждого аргумента, передаваемого функции. Имена аргументов в списке Arglist отделяются друг от друга запятой и должны следовать правилам, применяемым к любому идентификатору VBA.
Ниже приведён пример, в котором используется пользовательская функция, вычисляющая сумму комиссионных. Суть задачи состоит в следующем. В таблице Excel, приведённой ниже, требуется подсчитать суммы комиссионных и оплаты.
Суммы комиссионных подсчитываются в процедуре-функции Комиссионные, обращение к которой осуществляется в процедуре Расчёт_Комиссионных, при помощи инструкции
Cells(i, 3) = Комиссионные(Cells(i, 2)),
параметром которой, является сумма продаж, находящаяся в ячейках Excel B3÷B6 (Cells(3, 2) – это ячейка B3 и т.д.).
Подсчёт оплаты выполняется при помощи инструкции
Cells(i, 4) = Cells(i, 2) + Cells(i, 3)
в процедуре Расчёт_Комиссионных.
Sub Расчёт_Комиссионных()
Sheets("Лист1").Select
Dim i As Integer
i = 3
Do While Cells(i, 1) <> ""
Cells(i, 3) = Комиссионные(Cells(i, 2))
Cells(i, 4) = Cells(i, 2) + Cells(i, 3) ‘ Оплата
i = i + 1
Loop
End Sub
Function Комиссионные(Продажи As Double) As Double
Dim РасчётКом As Double, Надбавка As Double
РасчётКом = Продажи * 0.05
If Продажи > 5000 Then
Надбавка = 0.01 * (Продажи - 5000)
РасчётКом = РасчётКом + Надбавка
End If
If Продажи > 10000 Then
Надбавка = 0.02 * (Продажи - 10000)
РасчётКом = РасчётКом + Надбавка
End If
If Продажи > 15000 Then
Надбавка = 0.03 * (Продажи - 15000)
РасчётКом = РасчётКом + Надбавка
End If
Комиссионные = РасчётКом
End Function
VBA передает все аргументы в процедуру-функцию как типы Variant. Можно объявлять определенные типы данных для каждого аргумента в списке аргументов. Определение типов аргументов для процедуры-функции помогает пользователю при вызове функции вводить аргументы правильного типа в правильном порядке.
Упражнение 2
1. На листе Excel Лист1 создайте таблицу «Суммы комиссионных», представленную выше, подсчитайте суммы комиссионных и оплаты, выполнив процедуру Расчёт_Комиссионных, которая вызывает на выполнение процедуру-функцию Комиссионные. Проанализируйте результат.
2. Скопируйте Лист1, введите наименование нового листа Новый. Удалите с него подсчитанные суммы комиссионных и оплаты. Дополните произвольными исходными данными столбцы Компания и Продажи.
3. Выполните процедуру Расчёт_Комиссионных. В процедуре не забудьте изменить имя активного листа.
4. Создайте макрос, форматирующий таблицу таким образом, чтобы она выглядела так, как показано ниже. Назовите макрос Формат, проверьте его работу и отладьте в случае необходимости.
5. Вставьте вызов макроса перед инструкцией End Sub в процедуру Расчёт_Комиссионных.
6. Повторите пункты 2 и 3 с той лишь разницей, что копировать Лист1 нужно на другой новый лист, а дополнять таблицу нужно тем же количеством записей (можно их скопировать с листа Новый).
7. Составить пользовательскую функцию Комиссионные_2, в которой вместо инструкции If…Then использовать инструкцию Select Case. Из процедуры Расчёт_Комиссионных нужно обращаться к функции Комиссионные_2. Для проверки результата использовать новый лист с теми же исходными данными, что на листе Новый. Запустить процедуру Расчёт_Комиссионных на выполнение и отладить в случае необходимости.
