In diesem Tutorial wird erklärt, wie Sie benutzerdefinierte Funktionen in VBA erstellen.
VBA besteht aus Unterprozeduren und Funktionsprozeduren. Funktionsprozeduren geben einen Wert zurück und können von Unterprozeduren aufgerufen oder in der Excel-Tabelle verwendet werden, wobei der von der Funktion erzeugte Wert an die Excel-Tabelle zurückgegeben wird. Excel hat natürlich eine Reihe von eingebauten Funktionen - wie die Sum-Funktion oder die If-Funktion. Die in VBA geschriebenen Funktionsprozeduren werden auf ähnliche Weise wie die integrierte Funktion verwendet und werden als benutzerdefinierte Funktionen (UDFs) bezeichnet.
Warum eine UDF erstellen?
Erforderliche Funktion fehlt
Einer der Hauptgründe für die Erstellung einer UDF in Excel besteht darin, dass keine integrierte Funktion vorhanden ist, die diese Aufgabe für Sie übernimmt. Das Schreiben einer eigenen Funktion in VBA ist normalerweise der effizienteste Weg, um das Problem zu lösen. Die folgende Funktion wandelt einen Wert von Kilogramm in Pfund um, wenn ein variabler Parameter (dblKilo) verwendet wird, um den Wert des Kilogramms für die Berechnung zu erhalten.
Ersetzen einer Unterroutine (Makro)
Sie könnten eine Unterprozedur (Makro) schreiben, um die Aufgabe für Sie zu lösen - aber Unterprozeduren geben keinen Wert zurück und sind nicht dynamisch - mit anderen Worten, wenn sich die Werte in Ihrem Arbeitsblatt ändern, müssen Sie die erneut ausführen Makro, damit die Berechnungen im Makro Ihre Daten aktualisieren. Die folgende Unterprozedur würde auch unsere Kilos in Pfund umrechnen, aber jedes Mal, wenn sich die Daten in A1 ändern, müssen Sie das Makro erneut ausführen, um das Ergebnis zu aktualisieren.
Ersetzen einer Formel
Möglicherweise haben Sie eine wirklich komplizierte Formel in Excel, die Sie wiederholt verwenden müssen - das Einfügen der Formel in VBA-Code erleichtert das Lesen und Verstehen - und möglicherweise auch Raum für Benutzerfehler beim Eingeben der Formel.
Erstellen von UDFs
Um eine UDF zu erstellen, fügen Sie zunächst entweder Ihrer Excel-Arbeitsmappe ein Modul hinzu, oder, wenn Sie über eine persönliche Makro-Arbeitsmappe verfügen, können Sie entweder ein vorhandenes Modul darin verwenden oder ein neues hinzufügen. Dazu müssen Sie sich im Visual Basic Editor (VBE) befinden. Um zur VBE zu gelangen, drücken Sie ALT + F11 oder klicken Sie auf die Visual Basic-Option in der Registerkarte Entwickler Ihres Menübands.
SPITZE: Wenn die Registerkarte Entwickler in Ihrem Menüband nicht aktiviert ist, gehen Sie zu Datei, Optionen und klicke auf Menüband anpassen. Stellen Sie sicher, dass das Kontrollkästchen Entwicklertools aktiviert ist und klicken Sie auf OK.
Um ein neues Modul einzufügen, wählen Sie das VBA-Projekt aus, in das Sie das Modul einfügen möchten (entweder das VBA-Projekt für das aktuelle Buch, in dem Sie arbeiten, oder das Persönliche Makro-Arbeitsbuch), klicken Sie auf das Einfügung Menü und klicken Sie auf Modul
Nachdem Sie Ihr Modul erstellt haben, können Sie mit der Erstellung Ihres UDF beginnen.
Alle UDFs beginnen mit Function und dann dem Namen der UDF. Funktionen können privat oder öffentlich sein, aber normalerweise möchten Sie, dass eine UDF öffentlich ist, damit sie im Dialogfeld Funktion einfügen in Excel angezeigt werden (siehe Verwenden einer Funktion aus einem Excel-Blatt weiter unten in diesem Artikel). Wenn Sie das Schlüsselwort Private nicht vor die Funktion setzen, ist die Funktion automatisch öffentlich.
| 123 | Funktion TestFunction1(intA As Integer) As IntegerTestFunktion1= intA * 7Endfunktion |
Die obige Funktion hat ein einzelnes Argument (intA). Sie können eine Funktion mit mehreren Argumenten erstellen
| 123 | Funktion TestFunction2(intA As Integer, intB As Integer, intC As Integer) As IntegerTestFunktion2 = (intA * intB) + intCEndfunktion |
Sie können auch eine Funktion mit optionalen Argumenten erstellen. Wenn das Argument weggelassen wird, können Sie einen Standardwert für das Argument in der Funktion festlegen.
| 123 | Funktion TestFunction3(intA As Integer, intB As Integer, Optional intC As Integer=10) As IntegerTestfunktion3 = (intA * intB) + intCEndfunktion |
Verwenden einer Funktion aus einer Excel-Tabelle heraus
Die von Ihnen erstellten Funktionen werden standardmäßig in Ihrer Funktionsliste im Abschnitt Benutzerdefiniert der Funktionsliste angezeigt.
Klicken Sie auf die fx , um das Dialogfeld Funktion einfügen anzuzeigen.
Auswählen Benutzerdefinierten aus der Kategorieliste
Wählen Sie die gewünschte Funktion aus den verfügbaren Benutzerdefinierte Funktionen.
Wenn Sie mit dem Schreiben Ihrer Funktion in Excel beginnen, sollte die Funktion alternativ in der Dropdown-Liste der Funktionen angezeigt werden.
Speichern der Funktionen mit Ihrer Excel-Datei
Da Funktionen in VBA-Code geschrieben werden, liegt es nahe, dass der Code für die Arbeitsmappe verfügbar sein muss, damit er in der Excel-Tabelle verwendet werden kann. Sie können Ihre Funktionen entweder in der Arbeitsmappe speichern, in der Sie sie verwenden, oder Sie können sie in Ihrer persönlichen Makro-Arbeitsmappe speichern. Ihre persönliche Makro-Arbeitsmappe ist eine versteckte Datei, die immer dann verfügbar ist, wenn Excel geöffnet ist, und daher für jede Arbeitsmappe in Excel verfügbar ist. Es wird normalerweise erstellt, wenn Sie ein Makro aufzeichnen und die Option zum Speichern des Makros in der persönlichen Makroarbeitsmappe auswählen.
Wenn Sie Ihre Funktionen in der Arbeitsmappe speichern möchten, an der Sie arbeiten, müssen Sie sicherstellen, dass beim Speichern der Arbeitsmappe diese als „Makro-aktivierte Arbeitsmappe‘ oder ein xlsm Datei.
