„Function“- und „Sub“-Prozeduren in VBA

Eingebaute VBA-Funktionen

Bevor Sie mit dem Erstellen Ihrer eigenen VBA-Funktionen beginnen, ist es gut zu wissen, dass Excel VBA über eine umfangreiche Sammlung vorgefertigter integrierter Funktionen verfügt, die Sie beim Schreiben Ihres Codes verwenden können.

Eine Liste dieser Funktionen kann im VBA-Editor angezeigt werden:

• Öffnen Sie eine Excel-Arbeitsmappe und starten Sie den VBA-Editor (klicken Sie dazu Alt + F11) und drücken Sie dann F2.
• Wählen Sie eine Bibliothek aus der Dropdown-Liste oben links auf dem Bildschirm aus VBA.
• Eine Liste der integrierten VBA-Klassen und -Funktionen wird angezeigt. Klicken Sie auf den Funktionsnamen, um seine kurze Beschreibung unten im Fenster anzuzeigen. drücken F1 öffnet die Online-Hilfeseite für diese Funktion.

Darüber hinaus finden Sie im Visual Basic Developer Center eine vollständige Liste der integrierten VBA-Funktionen mit Beispielen.

Benutzerdefinierte Prozeduren „Function“ und „Sub“ in VBA

In Excel Visual Basic wird eine Reihe von Befehlen, die eine bestimmte Aufgabe ausführen, in eine Prozedur eingefügt. Funktion (Funktion) bzw Sub (Subroutine). Der Hauptunterschied zwischen den Verfahren Funktion и Sub ist das das Verfahren Funktion gibt Ergebnis, Prozedur zurück Sub - nicht.

Wenn Sie also Aktionen ausführen und ein Ergebnis erzielen müssen (z. B. mehrere Zahlen summieren), wird normalerweise das Verfahren verwendet Funktion, und um einfach einige Aktionen auszuführen (z. B. die Formatierung einer Gruppe von Zellen zu ändern), müssen Sie das Verfahren auswählen Sub.

Argumente

Verschiedene Daten können mithilfe von Argumenten an VBA-Prozeduren übergeben werden. Die Argumentliste wird beim Deklarieren einer Prozedur angegeben. Zum Beispiel das Verfahren Sub in VBA fügt jeder Zelle im ausgewählten Bereich die angegebene Ganzzahl (Integer) hinzu. Sie können diese Nummer mit einem Argument wie dem folgenden an die Prozedur übergeben:

Sub AddToCells(i As Integer) ... End Sub

Denken Sie daran, Argumente für Verfahren zu haben Funktion и Sub in VBA ist optional. Einige Prozeduren erfordern keine Argumente.

Optionale Argumente

VBA-Prozeduren können optionale Argumente haben. Dies sind Argumente, die der Benutzer angeben kann, wenn er möchte, und wenn sie weggelassen werden, verwendet die Prozedur die Standardwerte für sie.

Zurück zum vorherigen Beispiel, um ein Integer-Argument für eine Funktion optional zu machen, würde es wie folgt deklariert werden:

Sub AddToCells (Optional i als ganze Zahl = 0)

In diesem Fall das Integer-Argument i der Standardwert ist 0.

Eine Prozedur kann mehrere optionale Argumente enthalten, die alle am Ende der Argumentliste aufgeführt sind.

Übergeben von Argumenten als Wert und als Referenz

Argumente in VBA können auf zwei Arten an eine Prozedur übergeben werden:

• ByVal – Übergabe eines Arguments nach Wert. Das bedeutet, dass nur der Wert (d. h. eine Kopie des Arguments) an die Prozedur übergeben wird und daher alle Änderungen am Argument innerhalb der Prozedur verloren gehen, wenn die Prozedur beendet wird.
• Byref – Übergabe eines Arguments als Referenz. Das heißt, die tatsächliche Adresse der Argumentstelle im Speicher wird an die Prozedur übergeben. Alle Änderungen, die an einem Argument innerhalb der Prozedur vorgenommen werden, werden gespeichert, wenn die Prozedur beendet wird.

Schlüsselwörter verwenden ByVal or Byref In der Prozedurdeklaration können Sie angeben, wie das Argument an die Prozedur übergeben wird. Dies wird in den folgenden Beispielen gezeigt:

Sub AddToCells(ByVal i As Integer) ... End Sub

Sub AddToCells(ByRef i As Integer) ... End Sub

Denken Sie daran, dass Argumente in VBA standardmäßig als Referenz übergeben werden. Mit anderen Worten, wenn keine Schlüsselwörter verwendet werden ByVal or Byref, dann wird das Argument als Referenz übergeben.

Bevor Sie mit den Verfahren fortfahren Funktion и Sub im Detail wird es nützlich sein, einen weiteren Blick auf die Merkmale und Unterschiede zwischen diesen beiden Arten von Verfahren zu werfen. Im Folgenden finden Sie kurze Erläuterungen zu VBA-Prozeduren Funktion и Sub und einfache Beispiele werden gezeigt.

VBA-Prozedur «Funktion»

Der VBA-Editor erkennt die Prozedur Funktionwenn es auf eine Gruppe von Befehlen stößt, die zwischen den folgenden öffnenden und schließenden Anweisungen eingeschlossen sind:

Funktion ... Funktion beenden

Wie bereits erwähnt, das Verfahren Funktion in VBA (im Gegensatz zu Sub) gibt einen Wert zurück. Für Rückgabewerte gelten folgende Regeln:

• Der Datentyp des Rückgabewerts muss im Header der Prozedur deklariert werden Funktion.
• Die Variable, die den Rückgabewert enthält, muss genauso benannt sein wie die Prozedur Funktion. Diese Variable muss nicht separat deklariert werden, da sie immer als integraler Bestandteil der Prozedur vorhanden ist. Funktion.

Dies wird im folgenden Beispiel gut veranschaulicht.

Beispiel einer VBA-Funktion: Durchführen einer mathematischen Operation mit 3 Zahlen

Im Folgenden finden Sie ein Beispiel für einen VBA-Prozedurcode Funktion, die drei Argumente des Typs akzeptiert Doppelt (Gleitkommazahlen mit doppelter Genauigkeit). Als Ergebnis gibt die Prozedur eine andere Nummer des Typs zurück Doppeltgleich der Summe der ersten beiden Argumente minus dem dritten Argument:

Function SumMinus(dNum1 As Double, dNum2 As Double, dNum3 As Double) As Double SumMinus = dNum1 + dNum2 - dNum3 End Function

Dieses sehr einfache VBA-Verfahren Funktion veranschaulicht, wie Daten über Argumente an eine Prozedur übergeben werden. Sie können sehen, dass der von der Prozedur zurückgegebene Datentyp definiert ist als Doppelt (Die Worte sagen Als Double nach der Liste der Argumente). Dieses Beispiel zeigt auch, wie das Ergebnis des Verfahrens ist Funktion in einer Variablen mit dem gleichen Namen wie der Prozedurname gespeichert.

Aufruf der VBA-Prozedur „Function“

Wenn das obige einfache Verfahren Funktion in ein Modul im Visual Basic-Editor eingefügt, kann es von anderen VBA-Prozeduren aufgerufen oder auf einem Arbeitsblatt in einer Excel-Arbeitsmappe verwendet werden.

Rufen Sie die VBA-Prozedur „Function“ aus einer anderen Prozedur auf

Verfahren Funktion kann von einer anderen VBA-Prozedur aufgerufen werden, indem Sie diese Prozedur einfach einer Variablen zuweisen. Das folgende Beispiel zeigt einen Aufruf einer Prozedur Summinus, die oben definiert wurde.

Sub main() Dim total als Double total = SumMinus(5, 4, 3) End Sub

Rufen Sie die VBA-Prozedur „Funktion“ aus einem Arbeitsblatt auf

VBA-Verfahren Funktion kann wie jede andere eingebaute Excel-Funktion aus einem Excel-Arbeitsblatt heraus aufgerufen werden. Daher wurde die Prozedur im vorherigen Beispiel erstellt Funktion - Summinus kann durch Eingabe des folgenden Ausdrucks in eine Arbeitsblattzelle aufgerufen werden:

=SumMinus(10, 5, 2)

VBA-Prozedur «Sub»

Der VBA-Editor versteht, dass ihm eine Prozedur vorangestellt ist Subwenn es auf eine Gruppe von Befehlen stößt, die zwischen den folgenden öffnenden und schließenden Anweisungen eingeschlossen sind:

Sub ... Ende Sub

VBA-Prozedur „Sub“: Beispiel 1. Zentrierte Ausrichtung und Änderung der Schriftgröße in einem ausgewählten Zellbereich

Betrachten Sie ein Beispiel für eine einfache VBA-Prozedur Sub, dessen Aufgabe es ist, die Formatierung des ausgewählten Zellbereichs zu ändern. Die Zellen werden zentriert (sowohl vertikal als auch horizontal) und die Schriftgröße wird auf die benutzerdefinierte Größe geändert:

Sub Format_Centered_And_Sized(Optional iFontSize As Integer = 10) Selection.HorizontalAlignment = xlCenter Selection.VerticalAlignment = xlCenter Selection.Font.Size = iFontSize End Sub

Dieses Verfahren Sub führt Aktionen aus, gibt aber kein Ergebnis zurück.

Dieses Beispiel verwendet auch ein optionales Argument Schriftgröße. Wenn die Argumentation Schriftgröße nicht zum Verfahren weitergeleitet Sub, dann ist sein Standardwert 10. Wenn jedoch das Argument Schriftgröße Verfahren übergeben Sub, dann wird der ausgewählte Zellbereich auf die vom Benutzer angegebene Schriftgröße gesetzt.

VBA-Unterprozedur: Beispiel 2: Zentriert ausrichten und Fettschrift im ausgewählten Zellbereich

Das folgende Verfahren ähnelt dem gerade besprochenen, aber dieses Mal wendet es eine fette Schriftart auf den ausgewählten Zellenbereich an, anstatt die Größe zu ändern. Dies ist ein Beispielverfahren Sub, die keine Argumente akzeptiert:

Sub Format_Centered_And_Bold() Selection.HorizontalAlignment = xlCenter Selection.VerticalAlignment = xlCenter Selection.Font.Bold = True End Sub

Aufrufen der „Sub“-Prozedur in Excel VBA

Rufen Sie die VBA-Prozedur „Sub“ von einer anderen Prozedur aus auf

So rufen Sie eine VBA-Prozedur auf Sub aus einer anderen VBA-Prozedur müssen Sie das Schlüsselwort schreiben Telefon, Prozedurname Sub und weiter in Klammern sind die Argumente des Verfahrens. Dies wird im folgenden Beispiel gezeigt:

Sub main() Aufruf von Format_Centered_And_Sized(20) End Sub

Wenn das Verfahren Format_Centered_And_Size mehr als ein Argument hat, müssen sie durch Kommas getrennt werden. So was:

Sub main() Call Format_Centered_And_Sized(arg1, arg2, ...) End Sub

Rufen Sie die VBA-Prozedur „Sub“ aus dem Arbeitsblatt auf

Verfahren Sub kann nicht direkt in eine Excel-Tabellenzelle eingegeben werden, wie dies mit einer Prozedur möglich ist Funktionweil das Verfahren Sub gibt keinen Wert zurück. Allerdings Verfahren Sub, die keine Argumente haben und als deklariert sind Öffentliche (wie unten gezeigt) steht den Benutzern des Arbeitsblatts zur Verfügung. Wenn also die oben besprochenen einfachen Verfahren Sub eingefügt in ein Modul im Visual Basic-Editor, die Prozedur Format_zentriert_und_fett stehen zur Verwendung in einem Excel-Arbeitsblatt und dem Verfahren zur Verfügung Format_Centered_And_Size – wird nicht verfügbar sein, da es Argumente hat.

Hier ist eine einfache Möglichkeit, eine Prozedur auszuführen (oder auszuführen). Sub, zugänglich über das Arbeitsblatt:

• Öffentlichkeitsarbeit/Presse Alt + F8 (drücken Sie die Taste Andere und halten Sie sie gedrückt, während Sie die Taste drücken F8).
• Wählen Sie in der angezeigten Liste der Makros dasjenige aus, das Sie ausführen möchten.
• Öffentlichkeitsarbeit/Presse Führen Sie (Lauf)

Um eine Prozedur durchzuführen Sub schnell und einfach können Sie ihm eine Tastenkombination zuweisen. Dafür:

• Öffentlichkeitsarbeit/Presse Alt + F8.
• Wählen Sie in der angezeigten Liste der Makros dasjenige aus, dem Sie eine Tastenkombination zuweisen möchten.
• Öffentlichkeitsarbeit/Presse Parameter (Optionen) und geben Sie im angezeigten Dialogfeld die Tastenkombination ein.
• Öffentlichkeitsarbeit/Presse OK und schließen Sie den Dialog Makro (Makro).

Aufmerksamkeit: Achten Sie bei der Zuweisung einer Tastenkombination zu einem Makro darauf, dass diese nicht standardmäßig in Excel verwendet wird (z. Ctrl + C). Wenn Sie eine bereits vorhandene Tastenkombination auswählen, wird diese dem Makro neu zugewiesen, was dazu führen kann, dass der Benutzer das Makro versehentlich startet.

Umfang der VBA-Prozedur

In Teil 2 dieses Tutorials wurden der Umfang von Variablen und Konstanten und die Rolle von Schlüsselwörtern behandelt. Öffentliche и Privat. Diese Schlüsselwörter können auch mit VBA-Prozeduren verwendet werden:

Public Sub AddToCells(i As Integer) ... End Sub

Private Sub AddToCells(i As Integer) ... End Sub

Denken Sie daran, dass if vor der Deklaration einer VBA-Prozedur Funktion or Sub Wenn das Schlüsselwort nicht eingefügt wird, wird die Standardeigenschaft für die Prozedur festgelegt Öffentliche (das heißt, es wird überall in diesem VBA-Projekt verfügbar sein). Dies steht im Gegensatz zu Variablendeklarationen, die standardmäßig sind Privat.

Vorzeitiges Verlassen der VBA-Prozeduren „Function“ und „Sub“

Wenn Sie die Ausführung einer VBA-Prozedur beenden müssen Funktion or Sub, ohne auf sein natürliches Ende zu warten, dann gibt es dafür Operatoren Beenden Funktion и Exit Sub. Die Verwendung dieser Operatoren wird im Folgenden anhand eines einfachen Verfahrens als Beispiel gezeigt. FunktionA, das erwartet, ein positives Argument zu erhalten, um weitere Operationen durchzuführen. Wenn der Prozedur ein nicht positiver Wert übergeben wird, können keine weiteren Operationen ausgeführt werden, sodass dem Benutzer eine Fehlermeldung angezeigt werden sollte und die Prozedur sofort beendet werden sollte:

Function VAT_Amount(sVAT_Rate As Single) As Single VAT_Amount = 0 If sVAT_Rate <= 0 Then MsgBox "Positiven Wert von sVAT_Rate erwartet, aber erhalten" & sVAT_Rate Exit Function End If ... End Function

Bitte beachten Sie dies, bevor Sie den Vorgang abschließen Funktion - Mehrwertsteuerbetrag, wird eine integrierte VBA-Funktion in den Code eingefügt MsgBox, die dem Benutzer ein Warn-Popup anzeigt.