Office: Erstellen benutzerdefinierter Funktionen in Excel

Erstellen benutzerdefinierter Funktionen in Excel
Excel für Microsoft 365 Excel für Microsoft 365 für Mac Excel für das Web Excel 2019 Excel 2016 Excel 2019 für Mac Excel 2013 Excel 2010 Excel 2007 Excel 2016 für Mac Mehr... Weniger Obwohl Excel eine Vielzahl von integrierten Arbeitsblattfunktionen aufweist, ist möglicherweise nicht für jede Art von Berechnung, die Sie durchführen, eine Funktion vorhanden. Die Excel-Entwickler können unmöglich die Berechnungsanforderungen aller Benutzer vorausahnen. Stattdessen bietet Excel die Möglichkeit, benutzerdefinierte Funktionen zu erstellen, die in diesem Artikel beschrieben werden.

Erstellen einer einfachen benutzerdefinierten Funktion
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.
   
   

Hinweis: Damit der Code besser lesbar ist, können Sie Zeilen mithilfe der TAB-TASTE einrücken. Das Einrücken ist lediglich als Hilfe für Sie gedacht und kann optional vorgenommen werden, da der Code mit oder ohne Einzug ausgeführt wird. Nachdem Sie eine eingerückte Zeile eingegeben haben, wird im Visual Basic-Editor davon ausgegangen, dass die nächste Zeile ebenfalls eingerückt ist. Um die Zeile um ein Tabstoppzeichen zurückzusetzen (d. h. nach links zu verschieben), drücken Sie UMSCHALT+TAB.

Verwenden benutzerdefinierter Funktionen
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:

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.

Grundregeln für benutzerdefinierte Funktionen
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.

Verwenden von VBA-Schlüsselwörtern in benutzerdefinierten Funktionen
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.

Dokumentieren von Makros und benutzerdefinierten Funktionen
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.

Allgemeines Bereitstellen von benutzerdefinierten Funktionen
Wenn Sie eine benutzerdefinierte Funktion verwenden möchten, muss die Arbeitsmappe, die das Modul enthält, in dem Sie die Funktion erstellt haben, geöffnet sein. Wenn diese Arbeitsmappe nicht geöffnet ist, erhalten Sie eine #Name? Fehler beim Versuch, die Funktion zu verwenden. Wenn Sie in einer anderen Arbeitsmappe auf die Funktion verweisen, müssen Sie dem Funktionsnamen den Namen der Arbeitsmappe voranstellen, in der sich die Funktion befindet. Wenn Sie beispielsweise in einer Arbeitsmappe mit dem Namen Personal.xlsb eine Funktion mit dem Namen Discount erstellen und diese Funktion aus einer anderen Arbeitsmappe aufrufen, müssen Sie =personal.xlsb! Discount ()eingeben, nicht einfach = Rabatt ().

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:

Windows macOS 

1. Nachdem Sie die benötigten Funktionen erstellt haben, klicken Sie auf Datei > Speichern unter.
   
   In Excel 2007 klicken Sie auf die Microsoft Office-Schaltfläche und dann auf Speichern unter.
   
   
2. Öffnen Sie im Dialogfeld Speichern unter die Dropdownliste Dateityp, und wählen Sie Excel-Add-In aus. Speichern Sie die Arbeitsmappe unter einem aussagekräftigen Namen (z. B. MeineFunktionen) im Ordner Add-Ins. Im Dialogfeld Speichern unter wird dieser Ordner vorgeschlagen, sodass Sie lediglich den Standardspeicherort übernehmen müssen.
   
   
3. Nachdem Sie die Arbeitsmappe gespeichert haben, klicken Sie auf Datei > Excel-Optionen.
   
   In Excel 2007 klicken Sie auf die Microsoft Office-Schaltfläche und dann auf Excel-Optionen.
   
   
4. Klicken Sie im Dialogfeld Excel-Optionen auf die Kategorie Add-Ins.
   
   
5. Wählen Sie in der Dropdownliste Verwalten die Option Excel-Add-Ins aus. Klicken Sie dann auf Gehe zu.
   
   
6. Aktivieren Sie im Dialogfeld Add-Ins das Kontrollkästchen neben dem Namen, den Sie zum Speichern der Arbeitsmappe verwendet haben (siehe unten).
   
   
   
   
   
   

1. Nachdem Sie die benötigten Funktionen erstellt haben, klicken Sie auf Datei > Speichern unter.
   
   
2. Öffnen Sie im Dialogfeld Speichern unter die Dropdownliste Dateityp, und wählen Sie Excel-Add-In aus. Speichern Sie die Arbeitsmappe unter einem aussagekräftigen Namen, z. B. MeineFunktionen.
   
   
3. Nachdem Sie die Arbeitsmappe gespeichert haben, klicken Sie auf Extras > Excel-Add-Ins.
   
   
4. Klicken Sie im Dialogfeld Add-Ins auf die Schaltfläche "Durchsuchen", um nach dem Add-In zu suchen, klicken Sie auf Öffnen, und aktivieren Sie dann das Kontrollkästchen neben dem Add-In im Feld Verfügbare Add-Ins.
   
   

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.

Informationen zu den Autoren
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.

Benötigen Sie weitere Hilfe?
Sie können jederzeit einen Experten in der Excel Tech Community fragen, Unterstützung in der Answers Community erhalten oder aber ein neues Feature oder eine Verbesserung auf Excel User Voice vorschlagen.

:)