Die Array()-Funktion wird im Quellcode als Sammler für Daten aller Typen verwendet. Sie erzeugt immer ein eindimensionales Array, in dem jedes Element vom Datentyp Variant ist. Natürlich ein mit Array() erzeugtes Feld auch andere Arrays beinhalten, auch solche, die mit der Funktion Array() erstellt wurden. In diesem Falle hat die Adressierung eine Eigenart, die ich hier noch vorstellen werde.
Die Array()-Funktion gibt den Datentyp Variant zurück, innerhalb eines Programms sollten also solche Arrays nur dem Datentyp Variant zugewiesen werden. Dazu ein Beispiel:
Public Function ZeigeSteuerSatz() Dim MwSt As Variant MwSt = Array(0.07, 0.19) MsgBox "Steuersatz " & Format(MwSt(1), "Percent") End Function
Arrays, die auf diese Art erstellt werden, speichern ihr erstes Element immer mit dem Index 0
Hinweise:
Bei den Zahlenwerten innerhalb des Codes gilt für Dezimalzahlen die US-Notation, hier trennt der Dezimalpunkt den Vorkommateil vom Nachkommateil.
In der Excel-Hilfe wird teilweise behauptet, mit Array() erzeugte Felder starten immer mit Index 0, unabhängig von Option Base – das stimmt zumindest in meiner Version von Office nicht.
Grundsätzlich bringt Option Base 1 kaum Vorteile, trotz der (geringen) Speicherplatzersparnis, sodass sogar die Excel-Hilfe vom Gebrauch abrät.
Mit Array() lassen sich Datenfelder auch verschachteln, z. B. auf diese Art:
Public Function DoppelArray() Dim TestArray As Variant TestArray = Array(Array(11, 22), Array(33, 44, 55), "XYZ") MsgBox TestArray(1)(2) ' Ausgabe: 55 End Function
Dieses Beispiel ist mehrfach bemerkenswert, denn hier sieht man, …
wie Arrays ineinander verschachtelt werden können
dass innere Arrays unterschiedliche Länge haben dürfen - was nur bei Arrays mit Elementen vom Variablentyp Variant geht
dass Indizes in dieser speziellen Form einzeln geklammert werden müssen, nämlich als TestArray(1)(2)
Warum ist das so? Dafür gibt es folgende Erklärung: Wie eingangs erklärt, ist das äußere Array() ein eindimensionaler Speicher für Variant-Werte, d. h. alles was in einer Variablen vom Typ Variant abgelegt werden kann, kann auch mit der Funktion Array() zu einem Datenfeld verbunden werden.
Beim Aufruf von TestArray(1) wird also der erste Inhalt des äußeren Arrays geholt, und das ist hier zufälligerweise ein anderes Array, nämlich Array(33, 44, 55). Auf dieses innere Array kann mit einem zweiten Index zugegriffen werden, also durch TestArray(1)(2).
Der letzte Wert des äußeren Arrays ist ein einfacher Text, auf ihn wird mit TestArray(2) zugegriffen. Da man so den ganzen Text greifen kann, ist keine zweite Klammer mehr möglich und nötig. Das zeigt die Logik dieses Array-Typs: Es ist aus Variant-Typen aufgebaut und jeder Variant kann einen anderen Variablentyp enthalten, beispielsweise einen konstanten Wert oder auch ein anderes Array.
Hinweis: Kombinations- und Listenfelder erlauben die Eingabe der Werte als Array, z. B. mit dem Aufruf Me.ListBox1.List() = …; Wegen der Adressierung mit doppelter Klammerung liefert die Array()-Funktion dafür kein geeignetes Datenformat, es wird ein zweidimensionales Array benötigt.
Für Array()-Funktion gibt es einige äußerst nützliche Anwendungen, deren Vorteil vor allem in der Übersichtlichkeit des Codes liegt. Das folgende Beispiel zeigt einige der eingebauten Dateieigenschaften in Office-Dokumenten an:
Public Sub DateiEigenschaftenAusgeben() Dim PropName As Variant, Ausgabe As String ' Einige der eingebauten Eigenschaften kopieren For Each PropName In Array("Title", "Subject", "Author", _ "Keywords", "Comments", "Category", "Manager", "Company") With ActiveWorkbook.BuiltinDocumentProperties(PropName) Ausgabe = Ausgabe & .Name & ": " & .Value & vbNewLine End With Next PropName MsgBox Ausgabe End Sub
Das Array enthält dabei die Namen der eingebauten Dateieigenschaften. ActiveWorkbook ist dabei das Dokumentobjekt in Excel, für Word wäre es ActiveDocument, für andere Anwendungen ist es entsprechend anzupassen.
Quiz für Codeversteher:
Warum gibt es in diesem Beispiel kein Problem mit der Frage, ob der Index bei 0 oder 1 (je nach Option Base Einstellung) anfängt?
Da das Array mit einer For-Each-Schleife verarbeitet wird, läuft es immer vom ersten Objekt bis zum letzten Objekt durch.
Was ist hier der besondere Vorteil des Arrays?
Das Array ist wie eine Definition mit Const sehr übersichtlich. Im Code kann ganz leicht eine Dateieigenschaft hinzugefügt oder entfernt werden, insbesondere wenn das Array in der Nähe von Dim und Const-Anweisungen platziert wird.
Die BuiltinDocumentProperties können sowohl über ihren Eigenschaftsnamen als auch ihre Eigenschaftsnummer angesprochen werden. BuiltinDocumentProperties akzeptiert auch diese Zahlen, sodass eine Änderung des Arrays in Array(1, 2, 3, 4, 5, 18, 20, 21) zur gleichen Ausgabe führen würde.
Die Array()-Funktion lässt sich im Code über mehrere Zeilen mit _ umbrechen.
Ein weiteres Excel-spezifisches Anwendungsbeispiel sind Bereiche in Tabellenblättern, denn der Datentyp Variant kann natürlich auch Objekte wie das Range-Objekt speichern. Im folgenden Beispiel werden Referenzen auf Zellen gespeichert. Eine Variable vom Typ Objekt in VBA enthält stets eine Referenz auf das Objekt (nur 4 Bytes), nie das Objekt selbst!
Public Sub LeseZelle() Dim ZellenListe As Variant, Wert As Variant, Ausgabe As String ' Drei verschiedene Bezugsarten With ActiveWorkbook.Worksheets("Matrix") ZellenListe = Array(.Range("A1"), .Cells(2, 2), .[C3]) End With For Each Wert In ZellenListe Ausgabe = Ausgabe & Wert.Address & ": " & Wert.Value & vbNewLine Next Wert MsgBox Ausgabe End Sub
Quiz für Codeversteher:
Warum macht IntelliSense in der For Each-Schleife bei der Eingabe von Wert. keine Vorschläge für die Methoden und Eigenschaften des Range-Objekts?
Das liegt daran, dass ZellenListe und Wert Variant-Typen sind und demnach keine Eigenschaften oder Methoden eines Objektes mitbringen. Erst zur Laufzeit wird geprüft, ob der Inhalt der Variablen Wert überhaupt ein Objekt enthält und ob dieses auch eine Address- und Value-Eigenschaft besitzt.
Die With-Anweisung dient nur dazu, den Inhalt des Arrays kurz zu halten. Hier werden also Objekte in einem Array gespeichert. Da das Array vom Typ Variant ist, darf hier kein Set-Befehl für die Zuweisung verwendet werden! Die Anweisung Set ZellenListe = Array(…) würde zu einem Laufzeitfehler führen.
ActiveWorkbook.Worksheets("Matrix") verweist hier auf den Tabellennamen. In Excel sollte man als Referenz den Objektnamen (steht in der Eigenschaft CodeName), da dann der Code unempfindlich gegen Änderungen des Tabellennamens ist. Details finden sich in der Excel-Hilfe zur CodeName-Eigenschaft
Das folgende Code-Beispiel dient zum Ersetzen von Umlauten und anderen Spezialzeichen in einem Text. Durch ByRef wird der Text direkt in der Original-Variablen bearbeitet:
Public Sub TextKorrigieren(ByRef MeinText As String) Dim Zeichen, ZeichenAlt, ZeichenNeu, i As Long ZeichenAlt = Array("Ä", "ä", "Ö", "ö", "Ü", "ü", "ß", vbNewLine, vbCr, vbTab) ZeichenNeu = Array("Ae", "ae", "Oe", "oe", "Ue", "ue", "ss", vbLf, vbLf, " ") ' Ersetzung der einzelnen Zeichen in der Schleife For i = LBound(ZeichenAlt) To UBound(ZeichenAlt) MeinText = Replace(MeinText, ZeichenAlt(i), ZeichenNeu(i), , , vbBinaryCompare) Next i End Sub
Die Lösung greift gleichzeitig auf zwei Arrays zu, weshalb man hier nicht mit der For-Each-Schleife arbeiten kann. In den beiden Arrays steht jeweils das zu suchende Zeichen und sein Ersatz an gleicher Position. Der Tabulator (vbTab) wird durch vier Leerzeichen ersetzt. Speziell für Excel werden hier vbNewLine und vbCr durch vbLf ausgetauscht, weil Excel in Zellen und Kommentaren nur vbLF als Zeilenumbruch verwendet.
Alternativ könnte man das Beispiel auch mit einem verschachtelten (zweidimensionalen) Array so programmieren:
Public Sub TextKorrigieren(ByRef MeinText As String) Dim Zeichen, ZeichenListe ZeichenListe = Array(Array("Ä", "Ae"), Array("ä", "ae"), Array("Ö", "Oe"), _ Array("ö", "oe"), Array("Ü", "Ue"), Array("ü", "ue"), Array("ß", "ss"), _ Array(vbNewLine, vbLf), Array(vbCr, vbLf), Array(vbTab, " ")) For Each Zeichen In ZeichenListe MeinText = Replace(MeinText, Zeichen(0), Zeichen(1), , , vbBinaryCompare) Next Zeichen End Sub
Quiz für Codeversteher:
Warum muss in dieser Lösung nur ein Index für Zeichen in der Schleife angegeben werden?
Weil die For-Each-Schleife das Array schon auflöst und die Variable Zeichen nur noch das innere Array enthält.
Was passiert, wenn im Code Option Base 1 steht?
Dann gibt es einen Laufzeitfehler, weil dann Zeichen(1) und Zeichen(2) abgefragt werden müssen. Gegen diesen Fehler kann der Code durch Zeichen(LBound(Zeichen)) statt Zeichen(0) und Zeichen(UBound(Zeichen)) statt Zeichen(1) abgesichert werden.
Warum wurde hier As Variant in der Dim-Anweisung weggelassen?
Weil Variant in VBA der Standard-Datentyp ist, der immer angenommen wird, wenn nichts anderes definiert wurde. Meist ergänze ich zur klareren Darstellung trotzdem Variant.
Auch Steuerelemente eines Formulars lassen sich mit der Array()-Funktion verbinden. Das kann beispielsweise nützlich sein, wenn man für mehrere CheckBoxen (Kontrollkästchen) überprüfen will, ob mindestens eines aktiviert ist. Der folgende Code läuft nur im zugehörigen Formular-Modul:
Private Sub EingabePrüfungChk() Dim Auswahl As Variant For Each Auswahl In Array(Me.chkBox1, Me.chkBox2, Me.chkBox3, Me.chkBox4) If Auswahl.Value Then Exit For Next Auswahl If IsEmpty(Auswahl) Then MsgBox "Min. 1 Kästchen auswählen" End Sub
Hier steht wieder ein Array im Code, wobei jedes Element der Objektverweis auf ein Kontrollkästchen (Checkbox) im Formular ist. Mit einer For-Each-Schleife werden die Checkboxen abgefragt. Ist wenigstens eine Value-Eigenschaft True, so ist auch mindestens ein Kontrollkästchen aktiviert und die Bedingung für die weitere Ausführung des Formularcodes ist erfüllt.
Man könnte das Array im vorigen Beispiel auch mit den Steuerelement-Namen der Checkboxen als Text füllen, die For-Each-Schleife des vorigen Beispiels sähe dann so aus:
For Each Auswahl In Array("chkBox1", "chkBox2", "chkBox3", "chkBox4") If Me.Controls(Auswahl).Value Then Exit For Next Auswahl
Der direkte Objektverweis des ersten Beispiels von EingabePrüfung() hat aber einen großen Vorteil, denn Änderungen am Namen einer Checkbox oder das Löschen einer Checkbox (was beim Programmieren manchmal vorkommt) macht so durch einen Kompilierfehler auf sich aufmerksam. Wenn die zweite Version der Schleife unter einer On Error Resume Next-Anweisung läuft, kann ein Fehler unentdeckt bleiben oder in ein schönes Suchspiel ausarten.
Ein weiteres Beispiel, ebenfalls aus der Eingabeprüfung in einem Formular, fragt Textfelder ab, ob der Anwender überall numerische Daten eingegeben hat:
Private Sub EingabePrüfungTxt() Dim TextFeld As Variant For Each TextFeld In Array(Me.txtZahl1, Me.txtZahl2, Me.txtZahl3, Me.txtZahl4) If Not IsNumeric(TextFeld.Value) Then TextFeld.SetFocus MsgBox "Es wird eine Zahl erwartet", vbCritical, Me.Caption Exit Sub End If Next TextFeld End Sub
Wenn ein Feld keinen Inhalt hat oder keine Zahl enthält, setzt das Programm den Fokus auf das Textfeld und bricht die Ausführung ab, sodass der Anwender sofort an der richtigen Stelle seine Eingabe nachholen kann.
Quiz für Codeversteher:
Woran erkennt das Programm EingabePrüfungChk(), dass mindestens ein Kästchen geklickt wurde?
Sobald eine aktivierte Checkbox gefunden wurde, springt das Programm aus der Schleife. Dabei bleibt die Referenz auf das gefundene (aktive) Kontrollkästchen in der Laufvariablen Auswahl gespeichert. Wenn die For-Each-Schleife dagegen durchläuft, ohne auf ein aktiviertes Kontrollkästchen zu treffen (also nicht mit Exit For beendet wird), dann ist die Laufvariable Auswahl leer (Empty). Dieser Unterschied wird mit IsEmpty() erkannt.
Warum ist die Laufvariable am Ende nicht Nothing (= kein Objekt referenziert) sondern Empty?
Die Variable Auswahl ist vom Typ Variant und wird von einem Variant-Array mit verschiedenen Objektreferenzen gespeist. Eine leere Variable ist dann Empty, weil nichts (auch keine tote Objektreferenz) mehr enthalten ist. Wäre Auswahl eine Objektvariable, würde sie zum Schluss auf das pure Nichts (Nothing) verweisen. Damit die Verwirrung komplett wird: Natürlich kann man Nothing in eine Variant-Variable speichern, aber hier ist der Lieferant von Werten für Auswahl das Array, und dieses hier kann nur Variant.
Die Laufvariable in For-Each-Schleifen kann vom Typ Variant oder Object sein. Warum kann Auswahl hier nicht als Typ Object deklariert werden?
Ganz klar, weil auch hier wieder ein Array von Variant-Werten bearbeitet wird, welcher jeden beliebigen Untertyp (hier: Object) haben kann.
Vorsicht: In einem Array können verschiedene Objekt-Typen gemischt werden. Wenn Sie beispielsweise Textfelder und Labels beim Öffnen eines Formulars (zur Laufzeit) mit sprachspezifischen Inhalten aktualisieren wollen, wird es einen Laufzeitfehler beim Zugriff auf die Inhalte geben, denn Labels haben keine .Value -Eigenschaft und Textfeldern fehlt die .Caption-Eigenschaft.
Nehmen wir zum Abschluss des Kapitels zur Array()-Funktion folgendes Beispiel: A = Array("A", , "C")
Offensichtlich ist A(0) = "A" und A(2) = "C". Aber was ist A(1)?
Nach allem, was wir bisher über die Array()-Funktion herausgefunden haben, sollte das Ergebnis Empty sein. Aber IsEmpty(A(1)) liefert False. Der fehlende Wert wird dagegen von der IsMissing()-Funktion erkannt.
Warum IsMissing()? Darüber kann ich nur spekulieren, doch die Array()-Funktion scheint die gleiche Implementierung wie ParamArray in der Argumentliste von Sub und Function zu haben, und bei ParamArray macht IsMissing() Sinn.
Hier noch ein weiteres Beispiel: A = Array()
Wie viele Elemente wird A() wohl haben? Die Funktion UBound liefert hier -1 und LBound ist 0, so dass wir nach der Formel [Anzahl der Elemente] = [Obergrenze] – [Untergrenze] + 1 auf 0 Elemente kommen. Bei allen anderen Array-Typen, die mit Dim/ReDim erstellt wurden, führt ein leeres Array zum Laufzeitfehler.