Функции, определённые пользователем

Функция, определённая пользователем, называемая также процедурой-функцией, – это особый вид процедуры 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. Для проверки результата использовать новый лист с теми же исходными данными, что на листе Новый. Запустить процедуру Расчёт_Комиссионных на выполнение и отладить в случае необходимости.