VBA-Урок 14.2. Створення користувацької функції

За допомогою VBA ви можете створити власну функцію (так звану User Defined Function), яку можна використовувати на робочих аркушах так само, як і звичайні функції. Це корисно, коли існуючих функцій Excel недостатньо.

User Defined Function (UDF) - це функція, створена самим користувачем, та якої немає в стандартному функціоналі Excel.

В цьому прикладі ми створимо власну функцію IF, що може бути використана в розрахунках на робочому аркуші, подібно до інших Excel функцій.

Щоби відобразити значення (B2) якщо комірка (C2) має значення ("YES"), ми могли б використати функцію IF у формулі =IF(C2="YES",B2,0):

Нашою ціллю в цьому випадку є створення функції, яка б могла робити це =IF(C2 has a green background,B2,0), і яку ми б записали, типу: =IF_GREEN(C2,B2):

Створення власної функції (UDF)

Почнемо створювати нашу власну UDF функцію:

Function IF_GREEN(paid As Range, amount)
     
End Function

Аргументи:

paid As Range : комірка, яку ми будемо перевіряти;

amount : значення, яке ми будемо вставляти, якщо TRUE.

В даному випадку, якщо перевірка видає FALSE, значення завжди буде 0, отож немає причин прописувати аргумент для цього.

Щоби перевірити, чи колір є правильним, ви можете використати комірку, яка містить необхідний вам колір:

Function IF_GREEN(paid As Range, amount)

     green_color = Sheets("Sheet1").Range("K1").Interior.color

End Function

Але щоб уникнути залежності від комірки, ми просто використаємо номер потрібного кольору:

Function IF_GREEN(paid As Range, amount)

     green_color = 5296274 'Зелений

End Function

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

Sub test_color()
     MsgBox ActiveCell.Interior.color
End Sub

Тепер, все що нам необхідно зробити, це перевірити колір комірки використовуючи IF:

Function IF_GREEN(paid As Range, amount)
     
     Application.Volatile
     
     green_color = 5296274 'Зелений
    
     If paid.Interior.color = green_color Then 'ЯКЩО ПРАВДА
        IF_GREEN = amount
     Else 'ЯКЩО БРЕХНЯ
        IF_GREEN = 0
     End If
     
End Function

Тепер наша функція готова до використання.

Application.Volatile говорить нам, що програма (Excel) має бути чутливою до змін (як це має місце при роботі звичайної функції IF). Це означає, що вона буде перераховувати функцію кожен раз, коли змінюється значення. Наприклад, якщо ви зміните будь-яке значення в колонці Amount (або, навіть, будь-яку іншу клітинку), то ця функція буде перерахована і буде відображена оновлена сума.

Однак, зміна заливки комірки не є подією, що запускає перерахунок. Щоби примусити Ексель перерахувати значення, ви можете вибрати будь-яку порожню комірку та натиснути "Видалити" або добавити кнопку "Оновити" ("Refresh"), яка все оновлюватиме, коли ви її натискатимите:

Sub refresh_macro()
     Application.Calculate
End Sub

Додатково

Нижче наведений скорочений спосіб написання тієї ж функції:

Function IF_GREEN(paid As Range, amount)
     Application.Volatile
     IF_GREEN = 0 'ЯКЩО БРЕХНЯ
     If paid.Interior.color = 5296274 Then IF_GREEN = amount 'ЯКЩО ПРАВДА
End Function

Ви можете завантажити Excel файл, що містить цей приклад, натиснувши тут: 🢃