VBA-Lekcja 14.2. Tworzenie funkcji niestandardowej

Za pomocą VBA możesz utworzyć niestandardową funkcję (zwaną User Defined Function), której można używać w arkuszach kalkulacyjnych tak samo jak zwykłych funkcji. Jest to przydatne, gdy istniejące funkcje Excel nie są wystarczające.

User Defined Function (UDF) to funkcja utworzona przez użytkownika, która nie istnieje w standardowej funkcjonalności Excel.

W tym przykładzie utworzymy niestandardową funkcję IF, której można używać w obliczeniach w arkuszu, podobnie jak inne funkcje Excel.

Aby wyświetlić wartość (B2), jeśli komórka (C2) ma wartość („YES”), możemy użyć funkcji IF we wzorze =IF(C2="YES",B2,0):

Naszym celem w tym przypadku jest stworzenie funkcji, która mogłaby to zrobić =IF(C2 ma zielone tło, B2,0) i którą zapisalibyśmy w następujący sposób: =IF_GREEN(C2,B2):

Tworzenie funkcji niestandardowej (UDF)

Zacznijmy tworzyć własną funkcję UDF:

Function IF_GREEN(paid As Range, amount)
     
End Function

Argumenty:

W tym przypadku, jeśli sprawdzenie zwróci FALSE, wartość będzie zawsze równa 0, więc nie ma powodu podawać dla niej argumentu.

Aby sprawdzić, czy kolor jest prawidłowy, możesz użyć komórki zawierającej potrzebny kolor:

Function IF_GREEN(paid As Range, amount)

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

End Function

Aby jednak uniknąć zależności od komórki, po prostu użyjemy numeru żądanego koloru:

Function IF_GREEN(paid As Range, amount)

     green_color = 5296274 'Zielony

End Function

Aby określić numer koloru tła komórki, wybierz komórkę i uruchom to makro:

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

Teraz wszystko, co musimy zrobić, to sprawdzić kolor komórki za pomocą IF:

Function IF_GREEN(paid As Range, amount)
     
     Application.Volatile
     
     green_color = 5296274 'Zielony
    
     If paid.Interior.color = green_color Then 'JEŚLI PRAWDA
        IF_GREEN = amount
     Else 'JEŚLI KŁAMIE
        IF_GREEN = 0
     End If
     
End Function

Teraz nasza funkcja jest gotowa do użycia.

Jednak zmiana wypełnienia komórek nie jest zdarzeniem powodującym ponowne obliczenie. Aby zmusić program Excel do ponownego obliczenia wartości, możesz zaznaczyć dowolną pustą komórkę i kliknąć Usuń lub dodać przycisk Odśwież („Refresh”), który odświeży wszystko po kliknięciu:

Sub refresh_macro()
     Application.Calculate
End Sub

Dodatkowo

Poniżej znajduje się skrócony sposób zapisu tej samej funkcji:

Function IF_GREEN(paid As Range, amount)
     Application.Volatile
     IF_GREEN = 0 'JEŚLI KŁAMIE
     If paid.Interior.color = 5296274 Then IF_GREEN = amount 'JEŚLI PRAWDA
End Function

Możesz pobrać plik Excel zawierający ten przykład, klikając tutaj: 🢃