Die Split-Funtion zerlegt einen Text in ein Array vom Typ String, wobei ein einzelnes Trennzeichen vorgegeben wird. Dies kann besonders vorteilhaft sein, wenn ein Text die Struktur einer Tabellenzeile mit festen Inhalten hat.
Das folgende Beispiel zeigt, wie man in Excel den Spaltennamen (A…XFD) einer Zelle ermittelt:
Public Function SpaltenName(Bereich As Range) As String
SpaltenName = Split(Bereich.Cells(1).Address, "$")(1)
End Function
Quiz für Codeversteher:
Was macht .Cells(1) in dem Beispiel?
Wenn Bereich aus mehreren Zellen besteht, sorgt Cells(1) dafür, dass nur die Spalte der oberen, linken Zelle ermittelt wird.
Wie viele Werte enthält das Array, welches Split in der Beipielfunktion SpaltenName liefert?
Es sind drei Werte. Die Address-Eigenschaft liefert als Default den absoluten Bezug, z.B. $C$3; Split macht daraus ein leeres Feld vor dem ersten $ (Array-Index 0), ein Feld mit der Spaltenbezeichnung C (Index 1) und ein Feld mit der Zeilennummer (Index 2)
Was ermitteln die Funktionen IsMissing() und IsEmpty() für den Inhalt des ersten Feldes (Index 0)?
Da Split ein Array vom Typ String ist, liefern beide Funktionen den Wert FALSE zurück. Der in Index 0 gespeicherte Wert ist ein leerer Text "", so wie in VBA die Konstante vbNullString
Split verarbeitet nur ein einzelnes Trennzeichen, in meinem Text ist der Zeilenumbruch aber durch zwei Zeichen, nämlich "\n" gekennzeichnet. Was kann ich tun?
Ersetzen Sie zunächst die beiden Zeichen durch ein einzelnes mit der Replace-Funktion. Verwenden Sie ein Ersatzzeichen,
welches sicher niemand im Text verwendet, beispielweise den ASCII-Wert 0, in VBA-Code leicht als Konstante vbNullChar
verfügbar. Die Anweisung sieht dann für das Beispiel so aus:
MeinTextArray = Split(Replace(MeinText, "\n", vbNullChar), vbNullChar) …
Ein Problem von Arrays im Code ist, dass sie nicht als Konstanten definiert werden können. Split eröffnet aber die Möglichkeit, Werte dennoch kompakt als Konstante zu speichern und in ein Array zu übernehmen. Das folgende Beispiel zeigt einen Prozedurkopf:
Public Sub KonstantArray()
Const TextListe = "D|NL|F|E|GB|IRL"
Dim TextArray() As String
TextArray = Split(TextListe, "|")
…
End Sub
Diese Möglichkeit ist auf Texte beschränkt. Die Zuweisung von Split an ein Array von einem numerischen Datentyp würde auch dann einen Fehler auslösen, wenn alle Werte in Zahlen kovertierbar wären, da zwischen den Arrays keine automatische Typkonvertierung stattfindet.
Die Umkehrung der Split-Funktion liefert Join. Join fasst die Arrays wieder zu einem Text (Datentyp String) zusammen und fügt ein wählbares Trennzeichen ein.
Die Anzahl der Elemente und die Dimensionen eines Arrays kann man auf verschiedene Art festlegen:
Dim A(5) As Double
Eindimensionales Array. Ohne Option Base oder mit Option Base 0 sind A(0) bis A(5) adressierbar. Mit Option Base 1 sind A(1) bis A(5) adressierbar. Bei numerischen Datentypen wird jedes Element mit 0 initialisiert.
Dim X(-2 to 5) As Integer
Eindimensionales Array, die Indizes werden von -2 bis +5 gezählt, es können also 8 Elemente adressiert werden. Die Dimension ist zur Laufzeit nicht mehr veränderbar, und Option Base hat keinen Einfluss.
Dim M(1 To 7, 1 To 5) As Double
Zweidimensionales Array. Vom Datentyp und von den Indizes passt dies zu einer 7×5 Matrix. Die Dimension ist zur Laufzeit nicht mehr veränderbar, und Option Base hat keinen Einfluss.
Dim TempTextA(5) As String
Speichert 5 Werte als Text, je nach Option Base Einstellung von 0…5 oder von 1...5; Dimension zur Laufzeit nicht mehr veränderbar. Jedes Element des Arrays wird mit vbNullString ("") initialisiert, für die Strings besteht keine Längenbegrenzung.
Dim TempTextB(5) As String * 3
Wie oben, allerdings hat der Text immer eine feste Länge von 3 Zeichen und jedes Element wird mit vbNullChar (00h, hier wegen der Länge 3 mit 000000h) initialisiert. Der Vergleich TempTextB(2) = vbNullString oder wird also nie True ergeben, da jedes Element immer genau 3 Zeichen hat.
Dim WochenTag(1 To 366) As vbDayOfWeek
In VBA gibt es zahlreiche Konstanten, wie z. B. die Wochentage. Natürlich kann man diese als Typ heranziehen. Der Vorteil liegt darin, dass nun IntelliSense Vorschläge macht, wenn man den Wert in ein Element des Arrays eintragen will. In diesem Falle ist das kompatibel, aber unökonomisch, denn vbDayOfWeek ist vom Typ Long (4 Bytes × 366 Elemente), aber für Wochentage reicht der Typ Byte (1 Byte × 366 Elemente) aus.
Dim Werte() As Long
ReDim Werte(1 to 3, 1 to 3)
Hier wird zunächst die Variable Werte() als Array vom Typ Long deklariert und später mit ReDim auf die richtige Größe gebracht. In diesem Beispiel habe ich Zahlen für die Dimensionen eingetragen, aber bei ReDim können statt dessen auch Variable eingesetzt werden. Mit ReDim Preserve lassen sich später Arrays verkleinern oder vergrößern, ohne dass die verbliebenen Felder ihre Daten verlieren. Vorsicht: Bevor erstmals ReDim eingesetzt wurde, liefern LBound() und UBound() einen Fehler. Mit IsArray() lässt sich hier aber schon feststellen, dass es sich bei Werte um ein Datenfeld handelt.
Dim Werte
ReDim Werte(1 to 3, 1 to 3) As Long
Hier wurde Werte als Variant definiert (fehlende Typdefinition erzeugt immer Variant), deshalb kann man den Datentyp Long auch später festlegen. Nur hat man hier eben kein echtes Datenfeld, sondern eine einzelne Variant-Variable, die mit einem Datenfeld gefüllt wird.
Besonderheiten von ReDim:
Arrays, deren Größe bereits mit der Dim-Anweisung festgelegt wurden, lassen sich nicht mehr mit ReDim verändern.
Nur beim erstmaligen Gebrauch von ReDim lassen sich mehrere Dimensionen anlegen.
Die Größe der letzten Dimension lässt sich mit ReDim beliebig oft ändern.
Redim ohne das Schlüsselwort Preserve löscht die Inhalte aller Elemente.
Manche Quellen weisen darauf hin, dass ein ReDim sehr langsam ist. Wenn ein Array in einem Programm regelmäßig vergrößert werden muss, so gibt es folgende Empfehlung: Das Array gelegetntlich um einen größere Anzahl von Elementen (z. B. 100) vergrößern, statt einzeln zu vergrößern. Dazu braucht man einen eigenen Zeiger für das letzte benutzte Element. Ist das Array befüllt, kann es mit einem letzten ReDim auf die richtige Größe verkleinert werden.
Quiz für Codeversteher:
Sind Dim X(1 to 3) As Variant oder Dim X() As Variant typisierte Arrays oder nicht?
Ja, sie sind typisiert, denn nicht das Array wird in einem Variant gespeichert, sondern das Array hat den Typ Variant. Die Verwirrung lässt sich perfektionieren, denn beide könnten auch in einer Variablen vom Typ Variant gespeichert werden.
Der Geltungsbereich einer Array-Variablen hängt vom Ort ab, an dem es mit Dim deklariert wird:
Als lokale Variable, die innerhalb einer Function, Sub oder Property deklariert wird wo nur dort zugegriffen werden kann
Als Variable im Deklarationsteil eines Standardmoduls, welches dort auch die Deklaration Option Private Module (nicht alle Office-Programme) besitzt, sodass die Variablen im ganzen Projekt (Datei) sichtbar sind, auf die aber nicht aus dem Code einer anderen Datei zugegriffen werden kann.
Als Variable im Deklarationsteil eines Standardmoduls, ohne die Deklaration Option Private Module die Variable in anderen Projekten (gleichzeitig geöffneten Dateien) sichtbar macht.
Als Variable in einem Klassenmodul (insbesondere Tabelle, Formular), wo das Array für alle Programme in dem Klassenmodul sichtbar ist.
Wenn ein Array ohne Dimensionen angelegt wurde, enthält es noch keine Elemente. Um den Beladungszustand eines Arrays festzustellen kann man beispielsweise so vorgehen:
Public Sub Größe()
Dim Werte() As Long
MsgBox IsArray(Werte) ' Ist es ein Array?
MsgBox LBound(Werte) ' Untere Grenze?
MsgBox UBound(Werte) ' Obere Grenze?
End Sub
IsArray() erkennt zuverlässig auch Arrays in Variant-Werten. Aber das Problem ist, dass in diesem halb-definierten Zustand der oben angelegten Variablen Werte() die Funktionen UBound() und LBound() zu einem Laufzeitfehler führen - egal welchen Datentyp Werte hat (auch Variant!).
Dieses Verhalten ist höchst inkonsistent, denn ParamArray gibt bei einem leeren Array LBound(Werte) = 0 und UBound(Werte) = -1 und ebenfalls problemlos ist es, wenn man einem leer dimensionierten Variant-Array mit … = Array() ein leeres Array zuweist.
VBA fehlt auch eine Funktion, um die Anzahl der Dimensionen zu bestimmen. Hier ist also eine etwas unschöne Funktion erforderlich, die einen Laufzeitfehler zur Erkennung braucht, so wie im Beispiel gezeigt:
Public Function AnzArrayDim(Feld) As Long AnzArrayDim = -1 If Not IsArray(Feld) Then Exit Function On Error GoTo AusstiegBeiLaufzeitFehler Do AnzArrayDim = AnzArrayDim + 1 Loop While UBound(Feld, AnzArrayDim + 1) >= 0 AusstiegBeiLaufzeitFehler: End Function
Ist der Rückgabewert -1, so handelt es sich nicht um ein Array. Wurde das Array noch nicht dimensioniert, so ist der Rückgabewert 0 und sonst erhält man die Anzahl der Dimensionen.
Auf diese Funktion aufbauend, lassen sich auch die Elemente des Arrays zählen:
Public Function AnzElem(Feld) As Long Dim AnzDim As Long, i As Long AnzDim = AnzArrayDim(Feld) AnzElem = IIf(AnzDim < 1, AnzDim, 1) For i = 1 To AnzDim AnzElem = AnzElem * (UBound(Feld, i) - LBound(Feld, i) + 1) Next i End Function
Hier ein paar kleine Code-Schnipsel zur schnellen Verwendung.
Das erste Beispiel passt in eine Programmzeile und erinnert ein wenig an die VBA-Funktion Choose:
MsgBox Array("Mo", "Di", "Mi", "Do", "Fr", "Sa", "So")(Weekday(Now, vbMonday) – 1)
Diese Zeile gibt die deutsche Kurzbezeichnung für den Wochentag von heute (Quelle: Funktion Now, welche das Systemdatum des Rechners liefert) heraus.
Die möglichen Ausgabewerte werden als Elemente in das Array eingetragen. Sofort hinter dem Array kommt die Klammer, in der der Index zur Auswahl erwartet wird. Diesen Index liefert die Funktion Weekday() aus dem Tagesdatum Now(), wobei in Deutschland der Montag als erster Tag in der Woche gilt. Da Weekday() Werte zwischen 1-7 zurückgibt, muss vom Ergebnis von Weekday() noch 1 abgezogen werden.
Quiz für Codeversteher: Was könnte ein Unterschied zu Choose sein?
In diesem Falle macht es keinen großen Unterschied. Aber man könnte hier mit verschachtelten Arrays arbeiten, was komplexere Auswahlen erlaubt.
Im folgenden Beispiel wird ein eindimensionales Array um ein Element vergrößert:
ReDim Preserve Werte(LBound(Werte) To UBound(Werte) + 1)
Voraussetzung ist, dass die Dimension erst durch ein ReDim angelegt wurde. Das Schlüsselwort Preserve sorgt dafür, dass die vorhandenen Elemente nicht gelöscht werden.
Quiz für Codeversteher: Warum muss in diese Beispiel das Array Werte() eindimensional sein, wenn es mit ReDim Werte(UBound(Werte) + 1) bearbeitet wird?
Arrays können nur in der letzten Dimension verändert werden und da weder bei LBound() noch bei UBound() eine Dimension angegeben wurde, nimmt VBA den Default-Wert 1 an. Das Array ist also eindimensional.
Hier ein paar Tipps, um sicher mit Arrays zu arbeiten:
Fragen Sie immer die Grenzen von Arrays mit LBound() oder UBound() ab, vor allem, wenn Sie in Schleifen arbeiten. Mit Dim A(x To y) können x und y beliebige Ganzzahlen sein. Das Problem könnte auftreten, wenn Sie ein Unterprogramm zur Bearbeitung eines Arrays verwenden.
Schleifenzähler (Laufvariable i in Strukturen wie For i = …) sollten vom Typ Long sein, keinesfalls jedoch vom Typ Byte. Wenn Sie nämlich mit Step abwärts zählen, könnte -1 als Ergebnis herauskommen. UBound() von Parameter-Arrays (als Argument von Function) konnte ebenfalls -1 sein und mit einer Dim A(x To y)-Anweisung kann ein Array mit beliebigen negativen Indizes eingestellt werden. Mit dem Datentyp Byte würde hier der Laufzeitfehler eingebaut.
Versuchen möglichst den Datentyp Variant zu vermeiden und spezifische Typen oder benutzerdefinierte Typen zu verwenden. Dadurch erkennen Sie falsche Inhalte in Arrays an einem Laufzeitfehler früher und ich vermute, dass typisierte Arrays schneller bearbeitet werden.
Es gibt verschiedene Beispielprogramme für Arrays:
Sortierfunktionen bei Microsoft
Sortieren einer Matrix nach einer Spalte von Max Kaffl
Pearson Software Consulting bietet die bisher umfangreichste Sammlung von VBA-Code zur Bearbeitung von Arrays, die ich kenne (Dank an Christoph J. Dorner für den Tipp).
In Excel gibt es einige versteckte Begrenzungen, die teilweise undokumentiert sind. Speziell die von Arrays kann man sich beiXL-Limits ansehen, wobei dort der Begriff Arrays etwas weiter gefasst wird, als in meinem Beitrag hier.
Einige Funktionen in VBA fehlen leider, sodass sie per Programm nachgerüstet werden müssen. Zu diesen Funktionen gehören:
Arrays können nicht als Public-Variable in Klassenmodulen definiert werden.
Alternative: Deklarieren Sie eine Public Function, deren Rückgabewert das gewünschte Array ist.
Arrays können nicht als Konstante im Deklarationsteil von Modulen oder Klassenmodulen stehen, selbst wenn sie mit der Array()-Funktion und konstanten Werten erzeugt werden.
Für numerische Arrays fehlen Addition und Subtraktion gleichgroßer Arrays, Multiplikation aller Elemente mit einer Konstanten, Addition einer Konstanten zu allen Elementen
Typumwandlungen sind nicht einmal von Typen wie Integer nach Long möglich.
Löschen des Array Inhaltes. Wenn sich das Array mit ReDim bearbeiten lässt, kann man hier immerhin das Array beim Neudimensionieren ohne das Schlüsselwort Preserve löschen.
Erweitern und verbinden von Arrays. Es gibt keine Funktion, um an ein Array eine ganze Zeile mit Daten anfügen. Es geht nur, indem man ReDim ausführt und dann die neuen Elemente einzeln mit Daten belegt.
Vergrößern und Verkleinern geht nur mit ReDim und nur in der letzten Dimension. Bei einer Matrix bedeutet das, dass nur noch die Zahl der Spalten, aber nicht mehr die Anzahl der Zeilen verändert werden kann.
Arrays im Code können nur sehr umständlich angelegt werden. In VB gibt es die Möglichkeit, Arrays mit { } aufzubauen, was die Übersichtlichkeit verbessert.
Ich habe den Eindruck, dass Microsoft es noch nie verstanden hat, nützliche Konzepte weiterzuentwickeln. So erscheint mir die Office-Entwicklung (von Randfeatures abgesehen) praktisch tot und eine Behebung der hier genannten Lücken im Array-Konzept ist für VBA nicht mehr zu erwarten. Statt dessen versucht Microsoft offenbar, mit neuen Konzepten Kunden zu halten (Ribbons, Cloud, 64-Bit mit Inkompatibilität) und wirft Bewährtes und Beliebtes über Bord.
Dieses strategiefreie und meiner Meinung nach kundenfeindliche Konzept führte dazu, dass ich diesen Beitrag mit LibreOffice Writer und Excel 2000 erstellt habe. Dafür laufen meine Beispiele aber auch auf so ziemlich allen 32-bit Office-Versionen seit Office 2000.