Sub und Function Prozeduren

Der VBA-Code, der im Visual Basic Editor geschrieben wird, wird als Prozedur bezeichnet.
Wikipedia definiert den Begriff Prozedur wie folgt: „Prozedur ist ein Begriff aus der Programmierung von Computersystemen. Im Allgemeinen versteht man darunter eine Variante zum Begriff „Unterprogramm“: Die Anweisungen einer Prozedur können über ihre Benennung aufgerufen und dadurch mehrfach verwendet werden.“
In VBA unterscheidet man Sub und Function Prozeduren:

Inhaltsverzeichnis

Sub Prozeduren

Sub Prozeduren (Subroutinen) führen Anweisungen aus. Sie geben aber keine Werte zurück. Sub Prozeduren beginnen mit dem Schlüsselwort Sub und enden mit den Schlüsselwörtern End Sub. An den Namen der Prozedur wird ein Klammerpaar angefügt

Beispiel:

Sub ZeigeText()
' Hello World im Direktfenster ausgeben
Debug.Print "Hello World"
End Sub

Argumente in Sub Prozeduren

Es können Argumente von anderen Prozeduren an Sub-Prozeduren übergeben werden. Die Argumente werden zwischen den Klammern eingefügt.
Beispiel:

Sub ZeigeText(MeinText)
' MeinText im Direktfenster ausgeben
Debug.print  MeinText
End Sub

Ausführen einer Sub Prozedur

Eine Sub Prozedur kann in VBA ausgeführt werden, falls sie keine Argumente hat. Hierzu klickt man im Visual Basic Editor in die Sub Prozedur, die ausgeführt werden soll. Für das Ausführen der Sub Prozedur gibt es mehere Möglichkeiten:

Die Funktionstaste F5 drücken
Auf das grüne Dreieck in der Symbolleiste klicken

In der Befehlsleiste auf „Ausführen“ und dann auf den Untermenüpunkt „Sub/UserForm ausführen“ klicken.

Aufruf einer Sub Prozedur in einer anderen Sub Prozedur

Eine Sub Prozedur kann in einer anderen Sub Prozedur mit dem Schlüsselwort „call“ aufgerufen werden.

Function Prozeduren

Eine Function Prozedur führt VBA-Anweisungen aus, die eine Berechnung durchführt und einen Wertzurückgibt. Function Prozeduren beginnen mit dem Schlüsselwort Function und enden mit den Schlüsselwörtern End Function. Es empfiehlt sich, den Datentyp des Rückgabewertes wie in folgendem Beispiel gezeigt anzuhängen. Wird kein Datentyp angehängt wird der voreingestellt Datentyp „Variant“ verwendet.

Argumente in Function Prozeduren

Es können Argumente von anderen Prozeduren an Function Prozeduren übergeben werden. Die Argumente werden zwischen den Klammern eingefügt.

Beispiel:

Sub TestQuadrat()
Dim Zahl As Double, Ergebnis as Double
Zahl = 2#
Ergebnis = Quadrat(Zahl)
Debug.Print Ergebnis
End Sub

Function Quadrat(Zahl As Double) As Double
Quadrat = Zahl * Zahl
End Function

Mit der Sub Prozedur „TestQuadrat“ kann man die Funktion „Quadrat“ testen.

Aufruf einer Function Prozedur

Der Aufruf erfolgt mit folgender Anweisung
Variable = Funktionsname(Parameterliste)

Verwendung der Funktion in einem Excel Tabellenblatt

Eine Funktion kann in einer Zelle eines Excel Tabellenblattes verwendet werden:
= Funktionsname(Parameterliste)

Vordefinierte Funktionen

In VBA gibt es bereits ungezählte vordefinierte Funktionen.

Beispiele für vordefinierte mathematische Funktionen sind in der folgenden Tabelle aufgeführt.

Es gibt eine Vielzahl weiterer Funktionen, die man z. B. in der VBA Hilfe finden kann. Beispielsweise gibt es Funktionen für folgende Kategorien:

Finanzmathematik
Mathematik und Trigonometrie
Zeichenketten und Text
Logik
Datum und Zeit
Dateien und Verzeichnisse
Datentypumwandlung

sowie viele weitere Funktionen.

Anmerkungen zu den Argumenten in Sub oder Function Prozeduren

Datenfelder oder Bereiche als Argumente
Als Argumente können nicht nur einfache Variable verwendet werden. Man kann auch Datenfelder (engl. Array), Bereiche (engl. Range) oder Objekte als Argumente verwenden.
Beispiel1: Datenfeldes als Argument einer Function Prozedur

Function mySum2(Datenfeld() As Integer) As Integer
Dim i As Integer
mySum2 = 0
For i = LBound(Datenfeld) To UBound(Datenfeld)
    mySum2 = mySum2 + Datenfeld(i)
Next i
End Function
'
Sub Test_mySum2()
Dim Datenfeld(1 To 5)  As Integer
Datenfeld(1) = 1
Datenfeld(2) = 2
Datenfeld(3) = 3
Datenfeld(4) = 4
Datenfeld(5) = 5
Debug.Print mySum2(Datenfeld)
End Sub

Beispiel 2: Bereich als Argument einer Function Prozedur:

Function mySum(rng As Range) As Double
Dim myCell As Variant
For Each myCell In rng
    mySum = mySum + myCell.Value
Next myCell
End Function
'
Sub Test_mySum()
Dim rng As Range 'Deklariert die Variable rng des Typs Range
Set rng = Application.Range("Tabelle1!E1:E9")
'Das Schlüsselwort Set ist erforderlich, um ein Objekt einer Variablen (z. B. einem Bereich) zuzuweisen.'
Debug.Print mySum(rng)
End Sub

Wichtig:
Die Function mySum kann in einem Excel Tabellenblatt ähnlich wie eine eingebaute Excel Funktion verwendet werden. In Excel gibt es bereits die Funktion „Summe“, mit der man über die Werte in einem Bereich summieren kann. mySum kann in Excel genauso verwendet wie die Funktion „Summe“.
Excel Funktionen kann man auch in VBA verwenden, in dem man „Application.WorksheetFunction“ voranstellt. Außerdem muss man den englischen Namen der Funktion verwendet statt dem deutschen Namen z. B. „sum“ statt „summe“.
Beispiel:

Function mySum3(rng As Range) as Double
mySum3 = Application.WorksheetFunction.Sum(rng)
End Function
'
Sub Test_mySum3()
Dim rng As Range 'Deklariert die Variable rng des Typs Range
Set rng = Application.Range("Tabelle1!E1:E9")
'Das Schlüsselwort Set ist erforderlich, um ein Objekt einer Variablen (z. B. einem Bereich) zuzuweisen.'
Debug.Print mySum3(rng)
Set rng = nothing
End Sub
'
Sub Test_mySum3b()
Dim rng As Range 'Deklariert die Variable rng des Typs Range
Dim ws as Worksheet
set ws = Worksheets("Tabelle1")
Set rng = ws.Range("E1:E9")
'Das Schlüsselwort Set ist erforderlich, um ein Objekt einer Variablen (z. B. einem Bereich) zuzuweisen.'
Debug.Print mySum3(rng)
' Variablen loeschen
set rng = Nothing
set ws = Nothing
End Sub

Argumentübergabe mit ByRef oder ByVal:
In VBA können Variablen als Wert (ByVal) oder als Verweis (ByRef) an eine Prozedur übergeben werden. Durch Angabe des ByVal – oder ByRef-Schlüsselworts vor jedem Parameter in einer Sub oder Function Prozedurdeklaration wird die Art der Variablenübergabe für jeden Parameter bestimmt:

- Übergabe als Wert (ByVal):
  Die Variable wird als Kopie an eine Sub oder Function Prozedur übergeben. Eine Veränderung der Kopie hat keine Auswirkung auf die Originalvariable.
- Übergabe als Verweis (ByRef):
  Die Variable wird als Referenz an eine Sub oder Function Prozedur übergeben. Eine Veränderung der Variablen in der Prozedur verändert auch die Originalvariable.
  Dies ist auch die Voreinstellung, falls ByRef oder ByVal nicht explizit in der Prozedurdeklaration angegeben wird.

Beispiele:

Sub TestByVal()
Dim Zahl As Integer, strZahl As String
Zahl = 5
strZahl = CStr(Zahl)
Call SetzeZahlByVal(Zahl)
Debug.Print "Vorher: Zahl = " & strZahl & _
   vbLf & "Nachher: Zahl = " & CStr(Zahl)
End Sub
Sub SetzeZahlByVal(ByVal Number As Integer)
Number = 10
End Sub

Sub TestByRef()
Dim Zahl As Integer, strZahl As String
Zahl = 5
strZahl = CStr(Zahl)
Call SetzeZahlByRef(Zahl)
Debug.Print "Vorher: Zahl = " & strZahl & _
   vbLf & "Nachher: Zahl = " & CStr(Zahl)
End Sub
Sub SetzeZahlByRef(ByRef Number As Integer)
Number = 10
End Sub

Excel Funktionen in VBA verwenden

Wie schon erwähnt kann man Excel Funktionen auch in VBA verwenden, in dem man „Application.WorksheetFunction“ voranstellt. Außerdem muss man den englischen Namen der Funktion verwendet statt dem deutschen Namen z. B. „sum“ statt „summe“.

Beispiel: Minimalwert in einem Zellbereich beziehungsweise in einem Datenfeld (engl. Array) ermitteln und im Direktfenster ausgeben:

Sub Minimalwert_in_einem_Zellbereich_ermitteln()
Dim ws As Worksheet, Ergebnis As Double
Dim meinBereich As Range
'
' Die beiden folgenden Zeilen muessen angepasst werden
Set ws = Worksheets("Tabelle1")
Set meinBereich = ws.Range("E1:E10")
'
Ergebnis = Application.WorksheetFunction.Min(meinBereich)
Debug.Print Ergebnis

Set ws = Nothing
End Sub

Sub Minimalwert_in_einem_Datenfeld_ermitteln()
Dim Datenfeld(1 To 5)  As Integer
Datenfeld(1) = 1
Datenfeld(2) = 2
Datenfeld(3) = 3
Datenfeld(4) = 4
Datenfeld(5) = 5
Debug.Print Application.WorksheetFunction.Min(Datenfeld)
End Sub

Beispiel: Maximalwert in einem Zellbereich beziehungsweise in einem Datenfeld (engl. Array) ermitteln und im Direktfenster ausgeben:

Sub Maximalwert_in_einem_Zellbereich_ermitteln()
Dim ws As Worksheet, Ergebnis As Double
Dim meinBereich As Range
'
' Die beiden folgenden Zeilen muessen angepasst werden
Set ws = Worksheets("Tabelle1")
Set meinBereich = ws.Range("E1:E10")
'
Ergebnis = Application.WorksheetFunction.Max(meinBereich)
Debug.Print Ergebnis

Set ws = Nothing
End Sub
'
Sub Maximalwert_in_einem_Datenfeld_ermitteln()
Dim Datenfeld(1 To 5)  As Integer
Datenfeld(1) = 1
Datenfeld(2) = 2
Datenfeld(3) = 3
Datenfeld(4) = 4
Datenfeld(5) = 5
Debug.Print Application.WorksheetFunction.Max(Datenfeld)
End Sub

Beispiel: Die Zahl Pi ermitteln und im Direktfenster ausgeben.
Hier wird zum Vergleich die Zahl Pi mit Pi = 4*ATN(1) berechnet.

Sub Zahl_Pi_ermitteln()
Dim meinPi As Double, ExcelPi As Double
'
meinPi = 4# * Atn(1#)
ExcelPi = Application.WorksheetFunction.Pi()
'
Debug.Print ExcelPi, meinPi
Debug.Print ExcelPi - meinPi
End Sub