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[Bearbeiten]
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[Bearbeiten]
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[Bearbeiten]
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[Bearbeiten]
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.