Microsoft Excel

Excel und VBA: Formeln, Programmierung, Lösungen

Inhalt dieser Seite

Begriffsbestimmung, Deklaration und Parameter

In VBA ist Prozedur der Oberbegriff für Funktionen und Sub's.

Die Deklaration der Prozeduren kann erfolgen als:

  • Public
    Auf die Sub-Prozedur kann von allen anderen Prozeduren in allen Modulen zugegriffen werden. Bei Verwendung in einem Modul (mit einer Option Private-Anweisung) kann auf die Prozedur nur innerhalb des Projekts zugegriffen werden.
  • Private
    Auf die Sub-Prozedur kann nur durch andere Prozeduren aus dem Modul zugegriffen werden, in dem sie deklariert wurde.
  • Static
    Die lokalen Variablen der Sub-Prozedur bleiben zwischen Aufrufen erhalten. Das Attribut Static wirkt sich nicht auf Variablen aus, die außerhalb der Sub-Prozedur deklariert wurden, auch wenn sie in der Prozedur verwendet werden.

Die Voreinstellung ist Public.

Die Festlegung der Parameter kann erfolgen als:

  • Optional
    Schlüsselwort, das angibt, daß ein Argument nicht erforderlich ist. Alle im Anschluß an Optional in der Argumentenliste angegebenen Argumente müssen auch optional sein und mit dem Schlüsselwort Optional deklariert werden. Optional kann nicht verwendet werden, wenn ParamArray verwendet wird.
  • ByVal
    Das Argument wird als Wert übergeben.
  • ByRef
    Das Argument wird als Referenz übergeben.
  • ParamArray
    Ist nur als letztes Argument in ArgListe zulässig und gibt an, daß das letzte Element ein als Optional deklariertes Datenfeld mit Variant-Elementen ist. Das Schlüsselwort ParamArray erlaubt die Angabe einer variablen Anzahl von Argumenten und darf nicht in Kombination mit den Schlüsselwörtern ByVal, ByRef oder Optional verwendet werden.

Benutzerdefinierte Funktionen (UDF)

Weitere Informationen: VBA_in_Excel - Grundlagen: Funktionen

Funktionen werden mit oder ohne Parameter aufgerufen und geben Werte zurück. Der Aufruf kann sowohl über andere Funktionen oder Prozeduren als auch über die Eingabe im Arbeitsblatt erfolgen. Sie kann Excel- und VBA-Funktionen integrieren.

Beispiel für eine Funktion:

Function Ostern(iYear As Integer)
   Dim iDay As Integer
   iDay = (((255 - 11 * (iYear Mod 19)) - 21) Mod 30) + 21
   Ostern = DateSerial(iYear, 3, 1) + iDay + (iDay > 48) + _
      6 - ((iYear + iYear \ 4 + iDay + (iDay > 48) + 1) Mod 7)
End Function

Beispiel für den Aufruf aus einer Prozedur:

Sub WannIstOstern()
   MsgBox "Ostersonntag: " & Ostern(2007)
End Sub

Im Arbeitsblatt kann die Funktion durch folgende Eingabe verwendet werden (Jahreszahl in Zelle A1):

=ostern(A1)

Sub's

Prozeduren werden mit oder ohne Parameter aufgerufen und geben keine Werte zurück, können aber übergebene Variablenwerte verändern. Der Aufruf erfolgt über andere Prozeduren, nicht jedoch über eine Eingabe im Arbeitsblatt. Sie können Excel- und VBA-Funktionen integrieren.

Beispiel einer Prozedur mit dem Aufruf eines Unterprogramms:

Sub WertEintragen()
   Dim datStart As Date, datEnd As Date
   Dim iTage As Integer
   datStart = DateSerial(Year(Date), 2, 15)
   datEnd = DateSerial(Year(Date), 12, 11)
   Call WertErmitteln(datStart, datEnd, iTage)
   Range("A1").Value = iTage
End Sub

Sub WertErmitteln(ByVal datBeginn, ByVal datEnde, ByRef iDiff As Integer)
   iDiff = datEnde - datBeginn
End Sub

Informationen über ByRef/ByVal: VBA_in_Excel - Grundlagen: ByRef und ByVal

Wann sind Funktionen und wann sind Sub's einzusetzen?

Verwenden Sie Funktionen immer dann, wenn Sie diese im Tabellenblättern als Formel einsetzen möchten oder wenn Sie aus einer Sub heraus Rückgabewerte anfordern möchten. In allen anderen Fällen sollten Sie sich für Sub's entscheiden.