Zählermarke der VG Wort Übersicht · Array()-Funktion · Arrays im VBA-Code · Arrays in Excel · Allgemeines

Excel-Tabellen und Arrays

Speziell in Excel lassen sich in VBA viele Tabellenfunktionen auf Arrays anwenden, allerdings muss man mehrere Punkte beachten:

Will man Excel-Tabellenfunktionen für Arrays in anderen Anwendungen wie Access nutzen, so muss zunächst ein vollständiger Objektverweis her:


    Dim wsf As Object
    Set wsf = CreateObject("Excel.Application").WorksheetFunction

Wenn man in Access auch andere Methoden von Excel braucht, sollte man so definieren:


    Dim wsf As Object, XL As Object
    Set XL = CreateObject("Excel.Application")
    Set wsf = XL.WorksheetFunction

So kann in jedem VBA-fähigen Programm oder aus VBS heraus das WorksheetFunction-Objekt über die Objektvariable wsf benutzt werden – sofern Excel auf dem System installiert ist.

Zu den Funktionen, die auf ein Array anwendbar sind, gehören alle Funktionen, die in der Tabelle einen Bereich auswerten und zu einer Zahl verdichten, also Summe, Produkt, Anzahl, Min, Max usw. Wenn Sie also den größten Wert in einem Array A() von Zahlen suchen, dann ist WorksheetFunction.Max(A) ihr Freund.

Matrixalgebra im Code

Eine besondere Gruppe von Tabellenfunktionen sind die Funktionen für die Matrixalgebra, mit denen Excel beispielsweise Gleichungssysteme lösen kann. In VBA sind diese Funktionen zwar verwendbar, aber für umfangreiche Matrixalgebra reichen sie nicht aus. Der Grund liegt vor allem darin, dass es keine Excel-Funktion zur Addition/Subtraktion zweier Matrizen gibt. Damit könnte man beispielsweise eine iterative Verbesserung von Lösungen eines Gleichungssystems erreichen. Im folgenden Beispiel zeige ich, wie man im Code die quadratische Matrix und den Zeilenvektor anlegt und befüllt. Für das Beispiel habe ich diese Aufgabe in eine eigene Prozedur ausgelagert:


Private Sub MatrixFüllen(ByRef A() As Double, ByRef y() As Double)
    ' Quadratische Koeffizientenmatrix
    A(0, 0) = 2: A(0, 1) = 7: A(0, 2) = 15
    A(1, 0) = 4: A(1, 1) = 8: A(1, 2) = 21
    A(2, 0) = 5: A(2, 1) = 4: A(2, 2) = 16

    ' Spaltenvektor als Zeilenvektor gespeichert
    y(0) = 2: y(1) = 4: y(2) = 8
End Sub

Die Variable A enthält die quadratische Koeffizientenmatrix des Gleichungssystems und die Variable y den Vektor mit den Konstanten. Durch ByRef wird sichergestellt, dass nicht Kopien der Arrays bearbeitet werden, sondern die Originale. Nebenbei kann die Funktion MatrixFüllen() so zwei Werte zurückgeben.

Es ist offensichtlich, dass das Befüllen eines Arrays im Code sehr mühevoll ist. Da sich Anweisungen in einer Zeile durch den Doppelpunkt trennen lassen, konnte ich hier grob die Struktur der Matrix nachbilden.

Die Funktion LösungsVektor() soll ein Gleichungssystem lösen. Das folgende Beispiel LinGlSys() zeigt, wie der Aufruf für die Funktion LösungsVektor() aussehen muss:


Public Sub LinGlSys()
    Dim A(2, 2) As Double, y(2) As Double, x As Variant
    MatrixFüllen A, y
    x = LösungsVektor(A, y)
End Sub

Zunächst werden also die Arrays erstellt und mit der Hilfsfunktion MatrixFüllen befüllt. Der Aufruf der Funktion LösungsVektor() übergibt nun die Matrix A und den Konstanten-Vektor y an die Funktion LösungsVektor:


Public Function LösungsVektor(ByRef A() As Double, ByRef y() As Double) As Variant
    Dim m() As Variant, v() As Variant
        With WorksheetFunction
        If .MDeterm(A) = 0 Then
            ' Fehler ausgeben, wenn Determinante A = 0
            LösungsVektor = CVErr(xlErrNum)
            Exit Function
        End If
       
        m = .MInverse(.MMult(.Transpose(A), A))
        v = .MMult(.Transpose(A), .Transpose(y))
        LösungsVektor = .Transpose(.MMult(m, v)) ' Lösung
    End With
End Function

Quiz für Codeversteher:

Das folgende Beispiel bietet mit Hilfe der Tabellenfunktion RGP (im VBA-Code .LinEst) einen alternativen Berechnungsweg. RGP löst ein überbestimmtes Gleichungssystem (mehr Gleichungen als Variable) nach der Gaußschen Fehlerquadratmethode, d. h. wenn die Anzahl der Gleichungen größer ist, als die Anzahl der Variablen, dann sind die Lösungen (x) Schätzwerte, die zwar keine Gleichung mehr exakt erfüllen werden, aber in der Summe immer noch den besten Kompromiss mit den kleinsten Abweichungen darstellt. Wenn man RGP nur so viele Gleichungen gibt, wie es Variable berechnen soll, dann rechnet RGP exakte Lösungen aus, wie das zuvor vorgestellte Programm.


Public Function LösungsVektor2(ByRef A() As Double, ByRef y() As Double) As Variant
    Dim m As Variant, v As Variant
   
    ' Lösung des Gleichungssystems
    With WorksheetFunction
        If .MDeterm(.MMult(A, A)) = 0 Then
            ' Fehler ausgeben, wenn Determinante A = 0
            LösungsVektor2 = CVErr(xlErrNum)
            Exit Function
        End If
       
        LösungsVektor2 = .LinEst(.Transpose(y), A, False, False' Lösung
    End With
End Function

Die Funktion RGP (LinEst) hat einige Merkwürdigkeiten: Das in x() zurückgegebene Array enthält ein Element mehr, als die vorherige Lösung. Das kann ich zwar erklären, aber ich habe über die Gaußsche Fehlerquadratmethode in Excel ein ganzes Buch geschrieben, deshalb glauben Sie mir bitte, dass ich die Gründe kenne, aber das Ganze so umfangreich ist, dass ich es hier nicht erklären möchte.

Also: RGP sich zur Lösung von normalen als auch von überbestimmten Gleichungssystemen verwenden. RGP liefert ein Array zurück, welches mit dem Index 1 beginnt und ein Element mehr hat, als y. Diesen den letzten Wert, also x(4) können Sie ignorieren (weil 0), wenn Sie bei LinEst() zweimal False als Parameter angeben. Die Reihenfolge der Lösungen wird umgedreht, sie finden x1 in x(3), x2 in x(2) und x3 in x(1).

Quiz für Codeversteher: Warum wird in diesem Beispiel If .MDeterm(.MMult(.Transpose(A), A)) = 0 geprüft und nicht nur If .MDeterm(A) = 0? Weil dieses Programm rechteckige Matrizen für A zulässt, aber MDeterm nur quadratische Matrizen berechnen kann. Das Matrixprodukt AT·A, also .MMult(.Transpose(A), A), ist immer quadratisch und erlaubt MDeterm die Bestimmung, ob die Rechnung aufgeht oder A singulär ist.

Die folgende Tabelle zeigt einen Ausschnitt der Tabellenfunktionen, die sich auf Arrays anwenden lassen (es gibt mehr). Im Prinzip sind es Funktionen, die mit einem rechteckigen Bereich auf dem Tabellenblatt arbeiten. Der rechteckige Bereich wird dann in VBA durch die Variable mit dem ein- oder zweidimensionale Array ersetzt.

Tabellenfunktion

VBA WorksheetFunction

Bedeutung

MTrans

Transpose

Transponieren einer rechteckigen Matrix, beim Ergebnis vertauschen sich Zeilenzahl und Spaltenzahl

MMult

MMult

Multiplizieren zweier rechteckiger Matrizen, wenn die Spaltenzahl der ersten Matrix gleich der Zeilenzahl der zweiten Matrix ist. Das Ergebnis hat die Zeilenzahl der ersten Matrix und die Spaltenzahl der zweiten Matrix.

MDet

MDeterm

Determinante einer quadratischen Matrix, das Ergebnis ist eine Zahl und keine Matrix.

MInv

MInverse

Inverse einer Matrix. Kann nicht berechnet werden, wenn die Determinante 0 ist.

RGP

LinEst

Linearer Trend berechnet nach der Gaußschen Fehlerquadratmethode

SVERWEIS

VLookup(x, M, s, False)

In einem zweidimensionalen Array (Matrix M) sucht VLookup in der ersten Spalte nach einem Wert x; Falls der Wert in Zeile z gefunden wird, liefert es aus dieser Zeile den Wert in Spalte s. Wenn False gewählt wurde, und kein passender Wert gefunden wird, führt die Funktion zu einem auffangbaren Fehler.

ANZAHL

Count

Count zählt nur Zahlen (siehe Beschreibung zur Tabellenfunktion). In Arrays vom numerischen Typ gibt Count immer die Anzahl der Elemente zurück, bei Variant-Typen kommt es auf den Inhalt an.

ANZAHL2

CountA

CountA zählt Zellen mit Inhalt (siehe Beschreibung zur Tabellenfunktion); Allerdings ist die Definition von Inhalt etwas seltsam. Laut Hilfe ist "" (entspricht vbNullString) nicht leer, andererseits ergibt der Vergleich Empty = vbNullstring in VBA True.

MIN, MAX, MITTELWERT, SUMME, …

Min, Max, Average, Sum, …

Kleinster Wert, größter Wert, Mittelwert, Summe

Arrays als Rückgabewert für Tabellenfunktionen

In Excel lassen sich für Berechnungen mit Zellformeln in Tabellen eigene Funktionen in VBA schreiben. Diese werden benutzerdefinierten Tabellenfunktionen genannt.

Benutzerdefinierte Tabellenfunktionen sind eine Untermenge der VBA-Funktionen. Diese benutzer­definierten Funktionen können bestimmte Operationen nicht ausführen können, z. B. Ändern von Tabellennamen, Zellfarben und ähnlichem, also den Dingen, die eine eingebaute Tabellenfunktion auch nicht tun würde.

Ganz allgemein sollte man bei benutzerdefinierten Tabellenfunktionen die Zeile


    Application.Volatile True

einbauen. Dadurch wird die benutzerdefinierte Funktion regelmäßig neu berechnet. Lässt man diese Zeile weg, dann erfolgt die Neuberechnung nur noch ausnahmsweise, was eigentlich nur bei langsamen Funktionen Sinn macht.

Eine solche benutzerdefinierte Tabellenfunktion kann mehrere Werte zurückgeben, die auf mehreren Zellen in der Tabelle verteilt werden. Dieses Verhalten ähnelt den Matrixfunktionen. Wenn eine benutzerdefinierte Tabellenfunktion ihr Ergebnis in mehrere Zellen schreiben soll, dann muss sie wie eine Matrixfunktion eingegeben werden, d. h.

Fangen wir mit dem ersten Beispiel an und verwenden die Array()-Funktion gleich auch noch, um ganz einfach andere Funktionen aufrufen und deren Rückgabewerte sammeln. Die folgende benutzerdefinierte Funktion berechnet von einem Bereich die Ergebnisse der Tabellenfunktionen Min, Mittelwert und Max.


Public Function KennWerte1(Bereich As Excel.Range) As Variant
    Application.Volatile True
    With WorksheetFunction
        KennWerte1 = Array(.Min(Bereich), .Average(Bereich), .Max(Bereich))
    End With
End Function

Anwendungsbeispiel: Nehmen wir an, der Bereich A1:C3 enthält verschiedene Zahlen. Weil die Funktion KennWerte1() drei Zahlen zurückliefert und die Array-Funktion einen Zeilenvektor liefert, markieren wir die drei Zellen A5:C5, geben "=KennWerte1(A1:C3)" ein und schließen die Formel mit dem gleichzeitigen Drücken von [Umschalt]+[Strg]+[Eingabe] ab. Nun stehen die Werte in aufsteigender Reihenfolge in A5, B5 und C5.

Die Rückgabe kann auch als Spaltenvektor erfolgen, d. h. In der Tabelle stehen die Ergebnisse in drei Zellen untereinander. Der einfachste Weg dazu ist die Transpose-Funktion:


Public Function KennWerte2(Bereich As Excel.Range) As Variant
    Application.Volatile True
    With WorksheetFunction
        KennWerte2 = .Transpose(Array(.Min(Bereich), _
            .Average(Bereich), .Max(Bereich)))
    End With
End Function

Die Funktion KennWerte2() liefert einen Spaltenvektor, markieren wir die drei Zellen A6:A8, geben "=KennWerte2(A1:C3)" ein und schließen die Formel mit dem gleichzeitigen Drücken von [Umschalt]+[Strg]+[Eingabe] ab. Nun stehen die Werte in aufsteigender Reihenfolge in A6, A7 und A8.

Die benutzerdefinierte Funktion KennWerte1() kann natürlich auch anders erstellt werden:


Public Function KennWerte1(Bereich As Excel.Range) As Variant
    Dim Ergebnis(2) As Double
    Application.Volatile True
    Ergebnis(0) = WorksheetFunction.Min(Bereich)
    Ergebnis(1) = WorksheetFunction.Average(Bereich)
    Ergebnis(2) = WorksheetFunction.Max(Bereich)
    KennWerte1 = Ergebnis
End Function

Der Aufbau der Dimensionen von Ergebnis() gibt die Art des Ergebnisvektors vor. Hier ist es ein Zeilenvektor.

Die folgende Funktion berechnet aus dem Realteil und dem Imaginärteil einer komplexen Zahl deren komplexes Quadrat:


Public Function cQuadrat(Re As Double, Im As Double) As Variant
    Application.Volatile True
    cQuadrat = Array(Array((Re - Im) * (Re + Im)), Array(2 * Re * Im))
End Function

Die zeigt einen weiteren Weg, einen Spaltenvektor zu erzeugen. Durch die Verschachtelung der Arrays erfolgt die Ausgabe als Spaltenvektor. Der obere Wert ist der Realteil, der untere der Imaginärteil des komplexen Quadrats.

Transfer zwischen Zellen und Arrays

Manchmal lassen sich aufwendige Operationen wie Sortieren, Berechnen oder Vertauschen mit Zellen in einem Tabellenblatt besser durchführen, als in einem Array. Die folgenden Beispiele zeigen, wie man Daten von Arrays in eine Tabelle und umgekehrt übertragen kann.

Die Übernahme eines Bereiches auf einem Tabellenblatt in ein Array ist einfach. Der folgende Code übernimmt eine Auswahl (Selection in VBA) in eine Matrix:


Public Sub BreichInMatrix()
    Dim Matrix() As Variant
    Matrix = Selection
    ...
End Sub

Das Selection-Objekt wird einfach der Matrix zugewiesen, alternativ könnte hier auch Worksheet("Tabelle1").Range("A1:C3") stehen. Das Array erbt seine Dimensionen vom Zellbereich, daher muss nicht dimensioniert werden. Die Elemente des Arrays enthalten das, was die Value-Eigenschaft des Range-Objektes ausgibt, d. h. bei Zellformeln wird das Ergebnis, nicht die Formel ins Array übernommen.

Praktisch gesehen, ist ein Zellbereich in einem Tabellenblatt ein Array aus lauter Variant-Werten und jede Zelle wird zu einem Element vom Typ Variant in einer Matrix. Es ist daher nur möglich, Zellinhalte in ein Variant-Array zu übernehmen, nicht jedoch in ein typisiertes Array.


Quiz für Codeversteher:

Der Rückweg vom Array in die Tabelle ist ebenfalls recht einfach:


Public Sub MatrixInBereich(Matrix() As Variant, ZielZelleOL As Range)
    Dim b As Long, h As Long
    h = UBound(Matrix, 1) - LBound(Matrix, 1)
    b = UBound(Matrix, 2) - LBound(Matrix, 2)
   
    Range(ZielZelleOL, ZielZelleOL.Offset(h, b)).Value = Matrix
End Sub

Die Funktion verlangt zum einen das Array und zum anderen eine Zelle, die als linke, obere Ecke des Ausgabebereichs auf dem Tabellenblatt dient.

Hier muss die Größe des Ausgabebereichs auf dem Tabellenblatt angegeben werden, damit alle Werte aus dem Array in die Tabelle übernommen werden. Ist der angegebene Zielbereich in der Tabelle größer als die Elemente im Array, wird die Tabelle mit dem Fehlerwert #NV aufgefüllt.

Hinweis für Codeversteher: Bei der Berechnung der Breite b und der Höhe h des Ausgabebereichs sind die Werte um eins zu klein. Das gleicht hier die OffSet-Funktion aus.