VBA. Использование пользовательских функций при расчетах в таблице

Если в программе Microsoft Excel 2000 часто используются одни и те же сложные расчеты, необязательно повторно вводить длинные и сложные формулы. Вместо этого можно создать пользовательскую функцию для выполнения нужных расчетов. В дальнейшем использование этой функции позволит упростить процесс ввода и обновления вновь создаваемых формул.

Пользовательские функции создаются в среде Microsoft Visual Basic® для приложений (VBA). VBA — это встроенный язык программирования Excel. VBA является весьма гибким инструментом, обеспечивающим все возможности формул Excel, а также обладающим многими другими достоинствами.

Предположим, например, что для расчета комиссионного вознаграждения используется сложная формула, в которой комиссионные проценты изменяются в зависимости от нескольких факторов, таких как товары и номенклатура товаров, проданных сотрудником, наличие нормы продажи для этого сотрудника, а также общий объем продаж за данный квартал. Вместо того чтобы при каждом рассчете комиссионных с продаж вводить длинную формулу, учитывающую все эти факторы, удобнее создать пользовательскую функцию комиссионных. Затем останется только ввести имя созданной функции в имеющиеся формулы расчета комиссионных.

Более того, в случае очередного изменения правил расчета комиссионных в данной организации не придется утомительно долго искать и вносить изменения во все эти сложные формулы. Для обновления всех формул в книге необходимо будет внести изменения только в одном месте — в пользовательской функции.

Ниже описан порядок создания и применения пользовательской функции. Для простоты в этом примере используется функция расчета комиссионных с продаж по фиксированной ставке 6%. Чтобы в полной мере воспользоваться преимуществами пользовательских функций, необходимо, вместо этого простого расчета процентов, подставить более сложную VBA-программу.

1. В меню Сервис выберите команду Макрос, а затем — команду Редактор Visual Basic.

2. В окне Microsoft Visual Basic в меню Insert выберите команду Module.

3. В окне Module номер введите программу, реализующую нужную функцию. Например, функция для расчета комиссионных с продаж на уровне 6% может выглядеть следующим образом:

В первой строке представленного выше примера создается функция с именем Commission для выполнения расчетов с использованием чисел или ссылок на ячейки. В этой функции используется переменная MyNum, предназначенная для хранения числа или значения ячейки. Во второй строке осуществляется расчет значения функции Commission путем умножения числа или ссылки на ячейку (MyNum) на коэффициент 0,06 (в более сложном случае для соответствующих расчетов может понадобиться несколько строк программы). Третья строка завершает текст программы для данной функции.