Zählermarke der VG Wort Übersicht · Übersicht · Array()-Funktion · Arrays im VBA-Code · Arrays in Excel · Allgemeines

Arrays in VBA-Programmen

Grundsätzlich gilt beim Austausch von Arrays über Funktionen und Prozeduren, dass der Datentyp entweder Variant oder absolut identisch sein muss. Selbst eine einfache Typkonvertierung von Integer nach Long, die technisch immer geht, bewirkt in VBA spätestens zur Laufzeit einen Fehler.

Argumente von Programmen

Einer Sub, Function, Property oder auch Declare-Anweisung kann man Arrays als Argument übergeben. Dazu gibt es mehrere Arten:

  1. Über eine Variant-Variable, die ein Array enthält

  2. Über eine Array-Variable, die nicht dimensioniert ist und keinen Datentyp hat, also als Variant behandelt wird

  3. Über eine Array-Variable, die nicht dimensioniert ist aber einen spezielen Datentyp hat, der dann auch von der übergebenen Variablen im aufrufenden Programm erwartet wird

  4. Mit dem Schlüsselwort ParamArray können beliebig viele Argumente übergeben werden, die im aufgerufenen Programm als Array zur Verfügung stehen. ParamArray sammelt einzelne Argumente wie die VBA-Funktion Array() ein und stellt sie als Variant-Array zur Verfügung.

ParamArray verhält sich auch sonst wie die Funktion Array() und ist damit in mehreren Punkten inkonsistent zu normalen Arrays. Es liefert ein Variant-Array, welches unabhängig von Option Base mit Index 0 beginnt. Das folgende (völlig sinnfreie) Beispielprogramm ParaTest() zeigt, wie sich VBA hier verhält:


Public Sub ParaTest(ParamArray Werte())     Dim AnzArgumente As Integer     AnzArgumente = UBound(Werte) - LBound(Werte) + 1     MsgBox LBound(Werte) & " ... " & UBound(Werte) & _         vbNewLine & "Anzahl Argumente: " & AnzArgumente End Sub

Ruft man einfach nur ParaTest() ohne Parameter auf, so liefert UBound = -1 und LBound = 0, es tritt also kein Laufzeitfehler auf. Dagegen zählt ParaTest "A" genau ein Argument.

Prinzipiell kann man mit ParamArray auch leere Elemente übergeben, beispielsweise bei ParaTest "A",  , "C" wird Werte(1) keinen Inhalt haben. Im Unterschied zu normalen Arrays kann dieses fehlende Argument nur mit IsMissing(Werte(1)) erkannt werden, IsEmpty() erkennt es nicht!

Das nächste Beispiel zeigt, wie man auf ein Array zugreift, welches durch ParamArray geliefert wurde:


Public Sub TestPara()     ParaTest2 "A", Array(11, 22, 33), "C" End Sub Public Sub ParaTest2(ParamArray Werte())     MsgBox Werte(1)(0) ' Ausgabe: 11 End Sub

Auch hier an der einzelnen Klammerung der Argumente ist die Verwandschaft zur Array()-Funktion zu erkennen. Um auf die erste Zahl im Array in Werte(1) zuzugreifen muss mit Werte(1)(0) adressiert werden.

Rückgabewerte von Funktionen

Grundsätzlich kann man auf zwei Wegen ein Array aus einer Funktion zurückgeben:

  1. Der Rückgabewert der Funktion wird als Variant deklariert und nimmt ein beliebiges Array auf:

    Public Function RückgabeAlsArray() As Variant

  2. Der Rückgabewert erhält einen bestimmten Typ, als Kennzeichen muss der Datentyp mit () abgeschlossen werden, also so:

    Public Function RückgabeAlsArray() As Long()

Beide Lösungen haben den Nachteil, dass innerhalb der Funktion mit Dim/ReDim ein Hilfs-Array definiert werden muss, welches die Daten aufnimmt. Dieses kann dann dem Rückgabewert der Funktion zugewiesen werden.

Fall (b) ist interessant, denn:

Und so wird's gemacht:


Public Function RückgabeAlsArray() As Long()     Dim Liste(2, 2) As Long         ' Einige Elemente des Array füllen     Liste(0, 0) = 11     Liste(1, 1) = 22     Liste(2, 2) = 33         ' Rückgabewert der Funktion festlegen     RückgabeAlsArray = Liste End Function

Hier wird zunächst das lokale Array Liste() erzeugt, welches vom gleichen Datentyp wie der Rückgabewert der Funktion sein muss (hier: Long). Durch die Zuweisung in der letzten Zeile erbt der Rückgabewert der Funktion RückgabeAlsArray() die Dimensionen von Liste().

Soll Liste() als Array in einem Variant zurückgegeben werden, so unterscheidet sich die ganze Funktion nur in der ersten Zeile:


Public Function RückgabeAlsArray() As Variant
Die folgende Beispielfunktion erzeugt eine quadratische Matrix und füllt die Elemente mit einer fortlaufenden Nummer. Es wurde sicher programmiert, denn die Größe des Arrays wird in den Schleifen mit LBound() und UBound() abgefragt. Hier ist auch zu sehen, welche der beiden Dimensionen mit diesen Funktionen abgerufen wird:


Public Function MatrixFüllen(Dimension As Byte) As Long()     Dim Liste() As Long, Zeile As Long, Spalte As Long     ReDim Liste(1 To Dimension, 1 To Dimension)         For Zeile = LBound(Liste, 1) To UBound(Liste, 1)         For Spalte = LBound(Liste, 2) To UBound(Liste, 2)             Liste(Zeile, Spalte) = (Zeile - 1) * Dimension + Spalte         Next Spalte     Next Zeile         ' Rückgabewert der Funktion festlegen     MatrixFüllen = Liste End Function

In einer optimierten Lösung würde man UBound() durch Dimension ersetzen.

Wie verarbeitet man nun die Rückgabewerte so einer Funktion in der aufrufenden Funktion? Die folgende Funktion Test_MatrixFüllen() lässt sich eine 3×3 Matrix erzeugen und nimmt das Array in der Variablen Ergebnis() entgegen:


Public Sub Test_MatrixFüllen()     Dim Ergebnis() As Long     Ergebnis = MatrixFüllen(3)     MsgBox Ergebnis(2, 3) ' Anzeige: 6 End Sub

Da wir den Rückgabewert von MatrixFüllen() als typisiertes Array definiert haben, muss Ergebnis() entweder vom gleichen Datentyp sein und darf nicht dimensioniert sein (Ergebnis() erbt hier die Dimension vom Rückgabewert) oder Ergebnis() muss vom Typ Variant sein:


Dim Ergebnis As Variant

Was ändert sich, wenn die Funktion ein Array in einem Variant zurückgibt? Probieren wir es aus:


Public Function MatrixFüllen(Dimension As Byte) As Variant

Am Verhalten der Programme ändert sich nichts. Das Programm Test_MatrixFüllen() arbeitet unverändert und interessanterweise darf Ergebnis() auch vom Typ Longsein, hier wird also der Variant-Typ wieder in ein Array konvertiert.

Der folgende Spezialfall mag vielleicht als Verschwendung erscheinen, aber zu einer vollständigen Beschreibung gehört auch das. Braucht man nur einen einzelnen Wert aus der Matrix (hier: Zeile 2, Spalte 3), so kann man den Aufruf so verkürzen:


Public Sub Test_MatrixFüllen()     Dim EinzelWert As Integer     EinzelWert = MatrixFüllen(3)(2, 3)     MsgBox EinzelWert ' Anzeige: 6 End Sub

Arrays mit benutzerdefinierten Typen

Die Arrays-Strukturen sind (bis auf Variant) typenrein, d. h. es ist nicht möglich, in einem Array verschiedene Datentypen (Zahlen, Texte, Objekte) zu speichern, es sei denn, man wählt ein Variant-Array. Das hat jedoch zwei Nachteile:

Nehmen wir folgenden Fall als Beispiel an: Sie haben ein mehrsprachiges Programm und es soll je nach Sprachwahl die richtigen Wochentage ausgeben. Mit der VB-Konstante VbDayOfWeek haben Sie einen Wert, mit dem das Programm arbeitet und für die Ausgabe soll es in den Klartextnamen umgewandelt werden. Sie könnten das mit einem Dictionary-Objekt elegant lösen, aber hier wollen wir mit Gewalt die Arrays verwenden.

Zunächst erstellen Sie im Deklarationsteil eines Standardmoduls (also ganz oben) eine Typdefinition für den Wochentag, z. B. diese:


Public Type WochenTag Tag  As VbDayOfWeek ' Interner Wert Name  As String      ' Langer Name Kurz2 As String * 2  ' Kurzbezeichnung Kurz3 As String * 3  ' Kurzbezeichnung End Type

Die durch vbDayOfWeek bereitgestellten Konstanten sichern eine hohe Kompatibilität der Anwendung und gleichzeitig gute Lesbarkeit, denn immer wenn Sie nach dem = etwas eingeben, schlägt IntelliSense die passenden Werte vor. Die anderen Elemente der Typdefinition sollen die verschiedenen sprachspezifischen Namen der Wochentage aufnehmen.

Hinweis: VbDayOfWeek ist vom Typ Long (4 Byte), für die 7 Tage der Woche würde aber auch der Typ Byte (1 Byte) reichen, andererseits rechnet VBA schneller mit Long als mit Byte-Werten.

Leider kann so ein Array im Code nicht effizient befüllt werden, wie die folgenden drei Möglichkeiten im Beispielprogramm zeigen:


Private Sub TestWochentag()     Dim TagDE(1 To 7) As WochenTag, tmpTag As WochenTag     TagDE(1).Tag = vbMonday     TagDE(1).Name = "Montag"     TagDE(1).Kurz2 = "Mo"     TagDE(1).Kurz3 = "Mon"     With TagDE(2)         .Tag = vbTuesday         .Name = "Dienstag"         .Kurz2 = "Di"         .Kurz3 = "Die"     End With     With tmpTag         .Tag = vbWednesday         .Name = "Mittwoch"         .Kurz2 = "Mi"         .Kurz3 = "Mit"     End With     TagDE(3) = tmpTag End Sub

Im Beispiel wird Element 1 einzeln befüllt, bei Element 2 wird der Zugriff durch eine With-Anweisung strukturiert und Element 3 wird über die Hilfsvariable tmpTag befüllt. In einer praktischen Excel-Anwendung wird man solche konstanten Werte eher über eine versteckte Tabelle mitliefern, wobei die relevanten Inhalte der Tabelle dann zur Laufzeit in das Array kopiert werden.

Wenn Sie einen benutzerdefinierten Typ verwenden, dann können Sie Arrays auch wieder verschachteln, in dem nämlich die Typdefinition schon ein Element enthält, welches ein Array ist.

Das folgende fiktive Beispiel dient der regionalen Wetterbeobachtung. Es gibt eine Typdefinition TagesWetter, wobei dieser Typ für einen Tag das Datum, zu jeder vollen Stunde (0…23 Uhr) die Temperatur festhält und für die Dauer von je einer Stunde die Blitze zählen soll. Da es zu jeder Stunde eine Temperatur gibt, wird das Element Temperatur() vordimensioniert. Da es aber nicht jeden Tag gewittert, soll das Element Blitze() in der Typdefinition noch nicht dimensioniert werden, um Platz zu sparen.

Die Typdefinition sieht so aus:


Public Type TagesWetter     Tag As Date     Temperatur(0 To 23) As Single     Blitze()            As Byte End Type

Die folgende Prozedur zeigt, wie es in VBA angewendet werden könnte. Zunächst wird für einen Monat ein Array für 31 Tage aus dem benutzerdefinierten Typ TagesWetter deklariert.

Am 13. des Monats gab es zwischen 8:30 Uhr und 12:50 Uhr ein Gewitter, darum wird für den MonatsWetter(13) der Untertyp .Blitze mit ReDim für diese Stunden angelegt. ReDim akzeptiert hier sogar Funktionen, in diesem Beispiel holt Hour() die Indizes aus zwei Datumsvariablen, die hier zu Beispielzwecken feste Werte erhalten haben.


Public Sub WetterDatenSpeicherung()     Dim MonatsWetter(1 To 31) As TagesWetter     Dim GwtrEin As Date, GwtrAus As Date, i As Long         ' Beispiel für einen Tag     MonatsWetter(13).Tag = Date     GwtrEin = CDate("08:30")     GwtrAus = CDate("12:50")     ReDim MonatsWetter(13).Blitze(Hour(GwtrEin) To Hour(GwtrAus))         ' Belegung der Elemente von Blitze mit Zufallszahlen     With MonatsWetter(13)         For i = LBound(.Blitze) To UBound(.Blitze)             .Blitze(i) = Int(Rnd() * 70)         Next i     End With End Sub

Die For-Schleife zeigt, wie man die Anzahl der Blitze in das Array eintragen kann. In diesem Beispiel werden dazu Zufallswerte erzeugt. Wichtig ist, hier die Struktur des Datenzugriffs zu erkennen. Um die Temperatur auszulesen, die am 7. des Monats um 14 Uhr gemessen wurde, greift man so zu:


    MsgBox MonatsWetter(7).Temperatur(14)