<\/p>\n
über Arrays unter VBA wurde schon vieles geschrieben und gesagt. Dennoch gibt es praktische Hinweise, die man gelegentlich benötigt, nicht so einfach im Internet. Also werfen wir in diesem Beitrag einen genaueren Blick auf die Möglichkeiten, die Arrays bieten, und liefern einige neue Funktionen rund um die Felder zum temporären Speichern und Bereitstellen von Daten. Unter anderem sehen wir uns an, wie Sie die Inhalte von Arrays zu Debugging-Zwecken im Direktbereich oder in einer Excel-Tabelle ausgeben oder wie Sie prüfen, ob ein Array leer ist.<\/b><\/p>\n
Array aus Datensatz füllen<\/b><\/p>\n
Manchmal möchten Sie vielleicht die Daten eines oder mehrerer Felder eines Datensatzes in einem Array speichern, um diese Daten performant bereitzustellen.<\/p>\n
Wenn Sie nur ein Feld eintragen möchten, reicht ein eindimensionales Array aus. Dieses füllen Sie mithilfe der Funktion RecordsetInArrayEindimensional<\/b> aus Listing 1. Die Funktion erwartet das einzulesende Recordset mit dem Parameter rst<\/b>.<\/p>\n Listing 1: Einlesen eines Feldes eines Recordsets in ein eindimensionales Array<\/span><\/b><\/p>\n Es deklariert ein Array namens arr <\/b>mit dem Datentyp Variant<\/b>. In einer Do While<\/b>-Schleife durchläuft die Funktion alle Datensätze des Recordsets und führt dabei zwei Anweisungen aus:<\/p>\n Danach übergibt die Funktion das Array als Funktionswert an die aufrufende Routine. Diese sieht beispielsweise wie in Listing 2 aus. Die Routine füllt das Recordset-Objekt mit den Datensätzen der Tabelle tblArtikel<\/b>, wobei nur das Feld Artikelname <\/b>berücksichtigt wird.<\/p>\n Listing 2: Test zum Einlesen eines eindimensionalen Arrays aus einem Recordset<\/span><\/b><\/p>\n Dann ruft sie die Funktion Recordset-In-ArrayEindimensional <\/b>auf und übergibt das Recordset als Parameter.<\/p>\n Um zu prüfen, ob alle Werte wie gewünscht in das Array eingelesen wurden, durchläuft die Routine anschließend in einer For…Next<\/b>-Schleife alle Elemente des Arrays und gibt den jeweils aktuellen Wert im Direktbereich des VBA-Editors aus.<\/p>\n ReDim vorziehen<\/b><\/p>\n Wenn schon vorher klar ist, wie viele Elemente im Array landen sollen, können Sie dieses natürlich auch bereits vor dem Eintritt in die Do While<\/b>-Schleife redimensionieren.<\/p>\n Der Versuch, dies direkt beim Deklarieren des Arrays zu erledigen, schlägt allerdings fehl – mit der Fehlermeldung Konstanter Ausdruck erforderlich<\/b>, wie in Bild 1 zu erkennen.<\/p>\n Bild 1: Automatische Ergänzung eines Feldnamens<\/span><\/b><\/p>\n Also deklarieren wir das Array in einer neuen Version der Funktion (s. Listing 3) zunächst ohne Angabe der Anzahl der enthaltenen Elemente und ändern diese dann später mit der ReDim<\/b>-Anweisung. Dieser übergeben wir den Ausdruck rst.RecordCount<\/b>. Damit diese Eigenschaft des Recordsets immer den richtigen Wert liefert, verschieben wir den Datensatzzeiger zuvor mit MoveLast <\/b>einmal an das Ende der Datensatzgruppe und mit MoveFirst <\/b>wieder an die erste Position.<\/p>\n Listing 3: Einlesen eines eindimensionalen Arrays mit vorheriger Dimensionierung<\/span><\/b><\/p>\n Im Gegensatz zum ReDimensionieren innerhalb der Do While<\/b>-Schleife entfällt hier natürlich das Preserve<\/b>-Schlüsselwort der ReDim<\/b>-Anweisung, denn das Array ist ja ohnehin noch leer.<\/p>\n Gleichzeitig beheben wir noch einen kleinen Fehler bei der Dimensionierung: Dort haben wir nämlich zunächst den Wert von rst.RecordCount <\/b>als Obergrenze angegeben. Die Untergrenze ist jedoch, wenn nicht anders angegeben, 0<\/b>. Das heißt, dass wir das Array um ein Element zu groß dimensionieren würden. Also ändern wir den Ausdruck für die obere Grenze in rst.RecordCount – 1<\/b>.<\/p>\n Mehrdimensionale Arrays mit Daten füllen<\/b><\/p>\n Wenn Sie nun mehrere Felder in das Array einlesen möchten, müssen Sie dieses entsprechend anders dimensionieren – nämlich als zweidimensionales Array. Dazu deklarieren wir in der Funktion RecordsetInArrayMehrdimensional <\/b>zunächst wieder das Array arr<\/b> (s. Listing 4).<\/p>\n Listing 4: Einlesen eines mehrdimensionalen Arrays mit vorheriger Dimensionierung<\/span><\/b><\/p>\n Anschließend redimensioniert die Funktion es, und zwar mit der Anzahl der Datensätze in der ersten Dimension und mit der Anzahl der Felder in der zweiten. Danach durchläuft die Funktion in einer äußeren Do While<\/b>-Schleife die Datensätze des Recordsets und in einer inneren Schleife die Felder – und zwar über die Fields<\/b>-Auflistung des Recordsets. Innerhalb der Schleife füllt die Funktion das Array. Da fld <\/b>keinen Index mitliefert, müssen wir immer noch umständlich eine Zählervariable namens i <\/b>mitlaufen lassen, um die Position für die zweite Dimension anzugeben. Dies können wir auch noch eleganter erledigen, indem wir die innere Schleife als For…Next<\/b>– statt als For…Each<\/b>-Schleife implementieren:<\/p>\n Die Funktion zum Testen dieser Funktion finden Sie in Listing 5. Die Prozedur erstellt diesmal ein Recordset mit den beiden Feldern ArtikelID <\/b>und Artikelname <\/b>der Tabelle tblArtikel<\/b> und übergibt dieses als Parameter an die Funktion RecordsetInArrayMehrdimensional<\/b>.<\/p>\n Listing 5: Testprozedur zum Einlesen mehrdimensionaler Arrays<\/span><\/b><\/p>\n <\/p>\n Das Ergebnis durchläuft die Prozedur dann in zwei verschachtelten For…Next<\/b>-Schleifen. Bei der Ermittlung der Arraygrenzen der beiden Dimensionen verwenden wir natürlich eine andere Form der LBound<\/b>– und UBound<\/b>-Funktionen. Die untere Grenze der ersten Dimension ermitteln wir beispielsweise mit LBound(arr, 1)<\/b>, die der zweiten Dimension mit LBound(arr, 2)<\/b>.<\/p>\n Innerhalb der inneren Schleife sollen die beiden Feldwerte des aktuellen Datensatzes ausgegeben werden. Damit diese in einer Zeile erscheinen, versehen wir die Debug.Print<\/b>-Anweisungen noch mit einem folgenden Semikolon (;<\/b>). Dies verhindert den Sprung in die folgende Zeile.<\/p>\n Allerdings würden so alle Elemente hintereinander in eine einzige Zeile geschrieben werden. Also müssen wir für jeden Datensatz, der ja in der äußeren Schleife durchlaufen wird, noch eine Debug.Print<\/b>-Anweisung ohne Parameter einfügen.<\/p>\n Funktion zur Ausgabe von Arrays<\/b><\/p>\n Damit haben wir nun zwei Funktionen geschaffen, mit denen wir Arrays etwa auf Basis eines Recordsets füllen können. Allein die Ausgabe zur Prüfung des Arrays mit dem Durchlaufen der Elemente der einzelnen Dimensionen steckt noch in den beiden Testprozeduren.<\/p>\n Dabei wäre doch gerade die Ausgabe des Inhalts eines Arrays bei Entwickeln hilfreich, denn damit könnte man auf die Schnelle herausfinden, ob das Array auch die erwarteten Daten enthält. Also programmieren wir uns eine kleine Funktion, welche ein Array als Parameter erwartet und dieses im Direktbereich von Access ausgibt.<\/p>\n Die Prozedur heißt OutputVariant <\/b>und ist in Listing 6 zu finden. Die Prozedur deklariert zwei Variablen namens i <\/b>und j <\/b>zum Durchlaufen der verschiedenen Dimensionen des Arrays sowie vier Variablen namens int1Min<\/b>, int1Max<\/b>, int2Min <\/b>und int2Max<\/b>, um die Unter- und Obergrenzen der Dimensionen des Arrays aufzunehmen.<\/p>\n Listing 6: Prozedur zur Ausgabe von Arrays<\/span><\/b><\/p>\n Diese Variablen füllt die Prozedur in den folgenden vier Zeilen, für die durch die Anweisung On Error Resume Next <\/b>die Fehlerbehandlung deaktiviert wurde. Dies hat den Hintergrund, dass die Anzahl der Dimensionen ja auch eins sein kann. In diesem Fall führt der Zugriff etwa auf LBound(varArray, 2) <\/b>zum Fehler Index außerhalb des gültigen Bereichs<\/b>.<\/p>\n In diesem Fall behält die Variable int2Max<\/b> den Wert 0<\/b>, was im nächsten Schritt geprüft wird. Dann liegt offensichtlich ein eindimensionales Array vor, das innerhalb einer einfachen For…Next<\/b>-Schleife über die Werte von int1Min <\/b>bis int1Max <\/b>ausgegeben werden kann.<\/p>\n Anderenfalls liegt wohl ein mehrdimensionales Array vor, für dessen Ausgabe zwei For…Next<\/b>-Schleifen mit den Laufvariablen i <\/b>und j <\/b>durchlaufen werden.<\/p>\n Um leere Inhalte besser erkennen zu können, sollen diese durch ein x <\/b>ersetzt werden. Dies können Sie gegebenenfalls ändern, wenn Sie ein anderes oder gar kein Zeichen wünschen. Auch hier wenden wir wieder den Trick mit dem Semikolon (;<\/b>) hinter der Debug.Print<\/b>-Anweisung an, um die Elemente der zweiten Dimension in einer Zeile auszugeben.<\/p>\n Diese Routine hat allerdings eine Einschränkung, die mit dem Datentyp des Arrays zusammenhängt. Wenn Sie es einmal mit der folgenden Prozedur testen, die ein Array mit dem Datentyp Integer<\/b> füllt und dieses dann der Routine OutputVariant <\/b>übergibt, erhalten wir den Fehler aus Bild 2:<\/p>\n Bild 2: Fehler bei falschen Array-Datentyp<\/span><\/b><\/p>\n Anscheinend benötigen wir also für jeden Datentyp eine eigene Funktion zur Ausgabe der Daten eines Arrays – dabei brauchen Sie allerdings nur den Datentyp sowie den Funktionsnamen zu ändern.<\/p>\n x statt leerer Felder ausgeben<\/b><\/p>\n Manch einer mag sich fragen, wozu wohl die Ausgabe des Zeichens x statt eines leeren Feldes dienen mag. Der Grund ist einfach: Auf diese Weise lassen sich beispielsweise Arrays, die zu groß dimensioniert wurden, enttarnen. Schauen Sie sich das folgende Beispiel an:<\/p>\n Hier wird ein Array scheinbar komplett mit dem Wert bla <\/b>gefüllt. Wenn wir uns die Ausgabe der Prozedur OutputVariant <\/b>für das Array arr <\/b>ansehen, werden wir jedoch eines Besseren belehrt (s. Bild 3).<\/p>\n Bild 3: Dieses Array hat mehr Elemente als gedacht.<\/span><\/b><\/p>\n Hier ist ein typischer Fehler passiert: Im Eifer des Gefechts ging ich davon aus, dass ich für die Dimensionierung eines Arrays mit zehn mal zehn Elementen den Ausdruck arr(10, 10) <\/b>angeben müsste. Allerdings sind die Werte für die Dimensionierung 0-basiert, sodass arr(9, 9) <\/b>korrekt gewesen wäre. Durch die Ausgabe des Buchstaben x <\/b>statt der leeren Elemente konnte ich erkennen, dass die Dimensionierung etwas zu groß geraten ist.<\/p>\n Array mit anderen Datentypen als Variant ausgeben<\/b><\/p>\n Wenn Sie ein Array eines anderen Datentyps im Direktfenster ausgeben, müssen Sie die Prozedur OutputVariant <\/b>anpassen. Dazu reicht es aus, den Datentyp in der ersten Zeile der Prozedur durch den gewünschten Datentyp zu ersetzen – und natürlich den Dateinamen. Für ein Integer<\/b>-Array sieht das etwa so aus:<\/p>\n Es gibt jedoch noch eine andere Möglichkeit, die uns doch noch die Erstellung mehrerer gleichartiger Prozeduren erspart: Wir lassen einfach die Klammern hinter dem Parameter für das Array weg. Heraus kommt dann dieser Prozedurkopf mit dem neuen Namen:<\/p>\n Array zu groß für Direktbereich<\/b><\/p>\n Früher oder später werden Sie mit Arrays arbeiten, die zu groß sind, um sinnvoll im Direktbereich ausgegeben zu werden.<\/p>\n Aber zum Glück gibt es ja Excel: Geben Sie doch das Array einfach in einer Tabelle unter Excel aus, dann können Sie diese in aller Ruhe analysieren. Wenn das Array beispielsweise die Daten der Tabelle tblArtikel<\/b> enthält, sieht das Ergebnis unter Excel wie in Bild 4 aus.<\/p>\n Bild 4: Array nach Excel ausgeben<\/span><\/b><\/p>\n Je nach der Größe des Arrays kann die Ausgabe nach Excel schon deshalb sinnvoll sein, da der Direktbereich des VBA-Editors nur ein begrenztes Fassungsvermögen aufweist.<\/p>\n Schauen wir uns die Prozedur OutputExcel<\/b> im Detail an. Sie erwartet natürlich das zu untersuchende Array als Parameter (s. Listing 7).<\/p>\nPublic Function <\/span>RecordsetInArrayEindimensional(rst As <\/span>DAO.Recordset) As Variant<\/span>()\r\n Dim <\/span>arr() As Variant<\/span>\r\n Do While<\/span> Not<\/span> rst.EOF\r\n ReDim Preserve arr(rst.AbsolutePosition)\r\n arr(rst.AbsolutePosition) = rst.Fields(0).Value\r\n rst.MoveNext<\/span>\r\n Loop<\/span>\r\n RecordsetInArrayEindimensional = arr\r\nEnd Function<\/span><\/pre>\n\n
Public Sub <\/span>Test_RecordsetInArray()\r\n Dim <\/span>db As <\/span>DAO.Database\r\n Dim <\/span>rst As <\/span>DAO.Recordset\r\n Dim <\/span>i As Integer<\/span>\r\n Dim <\/span>arr() As Variant<\/span>\r\n Set<\/span> db = CurrentDb\r\n Set<\/span> rst = db.OpenRecordset(\"SELECT Artikelname FROM tblArtikel\", dbOpenDynaset)\r\n arr = RecordsetInArrayEindimensional(rst)\r\n For i = LBound<\/span>(arr) To UBound<\/span>(arr)\r\n Debug.Print<\/span> arr(i)\r\n Next<\/span> i\r\nEnd Sub<\/span><\/pre>\n![\"Automatische](\"..\/fileadmin\/_temp_\/2014_06\/pic_963_001.png\")
<\/p>\nPublic Function <\/span>RecordsetInArrayEindimensional(rst As <\/span>DAO.Recordset) As Variant<\/span>()\r\n Dim <\/span>arr() As Variant<\/span>\r\n rst.MoveLast\r\n rst.MoveFirst\r\n ReDim arr(rst.RecordCount - 1)\r\n Do While<\/span> Not<\/span> rst.EOF\r\n arr(rst.AbsolutePosition) = rst.Fields(0).Value\r\n rst.MoveNext<\/span>\r\n Loop<\/span>\r\n RecordsetInArrayEindimensional = arr\r\nEnd Function<\/span><\/pre>\nPublic Function <\/span>RecordsetInArrayMehrdimensional(rst As <\/span>DAO.Recordset) As Variant<\/span>()\r\n Dim <\/span>arr() As Variant<\/span>\r\n Dim <\/span>fld As <\/span>DAO.Field\r\n Dim <\/span>i As Integer<\/span>\r\n rst.MoveLast\r\n rst.MoveFirst\r\n ReDim arr(rst.RecordCount - 1, rst.Fields.Count - 1)\r\n Do While<\/span> Not<\/span> rst.EOF\r\n For Each fld In rst.Fields\r\n arr(rst.AbsolutePosition, i) = fld.Value\r\n i = i + 1\r\n Next<\/span> fld\r\n rst.MoveNext<\/span>\r\n Loop<\/span>\r\n RecordsetInArrayMehrdimensional = arr\r\nEnd Function<\/span><\/pre>\nDo While<\/span> Not<\/span> rst.EOF\r\n For i = 0 To rst.Fields.Count - 1\r\n arr(rst.AbsolutePosition, i) _\r\n = rst.Fields(i)\r\n Next<\/span> i\r\n rst.MoveNext<\/span>\r\nLoop<\/span><\/pre>\nPublic Sub <\/span>Test_RecordsetInArrayMehrdimensional()\r\n Dim <\/span>db As <\/span>DAO.Database\r\n Dim <\/span>rst As <\/span>DAO.Recordset\r\n Dim <\/span>i As Integer<\/span>\r\n Dim <\/span>j As Integer<\/span>\r\n Dim <\/span>arr() As Variant<\/span>\r\n Set<\/span> db = CurrentDb\r\n Set<\/span> rst = db.OpenRecordset(\"SELECT ArtikelID, Artikelname FROM tblArtikel\", _\r\n dbOpenDynaset)\r\n arr = RecordsetInArrayMehrdimensional(rst)\r\n For i = LBound<\/span>(arr, 1) To UBound<\/span>(arr, 1)\r\n For j = LBound<\/span>(arr, 2) To UBound<\/span>(arr, 2)\r\n Debug.Print<\/span> arr(i, j);\r\n Next<\/span> j\r\n Debug.Print<\/span>\r\n Next<\/span> i\r\nEnd Sub<\/span><\/pre>\nPublic Sub <\/span>OutputVariant(varArray() As Variant<\/span>)\r\n Dim <\/span>i As Integer<\/span>\r\n Dim <\/span>j As Integer<\/span>\r\n Dim <\/span>int1Min As Integer<\/span>\r\n Dim <\/span>int1Max As Integer<\/span>\r\n Dim <\/span>int2Min As Integer<\/span>\r\n Dim <\/span>int2Max As Integer<\/span>\r\n On Error Resume Next<\/span>\r\n int1Min = LBound<\/span>(varArray, 1)\r\n int1Max = UBound<\/span>(varArray, 1)\r\n int2Min = LBound<\/span>(varArray, 2)\r\n int2Max = UBound<\/span>(varArray, 2)\r\n On Error GoTo<\/span> 0\r\n If <\/span>int2Max = 0 Then<\/span>\r\n For i = int1Min To int1Max\r\n Debug.Print<\/span> varArray(i);\r\n Next<\/span> i\r\n Debug.Print<\/span>\r\n Else<\/span>\r\n For i = int1Min To int1Max\r\n For j = int2Min To int2Max\r\n If <\/span>varArray(i, j) = \"\" Then<\/span>\r\n Debug.Print<\/span> \" x \";\r\n Else<\/span>\r\n Debug.Print<\/span> varArray(i, j);\r\n End If<\/span>\r\n Next<\/span> j\r\n Debug.Print<\/span>\r\n Next<\/span> i\r\n End If<\/span>\r\nEnd Sub<\/span><\/pre>\n![\"Fehler](\"..\/fileadmin\/_temp_\/2014_06\/pic_963_002.png\")
<\/p>\nPublic Sub <\/span>TestArrayInteger()\r\n Dim <\/span>i As Integer<\/span>\r\n Dim <\/span>j As Integer<\/span>\r\n Dim <\/span>arr(9, 9) As Integer<\/span>\r\n For i = 0 To 9\r\n For j = 0 To 9\r\n arr(i, j) = 1\r\n Next<\/span> j\r\n Next<\/span> i\r\n OutputVariant arr\r\nEnd Sub<\/span><\/pre>\nPublic Sub <\/span>TestMitPlatzhaltern()\r\n Dim <\/span>i As Integer<\/span>\r\n Dim <\/span>j As Integer<\/span>\r\n Dim <\/span>arr(10, 10) As Variant<\/span>\r\n For i = 0 To 9\r\n For j = 0 To 9\r\n arr(i, j) = \"bla\"\r\n Next<\/span> j\r\n Next<\/span> i\r\n OutputVariant arr\r\nEnd Sub<\/span><\/pre>\n![\"Dieses](\"..\/fileadmin\/_temp_\/2014_06\/pic_963_003.png\")
<\/p>\nPublic Sub <\/span>OutputInteger(varArray() As Integer<\/span>)<\/pre>\nPublic Sub <\/span>OutputArray(varArray As Variant<\/span>)<\/pre>\n![\"Array](\"..\/fileadmin\/_temp_\/2014_06\/pic_963_004.png\")
<\/p>\nPublic Sub <\/span>OutputExcel(varArray As Variant<\/span>)\r\n Dim <\/span>objWorksheet As <\/span>Excel.Worksheet\r\n Dim <\/span>i As Integer<\/span>\r\n Dim <\/span>j As Integer<\/span>\r\n Dim <\/span>int1Min As Integer<\/span>\r\n Dim <\/span>int1Max As Integer<\/span>\r\n Dim <\/span>int2Min As Integer<\/span>\r\n Dim <\/span>int2Max As Integer<\/span>\r\n Set<\/span> objWorksheet = GetWorksheet\r\n On Error Resume Next<\/span>\r\n int1Min = LBound<\/span>(varArray, 1)\r\n int1Max = UBound<\/span>(varArray, 1)\r\n int2Min = LBound<\/span>(varArray, 2)\r\n int2Max = UBound<\/span>(varArray, 2)\r\n On Error GoTo<\/span> 0\r\n If <\/span>int2Max = 0 Then<\/span>\r\n For i = int1Min To int1Max\r\n objWorksheet.Cells(i + 1, 1) = i + 1\r\n If <\/span>varArray(i) = \"\"