VBA in Excel/ Schleifen und Matrizen

|

Matrizen in VBA werden als Arrays bezeichnet. Grundsätzlich gibt es mehrere Möglichkeiten, ein Array zu erzeugen:

Über Dim als Datenfeld, z.B. ergibt die Anweisung Dim Matrix(1 To 3, 1 To 3) eine 3 × 3-Matrix mit der mathematisch richtigen Indizierung der Zeilen und Spalten jeweils von 1..3
An eine Variable vom Typ Variant kann ein Array aus einer anderen Variablen zugewiesen werden
Über die Anweisung array() kann an eine Variable vom Typ Variant ein Array zugewiesen werden, z.B. mit Var1D = array(11,12,13); Auf diese Art ist es auch möglich, ein zweidimensionales Array anzulegen, z.B. durch Var2D = array(array(11, 12), array(21, 22)); Arrays höherer Dimensionen lassen sich auf vergleichbare Weise anlegen.

Arrays können auch als Rückgabewert einer benutzerdefinierten Funktion definiert werden. Wenn eine benutzerdefinierte Funktion eine 2 × 2-Matrix in ein Tabellenblatt zurückgeben soll, muss auf dem Tabellenblatt zuerst ein Bereich mit 2 × 2 Zellen markiert werden, dann tippt man die Funktion ein und schließt die Eingabe wie bei einer Matrixformel mit Umschalt+Strg+Eingabe ab.

Das Array lässt sich leider nicht als Konstante (über Const) speichern - weder in einer Prozedur/Funktion noch im Deklarationsteil eines Moduls.

Arrays in VBA

Das erste Beispiel prüft, ob eine Zahl durch eine Gruppe von anderen Zahlen teilbar ist - falls nicht, wird die Zahl selbst zurückgegeben. Der Vorteil bei dieser Schreibweise mit einem array() ist, dass das Programm zu einem späteren Zeitpunkt ohne besondere Kenntnisse des Codes erweitert werden kann, indem man der TeilerListe einfach noch ein paar Zahlen anhängt:

Public Function TeilerGefunden(Zahl As Long) As Long
    Dim TeilerListe As Variant  ' Liste der Primteiler
    Dim Teiler      As Variant  ' Schleifenvariable
    
    TeilerListe = Array(2, 3, 5, 7, 11, 13)
    TeilerGefunden = Zahl
    
    For Each Teiler In TeilerListe
        If Zahl Mod Teiler = 0 Then
            TeilerGefunden = Teiler
            Exit Function
        End If
    Next Teiler
End Function

Das nächste Beispiel nutzt folgende Eigenschaften in Excel: Tabellenblätter haben nicht nur einen Namen (Eigenschaft .Name), der auf der Registerkarte sichtbar ist, sondern auch einen Objektnamen (Eigenschaft .CodeName), der nur im Projekt-Explorer des VBA-Editors sichtbar ist und auch dann unverändert bleibt, wenn der Benutzer das Blatt umbenennt. Das deutsche Excel legt diesen Namen (.CodeName) standardmäßig wie den Blattnamen (.Name) an, aber -wie geschrieben- ändert er sich .CodeName nicht mehr bei einer Umbenennung des Blattes.

In diesem Falle enthält die Arbeitsmappe zwei Blätter, die als Objekte mit Tabelle1 und Tabelle2 angesprochen werden können. Die Prozedur bestimmt die Anzahl der benutzten Zellen in jedem Blatt und zeigt sie an:

Public Sub BelegungTabellenblätter()
    Dim ListeAllerTabellen  As Variant ' Liste aller Tabellen
    Dim Tabelle             As Variant ' Schleifenvariable
    
    ListeAllerTabellen = Array(Tabelle1, Tabelle2) ' Zuweisung des Objektarrays
    
    For Each Tabelle In ListeAllerTabellen
        MsgBox "Tabelle " & Tabelle.Name & " hat " & _
                Tabelle.UsedRange.Cells.Count & " belegte Zellen"
    Next Tabelle
End Sub

Dieses Beispiel zeigt also, dass das array() auch Objekte aufnehmen kann. Auch hier bietet sich wieder die einfache Möglichkeit, den Code später einfach von Hand zu ergänzen.

Eindimensionale vordimensionierte Matrix füllen

Eine dimensionierte eindimensionale Matrix wird mit der Zählvariablen gefüllt und danach werden die Werte per MsgBox ausgegeben.

Sub FuellenMatrixEinfach()
   Dim arrNumbers(1 To 3) As Integer
   Dim intCounter As Integer
   For intCounter = 1 To 3
      arrNumbers(intCounter) = intCounter
   Next intCounter
   For intCounter = 1 To UBound(arrNumbers)
      MsgBox arrNumbers(intCounter)
   Next intCounter
End Sub

Eindimensionale Matrix mit vorgegebenem Wert dimensionieren und füllen

Die Matrix wird auf die Hälfte der Anzahl der Zeilen der mit A1 verbundenen Zellen dimensioniert. Danach werden die Zellinhalte jeder zweiten Zelle der ersten Spalte in die Matrix eingelesen und über eine MsgBox wieder ausgegeben.

Sub FuellenMatrixSingle()
   Dim arrCells() As String
   Dim intCounter As Integer, intCount As Integer, intArr As Integer
   Dim strCell As String
   intCount = Range("A1").CurrentRegion.Rows.Count / 2
   ReDim arrCells(1 To intCount)
   For intCounter = 1 To intCount * 2 Step 2
      intArr = intArr + 1
      arrCells(intArr) = Cells(intCounter, 1)
   Next intCounter
   For intCounter = 1 To UBound(arrCells)
      MsgBox arrCells(intCounter)
   Next intCounter
End Sub

Mehrdimensionale Matrix füllen

Der mit der Zelle A1 zusammengehörige Bereich wird in eine Matrix ein- und eine einzelne Zelle über MsgBox wieder ausgelesen.

Sub FuellenMatrixMulti()
   Dim arrJahr As Variant
   arrJahr = Range("A1").CurrentRegion
   MsgBox arrJahr(3, 2)
End Sub

Das folgende Beispiel zeigt, wie man den markierten Bereich im aktiven Tabellenblatt ausliest. Die Funktion geht die Auswahl im Tabellenblatt Zeile für Zeile und dann Spalte für Spalte durch. Jeder gefundene Wert wird in ein Element der Variablen Matrix gespeichert. Diese ist dann der Rückgabewert der Funktion MatrixFüllen():

Public Function MatrixFüllen() As Double()
    Dim ZeileNr     As Long     ' Zeilenzähler
    Dim SpalteNr    As Long     ' Spaltenzähler
    
    Dim Matrix()    As Double   ' Matrix
    
    ' Matrix auf Zeilen- und Spaltenzahl der Auswahl bringen
    ' Dabei soll jeder Index bei 1 beginnen
    ReDim Matrix(1 To Selection.Rows.Count, 1 To Selection.Columns.Count)
    
    ' Auswahl zeilenweise lesen
    For ZeileNr = 1 To Selection.Rows.Count
        ' Auswahl spaltenweise lesen
        For SpalteNr = 1 To Selection.Columns.Count
            With Selection.Cells(ZeileNr, SpalteNr)
                If IsNumeric(.Value) Then
                    ' Matrix elementweise füllen
                    Matrix(ZeileNr, SpalteNr) = .Value
                Else
                    ' Fehlermeldung ausgeben
                    MsgBox "Zelle " & .Address & " enthält keine Zahl"
                    Exit Function
                End If
            End With
        Next SpalteNr
    Next ZeileNr
    
    ' Rückgabewert der Funktion
    MatrixFüllen = Matrix
End Function

Die Funktion MatrixFüllen() erstellt die Größe der Matrix anhand der Markierung dynamisch und weist den Inhalt der Matrix dem Rückgabewert der Funktion zu. Zur dynamischen Dimensionierung gehört im Beispiel auch, dass der Index der Matrix mit 1 beginnend definiert wird (mathematische Notation), ohne diese Angabe würde Excel gewohnheitsmäßig die Indizes bei 0 beginnen lassen. Falls eine Zelle keine Zahl enthält, erscheint eine Fehlermeldung. Leere Zellen werden als 0 interpretiert.

|

Hoch zu „Inhaltsverzeichnis“ |