Тема 3. VBA: Пользовательские функции
Visual Basic for Applications(в дальнейшем VBA) – является объектно-ориентированным языком программирования (ООЯП), позволяющим автоматизировать процесс решения многих экономических задач инструментальными средствами пакета MS Office. VBA в Excel - это сочетание одного из самых простых языков программирования и всех вычислительных возможностей Excel. Фирма Microsoft стремилась разработать простой язык программирования, который можно было бы использовать во всех приложениях этой фирмы. Требования к этому языку - минимальное время на его изучение, возможность разработки систем, объединяющих при работе несколько приложений. За основу был взят язык Basic. Теперь все приложения MS Office используют этот язык. VBA, относясь к ООЯП, применяет технологию визуального программирования. Основное отличие от обычных программ на Basic состоит в том, что наряду с обычными переменными и константами, эти программы манипулируют готовыми объектами приложений Microsoft Office, такими, например, как документы, абзацы, строки и слова Word; записи, поля, таблицы в Access; или рабочие книги, рабочие листы и диапазоны ячеек Excel. Чтобы создавать программный код на VBA, необходимо хорошо представлять себе функциональные возможности таких объектов, свойства, которыми они обладают и методы воздействия на них.
Основной программный код располагается в функциях и процедурах. Процедуры будут рассмотрены позднее, здесь же остановимся на функциях.
Функции, как стандартные (встроенные), так и пользовательские (созданные пользователем для своих нужд), в большей части предназначены для выполнения расчетов, анализа и преобразования данных. Они всегда возвращают результат, могут иметь аргументы (параметры), т.е. исходные данные, необходимые для получения результата. Пользовательские функции имеют некоторые ограничения: в них нельзя применять методы и изменять свойства объектов. Нежелательно также использование команд ввода-вывода данных. Вызов функции можно использовать на рабочем листе, обращаться к ней по имени из других функций и процедур.
По умолчанию функцию можно использовать в пределах данной книги, чтобы вызвать ее из других рабочих книг, надо установить ссылку на книгу с данной функцией. Пользовательская функция имеет вид:
Function имя ([аргумент as тип, аргумент as тип [,…]]) [as тип]
[инструкции]
имя = выражение
[Еxit Function]
End Function
Для создания пользовательской функции требуется активизировать редактор VBA (Alt + F11), активизировать окно проекта при его отсутствии (View / Project Explorer) и добавить в проект Модуль (Insert / Module).
Обращение к функции осуществляется с помощью Мастера функций (пиктограмма fx) из категории пользовательских функций.
Рис.5. Код функции
Индивидуальное задание № 3. Создать пользовательскую функцию для расчета НДФЛ, используя в качестве дополнительных аргументов данные табл. 3.
Таблица 3. Дополнительные аргументы функции НДФЛ
| N | Дополнительные параметры |
| доход, свыше которого налоговый вычет не предоставляется | |
| налоговый вычет на первого и второго ребенка и налоговый вычет на третьего и каждого последующего ребенка | |
| процент налога и доход, свыше которого налоговый вычет не предоставляется | |
| налоговый вычет на первого и второго ребенка | |
| налоговый вычет на третьего и каждого последующего ребенка | |
| процент налога | |
| доход, свыше которого налоговый вычет не предоставляется и налоговый вычет на первого и второго ребенка | |
| доход, свыше которого налоговый вычет не предоставляется и налоговый вычет на третьего и каждого последующего ребенка | |
| процент налога и налоговый вычет на первого и второго ребенка | |
| процент налога и налоговый вычет на третьего и каждого последующего ребенка |
Рассмотрим примеры пользовательских функций по обработке символьных выражений.
Пример 2. Определить, является ли символьное выражение палиндромом (палиндром – выражение, читаемое слева направо и справа налево одинаково). Данная задача будет решена несколькими способами.
Пример 3. Определить, имеется ли баланс открывающихся и закрывающихся круглых скобок в символьном выражении. Например, в выражении "1*(2+3)" баланс есть, в выражениях "1*((2+3)" и "1*)(2+3" баланса нет.
При решении использовались стандартные функции VBA. Их описание приведено в приложении 1.
Индивидуальное задание 4.Требуется добавить новое поле в БД на листе «Сотрудники». Ввод данных в новое поле необходимо осуществить с помощью самостоятельно созданной пользовательской функции. Номер варианта задания из табл. 4 назначается преподавателем.
Таблица 4. Варианты заданий
| N | Индивидуальное задание |
| Сформировать фамилию без инициалов, например Иванов А.П à Иванов | |
| Поставить точки в инициалах ИО, например Иванов АП à Иванов А.П. | |
| Поставить пробел после фамилии, например ИвановАП à Иванов АП | |
| Сформировать аббревиатуру ФИО, например Иванов А.П. à ИАП | |
| Сформировать название отдела без пробелов, например О К à ОК | |
| Проверить, имеется ли в поле ФИО запятая, например Иванов А,П. à есть, Иванов А.П. à нет | |
| В поле ФИО инициалы преобразовать в заглавные символы, например Иванов и.п. à Иванов И.П. | |
| Определить, есть ли в поле ФИО лишние пробелы, например Иванов А.П. à нет, Иванов А. П. à есть | |
| Определить количество заглавных букв в названии отдела, например, отдел кадров à 0, Отдел Маркетинга à 2 | |
| Определить, все ли буквы в поле Должность строчные, например, Директор à нет, дворник à да | |
| Определить, четное или нечетное количество символов в поле Должность | |
| Определить, начинается ли поле ФИО следующего сотрудника за интересующим сотрудником с той же буквы, например Иванов А.П и Петров М.О à нет, Иванов А.П и Ивкин М.О à да | |
| Определить, совпадают ли первый и последний символы в поле Должность без учета регистра, например Ректор à да, инженер à нет | |
| Определить, сколько букв по порядку с начала совпадают у полей Отдел и Должность без учета регистра, например Бухгалтерия и бухгалтер à 9, Маркетинга и маркетолог à6 | |
| Сформировать наименование должности с пробелами между букв, например дворник à д в о р н и к | |
| Определить, является ли фамилия двойной, например Мамин-Сибиряк Д.Н. à да, Иванов А.П. à нет | |
| Если фамилия является двойной, преобразовать первые буквы первой и второй фамилии в заглавные, например мамин-сибиряк Д.Н. à Мамин-Сибиряк Д.Н. | |
| Определить количество согласных букв в поле Должность, например дворник à 5 | |
| Сформировать наименование должности с заглавными гласными буквами, например охранник à ОхрАннИк | |
| Определить процент гласных букв в поле Должность, например агент à40% |