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

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

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

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

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

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

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

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

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

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

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 файл, що містить цей приклад, натиснувши тут.

Статті по темі:

  • VBA-Урок 14.1. Використання Excel функцій