Tutorial zu Excel-VBA-Funktionen: Rückgabe, Aufruf, Beispiele

By: Charlotte Miller Charlotte Miller
Hours Aktualisiert

⚡ Intelligente Zusammenfassung

Eine Excel-VBA-Funktion ist ein Codeblock, der eine Aufgabe ausführt und ein Ergebnis an den Aufrufer zurückgibt. Diese Seite behandelt die Deklarationssyntax, die Rückgabe eines Wertes, ein durchgerechnetes Additionsbeispiel und die Verwendung einer Funktion in einer Tabellenzelle.

  • 🎯 Definition: Eine Funktion führt eine bestimmte Aufgabe aus und gibt ein einzelnes Ergebnis an den aufrufenden Code zurück.
  • 🧾 Syntax: Function name(arguments) As Type öffnet den Block und End Function schließt ihn.
  • ️ Rückgabe eines Wertes: Weisen Sie das Ergebnis dem Funktionsnamen zu, wie in add.Numbers = ersteZahl + zweiteZahl.
  • 🔢 Rückgabetyp: Deklaration als lang oder als Double vermeidet die langsamere Standardvariante.
  • 🖱️ Berufung: Über eine Schaltfläche werden zwei Zahlen übergeben, deren Summe in einem Meldungsfeld angezeigt wird.
  • 📊 Verwendung des Arbeitsblatts: Eine öffentliche Funktion in einem Standardmodul wird in jeder Zelle zu einer benutzerdefinierten Formel.
MEHR LESEN

Excel VBA-Funktion

Was ist eine Funktion?

Eine Funktion ist ein Codeteil, der eine bestimmte Aufgabe ausführt und ein Ergebnis zurückgibt. Funktionen werden meist zum Ausführen sich wiederholender Aufgaben verwendet, z. B. zum Formatieren von Daten für die Ausgabe, zum Durchführen von Berechnungen usw.

Angenommen, Sie entwickelnping Ein Programm, das die Zinsen für ein Darlehen berechnet. Sie können eine Funktion erstellen, die den Darlehensbetrag und die Rückzahlungsfrist entgegennimmt. Die Funktion kann dann anhand des Darlehensbetrags und der Rückzahlungsfrist die Zinsen berechnen und den Wert zurückgeben.

Warum Funktionen verwenden?

Die Vorteile der Verwendung von Funktionen sind dieselben wie die für Unterprogramme: Sie zerlegen ein langes Programm in überschaubare Teile, sie können an beliebiger Stelle im Projekt wiederverwendet werden, und ein beschreibender Name dokumentiert die Funktion des Codes. Excel VBA-Subroutine-Tutorial Diese Leistungen sind vollständig abgedeckt.

Regeln zur Benennung von Funktionen

Die Namensregeln sind identisch mit denen für Unterprogramme. Ein Funktionsname darf kein Leerzeichen enthalten, muss mit einem Buchstaben oder einem Unterstrich beginnen und darf kein reservierter Begriff sein. VBA Schlüsselwörter wie Funktion, Privat oder Ende.

VBA-Syntax zum Deklarieren einer Funktion

Private Function myFunction (ByVal arg1 As Integer, ByVal arg2 As Integer)
    myFunction = arg1 + arg2
End Function

HIER in der Syntax,

Code Action
  • „Private Funktion myFunction(…)“
  • Hier wird das Schlüsselwort „Function“ verwendet, um eine Funktion namens „myFunction“ zu deklarieren und den Hauptteil der Funktion zu starten.
  • Mit dem Schlüsselwort „Private“ wird der Umfang der Funktion angegeben
  • „ByVal arg1 als Ganzzahl, ByVal arg2 als Ganzzahl“
  • Es deklariert zwei Parameter vom ganzzahligen Datentyp mit den Namen „arg1“ und „arg2“.
  • meineFunktion = arg1 + arg2
  • wertet den Ausdruck arg1 + arg2 aus und weist das Ergebnis dem Namen der Funktion zu.
  • „Funktion beenden“
  • „End Function“ wird verwendet, um den Funktionskörper zu beenden.

Wie man einen Wert zurückgibt und den Datentyp der Funktion festlegt

Eine Funktion hat eine Aufgabe, die eine Unterroutine nicht hat: Sie gibt einen Wert zurück. Zwei Details bestimmen diesen Wert, und beide werden leicht übersehen.

Das erste ist die Zuweisung. VBA kennt keine Return-Anweisung. Stattdessen wird das Ergebnis dem Funktionsnamen selbst zugewiesen, weshalb die Zeile so aussieht: meineFunktion = arg1 + arg2Wenn diese Zuweisung nie ausgeführt wird, gibt die Funktion stillschweigend einen leeren Wert zurück, anstatt einen Fehler auszulösen, sodass jeder Zweig des Codes ihn setzen muss.

Das zweite Merkmal ist der Rückgabetyp. Die obige Deklaration endet mit der schließenden Klammer, daher gibt die Funktion einen Variant zurück. Durch Hinzufügen einer As-Klausel nach den Klammern wird der Typ festgelegt, was schneller ist, weniger Speicher benötigt und dem Compiler ermöglicht, einen Typkonflikt zu erkennen.

Erklärung Rücksendungen Wann man es benutzt
Funktion f(x As Long) Variante Nur wenn sich der Ergebnistyp tatsächlich ändert
Funktion f(x As Long) As Long lang Ganze Zahlen wie Zählwerte und Zeilennummern
Funktion f(x As Long) As Double Double Jede Berechnung, die Dezimalzahlen erzeugt
Funktion f(x As Long) As String Schnur Formatierter Text zur Anzeige zurückgegeben
Funktion f(x As Long) As Boolean Boolean Eine Validierungsprüfung, die mit „wahr“ oder „falsch“ antwortet.

💡 Tipp: Verwenden Sie Exit Function, um die Unterroutine vorzeitig zu verlassen, sobald der Rückgabewert festgelegt ist, analog zur Vorgehensweise von Exit Sub, mit der eine Unterroutine verlassen wird.

Funktion am Beispiel demonstriert:

Funktionen sind Subroutinen sehr ähnlich. Der Hauptunterschied zwischen einer Subroutine und einer Funktion besteht darin, dass die Funktion beim Aufruf einen Wert zurückgibt. Eine Subroutine hingegen gibt beim Aufruf keinen Wert zurück. Angenommen, Sie möchten zwei Zahlen addieren. Sie können eine Funktion erstellen, die zwei Zahlen akzeptiert und die Summe der Zahlen zurückgibt.

  1. Erstellen Sie die Benutzeroberfläche
  2. Fügen Sie die Funktion hinzu
  3. Schreiben Sie Code für die Befehlsschaltfläche
  4. Testen Sie den Code

Schritt 1) Benutzeroberfläche

Fügen Sie dem Arbeitsblatt eine Befehlsschaltfläche hinzu, wie unten gezeigt

VBA-Funktionen und Unterprogramme

Legen Sie die folgenden Eigenschaften von CommandButton1 wie folgt fest.

S / N Kontrolle Eigenschaft Wert
1 Befehlsschaltfläche1 Name btnAddNumbers
2 Bildunterschrift Speichern Numbers Funktion

Ihre Oberfläche sollte nun wie folgt aussehen

VBA-Funktionen und Unterprogramme

ÄHNLICHE ARTIKEL

Schritt 2) Funktionscode.

  1. Drücken Sie Alt + F11, um das Codefenster zu öffnen
  2. Fügen Sie den folgenden Code hinzu
Private Function addNumbers(ByVal firstNumber As Integer, ByVal secondNumber As Integer)
    addNumbers = firstNumber + secondNumber
End Function

HIER im Code,

Code Action
  • „Private Veranstaltung hinzufügenNumbers(...) "
  • Es deklariert eine private Funktion „addNumbers”, das zwei ganzzahlige Parameter akzeptiert.
  • „ByVal firstNumber As Integer, ByVal secondNumber As Integer“
  • Es deklariert zwei Parametervariablen firstNumber und secondNumber
  • "addierenNumbers = erste Zahl + zweite Zahl“
  • Es addiert die Werte „firstNumber“ und „secondNumber“ und weist die zu addierende Summe zuNumbers.

Schritt 3) Schreiben Code die die Funktion aufruft

  1. Klicken Sie mit der rechten Maustaste auf die Schaltfläche „Hinzufügen“.Numbers Befehlsschaltfläche
  2. Wählen Sie „Ansicht“. Code
  3. Fügen Sie den folgenden Code hinzu
Private Sub btnAddNumbers_Click()
    MsgBox addNumbers(2, 3)
End Sub

HIER im Code,

Code Action
"NachrichtBox hinzufügenNumbers(2,3)
  • Es ruft die Funktion add aufNumbers und übergibt 2 und 3 als Parameter. Die Funktion gibt die Summe der beiden Zahlen fünf (5) zurück.

Schritt 4) Führen Sie das Programm aus. Sie erhalten die folgenden Ergebnisse

VBA-Funktionen und Unterprogramme

Laden Sie Excel herunter, das den obigen Code enthält

Laden Sie die obige Excel-Datei herunter. Code

Die Schaltfläche oben ruft die Funktion aus dem VBA-Code auf. Eine Funktion kann aber auch direkt aus dem Arbeitsblatt aufgerufen werden, ganz ohne Schaltfläche.

Wie man eine VBA-Funktion in einer Tabellenzelle verwendet

Eine in VBA geschriebene Funktion kann wie SUMME oder SVERWEIS in eine Zelle eingegeben werden. Excel nennt dies eine benutzerdefinierte Funktion (UDF), und dies ist der Grund, warum viele Anwender Funktionen vor Unterprogrammen lernen. Drei Bedingungen müssen erfüllt sein.

  • Platzieren Sie es in einem Standardmodul: Fügen Sie im Editor „Modul“ ein. Eine Funktion, die hinter einem Arbeitsblatt oder in „DieseArbeitsmappe“ gespeichert ist, ist in der Bearbeitungsleiste nicht sichtbar.
  • Erklären Sie es für öffentlich: Im obigen Beispiel wird „Private“ verwendet, wodurch die Daten in Excel verborgen bleiben. „Public“ ist die Standardeinstellung, daher genügt es, das Schlüsselwort einfach zu entfernen.
  • Gibt einen Wert zurück, ändert aber nichts: Eine benutzerdefinierte Funktion (UDF) kann keine Zellen formatieren, Zeilen löschen oder in eine andere Zelle schreiben. Excel blockiert diese Aktionen, und die Zelle zeigt den Fehlerwert #WERT! an.

Die unten stehende Funktion wandelt eine Temperatur um und kann an beliebiger Stelle im Tabellenblatt verwendet werden.

Public Function CelsiusToF(ByVal Celsius As Double) As Double
    CelsiusToF = (Celsius * 9 / 5) + 32
End Function

Speichern Sie die Arbeitsmappe als eine Makro-fähige .xlsm-Datei und geben Sie dann Folgendes ein: =CelsiusToF(A1) in eine beliebige Zelle. Das Ergebnis wird aktualisiert, sobald sich A1 ändert, und der Name erscheint in der Formel-Autovervollständigungsliste unter der Kategorie „Benutzerdefiniert“. Da die Arbeitsmappe nun Makros enthält, muss jeder, der sie öffnet, Inhalte aktivieren, bevor die Formel einen Wert anstelle von #NAME? zurückgibt.

Häufige VBA-Funktionsfehler und wie man sie behebt

Vier Probleme sind für die meisten Funktionen verantwortlich, die zwar kompilieren, aber das falsche Ergebnis liefern.

  • Die Funktion gibt entweder „Leer“ oder „0“ zurück: Das Ergebnis wurde der Funktion nie zugewiesen, oder ein Zweig einer If-Anweisung überspringt die Zuweisung. Setzen Sie den Rückgabewert in jedem Pfad.
  • #NAME? in einer Tabellenzelle: Die Funktion ist privat, befindet sich in einem Tabellenmodul anstatt in einem Standardmodul, oder die Arbeitsmappe wurde ohne aktivierte Makros gespeichert.
  • Überlauf bei Integer-Argumenten: Das Beispiel verwendet den Datentyp „As Integer“, der bei 32,767 endet. Ändern Sie beide Parameter und den Rückgabetyp in „Long“, um echte Daten zu verwenden.
  • Ein geändertes Argument überrascht den Anrufer: Lässt man `ByVal` weg, übergibt VBA die Variable selbst, sodass die Funktion den Wert des Aufrufers verändern kann. Verwenden Sie `ByVal` nur, wenn dieser Effekt gewünscht ist.

Häufig gestellte Fragen

Nicht direkt. Geben Sie ein Array oder einen benutzerdefinierten Typ zurück, um mehrere Werte in einem Ergebnis zu speichern, oder deklarieren Sie die zusätzlichen Parameter als ByRef, damit die Funktion in die Variablen des Aufrufers zurückschreibt.

Fügen Sie das Schlüsselwort Optional mit einem Standardwert hinzu, z. B. Optional ByVal Rate As Double = 0.05. Jeder Parameter nach einem Optional-Parameter muss ebenfalls Optional sein und muss an letzter Stelle in der Liste stehen.

Ja, beispielsweise über Application.WorksheetFunction.Sum(Range(“A1:A10”)). Funktionen, die VBA bereits bereitstellt, wie Left oder Trim, werden direkt ohne dieses Präfix aufgerufen.

Ja. Fügen Sie die Tabellenformel ein, und ein KI-Assistent liefert eine äquivalente öffentliche Funktion mit benannten Argumenten und einem deklarierten Rückgabetyp. Vergleichen Sie beide Ergebnisse anhand von Beispielzeilen, bevor Sie die Formel ersetzen.

Ja. Geben Sie die Funktion und die Formel an, und ein KI-Assistent weist auf mögliche Ursachen hin, wie z. B. einen Konflikt zwischen Argumenttypen, eine fehlende Rückgabewertzuweisung oder den Versuch, eine Zelle innerhalb einer benutzerdefinierten Funktion zu ändern.

Fassen Sie diesen Beitrag mit folgenden Worten zusammen: