VBA-Funktion – Aufrufen, Rückgabewert und Parameter

In diesem Tutorial lernen Sie, wie Sie Funktionen mit und ohne Parameter in VBA erstellen und verwenden können.

VBA enthält eine große Anzahl von eingebauten Funktionen, die Sie verwenden können, aber Sie können auch Ihre eigenen schreiben. Wenn Sie den Code in VBA schreiben, können Sie ihn in einer Sub- oder einer Funktionsprozedur schreiben. Eine Funktionsprozedur ist in der Lage, einen Wert an Ihren Code zurückzugeben. Dies ist äußerst nützlich, wenn Sie möchten, dass VBA eine Aufgabe ausführt und ein Ergebnis zurückgibt. VBA-Funktionen können auch innerhalb von Excel aufgerufen werden, genau wie die in Excel integrierten Funktionen.

Erstellen einer Funktion ohne Übergabeparameter

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 die Art der Daten angibt, die die Funktion zurückgeben soll.

Vielleicht möchten Sie eine Funktion erstellen, die bei jedem Aufruf, ähnlich wie eine Konstante, einen statischen Wert zurückgibt.

Function WertRueckgabe() As Integer
   WertRueckgabe = 50
End Function

Wenn Sie die Funktion ausführen, würde sie immer den Wert 50 zurückgeben.

Sie können auch Funktionen erstellen, die auf Objekte in VBA verweisen, aber Sie müssen das Schlüsselwort Set verwenden, um den Wert aus der Funktion zurückgeben zu können.

Function BereichZurueckgeben() As Range
  Set BereichZurueckgeben = Range("A1:G4")
End Function

Wenn Sie die obige Funktion in Ihrem VBA-Code verwenden, würde die Funktion immer den Bereich der Zellen A1 bis G4 in dem Blatt zurückgeben, in dem Sie gerade arbeiten.

Aufrufen einer Funktion aus einer Unterprozedur

Sobald Sie eine Funktion erstellt haben, können Sie sie von jeder anderen Stelle in Ihrem Code aus aufrufen, indem Sie eine Unterprozedur verwenden, um die Funktion aufzurufen.

Es würde immer der Wert 50 zurückgegeben werden. Sie können die Funktion GetRange auch über eine Sub aufrufen.

Im obigen Beispiel wird die Funktion GetRange von der Sub aufgerufen, um die Zellen im Bereichsobjekt fett darzustellen.

Funktionen erstellen

Einzelnes Argument

Sie können Ihrer Funktion auch einen oder mehrere Parameter zuweisen. Diese Parameter können als Argumente bezeichnet werden.

Function KiloInPfundUmrechnen (dblKilo As Double) As Double
   KiloInPfundUmrechnen = dblKilo*2.2
End Function

Die obige Funktion kann dann von einer Sub aus aufgerufen werden, um zu ermitteln, wie viele Pfund eine bestimmte Anzahl von Kilos entsprechen.

Eine Funktion kann bei Bedarf von mehreren Prozeduren innerhalb Ihres VBA-Codes aufgerufen werden. Dies ist sehr nützlich, da Sie so nicht immer wieder den gleichen Code schreiben müssen. Außerdem können Sie so lange Prozeduren in kleine, überschaubare Funktionen unterteilen.

Im obigen Beispiel haben wir 2 Prozeduren. Jede von ihnen verwendet die Funktion, um den Pfundwert der Kilos zu berechnen, die ihnen im  Funktionsargument dblKilo übergeben werden.

Mehrere Argumente

Sie können eine Funktion mit mehreren Argumenten erstellen und die Werte mit Hilfe einer Sub an die Funktion übergeben.

Function TagesDifferenzBerechnen(Datum1 As Date, Datum2 As Date) As Double
   TagesDifferenzBerechnen = Datum2 - Datum1
End Function

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.

Function TagesDifferenzBerechnen(Datum1 As Date, Optional Datum2 As Date) As Double
'prüft, ob das zweite Datum vorhanden ist, und wenn nicht, setzt es Datum2 mit dem heutigen Datum gleich.
    If Datum2 = 0 Then Datum2 = Date
'Differenz berechnen
   TagesDifferenzBerechnen = Datum2 - Datum1 
End Function

Standardwert der Argumente

Sie können auch den Standardwert der optionalen Argumente festlegen, wenn Sie die Funktion erstellen, so dass, der Wert stattdessen verwendet wird, den Sie als Standard festgelegt haben, wenn der Benutzer das Argument auslässt.

Function TagesDifferenzBerechnen(Datum1 As Date, Optional Datum2 As Date="06.02.2020") As Double 
'Differenz berechnen 
   TagesDifferenzBerechnen = Datum2 - Datum1 
End Function

ByVal und ByRef

Wenn Sie Werte an eine Funktion übergeben, können Sie die Schlüsselwörter ByVal oder ByRef verwenden. Wenn Sie eines der beiden auslassen, wird standardmäßig ByRef verwendet. ByVal bedeutet, dass Sie eine Kopie der Variablen an die Funktion übergeben, während ByRef bedeutet, dass Sie auf den ursprünglichen Wert der Variablen verweisen. Wenn Sie eine Kopie der Variablen (ByVal) übergeben, wird 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.

Function WertRueckgabe(ByRef intA As Integer) As Integer
   intA = intA * 4
   WertRueckgabe = intA
End Function

In der obigen Funktion könnte die ByRef weggelassen werden, und die Funktion würde auf die gleiche Weise funktionieren.

Function WertRueckgabe(intA As Integer) As Integer
   intA = intA * 4
   WertRueckgabe = intA
End Function

Um diese Funktion aufzurufen, können wir eine Unterprozedur ausführen.

Sub WerteZurueckgeben()
   Dim intVal As Integer
'Die Variable mit dem Wert 10 belegen
   intVal = 10
'Die Funktion WertRueckgabe ausführen und den Wert im Direktfenster anzeigen
   Debug.Print WertRueckgabe(intVal)
'Den Wert der Variablen intVal im Direktfenster anzeigen 
   Debug.Print intVal
End Sub

Beachten Sie, dass in den Debugger-Fenstern beide Male der Wert 40 angezeigt wird. 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 Variablen IntVal geändert. Dies zeigt sich, wenn Sie zuerst das Ergebnis der Funktion im Direktenster (40) und dann den Wert der IntVal-Variablen im Debugger-Fenster (ebenfalls 40) anzeigen. Wenn wir den Wert der ursprünglichen Variablen NICHT ändern wollen, müssen wir ByVal in der Funktion verwenden.

Function WertRueckgabe(ByVal intA As Integer) As Integer
   intA = intA * 4
   WertRueckgabe = intA
End Function

Wenn wir nun die Funktion von einer Sub aus aufrufen, bleibt der Wert der Variablen IntVal bei 10.

Funktion beenden

Wenn Sie eine Funktion erstellen, die eine bestimmte Bedingung prüft, und wenn diese erfüllt ist, möchten Sie den Wert aus der Funktion zurückgeben, müssen Sie möglicherweise eine Exit-Function-Anweisung in Ihre Funktion einfügen, um die Funktion zu beenden, bevor Sie den gesamten Code durchlaufen haben.

Function ZahlFinden(strSuchtext As String) As Integer
   Dim i As Integer
'Alle Buchstaben in der Zeichenkette durchlaufen
   For i = 1 To Len(strSuchtext)
   'Wenn der Buchstabe numerisch ist, den Wert an die Funktion zurückgeben
      If IsNumeric(Mid(strSuchtext, i, 1)) Then
         Zahlfinden= Mid(strSuchtext, i, 1)
   'Dann die Funktion verlassen
         Exit Function
      End If
   Next
   ZahlFinden= 0
End Function

Die obige Funktion durchläuft die angegebene Zeichenkette in einer Schleife, bis sie eine Zahl findet, und gibt dann diese Zahl aus der Zeichenkette zurück. Es wird nur die erste Zahl in der Zeichenkette gefunden, da die Funktion dann beendet wird. Die obige Funktion kann von einer Sub-Routine wie der folgenden aufgerufen werden:

Sub AufZahlPruefen()
   Dim IstZahl As Integer
'Einen Textstring an die Funktion "ZahlFinden" übergeben
   IstZahl = ZahlFinden("Upper Floor, 8 Oak Lane, Texas")
'Das Ergebnis im Direktfenster anzeigen
   Debug.Print IstZahl
End Sub

Verwenden einer Funktion aus einem Excel-Blatt heraus

Sie können eine Funktion nicht nur von Ihrem VBA-Code aus über eine Sub aufrufen, sondern auch von Ihrem Excel-Blatt aus. Die Funktionen, die Sie erstellt haben, sollten standardmäßig in Ihrer Funktionsliste im Abschnitt „Benutzerdefiniert“ in der Funktionsliste erscheinen. Klicken Sie auf das Symbol fx um das Dialogfenster „Funktion einfügen“ anzuzeigen.

Wählen Sie Benutzerdefiniert aus der Kategorieliste

Wählen Sie die gewünschte Funktion aus den verfügbaren benutzerdefinierten Funktionen (BDF) aus.

Wenn Sie Ihre Funktion in Excel schreiben, sollte sie auch in der Dropdown-Liste der Funktionen erscheinen.

Wenn Sie nicht möchten, dass die Funktion innerhalb eines Excel-Blatts verfügbar ist, müssen Sie das Wort Private vor das Wort Function setzen, wenn Sie die Funktion in Ihrem VBA-Code erstellen.

Private Function TagesDifferenzBerechnen(Datum1 As Date, Datum2 As Date) As Double 
   TagesDifferenzBerechnen = Datum2 - Datum1 
End Function

Die Funktion wird nun nicht mehr in der Dropdown-Liste mit den verfügbaren Excel-Funktionen angezeigt.

Interessanterweise können Sie die Funktion aber immer noch verwenden. Sie erscheint nur nicht mehr in der Liste, wenn Sie nach ihr suchen!

Wenn Sie das zweite Argument als Optional deklariert haben, können Sie es sowohl in der Excel-Tabelle als auch im VBA-Code weglassen.

Sie können die Funktion, die Sie erstellt haben, auch ohne Argumente in Ihrem Excel-Blatt verwenden.