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

Аргументи:

В даному випадку, якщо перевірка видає 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

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

Однак, зміна заливки комірки не є подією, що запускає перерахунок. Щоби примусити Ексель перерахувати значення, ви можете вибрати будь-яку порожню комірку та натиснути "Видалити" або добавити кнопку "Оновити" ("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 файл, що містить цей приклад, натиснувши тут: 🢃