Erstellen benutzerdefinierter Funktionen in Excel

Benutzerdefinierte Funktionen verwenden wie Makros die Programmiersprache VBA (Visual Basic for Applications). Sie unterscheiden sich von Makros in zwei wesentlichen Punkten. Erstens verwenden sie Function-Prozeduren anstelle von Sub-Prozeduren. Das heißt, dass sie mit einer Function-Anweisung anstelle einer Sub-Anweisung beginnen und mit End Function anstelle von End Sub enden. Zweitens werden damit Berechnungen anstelle von Aktionen ausgeführt. Bestimmte Arten von Anweisungen, z. B. Anweisungen, mit denen Bereiche ausgewählt und formatiert werden, sind von den benutzerdefinierten Funktionen ausgeschlossen. In diesem Artikel erfahren Sie, wie Sie benutzerdefinierte Funktionen erstellen und verwenden. Zum Erstellen von Funktionen und Makros verwenden Sie den Visual Basic-Editor (VBE), der in einem neuen Fenster unabhängig von Excel geöffnet wird.

Angenommen, Ihr Unternehmen bietet beim Verkauf eines Produkts einen Mengenrabatt von 10 Prozent, falls die Bestellung mehr als 100 Einheiten umfasst. In den folgenden Abschnitten wird das Erstellen einer Funktion zum Berechnen dieses Rabatts veranschaulicht.

Das folgende Beispiel zeigt ein Bestellformular, auf dem der jeweilige Artikel, die Menge, der Preis, der Rabatt (sofern zutreffend) und der sich ergebende Endpreis aufgelistet sind.

Führen Sie die folgenden Schritte aus, um eine benutzerdefinierte Funktion DISCOUNT (RABATT) zu erstellen:

1. Drücken Sie ALT+F11, um den Visual Basic-Editor zu öffnen (auf dem Mac drücken Sie FN+ALT+F11), und klicken Sie dann auf Einfügen > Modul. Ein Fenster für ein neues Modul wird auf der rechten Seite des Visual Basic-Editors angezeigt.

2. Kopieren Sie den folgenden Code, und fügen Sie ihn in das neue Modul ein.

   Function DISCOUNT(quantity, price)
      If quantity >=100 Then
        DISCOUNT = quantity * price * 0.1
      Else
        DISCOUNT = 0
      End If
    
    DISCOUNT = Application.Round(Discount, 2)
   End Function
   

Jetzt können Sie die neue Funktion DISCOUNT verwenden. Schließen Sie den Visual Basic-Editor, wählen Sie die Zelle G7 aus, und geben Sie Folgendes ein:

=DISCOUNT(D7;E7)

Excel berechnet den Rabatt von 10 Prozent für 200 Einheiten zu einem Stückpreis von 47,50 € und gibt 950,00 € zurück.

In der ersten Zeile des VBA-Codes, "Function DISCOUNT(quantity, price)", haben Sie angegeben, dass die Funktion DISCOUNT zwei Argumente erfordert: quantity (Menge) und price (Preis). Wenn Sie die Funktion in einer Arbeitsblattzelle aufrufen, müssen Sie diese beiden Argumente einschließen. In der Formel "=DISCOUNT(D7;E7)" ist D7 das Argument quantity (Menge), und E7 ist das Argument price (Preis). Jetzt können Sie die DISCOUNT-Formel in G8:G13 kopieren, um die unten gezeigten Ergebnisse zu erhalten.

Sehen wir uns einmal an, wie Excel diese Funktionsprozedur interpretiert. Wenn Sie die EINGABETASTE drücken, sucht Excel in der aktuellen Arbeitsmappe nach dem Namen DISCOUNT und stellt fest, dass es sich um eine benutzerdefinierte Funktion in einem VBA-Modul handelt. Die in Klammern eingeschlossenen Argumentnamen, quantity (Menge) und price (Preis), sind Platzhalter für die Werte, auf denen die Berechnung des Rabatts basiert.

Die WENN-Anweisung (If) im folgenden Codeblock wertet das Argument quantity (Menge) aus und bestimmt, ob die Anzahl der verkauften Artikel größer oder gleich 100 ist:

If quantity >= 100 Then
 DISCOUNT = quantity * price * 0.1
Else
 DISCOUNT = 0
End If

Ist die Anzahl der verkauften Artikel größer oder gleich 100, führt VBA die folgende Anweisung aus, mit der der Wert für quantity (Menge) mit dem Wert für price (Preis) multipliziert wird, und multipliziert dann das Ergebnis mit 0,1:

Discount = quantity * price * 0.1

Das Ergebnis wird als die Variable Discount (Rabatt) gespeichert. Eine VBA-Anweisung, die einen Wert in einer Variablen speichert, wird als Zuweisungsanweisung bezeichnet, da sie den Ausdruck auf der rechten Seite des Gleichheitszeichens auswertet und das Ergebnis dem Variablennamen auf der linken Seite zuweist. Da die Variable Discount (Rabatt) den gleichen Namen wie die Funktionsprozedur hat, wird der in der Variablen gespeicherte Wert an die Arbeitsblattformel zurückgegeben, die die Funktion DISCOUNT (RABATT) aufgerufen hat.

Wenn der Wert für quantity (Menge) kleiner als 100 ist, führt VBA die folgende Anweisung aus:

Discount = 0

Zum Schluss wird mit der folgenden Anweisung der Wert, der der Variablen Discount (Rabatt) zugewiesen ist, auf zwei Dezimalstellen gerundet:

Discount = Application.Round(Discount, 2)

VBA verfügt über keine Funktion ROUND (RUNDEN), aber in Excel ist eine solche Funktion enthalten. Wenn Sie ROUND (RUNDEN) in dieser Anweisung verwenden möchten, weisen Sie VBA an, im Application-Objekt (Excel) nach der Methode "Round" (Funktion) zu suchen. Dazu fügen Sie das Wort Application vor dem Wort "Round" hinzu. Verwenden Sie diese Syntax immer, wenn Sie von einem VBA-Modul auf eine Excel-Funktion zugreifen müssen.

Eine benutzerdefinierte Funktion muss mit einer Function-Anweisung beginnen und mit einer End Function-Anweisung enden. Zusätzlich zum Funktionsnamen ist in der Function-Anweisung normalerweise mindestens ein Argument angegeben. Sie können aber auch eine Funktion ohne Argumente erstellen. Excel enthält mehrere integrierte Funktionen (z. B. ZUFALLSZAHL und JETZT), die keine Argumente verwenden.

Nach der Function-Anweisung folgt eine Funktionsprozedur, die mindestens eine VBA-Anweisung enthält, mit der anhand von Argumenten, die an die Funktion übergeben werden, Entscheidungen getroffen und Berechnungen durchgeführt werden. Zum Schluss müssen Sie an einer Stelle in der Funktionsprozedur eine Anweisung einfügen, die einer Variablen mit demselben Namen wie die Funktion einen Wert zuweist. Dieser Wert wird an die Formel zurückgegeben, die die Funktion aufruft.

Die Anzahl der VBA-Schlüsselwörter, die Sie in benutzerdefinierten Funktionen verwenden können, ist geringer als die Anzahl von Schlüsselwörtern, die Sie in Makros verwenden können. Benutzerdefinierten Funktionen ist lediglich gestattet, einen Wert an eine Formel in einem Arbeitsblatt oder an einen Ausdruck, der in anderen VBA-Makros oder -Funktionen verwendet wird, zurückzugeben. Beispielsweise können mit benutzerdefinierten Funktionen nicht die Größe von Fenstern geändert, eine Formel in einer Zelle bearbeitet oder Schriftart, Farbe oder Musteroptionen für den Text in einer Zelle geändert werden. Wenn Sie einer Funktionsprozedur einen solchen "Aktionscode" hinzufügen, gibt die Funktion den Fehler #WERT! zurück.

Die einzige Aktion, die eine Funktionsprozedur (außer Berechnungen) ausführen kann, ist das Anzeigen eines Dialogfelds. Mithilfe einer InputBox-Anweisung in einer benutzerdefinierten Funktion können Sie eine Eingabe von dem Benutzer anfordern, der die Funktion ausführt. Mithilfe einer MsgBox-Anweisung können Sie dem Benutzer Informationen zukommen lassen. Sie können auch benutzerdefinierte Dialogfelder oder UserForms verwenden, doch wird dieses Thema nicht im Rahmen dieser Einführung behandelt.

Selbst einfache Makros und benutzerdefinierte Funktionen können schwer zu lesen sein. Sie können diese verständlicher machen, indem Sie erläuternden Text in Form von Kommentaren eingeben. Zum Hinzufügen von Kommentaren stellen Sie dem erläuternden Text ein Apostroph voran. Das folgende Beispiel zeigt beispielsweise die Funktion DISCOUNT mit Kommentaren. Durch das Hinzufügen von Kommentaren wie diesen können Sie oder andere Personen den VBA-Code im Lauf der Zeit einfacher verwalten. Wenn Sie in Zukunft eine Änderung des Codes vornehmen müssen, können Sie die ursprünglichen Eingaben einfacher verstehen.

Ein Apostroph weist Excel an, alles rechts davon in derselben Zeile zu ignorieren, sodass Sie Kommentare entweder in eigenen Zeilen oder rechts von Zeilen erstellen können, die VBA-Code enthalten. Sie können einen relativ langen Codeblock mit einem Kommentar beginnen, der den allgemeinen Zweck erläutert, und dann Kommentare in den Zeilen zum Dokumentieren einzelner Anweisungen verwenden.

Eine weitere Möglichkeit zum Dokumentieren von Makros und benutzerdefinierten Funktionen sind aussagekräftige Namen. Statt einem Makro beispielsweise den Namen Bezeichnungen zu geben, könnten Sie es Monatsbezeichnungen nennen, um den Zweck des Makros genauer zu beschreiben. Das Verwenden aussagekräftiger Namen für Makros und benutzerdefinierte Funktionen ist besonders hilfreich, wenn Sie viele Prozeduren erstellt haben, insbesondere dann, wenn Sie Prozeduren erstellen, die ähnlichen, jedoch nicht identischen Zwecken dienen.

Wie Sie Makros und benutzerdefinierte Funktionen dokumentieren, ist eine Frage Ihrer persönlichen Vorlieben. Wichtig ist, dass Sie sich für eine Dokumentationsmethode entscheiden und diese durchgängig verwenden.

Um eine benutzerdefinierte Funktion verwenden zu können, muss die Arbeitsmappe mit dem Modul, in dem Sie die Funktion erstellt haben, geöffnet sein. Wenn diese Arbeitsmappe nicht geöffnet ist, erhalten Sie eine #NAME? fehler, wenn Sie versuchen, die Funktion zu verwenden. Wenn Sie in einer anderen Arbeitsmappe auf die Funktion verweisen, müssen Sie dem Funktionsnamen den Namen der Arbeitsmappe vorangehen, in der sich die Funktion befindet. Wenn Sie beispielsweise eine Funktion namens DISCOUNT in einer Arbeitsmappe namens Personal.xlsb erstellen und diese Funktion aus einer anderen Arbeitsmappe aufrufen, müssen Sie =personal.xlsb!discount()eingeben, nicht einfach =discount().

Sie können einige Tastenanschläge (und somit mögliche Rechtschreibfehler) vermeiden, indem Sie die benutzerdefinierten Funktionen im Dialogfeld "Funktion einfügen" auswählen. Benutzerdefinierte Funktionen werden in der Kategorie "Benutzerdefiniert" angezeigt:

Eine einfachere Möglichkeit, um benutzerdefinierte Funktionen jederzeit verfügbar zu machen, ist das Speichern in einer anderen Arbeitsmappe und das anschließende Speichern dieser Arbeitsmappe als Add-In. Sie können dann das Add-In immer beim Ausführen von Excel bereitstellen. Hier erfahren Sie, wie das geht:

Nachdem Sie diese Schritte ausgeführt haben, stehen Ihre benutzerdefinierten Funktionen bei jedem Ausführen von Excel zur Verfügung. Wenn Sie diese zu Ihrer Funktionsbibliothek hinzufügen möchten, kehren Sie zum Visual Basic-Editor zurück. Wenn Sie im Projektexplorer des Visual Basic-Editors unter der Überschrift "VBA-Projekt" nachsehen, wird ein Modul angezeigt, das nach Ihrer Add-In-Datei benannt ist. Das Add-In hat die Erweiterung XLAM.

Durch Doppelklicken auf das Modul im Projektexplorer wird Ihr Funktionscode im Visual Basic-Editor angezeigt. Wenn Sie eine neue Funktion hinzufügen möchten, setzen Sie die Einfügemarke hinter die End Function-Anweisung, mit der die letzte Funktion im Codefenster endet, und beginnen Sie mit der Eingabe. Sie können beliebig viele Funktionen auf diese Weise erstellen, und diese stehen immer in der Kategorie "Benutzerdefiniert" im Dialogfeld Funktion einfügen zur Verfügung.

Dieser Inhalt wurde ursprünglich von Mark Dodge und Craig Stinson als Teil ihres Buchs Microsoft Office Excel 2007 Inside Out erstellt. Inzwischen wurde er aktualisiert, um auch neueren Excel-Versionen zu entsprechen.