In diesem Tutorial lernen Sie, Funktionen mit und ohne Parameter in VBA zu erstellen und zu verwenden
VBA enthält eine große Anzahl integrierter Funktionen, die Sie verwenden können, aber Sie können auch Ihre eigenen schreiben. Wenn Sie Code in VBA schreiben, können Sie ihn in eine Unterprozedur oder eine Funktionsprozedur schreiben. Eine Funktionsprozedur kann einen Wert an Ihren Code zurückgeben. Dies ist äußerst nützlich, wenn Sie möchten, dass VBA eine Aufgabe ausführt, um ein Ergebnis zurückzugeben. VBA-Funktionen können auch aus Excel heraus aufgerufen werden, genau wie die integrierten Excel-Funktionen von Excel.
Erstellen einer Funktion ohne Argumente
Um eine Funktion zu erstellen, müssen Sie die Funktion definieren, indem Sie der Funktion einen Namen geben. Die Funktion kann dann als Datentyp definiert werden, der den Datentyp angibt, den die Funktion zurückgeben soll.
Vielleicht möchten Sie eine Funktion erstellen, die bei jedem Aufruf einen statischen Wert zurückgibt - ein bisschen wie eine Konstante.
| 123 | Funktion GetValue() As IntegerGetValue = 50Endfunktion |
Wenn Sie die Funktion ausführen würden, würde die Funktion immer den Wert 50 zurückgeben.
Sie können auch Funktionen erstellen, die auf Objekte in VBA verweisen, aber Sie müssen das Set-Schlüsselwort verwenden, um den Wert der Funktion zurückzugeben.
| 123 | Funktion GetRange() als RangeSet GetRange = Range("A1:G4")Endfunktion |
Wenn Sie die obige Funktion in Ihrem VBA-Code verwenden würden, würde die Funktion immer den Zellbereich A1 bis G4 in dem Arbeitsblatt zurückgeben, in dem Sie arbeiten.
Aufrufen einer Funktion aus einer Unterprozedur
Nachdem Sie eine Funktion erstellt haben, können Sie sie an einer anderen Stelle in Ihrem Code aufrufen, indem Sie eine Unterprozedur verwenden, um die Funktion aufzurufen.
Der Wert 50 wird immer zurückgegeben.
Sie können die GetRange-Funktion auch aus einer Unterprozedur aufrufen.
Im obigen Beispiel wird die GetRange-Funktion von der Sub-Prozedur aufgerufen, um die Zellen im Bereichsobjekt fett zu formatieren.
Funktionen erstellen
Einzelargument
Sie können Ihrer Funktion auch einen oder mehrere Parameter zuweisen. Diese Parameter können als Argumente bezeichnet werden.
| 123 | Funktion ConvertKilosToPounds (dblKilo als Double) als DoubleConvertKiloToPounds = dblKilo*2.2Endfunktion |
Wir können dann die obige Funktion aus einer Unterprozedur aufrufen, um herauszufinden, wie viele Pfund eine bestimmte Menge Kilo sind.
Eine Funktion kann bei Bedarf von mehreren Prozeduren in Ihrem VBA-Code aufgerufen werden. Dies ist sehr nützlich, da es Sie davon abhält, immer wieder den gleichen Code zu schreiben. Es ermöglicht Ihnen auch, lange Prozeduren in kleine überschaubare Funktionen zu unterteilen.
Im obigen Beispiel haben wir 2 Prozeduren - jede von ihnen verwendet die Funktion, um den Pfundwert der Kilos zu berechnen, die ihnen in der übergeben wurden dblKilo Argument der Funktion.
Mehrere Argumente
Sie können eine Funktion mit mehreren Argumenten erstellen und die Werte über eine Unterprozedur an die Funktion übergeben.
| 123 | Funktion CalculateDayDiff(Date1 as Date, Date2 as Date) as DoubleCalculateDayDiff = Date2-Date1Endfunktion |
Wir können dann die Funktion aufrufen, um die Anzahl der Tage zwischen 2 Daten zu berechnen.
Optionale Argumente
Sie können auch optionale Argumente an eine Funktion übergeben. Mit anderen Worten, manchmal benötigen Sie das Argument und manchmal nicht - je nachdem, mit welchem Code Sie die Funktion verwenden.
| 123456 | Funktion CalculateDayDiff(Date1 as Date, Optional Date2 as Date) as Double'prüfen Sie das zweite Datum und wenn es nicht vorhanden ist, machen Sie Datum2 gleich dem heutigen Datum.Wenn Datum2=0, dann Datum2 = Datum'Differenz berechnenCalculateDayDiff = Date2-Date1Endfunktion |
Standardargumentwert
Sie können beim Erstellen der Funktion auch den Standardwert der optionalen Argumente festlegen, sodass, wenn der Benutzer das Argument weglässt, stattdessen der Wert verwendet wird, den Sie als Standardwert angegeben haben.
| 1234 | Funktion CalculateDayDiff(Date1 as Date, Optional Date2 as Date="02.06.2020") als Double'Differenz berechnenCalculateDayDiff = Date2-Date1Endfunktion |
ByVal und ByRef
Wenn Sie Werte an eine Funktion übergeben, können Sie die NachVal oder NachRef Schlüsselwörter. Wenn Sie einen dieser Punkte auslassen, wird der NachRef wird als Standard verwendet.
NachVal bedeutet, dass Sie eine Kopie der Variablen an die Funktion übergeben, während NachRef bedeutet, dass Sie sich auf den ursprünglichen Wert der Variablen beziehen. Wenn Sie eine Kopie der Variablen (NachVal), ist der ursprüngliche Wert der Variablen NICHT geändert, aber wenn Sie auf die Variable verweisen, wird der ursprüngliche Wert der Variablen von der Funktion geändert.
| 1234 | Funktion GetValue(ByRef intA As Integer) As IntegerintA = intA * 4GetValue = intAEndfunktion |
In der obigen Funktion könnte die ByRef weggelassen werden und die Funktion würde genauso funktionieren.
| 1234 | Funktion GetValue(intA As Integer) As IntegerintA = intA * 4GetValue = intAEndfunktion |
Um diese Funktion aufzurufen, können wir eine Unterprozedur ausführen.
| 123456789 | Untertestwerte()Dim intVal As Integer'die Variable mit dem Wert 10 füllenintVal = 10'Führen Sie die GetValue-Funktion aus und zeigen Sie den Wert im unmittelbaren Fenster anDebug.Print GetValue(intVal)'zeige den Wert der intVal-Variablen im unmittelbaren FensterDebug.Print intValEnd Sub |
Beachten Sie, dass die Debug-Fenster beide Male den Wert 40 anzeigen. Wenn Sie die Variable IntVal an die Funktion übergeben, wird der Wert 10 an die Funktion übergeben und mit 4 multipliziert. Wenn Sie das Schlüsselwort ByRef verwenden (oder es ganz weglassen), wird der Wert der IntVal-Variablen AMEND geändert. Dies wird angezeigt, wenn Sie zuerst das Ergebnis der Funktion im Direktfenster (40) und dann den Wert der IntVal-Variablen im Debug-Fenster (ebenfalls 40) anzeigen.
Wenn wir den Wert der ursprünglichen Variablen NICHT ändern möchten, müssen wir ByVal in der Funktion verwenden.
| 1234 | Funktion GetValue(ByVal intA As Integer) As IntegerintA = intA * 4GetValue = intAEndfunktion |
Wenn wir die Funktion nun aus einer Unterprozedur aufrufen, bleibt der Wert der Variablen IntVal bei 10.
Exit-Funktion
Wenn Sie eine Funktion erstellen, die auf eine bestimmte Bedingung testet, und sobald die Bedingung wahr ist, Sie den Wert der Funktion zurückgeben möchten, müssen Sie möglicherweise eine Exit Function-Anweisung in Ihrer Funktion hinzufügen, um die Funktion vorher zu beenden Sie haben den gesamten Code in dieser Funktion durchlaufen.
| 12345678910111213 | Funktion FindNumber(strSearch As String) As IntegerDim i As Integer'Schleife durch jeden Buchstaben in der ZeichenfolgeFür i = 1 zu Len(strSearch)'Wenn der Buchstabe numerisch ist, geben Sie den Wert an die Funktion zurückIf IsNumeric(Mid(strSearch, i, 1)) ThenFindNumber= Mid(strSearch, i, 1)'dann die Funktion verlassenExit-FunktionEnde WennNächsteFindNumber= 0Endfunktion |
Die obige Funktion durchläuft die bereitgestellte Zeichenfolge, bis sie eine Zahl findet, und gibt diese Zahl dann aus der Zeichenfolge zurück. Es wird nur die erste Zahl in der Zeichenfolge finden, da es dann Ausgang die Funktion.
Die obige Funktion kann von einer Unterroutine wie der folgenden aufgerufen werden.
| 1234567 | Unter CheckForNumber()Dim NumIs as Integer'übergeben Sie eine Textzeichenfolge an die Funktion zum Suchen von NummernNumIs = FindNumber("Obere Etage, 8 Oak Lane, Texas")'Ergebnis im Direktfenster anzeigenDebug.Print NumIsEnd Sub |
Verwenden einer Funktion aus einer Excel-Tabelle heraus
Zusätzlich zum Aufrufen einer Funktion aus Ihrem VBA-Code mithilfe einer Unterprozedur können Sie die Funktion auch aus Ihrem Excel-Blatt heraus aufrufen. Die von Ihnen erstellten Funktionen sollten standardmäßig in Ihrer Funktionsliste im Abschnitt Benutzerdefiniert der Funktionsliste erscheinen.
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 (UDFs).
Wenn Sie mit dem Schreiben Ihrer Funktion in Excel beginnen, sollte die Funktion alternativ in der Dropdown-Liste der Funktionen angezeigt werden.
Wenn die Funktion nicht in einer Excel-Tabelle verfügbar sein soll, müssen Sie beim Erstellen der Funktion in Ihrem VBA-Code das Wort Private vor das Wort Funktion setzen.
| 123 | Private Funktion CalculateDayDiff(Date1 as Date, Date2 as Date) as DoubleCalculateDayDiff = Date2-Date1Endfunktion |
Es wird jetzt nicht in der Dropdown-Liste mit den verfügbaren Excel-Funktionen angezeigt.
Interessanterweise können Sie die Funktion jedoch weiterhin verwenden - sie wird nur bei der Suche nicht in der Liste angezeigt!
Wenn Sie das zweite Argument als . deklariert haben Optional, können Sie es sowohl in der Excel-Tabelle als auch im VBA-Code weglassen.
Sie können auch die a-Funktion verwenden, die Sie ohne Argumente in Ihrem Excel-Blatt erstellt haben.
