Створення спеціальних функцій в Excel

Для створення спеціальних функцій, як і макросів, використовується мова програмування Visual Basic for Applications (VBA). Спеціальні функції відрізняються від макросів двома характерними рисами. По-перше, у них використовуються процедури типу Функція, а не Підпрограма. Це означає, що вони починаються з інструкції Function замість Sub і закінчуються інструкцією End Function замість End Sub. По-друге, вони виконують обчислення, а не дії. Спеціальні функції не обробляють певні типи інструкцій, зокрема такі, що вибирають і форматують діапазони. У цій статті описано, як створювати й використовувати спеціальні функції. Функції та макроси створюються в редакторі Visual Basic (VBE), який відкривається з Excel в окремому вікні.

Припустімо, ваша компанія пропонує знижку в розмірі 10 % тим, хто замовив більше 100 одиниць товару. Далі продемонстровано функцію, яка обчислює таку знижку.

У прикладі нижче наведено бланк замовлення, у якому зазначено всі позиції номенклатури, їхню кількість, ціну, знижку (якщо вона застосовується) і загальну ціну.

Щоб створити спеціальну функцію DISCOUNT у цій книзі, виконайте такі дії:

1. Натисніть клавіші Alt+F11, щоб відкрити редактор Visual Basic (на комп’ютері Mac натисніть клавіші Fn+Alt+F11), а потім клацніть Insert (Вставити) > Module (Модуль). У правій частині редактора Visual Basic відкриється вікно нового модуля.

2. Скопіюйте наведений нижче код і вставте його в новий модуль.

   Function DISCOUNT(quantity, price)
      If quantity >=100 Then
        DISCOUNT = quantity * price * 0.1
      Else
        DISCOUNT = 0
      End If
    
    DISCOUNT = Application.Round(Discount, 2)
   End Function
   

Тепер ви можете скористатися новою функцією DISCOUNT. Закрийте редактор Visual Basic, виберіть клітинку G7 і введіть такий текст:

=DISCOUNT(D7;E7)

Excel обчислює 10-відсоткову знижку на 200 одиниць товару за ціною 47,50 і повертає результат 950,00.

У першому рядку коду VBA (Function DISCOUNT(quantity, price)) зазначено, що функція DISCOUNT потребує два аргументи: quantity (кількість) і price (ціна). Коли ви викликаєте функцію в клітинці аркуша, аркуш має містити ці два аргументи. У формулі =DISCOUNT(D7;E7) D7 – це аргумент quantity, а E7 – price. Тепер можна скопіювати формулу DISCOUNT до діапазону клітинок G8:G13, щоб отримати наведені нижче результати.

Розгляньмо, як Excel інтерпретує цю функцію. Коли ви натискаєте клавішу Enter, Excel шукає ім’я DISCOUNT у поточній книзі та виявляє, що це спеціальна функція в модулі VBA. Імена аргументів у дужках (quantity та price) – це заповнювачі для значень, на яких ґрунтується обчислення знижки.

Інструкція If у наведеному нижче блоці коду аналізує аргумент quantity й визначає, чи кількість проданих одиниць товару перевищує або дорівнює 100.

If quantity >= 100 Then
 DISCOUNT = quantity * price * 0.1
Else
 DISCOUNT = 0
End If

Якщо кількість проданих одиниць товару перевищує або дорівнює 100, VBA виконує наведену нижче інструкцію, яка перемножує значення quantity (кількість) і price (ціна), а потім множить результат на 0,1.

Discount = quantity * price * 0.1

Результат зберігається як змінна Discount. Інструкція VBA, яка зберігає значення в змінній, має назву assignment (призначення), тому що вона оцінює вираз праворуч від знака рівності та призначає результат імені змінної ліворуч від нього. Змінна Discount має таке саме ім’я, що й функція, тож значення, яке зберігається в змінній, повертається до формули на аркуші, яка викликала функцію DISCOUNT.

Якщо значення аргументу quantity менше 100, VBA виконує таку інструкцію:

Discount = 0

Нарешті, ця інструкція округлює значення, призначене змінній Discount, до двох десяткових розрядів:

Discount = Application.Round(Discount, 2)

У VBA немає функції ROUND, а в Excel – є. Тож, щоб використовувати в цій інструкції функцію ROUND, слід дати VBA указівку шукати метод (функцію) Round в об’єкті Application (Excel). Щоб зробити це, перед словом Round потрібно додати слово Application. Цей синтаксис можна використовувати щоразу, коли потрібно отримати доступ до функції Excel із модуля VBA.

Спеціальна функція має починатися з інструкції Function і закінчуватися інструкцією End Function. Окрім імені функції інструкція Function зазвичай визначає один або кілька аргументів. Проте можна створити функцію без аргументів. В Excel є кілька вбудованих функцій, зокрема RAND і NOW, які не мають аргументів.

За інструкцією Function іде одна або кілька інструкцій VBA, які приймають рішення та виконують обчислення на основі аргументів, переданих функції. Нарешті, функція має містити інструкцію, що присвоює значення змінній із таким самим ім’ям, як у функції. Це значення повертається до формули, яка викликає функцію.

Кількість ключових слів VBA, які можна використовувати в спеціальних функціях, менша за кількість, які можна використовувати в макросах. Настроювані функції не можуть виконувати інші дії, окрім повернення значення до формули на аркуші або виразу, який використовується в іншому макросі чи функції VBA. Наприклад, настроювані функції не можуть змінювати розмір вікон, редагувати формулу в клітинці або змінювати шрифт, колір або параметри візерунка тексту в клітинці. Якщо включити код дії такого типу до процедури функції, функція поверне #VALUE! помилку #REF!.

Єдина дія, яку може виконувати функція (крім обчислень), – відображати діалогові вікна. У спеціальній функції можна використовувати інструкцію InputBox, щоб отримати дані від користувача. Передати інформацію користувачу можна за допомогою інструкції MsgBox. Крім того, можна використовувати спеціальні діалогові вікна (UserForms), але цю тему слід розглядати в окремій статті.

Навіть простий макрос або спеціальну функцію може бути складно зрозуміти. У такому випадку можна додати пояснювальний текст у вигляді приміток. Щоб додати примітку, перед пояснювальним текстом потрібно ввести апостроф. У наведеному нижче прикладі показано функцію DISCOUNT із примітками. Завдяки таким приміткам вам або іншому користувачу буде легше зрозуміти код VBA, повернувшись до нього через певний час. Якщо в майбутньому знадобиться змінити код, примітка допоможе згадати його первісне призначення.

Апостроф наказує Excel ігнорувати весь вміст праворуч у тому самому рядку, тому примітки можна додавати як окремі рядки або праворуч від коду VBA. Наприклад, ви можете почати відносно довгий блок коду приміткою, яка пояснює його призначення, а далі додавати примітки до окремих інструкцій у відповідних рядках.

Ще один спосіб задокументувати макрос або спеціальні функції – надати їм описові імена. Наприклад, щоб точніше пояснити призначення макросу, можна назвати його МіткиМісяців, а не просто Мітки. Використовувати описові імена для макросів і спеціальних функцій особливо корисно, коли ви створюєте багато процедур зі схожим, але не однаковим призначенням.

Ви можете документувати макроси й спеціальні функції на власний розсуд. Однак важливо обрати для себе найзручніший спосіб і дотримуватися його надалі.

Щоб використовувати спеціальну функцію, потрібно відкрити книгу з модулем, у якому створено функцію. Якщо цю книгу не відкрито, з'явиться #NAME? під час спроби використання функції. Якщо ви посилаєтеся на функцію в іншій книзі, перед іменем функції потрібно вказати ім'я книги, у якій міститься функція. Наприклад, якщо в книзі Personal.xlsb створюється функція DISCOUNT під назвою Personal.xlsb і викликається ця функція з іншої книги, потрібно ввести =personal.xlsb!discount(), а не просто =discount().

Щоб заощадити час і уникнути можливих помилок під час введення, ви можете вибрати спеціальну функцію в діалоговому вікні "Вставлення функції". Спеціальні функції відображаються в категорії "Визначені користувачем".

Простіший спосіб зробити спеціальні функції доступними в будь-який час – зберегти їх в окремій книзі, а потім зберегти її як надбудову. Після цього надбудову можна зробити доступною щоразу під час запуску програми Excel. Ось як це зробити:

Коли ви виконаєте ці дії, спеціальні функції будуть доступні щоразу після запуску Excel. Якщо потрібно додати функцію до бібліотеки, поверніться до редактора Visual Basic. У вікні проекту редактора Visual Basic під заголовком VBAProject відображатиметься модуль із таким самим ім’ям, як у файлу вашої надбудови. Надбудова матиме розширення XLAM.

Якщо двічі клацнути модуль у вікні проекту, редактор Visual Basic відобразить код функції. Щоб додати нову функцію, розташуйте курсор за інструкцією End Function, яка закінчує останню функцію у вікні коду, і введіть потрібний код. У такий спосіб можна створити будь-яку кількість функцій, і вони завжди відображатимуться в діалоговому вікні Вставлення функції в категорії "Визначені користувачем".

Початковий текст цієї статті було взято з книги Марка Доджа та Крейга Стінсона Microsoft Office Excel 2007 Inside Out. Пізніше статтю було оновлено для охоплення новіших версій Excel.