Загрузка...
Учебная работа. Лабораторная работа: Пользовательские функции рабочего листа Visual Basic
Функция юзера
юзер может сделать свою функцию в обычном модуле редактора 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
Функция
Возвращаемое
Абс (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 – наиблежайшее отрицательное целое число, большее или равное обозначенному.
Сделаем пользовательскую функцию:
.
В новеньком обычном модуле наберите код, представленный в листинге 3.
Листинг 3. Пользовательская функция с внедрением интегрированной математической функции
Function F(x)
Pi = Atn (1) * 4
F = Cos (Pi * x) ^ 2
End Function
В VBA нет функции, возвращающей число П («пи»), потому оно определяется через функцию Ant (арктангенс): Pi = 4 * Atn (1). Число П можно задать очевидно, указав достаточное число означающих цифр, но этот подход наименее элегантен, чем при помощи функции Ant.
метод внедрения функции юзера
Разместите данные, нужные для решения задачки, на рабочем листе (как показано на рисунке) и при помощи Мастера функций вызовите новейшую функцию:
Оператор условного перехода
Оператор условного перехода задает выполнение определенных групп инструкций зависимо от значения выражения.
Синтаксис:
If condition Then [statements ] [ Else elsestatements ]
Если условие condition воспринимает
к примеру, если скидка (скажем 50%) применяется лишь к суммам больше 10001, то в VBA это можно записать последующим образом:
If Money > 1000 Then Discount = 0.05 Else Discount = 0
либо, что равносильно
If Money > 1000 Then Discount = 0.05
Допускается также внедрение блочной формы синтаксиса, которая нередко упрощает восприятие оператора условного перехода. Направьте внимание на то, что при блочной организации оператора условного перехода употребляется ключевое слова End If для обозначения окончания оператора.
If condition Then
[statements ]
[ Else
[elsestatements ]]
End If
Приведенный выше пример со скидкой можно записать в последующей эквивалентной блочной структуре:
If Money > 1000 Then
Discount = 0.05
Else
Discount = 0
End If
либо
If Money > 1000 Then
Discount = 0.05
End If
Дерево критерий может оказаться еще наиболее сложным, чем просто проверка 1-го условия. В этом случае оператор If….Then….ElseIf, который дозволяет инспектировать множественные условия. Последующий пример указывает порядок проверки критерий.
If condition-1 Then
[Statements]
[ElseIf condition-2 Then
[elseifstatements]
[Else
[elsestatements]]
End If
Первым поверяется условие condition-1. Если оно имеет слово Else.
Пользовательская функция с 2-мя критериями
Поглядим для функции 
в обычном модуле пользовательскую функцию (листинг 3).
Листинг 4. Пользовательская функция с 2-мя критериями (вариант 1).
Function Y(x)
If x < 0.5 Then
Y=(1+Абс(0.2-x))/(1+x+x^2)
Else
Y=x^(1/3)
End If
End Function
естественно, ту же самую можно было бы запрограммировать с внедрением 2-ух операторов If. Но хотя этот код содержит меньше строк, он наименее лучше (листинг 4).
Пользовательская функция с 3-мя критериями
Сделаем в обычном модуле пользовательскую функцию (листинг 5).для функции
Листинг 5. Пользовательская функция с 2-мя критериями (вариант 1).
Function Z(x)
Select case x
Case Is < 0.2
Z=1+ Log(1 + Абс(x))
Case Is <= 0.8
Z=(1+x^(1/2))/(1+x)
Case Else
Z=2*Exp(-2*x)
End Select
End Function
Эту функцию можно запрограммировать с внедрением оператора If.
Листинг 6. Пользовательская функция с 2-мя критериями (вариант 2).
Function функцияZ1(x)
If x < 0.2 Then
функцияZ1 = 1 + Log(1 + Абс(x))
ElseIf x > 3.8 Then
функцияZ1 = 2 * Exp(-2 * x)
Else
функцияZ1 = (1 + x ^ (1 / 2)) / (1 + x)
End If
End Function
Область видимости переменной
В программке все идентификаторы (а именно, имена переменных и функций) имеют определенную область видимости (scope). Область видимости переменной описывает ту область кода, которая «понимает» о существовании данной переменной.
Переменная, объединенная снутри функции, не быть может применена либо найдена вне данной функции. Это значит, что юзер может применять то же самое имя переменной в иной функции, и VBA не перепутает их.
Таковым образом, для каждой из рассмотренных выше функций нет нужды создавать собственный обычный модуль.
Их все можно расположить в одном модуле, и VBA не запутается.
Код нескольких пользовательских функций в модуле
Примечание. Редактор Visual Basic для облегчения зрительного развития местоположения функций в модуле делает меж ними разделительные полосы. Для того чтоб эти разделительные полосы показывались на дисплее, изберите команду Tools — Options. На дисплее отобразится диалоговое окно Options. На вкладке Editor в группе Window Settings установите флаг Procedure Separator.
Расчет комиссионных
Разглядим задачку: менеджеру по продажам нужно создать функцию, позволяющую рассчитывать комиссионные. Процент комиссионных зависит от размера проданного продукта и начисляется по последующему правилу, представленному в таблице.
Размер продаж за недельку, руб.
Комиссионные, %
от 0 до 9999
8
от 10000 до 19999
10
от 20000 до 39999
12
Наиболее 40000
14
Построим для расчета комиссионных в обычном модуле пользовательскую функцию (листинг 7).
Листинг 7. Расчет комиссионных
Function Комиссионные1(Реализации)
If Реализации <= 9999 Then
Комиссионные1 = Реализации * 0.08
ElseIf Реализации <= 19999 Then
Комиссионные1 = Реализации * 0.1
ElseIf Реализации <= 39999 Then
Комиссионные1 = Реализации * 0.12
Else
Комиссионные1 = Реализации * 0.14
End If
End Function
Усложним задачку. Будем считать, что комиссионные зависят от ставки, занимаемой менеджером. Если он принят в неизменный штат конторы, то комиссионные начисляются по описанному чуть повыше закону. Если же он находится на испытательном сроке, то его комиссионные составляют 75% от номинала.
Для решения данной нам задачки к функции Комиссионные1 нужно добавить еще один аргумент – Ставка, который и идентифицирует ставку менеджера. Если Менеджернаходится на испытательном сроке, то
Листинг 8. Расчет комиссионных с учетом ставки менеджера
Function Комиссионные2(Реализации, Ставка)
Select Case Реализации
Case Is <= 9999
Оплата = Реализации * 0.08
Case Is <= 19999
Оплата = Реализации * 0.1
Case Is <= 39999
Оплата = Реализации * 0.12
Case Else
Оплата = Реализации * 0.14
End Select
If Ставка = 0 Then
Комиссионные2 = 0.75 * Оплата
Else
Комиссионные2 = Оплата
End If
End Function
1
тут и дальше, если не указана валютная единица, то имеются в виду некоторые условные единицы.
]]>