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.
Einer Sub, Function, Property oder auch Declare-Anweisung kann man Arrays als Argument übergeben. Dazu gibt es mehrere Arten:
Über eine Variant-Variable, die ein Array enthält
Über eine Array-Variable, die nicht dimensioniert ist und keinen Datentyp hat, also als Variant behandelt wird
Ü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
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.
Grundsätzlich kann man auf zwei Wegen ein Array aus einer Funktion zurückgeben:
Der Rückgabewert der Funktion wird als Variant deklariert und nimmt ein beliebiges Array auf:
Public Function RückgabeAlsArray() As Variant
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:
Anders als bei Variablen wird also das Klammerpaar, welches den Rückgabewert als Array kennzeichnet, an die Typdeklaration des Rückgabewertes angehängt, hier also an den Datentyp Long und nicht an den Namen der Variablen/Funktion!
Es gibt nur die Möglichkeit, ein Klammerpaar anzugeben, nichts weiter. Hier kann das zurückgegebene Array nicht mit einer Zahl oder einem Bereich (1 To 10) dimensioniert werden.
In der Funktion muss die Array-Hilfsvariable genau den gleichen Typ haben, wie als Rückgabewert der Funktion definiert, sonst klappt es nicht.
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:
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 RückgabeAlsArray() As Variant
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
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:
Gebündelte Datentypen können wesentlich effektiver und platzsparender bearbeitet werden, wenn sie gebündelt werden.
Hilfsfunktionen wie IntelliSense geben keine Hilfe bei Variant-Arrays, was das Programmieren erschwert.
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)