TOP

VBA-Lekcja 14.2. Tworzenie funkcji niestandardowej

YouLibreCalc for Excel logo

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:

  • paid As Range : komórka, którą sprawdzimy;
  • amount: wartość, którą wstawimy, jeśli TRUE.
  • 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.

    Application.Volatile mówi nam, że aplikacja (Excel) powinna być wrażliwa na zmiany (tak jak ma to miejsce w przypadku normalnej funkcji IF). Oznacza to, że funkcja zostanie przeliczona za każdym razem, gdy zmieni się wartość. Na przykład, jeśli zmienisz jakąkolwiek wartość w kolumnie Kwota (lub w dowolnej innej komórce), funkcja ta zostanie przeliczona i wyświetli się zaktualizowana kwota.

    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: 🢃