Sub BereichInArrayEinlesen()

Dim VarDat As Variant
Dim Zeile As Long
Dim Spalte As Long
Dim ZeileMax As Long
Dim Spaltemax As Long
   
 On Error GoTo BereichInArrayEinlesen_Error

    With tbl_Daten
     'Array mit Daten aus der Tabelle füllen
     VarDat = .UsedRange
     ZeileMax = .UsedRange.Rows.Count
     Spaltemax = .UsedRange.Columns.Count
      'Array, Feld für Feld verarbeiten
     For Zeile = 1 To ZeileMax
      For Spalte = 1 To Spaltemax
        VarDat(Zeile, Spalte) = VarDat(Zeile, Spalte) * 2
      Next Spalte
     Next Zeile
     End With

    'geänderten Array in tbl_Ergebnis ausleeren
    With tbl_Ergebnis
       .Range(.Cells(1, 1), .Cells(ZeileMax, Spaltemax)) = VarDat
    End With
    On Error GoTo 0
   Exit Sub
 BereichInArrayEinlesen_Error:
     MsgBox "Fehler " & Err.Number & " (" & Err.Description & _
            ") in prozedur BereichInArrayEinlesen of Modul mdl_Array in Zeile "
 End

Listing 1: Daten in Array einlesen, verarbeiten und ausgeben.

Sub ZeilenLöschen()
  Dim VarDat As Variant   Dim VardatZiel As Variant   Dim ZeileArr As Long   Dim Zeile As Long  
    On Error GoTo ZeilenLöschen_Error
   Debug.Print "Start:" & Now
  tbl_Rest.UsedRange.ClearContents
   With tbl_Gesamt.UsedRange
      'Array aus Tabelleninhalt befüllen
      VarDat = tbl_Gesamt.UsedRange
      Zeile = 1
       'gleichgroßen Zielarray anlegen
      ReDim VardatZiel(1 To .Rows.Count, 1 To .Columns.Count)
       For ZeileArr = 1 To UBound(VarDat)
       If VarDat(ZeileArr, 1) > 90 Then
         VardatZiel(Zeile, 1) = VarDat(ZeileArr, 1)
         Zeile = Zeile + 1
       End If
      Next ZeileArr
  End With
   With tbl_Rest
   .Range(.Cells(1, 1), .Cells(Zeile, UBound(VardatZiel, 2))) = VardatZiel
  End With 
   Debug.Print "Ende:" & Now
    On Error GoTo 0
   Exit Sub
 ZeilenLöschen_Error:
     MsgBox "Fehler " & Err.Number & " (" & Err.Description & _
    ") in prozedur ZeilenLöschen of Modul mdl_Löschen"
End Sub

Listing 2: Bestimmte Zeilen sollen gelöscht werden.

Das „Löschen der Daten“ wird hier mit Hilfe einer zusätzlichen Tabelle erledigt, d.h. es werden nicht wirklich Daten gelöscht, sondern eben nur die gewünschten Daten auf eine Zieltabelle ausgegeben. Dazu wird die Tabelle tbl_Rest vorab sicherheitshalber über die Methode ClearContents, die Sie auf den benutzten Bereich der Tabelle anwenden, eingesetzt.

Dazu gehen Sie wie folgt vor: Zunächst werden zwei Datenfelder vom Typ Variant deklariert. Die Größe der Datenfelder liegt zu diesem Zeitpunkt des Makros noch nicht vor. Zusätzlich benötigen Sie noch zwei Variablen vom Typ Long, um die Zeilen im Datenfeld verarbeiten zu können.

Danach wird der Startzeitpunkt über die Anweisung Debug.Print und der Funktion Now festgehalten. Damit wird ein Zeitstempel im Direktfenster der Entwicklungsumgebung gesetzt. Am Ende des Makros wird diese Anweisung wiederholt. Dies soll hier zur Orientierung dienen, wie schnell dieses Makro verarbeitet wird.

Im nächsten Schritt wird die komplette Tabelle, hier bestehend aus einer Spalte, in ein Datenfeld mit dem Namen VarDat transportiert. Jetzt wird ein gleichgroßer Ziel-Array mit dem Namen VarDatZiel angelegt. Denn es könnte rein theoretisch sein, dass alle Daten in die Zieltabelle übertragen werden würden. Zu diesem Zeitpunkt des Makros ist die Größe (=Zeilenanzahl) des Arrays bekannt. Um diesen Array jetzt neu zu dimensionieren, also seine tatsächliche Größe zuzuweisen, verwenden Sie die Anweisung ReDim. Es handelt sich hierbei um einen zweidimensionalen Array, der genau wie eine Tabelle aus Zeilen und Spalten besteht.

Im Anschluss daran wird der Array VarDat mittels einer For Next-Schleife Zeile für Zeile durchlaufen. Über die Funktion UBound, die Sie am Array VarDat ansetzen, können Sie die letzte Zeile des Arrays ermitteln. Innerhalb der Schleife prüfen Sie, ob der jeweilige Wert größer als der Wert 90 ist. Wenn ja, dann übertragen Sie den Wert aus dem Array VarDat in den Array VarDatZiel. Erhöhen Sie danach den Zeilenzähler jeweils um den Wert 1.

Beim Schleifenaustritt ist der Array VarDatZiel mit Daten gefüllt. Dieser Array muss jetzt in die Tabelle tbl_Rest eingefügt werden. Dazu muss die Größe des Bereichs in der Tabelle vorher genau angegeben werden.

Sub ZeilenFilternMehrereKriterien()
  Dim VarDat As Variant
  Dim VardatZiel As Variant
  Dim ZeileArr As Long
  Dim Zeile As Long
  Dim Spalte As Long
  Dim SpalteMax As Long
    On Error GoTo ZeilenFiltern_Error
   Debug.Print "Start:" & Now   tbl_PLZ_Umsatz.UsedRange.ClearContents
   With tbl_Kunden.UsedRange
      'Array aus Tabelleninhalt befüllen
      VarDat = tbl_Kunden.UsedRange
      SpalteMax = .Columns.Count
      Zeile = 1
            'gleichgroßen Zielarray anlegen
      ReDim VardatZiel(1 To .Rows.Count, 1 To .Columns.Count)
       For ZeileArr = 1 To UBound(VarDat)
       If VarDat(ZeileArr, 4) > 80000 And VarDat(ZeileArr, 6) > 250 Then
         For Spalte = 1 To SpalteMax
         VardatZiel(Zeile, Spalte) = VarDat(ZeileArr, Spalte)
         Next Spalte
          Zeile = Zeile + 1
       End If
      Next ZeileArr
  End With
   With tbl_PLZ_Umsatz
   .Range(.Cells(1, 1), .Cells(Zeile, UBound(VardatZiel, 2))) = VardatZiel
   .Range("A:F").Sort Key1:=.Range("F1"), Order1:=xlDescending, _
    Key2:=.Range("A1"), order2:=xlAscending, Header:=xlYes
    .Range("A:F").Columns.AutoFit
  End With
    Debug.Print "Ende:" & Now
    On Error GoTo 0
   Exit Sub
 ZeilenFiltern_Error:
     MsgBox "Fehler " & Err.Number & " (" & Err.Description & _
    ") in prozedur ZeilenFilternMehrereKriterien of Modul mdl_Löschen"
 End Sub

Listing 3: Eine Filterung mit mehreren Kriterien anwenden.

Wie schon im vorherigen Beispiel legen Sie zu Beginn des Makros aus dem vorherigen zwei Variablen vom Typ Variant an. Diese werden später die Daten aufnehmen.

Löschen Sie danach die Zieltabelle tbl_PLZ_Umsatz mit Hilfe der Methode ClearContents. Damit werden nur die Werte aber keine Formatierungen gelöscht. Danach übertragen Sie wie vorher schon beschrieben alle Daten aus der Tabelle tbl_Kunden in das Datenfeld VarDat. Jetzt wird ermittelt, wie viele Spalten denn überhaupt in Verwendung sind. Dieser Wert wird in der Variablen SpalteMax für die spätere Verwendung zwischengespeichert. Danach wird der Ziel-Array, der die gefilterten Daten aufnehmen soll, redimensioniert. Der Ziel-Array VarDatZiel ist dabei zu Beginn genauso groß wie auch der Quell-Array VarDat.

Im Anschluss daran wird der Quell-Array Zeile für Zeile mit einer For Next-Schleife durchlaufen. Innerhalb der Schleife werden die vierte Spalte sowie die sechste Spalte des Datenfelds geprüft. Nur wenn die Postleitzahl größer als 8000 und der Umsatz größer als 250 Euro betragen, wird die jeweilige Zeile in den Ziel-Array VarDatZiel geschoben. Die Befüllung erfolgt wiederum über eine For Next-Schleife, die Spalte für Spalte innerhalb der „gefilterten“ Zeile abarbeitet und im Ziel-Array ablegt. Wurden alle Zeilen des Quell-Arrays VarDat abgearbeitet, kann die wirkliche Größe des Ziel-Arrays VarDatZiel ermittelt werden. Diese Größe wird als Bereich in der Tabelle tbl_PLZ_Umsatz vorgehalten und der Ziel-Array VarDatZiel wird in einem Schritt in die Tabelle geschoben. Der Neuberechnungsaufwand ist dabei sehr gering.

Als nächstes werden die eingefügten Daten direkt in der Tabelle mit Hilfe der Methode Sort zunächst nach Höhe des Umsatzes und dann nach dem Namen sortiert. Damit die Daten gut lesbar sind, wenden Sie die Methode AutoFit auf die Spalten A-F an, die dafür sorgt, dass die Spalten nach ihrem Platzbedarf angepasst werden.

Ein wahrer Zeitfresser sind Arbeiten, die mit dem Vorbereiten und Konvertieren von Daten zusammenhängen. Auch hier können Makros helfen, Routinearbeiten automatisch und vor allem sehr schnell mit Hilfe von Arrays zu erledigen.

Bei der folgenden Aufgabe liegt eine Tabelle mit dem Namen tbl_Konvertieren wie in Abbildung 6 gezeigt vor.

Im Datenbestand liegen einige Probleme vor:

• In Spalte B der Tabelle tbl_Konvertieren liegen teilweise negative Werte vor. Leider befinden sich die Minuszeichen dabei auf der falschen Seite. Diese müssen am rechten Rand abgeschnitten und am linken Rand eingesetzt werden.
• In Spalte C gibt es bei einigen Zellen führende bzw. nachgestellte Leerzeichen. Nicht nur, dass man diese unerwünschten Zeichen schlecht sieht, nein, sie stellen auch bei späteren Auswertungen eine große Gefahr da. Auch diese Leerzeichen müssen entfernt werden.
• In Spalte D soll das Zeichen „/“ durch ein Minuszeichen ersetzt werden.
• Die Spalten E und F sollen zusammengeklebt und als Schlüssel verwendet werden.

Neben reinen Konvertierungsarbeiten müssen oft auch zusätzliche Informationen generiert werden, die im eigentlichen Datenbestand noch nicht vorhanden sind.

Lösen Sie alle diese Aufgabenstellungen mit dem folgenden Makro.

Sub DatenKonvertierenImArbeitsspeicher()
   Dim VarDat As Variant
   Dim Zeile As Long
 
    On Error GoTo DatenKonvertierenImArbeitsspeicher_Error
 
   With tbl_Konvertieren
     ZeileMax = .UsedRange.Rows.Count
     SpalteMax = .UsedRange.Columns.Count + 1 '+1, wegen der zusätzlichen Schlüsselspalte
      
      'Array befüllen
       VarDat = .Range(.Cells(1, 1), .Cells(ZeileMax, SpalteMax))
       
      'Daten im Array manipulieren
       For Zeile = LBound(VarDat) To UBound(VarDat)
 
        'Minuszeichen umstellen, bei Werten wo das Minuszeichen auf der falschen Seite steht
        If Right(VarDat(Zeile, 2), 1) = "-" Then
          VarDat(Zeile, 2) = Left(VarDat(Zeile, 2), Len(VarDat(Zeile, 2)) - 1) * -1
        End If
 
        'Trim (Entfernen der vorangestellten bzw. nachgestellten Leerzeichen
         VarDat(Zeile, 3) = Trim(VarDat(Zeile, 3))
         
         'Replace (Das Zeichen "/" wird durch das Zeichen "-" ersetzt
         VarDat(Zeile, 4) = Replace(VarDat(Zeile, 4), "/", "-")
         
         'Schlüssel bilden (Kst+Kontierung)
         VarDat(Zeile, 7) = VarDat(Zeile, 5) & VarDat(Zeile, 6)
 
       Next Zeile
   End With
   
   'Ausleeren des konvertierten Arrays in die Zieltabelle
  With tbl_KonvertierungZiel
   .Range(.Cells(1, 1), .Cells(Zeile, UBound(VarDat, 2))) = VarDat
  End With
 
    On Error GoTo 0
    Exit Sub
 
 
 DatenKonvertierenImArbeitsspeicher_Error:
 
     MsgBox "Fehler " & Err.Number & " (" & Err.Description & _
   ") in prozedur DatenKonvertierenImArbeitsspeicher of Modul mdl_Konvertieren"
 
 End Sub

Listing 4: Problematisches Datenmaterial wird bereinigt.