Speziell in Excel lassen sich in VBA viele Tabellenfunktionen auf Arrays anwenden, allerdings muss man mehrere Punkte beachten:
Die Excel-Tabellenfunktionen bearbeiten nur ein- und zweidimensionale Arrays, denn das entspricht einer Auswahl auf einem Tabellenblatt. Mehr Dimensionen führen zu einem Laufzeitfehler!
Excel-Tabellenfunktionen sind Methoden des WorksheetFunction-Objektes, d. h. jede Nutzung einer Tabellenfunktion beginnt mit dem Objektbezeichner WorksheetFunction. Man kann diesen Objektbezeichner abkürzen, indem beispielsweise eine Variable wsf definiert wird:
Dim wsf As Excel.WorksheetFunction
Set wsf = Excel.WorksheetFunction
Die Excel-Tabellenfunktionen haben in VBA üblicherweise einen anderen Namen als in der Tabelle. Auskunft gibt die Tabelle VBALISTE.XLS, die im Installationsordner von Office liegt (Ordner 1031 bei deutschsprachigen Installationen).
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.
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:
Warum kann der Rückgabewert einer Matrixfunktion nur ein Variant sein?
Matrixfunktionen sind für die Tabelle gemacht und Tabellenzellen sind Variant-Typen. Wie man im Beispiel sieht, wurden die Variablen m, v und x als Variant dimensioniert, weil Matrixfunktionen ihre Ergebnisse nur als Array-Struktur an einen Variant übergeben können.
Was bedeutet die If .MDeterm(A) = 0 Abfrage?
Hier handelt es sich um ein mathematisches Problem. Wenn die Determinante 0 ist, dann wird sie als singulär bezeichnet und für das Gleichungssystem lässt sich keine eindeutige Lösung berechnen. Einfaches geometrisches Beispiel: Ein 2×2 Gleichungssystem wird für zwei Geraden in der Ebene aufgestellt, um den Schnittpunkt zu bestimmen. Wenn die Geraden parallel sind, gibt es keinen Schnittpunkt, was die Determinante mit einer 0 ausdrückt.
Warum muss Dim m() As Variant und nicht Dim m As Variant deklariert werden?
Es muss nicht so dimensioniert werden. Beide Arten der Deklaration sind für m, v und x zulässig.
Warum muss auf y die Funktion .Transpose(y) angewendet werden?
Wenn wir die Variable y mit nur einem Index, z. B. Dim y(2) As Double erstellen, dann ist das in mathematischem Sinne ein Zeilenvektor. Für eine mathematisch korrekte Berechnung mit den Matrixfunktionen brauchen wir aber einen Spaltenvektor. Das Problem können wir so lösen:
Wir legen y mit Dim y(2,0) As Double an, das bedeutet aber, dass wir bei jedem Zugriff auf y mit zwei Indizes zugreifen müssten, also y(0,0), y(1,0) und y(2,0).
Alternativ bauen wir in die Berechnung ein .Transpose ein (Tabellenfunktion MTRANS). Die verwandelt den Zeilenvektor y(2) in den Spaltenvektor, und genau das habe ich gemacht.
Wenn y mehrere Spalten hat (dies kommt bei einigen seltenen mathematischen Problemen vor), dann sollte es gleich mit zwei Indizes angelegt werden.
Welchen Zweck hat die With-Anweisung?
Sie verkürzt die folgenden Zeilen, sodass der langatmige Objektverweis WorksheetFunction vor jeder Matixfunktion vermieden wird. Die Zerlegung der Berechnung mit den Variablen m und v dienen nur der Übersichtlichkeit, die ganze Berechnung kann in der Zeile Lösungsvektor = … zusammengefasst werden.
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 |
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 benutzerdefinierten 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.
Zunächst markieren Sie mehrere Zellen, in die die Ausgabe erfolgen soll
Sie geben die Formel ganz normal ein, ohne { geschweifte Klammern }
Schließen die Eingabe der Funktion mit dem gleichzeitigen klicken auf [Umschalt]+[Strg]+[Eingabe] und nicht nur mit [Eingabe], wie Sie das sonst tun würden.
Sie sehen, dass die Formel in jeder markierten Zellen steht und sie ist mit { } umschlossen.
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.
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:
Wann wird der Zugriff mit dem Selection-Objekt einen Laufzeitfehler auslösen?
Wenn die Auswahl Diagramme oder Zeichnungsobjekte enthält.
Was passiert, wenn oder die Auswahl aus mehreren Zell-Bereichen (Area-Objekten) zusammengesetzt ist?
Dann wird nur der erste markierte Teilbereich in die Matrix übernommen.
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.
In allen Office-Anwendungen kann man mit VBA Listenfelder und ComboBoxen in Formulare einbauen. Die Befüllung im Code mit aktuellen Daten mit der AddItem-Methode und der Column-Eigenschaft (bei mehreren Spalten) ist oft etwas mühselig und macht den Code sehr füllig. Dabei erlaubt die List-Eigenschaft die Übernahme des Inhalts aus einem Array.
Leider haben Arrays,die man mit der Funktion Array() aufbauen kann, nicht die richtige Struktur. Diese Arrays müssen erst gestürzt werden, was der Mathematiker auch Transponieren nennt. Nur Excel hat eine Tabellenfunktion TRANSPONIEREN() (in VBA Worksheetfunction.Transpose), mit der man die Arrays im Code in die richtige Form bringen kann.
Das folgende Beispiel zeigt, wie man mit der der List-Eigenschaft in VBA den Inhalt des Listenfeldes oder den Inhalt des Dropdown-Menüs einer ComboBox per Code einstellen kann. Dazu werden vier Zeilen mit drei Spalten angelegt, deren Inhalte jeweils in einem Array aufgelistet werden und diese drei Arrays werden wiederum von einem äußeren Array geklammert.
Die inneren Arrays müssen natürlich die gleiche Anzahl von Elementen haben und die Anzahl der inneren Arrays entspricht der Anzahl der Spalten, die das Steuerelement anzeigt.
Im Formular bietet sich dazu das UserForm_Initialize-Ereignis an, welches beim Öffnen des Formulars zuerst ausgeführt wird (sofern vorhanden):
Public Sub UserForm_Initialize()
Me.cmbTestBox.ColumnCount = 3
Me.cmbTestBox.List() = WorksheetFunction.Transpose( _
Array( _
Array(0, 1, 2, 3), _
Array("Null", "Eins", "Zwei", "Drei"), _
Array("Zero", "One", "Two", "Three") _
))
End Sub
Das erste Array (hier mit den Zahlen 0…3) wird bei der ComboBox nach der Auswahl ins Textfeld übernommen. Über die Eigenschaft ColumnWidths lässt sich die erste Spalte verstecken, was aber nur bei der ListBox Sinn macht.
Die Eigenschaft ColumnCount wurde hier zur Erinnerung in den Code aufgenommen, aber eigentlich sollte man die Anzahl der Spalten in den Eigenschaften des Steuerelements einstellen.