Власні функції в Excel: зроби один раз і користуйся постійно!

Доброго дня, друзі!

Сьогодні вночі я повернувся з Києва, де проводив черговий тренінг – цього разу з програмування мовою VBA у середовищі Microsoft Excel.

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

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

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

Давайте також уявимо, що обчислюємо середній бал ми за таким правилом – відкидаємо найбільшу і найменшу оцінки, а те, що зосталось усереднюємо.

Для розрахунків середнього балу, за припущення, що оцінки знаходяться у комірках А1:А25, вам доведеться написати формулу на кшталт такої:

=(SUM(A1:A25)-MIN(A1:A25)-MAX(A1:A25))/(COUNT(A1:A25)-2)

Звичайно, ви можете справедливо розкритикувати такий спосіб розрахунків, однак ще раз повторюю – це чисто гіпотетичний сценарій, приклад, який я щойно вигадав.

Формула, яку я навів, досить невеличка, однак якщо вам треба ввести її для обчислення оцінок різних аспектів заходу хоча б з десяток разів, ви вже замислитесь про те, як би її скоротити!

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

Почнемо з виклику середовища розробки VBA – натиснемо Alt+F11. Ви маєте побачити щось подібне:

Не лякаємось, а додаємо новий модуль з кодом. Для цього у меню Insert обираємо пункт Module:

У результаті відкриється віконце для вводу коду:

Тут ви маєте написати ось такий код (зараз я все поясню):

Public Function RealAVG(r As Variant) As Variant

      
        RealAVG = (WorksheetFunction.Sum(r) – WorksheetFunction.Min(r) – WorksheetFunction.Max(r)) / (WorksheetFunction.Count(r) – 2)
   

End Function

Середній рядок (починається з “RealAVG=”) пишемо в один рядок – тут, в блозі він просто не вмістися за шириною запису. Так, в VBA можна переносити рядки (за допомогою пробілу і підкреслення), однак в даному випадку ми обійдесось без цього.

Все! Тепер ви можете під час роботи в Excel замість довгої формули писати просто:

=RealAVG(A1:A25)

Працює так само, але набагато коротше, чи не так?

Давайте тепер розглянемо код нашої функції аби зрозуміти що є що, і як вам перенести до VBA свої власні формули.

У першому рядку ми написали, що далі буде опис публічної (доступної звідусюди у вашому документі) функції з іменем RealAVG (“справжнє середнє”). Ім’я ви можете вигадати будь-яке. Лише одна порада – воно має бути красномовним, щоб одразу можна було зрозуміти що робить функція.

Далі в дужках йдуть параметри – ті початкові дані, які функція буде використовувати для обчислення результату. У нас параметр тільки один – змінна з ім’ям r типу Variant, тобто її значення може бути будь-яким.  У нашому випадку ми повинні дати функції дійсно один параметр – адресу діапазону з початковими даними. Якщо вам потрібно більше параметрів, опишіть їх так само і через кому.

Далі ми бачимо слова As Variant – це ми говоримо, що результат, який поверне нам функція, може бути будь-якого типу – число, рядок, що завгодно. Аби не морочитись, ви можете описувати подібним чином всі свої функції.

А далі – найцікавіше – ми описуємо правило обчислення результату. Робиться це просто – пишете ім’я функції, потім знак рівності (тут він означає присвоєння нового значення) і далі – формулу для обчислення. Думаю, ви її впізнали. Єдина відмінність від екселівської формули – замість SUM ви пишете WorksheetFunction.Sum. Так само чинимо і з іншими функціями. Чому? Думаю, ви вже здогадались – VBA не знає екселівських функцій, тож треба йому сказати, що Sum – це не його власна функція, а одна з тих, які ви можете використовувати у комірках листів книги в Excel. До речі, VBA підтримує не всі з них! Будьте уважними!

На цьому можна було б і закінчити, але є ще два нюанси:

Перший: а що, коли у якості аргумента ви випадкого напишете нісенітницю? Наприклад, ось так: =RealAVG("FDDGhhkjk")

Хотілося б, щоб у таких випадках функція автоматично інформувала вас про помилки. І це можна зробити! Достатньо змінити вашу функцію таким чином:

Public Function RealAVG(r As Variant) As Variant
On Error GoTo Err

        RealAVG = (WorksheetFunction.Sum(r) – WorksheetFunction.Min(r) – WorksheetFunction.Max(r)) / (WorksheetFunction.Count(r) – 2)

On eroor GoTo 0
Exit Function

Err:
    RealAVG = "Wrong function parameter!"
    ActiveCell.Font.Color = vbRed
0:

End Function

Тепер, якщо результат не вдалось обчислити, функція замість середнього балу виведе червоним кольором напис "Wrong function parameter!". Думаю, як працює код зрозуміло. Це, звичайно, не ідеал функції,

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

До зустрічі за тиждень!