Пользовательские функции рабочего листа VBA
Функция пользователя
Пользователь может создать собственную функцию в стандартном модуле редактора VBA.
Для вызова редактора VBA задайте команду Сервис Ø Макрос Ø Редактор Visual Basic или нажмите комбинацию клавиш Alt+F11. В результате пользователь попадает в интегрируемую среду приложений IDE редактора Visual Basic. Она имеет стандартный вид для Windows-приложений:
строка меню,
панель инструментов (в данном случае Standart),
дваокнаProject – VBAProject иProperties.
В окне Project – VBAProject отображается реестр модулей и форм, входящих в создаваемый проект.
Модуль — это лист (не путать с рабочим листом), в котором набирается код.
Двойным щелчком на значке модуля в окне Project – VBAProject можно открыть соответствующий модуль. Значок активного модуля в окне Project – VBAProject выделяется серым цветом.
В VBA у каждого рабочего листа имеется собственный модуль. Кроме того, рабочая книга обладает своим, и если в проекте создаются пользовательские формы, то каждая из них имеет по модулю. В проекте также можно добавлять модули классов для описания создаваемых пользовательских классов (эти модули будут рассмотрены позже). Единый модуль, который нам необходим, — это стандартный модуль. Для того чтобы его добавить в проект, выберите команду InsertØModule.
Структура кода функции пользователя
В окне стандартного модуля с клавиатуры набирается текст процедуры — функции пользователя. В общем случае функция пользователя имеет следующую структуру:
Function имя_функции (список_аргументов)
[инструкции]
End Function
где
имя_функции – имя создаваемой функции, придуманное пользователем;
список_аргументов – список аргументов;
инструкции – это последовательность команд, выполняемых при нахождении значения функции. В совокупности они образуют тело функции.
Важная особенность функции пользователя заключается в том, что носителем возвращаемого значения является ее имя (идентификатор имя_функции). Поэтому среди инструкций должна присутствовать, по крайней мере, одна, которая является оператором присваивания имени функции значения какого-либо выражения.
Примечание. Разрешается досрочный выход из функции по инструкции Exit Function. В теле функции может располагаться несколько инструкций Exit Function.
Пример 1. Создание функции пользователя
Рассмотрим построение простой функции, которая возвращает стоимость товара по его стоимости без НДС и значению ставки НДС.
Вызовите редактор VBA командой Сервис Ø Макрос Ø Редактор Visual Basic или комбинацией клавиш Alt+F11. Добавьте в проект стандартный модуль командой Insert Ø Module. В окне модуля наберите код, представленный в листинге 1.
Листинг 1. Пользовательская функция определения стоимости товара
Function Стоимость (СтоимостьБезНДС, НДС)
Стоимость = СтоимостьБезНДС * (1 + НДС / 100)
End Function
Применение пользовательской функции для решения задачи
К созданной пользовательской функции можно обратиться через Мастер функций. По умолчанию она попадает в раздел Определенные пользователем списка Категория окна Мастера функций. Используем созданную функцию для определения стоимости товара с учетом НДС при условии, что НДС равен, например, 25 %, а стоимость товара без учета НДС равна 1000.
Алгоритм применения функции пользователя
Разместите данные, необходимые для решения задачи, на рабочем листе как показано на рисунке:
/>
В ячейку А2 введите число 1000 (стоимость без НДС), а в ячейку В2 — число 25 (НДС).
Для определения значения стоимости товара с учетом НДС переместите табличный курсор в ячейку С2 и вызовите Мастер функций (например, командой Вставка ØФункция).
В окне Мастера функций в списке Категория найдите значение Определенные пользователем, затем в списке Функция выберите функцию Стоимость и подтвердите свой выбор щелчком по командной кнопке ОК.
/>
В появившемся окне функции Стоимость в полях СтоимостьБезНДС и НДС задайте ссылки на ячейки А2 и В2 с исходными данными для этих аргументов (щелкните левой кнопкой мыши по соответствующим ячейкам, чтобы не набирать вручную с клавиатуры их адреса) и нажмите командную кнопку ОК.
/>
Примечание. Возможность использовать в качестве имен переменных русские названия новая функция может быть легко распознана любым пользователем по ее имени, и смысл параметров функции становится понятным.
Результат вычисления
/>
Пример 2. Создание функции пользователя
Необходимо создать функцию определения суммы заработной платы сотрудника за отработанные дни. Аргументами функции являются: величина месячного оклада сотрудника, количество рабочих дней в месяце, количество отработанных сотрудником дней. Добавьте в проект стандартный модуль командой Insert ØModule. В окне модуля наберите код, представленный в листинге 2.
Листинг 2. Пользовательская функция определения суммы заработной платы сотрудника за отработанные дни
Function Зар_плата_отраб_время (Оклад, РабДни, ОтрабДни)
Зар_плата_отраб_время = Оклад / РабДни * ОтрабДни
End Function
Алгоритм применения функции пользователя
Разместите данные, необходимые для решения задачи, на рабочем листе как показано на рисунке:
/>
Переместите табличный курсор в ячейку Е2 и вызовите Мастер функций, в окне Мастера функций в списке Категория выберите пункт Определенные пользователем, затем в списке Функция — функцию Зар_плата_отраб_время и подтвердите свой выбор щелчком по командной кнопке ОК. В появившемся окне функции Зар_плата_отраб_время в полях Оклад, РабДни и ОтрабДни задайте ссылки на ячейки В2, С2 и D2 с исходными данными для этих аргументов и нажмите командную кнопку ОК:
/>
Результат вычисления
/>
Пример 3. Создание функции пользователя с использованием стандартных математических функций
Математические функции
При создании функций пользователю могут потребоваться встроенные в VBA математические функции. Перечень таких функций представлен в таблице 1.
Таблица 1. Встроенные математические функции VBA
Функция
Возвращаемое значение
Abs (number)
абсолютная величина (модуль) числа
Atn (number)
арктангенс
Cos (number)
косинус
Exp (number)
экспонента ех (результат возведения основания натурального логарифма в указанную степень)
Log (number)
натуральный логарифм
Rnd (number)
случайное число между 0 и 1. Если аргумент number меньше нуля, то функция Rnd возвращает каждый раз одно и то же число. Если number больше нуля или опущено, то Rnd возвращает следующее случайное число в последовательности. Если number равняется нулю, то Rnd возвращает случайное число, полученное при предыдущем вызове этой функции. Перед вызовом функции Rnd используйте оператор Randomize.
Sgn (number)
знак числа (возвращает 1, если аргумент является положительным; -1, если аргумент — отрицательный; 0, если аргумент равен нулю)
Sin (number)
синус
Sqr (number)
квадратный корень из числа
Tan (number)
тангенс
Fix (number)
Int (number)
обе функции Fix, Int отбрасывают дробную часть числа и возвращают целое значение. Различие между данными функциями состоит в том, что для отрицательного значения параметра число функция Int возвращает ближайшее отрицательное целое число, меньшее либо равное указанному, а Fix – ближайшее отрицательное целое число, большее либо равное указанному.--PAGE_BREAK----PAGE_BREAK--
8
от 10000 до 19999
10
от 20000 до 39999
12
Более 40000
14
Построим для расчета комиссионных в стандартном модуле пользовательскую функцию (листинг 7).
Листинг 7. Расчет комиссионных
Function Комиссионные1(Продажи)
If Продажи
Комиссионные1 = Продажи * 0.08
ElseIf Продажи
Комиссионные1 = Продажи * 0.1
ElseIf Продажи
Комиссионные1 = Продажи * 0.12
Else
Комиссионные1 = Продажи* 0.14
End If
End Function
Усложним задачу. Будем считать, что комиссионные зависят от ставки, занимаемой менеджером. Если он принят в постоянный штат фирмы, то комиссионные начисляются по описанному выше закону. Если же он находится на испытательном сроке, то его комиссионные составляют 75% от номинала.
Для решения этой задачи к функции Комиссионные1 надо добавить еще один аргумент – Ставка, который и идентифицирует ставку менеджера. Если менеджер находится на испытательном сроке, то значение аргумента Ставка полагается равным 0, а если он зачислен в постоянный штат, то значение аргумента Ставка полагается равным 1. Тогда, функция Комиссионные2 примет вид, представленный в листинге 8.
Листинг 8. Расчет комиссионных с учетом ставки менеджера
Function Комиссионные2(Продажи, Ставка)
Select Case Продажи
Case Is
Оплата= Продажи* 0.08
Case Is
Оплата = Продажи * 0.1
Case Is
Оплата = Продажи * 0.12
Case Else
Оплата= Продажи* 0.14
End Select
If Ставка = 0 Then
Комиссионные2 = 0.75 * Оплата
Else
Комиссионные2 = Оплата
End If
End Function
/>