VBA-Lektion 14.2. Erstellen einer benutzerdefinierten Funktion

Mit VBA können Sie eine benutzerdefinierte Funktion erstellen (genannt User Defined Function), die wie normale Funktionen in Arbeitsblättern verwendet werden kann. Dies ist nützlich, wenn die vorhandenen Excel-Funktionen nicht ausreichen.

User Defined Function (UDF) ist eine vom Benutzer erstellte Funktion, die in der Standardfunktionalität von Excel nicht vorhanden ist.

In diesem Beispiel erstellen wir eine benutzerdefinierte IF-Funktion, die in Berechnungen auf einem Arbeitsblatt verwendet werden kann, ähnlich wie andere Excel-Funktionen.

Um den Wert von (B2) anzuzeigen, wenn Zelle (C2) den Wert („YES“) hat, könnten wir die Funktion IF in der Formel =IF(C2="YES",B2,0) verwenden:

Unser Ziel in diesem Fall ist es, eine Funktion zu erstellen, die Folgendes tun könnte =IF(C2 hat einen grünen Hintergrund,B2,0) und die wir wie folgt schreiben würden: =IF_GREEN(C2,B2):

Erstellen einer benutzerdefinierten Funktion (UDF)

Beginnen wir mit der Erstellung unserer eigenen UDF-Funktion:

Function IF_GREEN(paid As Range, amount)
     
End Function

Argumente:

Wenn die Prüfung in diesem Fall FALSE zurückgibt, ist der Wert immer 0, es gibt also keinen Grund, ein Argument dafür anzugeben.

Um zu überprüfen, ob die Farbe korrekt ist, können Sie die Zelle verwenden, die die benötigte Farbe enthält:

Function IF_GREEN(paid As Range, amount)

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

End Function

Um jedoch eine Abhängigkeit von der Zelle zu vermeiden, verwenden wir einfach die Nummer der gewünschten Farbe:

Function IF_GREEN(paid As Range, amount)

     green_color = 5296274 'Grün

End Function

Um die Hintergrundfarbnummer einer Zelle zu bestimmen, wählen Sie die Zelle aus und führen Sie dieses Makro aus:

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

Jetzt müssen wir nur noch die Farbe der Zelle mit IF überprüfen:

Function IF_GREEN(paid As Range, amount)
     
     Application.Volatile
     
     green_color = 5296274 'Grün
    
     If paid.Interior.color = green_color Then 'WENN WAHR
        IF_GREEN = amount
     Else 'WENN LÜGT
        IF_GREEN = 0
     End If
     
End Function

Jetzt ist unsere Funktion einsatzbereit.

Allerdings ist die Änderung der Zellfüllung nicht das Ereignis, das die Neuberechnung auslöst. Um Excel zu zwingen, die Werte neu zu berechnen, können Sie eine beliebige leere Zelle auswählen und auf „Löschen“ klicken oder eine Schaltfläche „Aktualisieren“ („Refresh“) hinzufügen, die alles aktualisiert, wenn Sie darauf klicken:

Sub refresh_macro()
     Application.Calculate
End Sub

Zusätzlich

Nachfolgend finden Sie eine verkürzte Schreibweise derselben Funktion:

Function IF_GREEN(paid As Range, amount)
     Application.Volatile
     IF_GREEN = 0 'WENN LÜGT
     If paid.Interior.color = 5296274 Then IF_GREEN = amount 'WENN WAHR
End Function

Sie können die Excel-Datei mit diesem Beispiel herunterladen, indem Sie hier klicken.