Das Portal zum Thema Excel-Formeln

Diese HP ist ja eigentlich VBA-freie Zone. Zur vollständigen Dokumentation von Arrayformeln soll hier aber eine Ausnahme gemacht werden.
Wie lassen sich Arrayformeln in VBA einbinden?

Daß man eine Arrayformel mit z.B.
ActiveCell.FormulaArray = "=SUM(LEFT(R1C3:R5C3,2)*1)"
in eine Excelzelle einfügen kann, ist bekannt. Kaum geläufig ist, wie man eine Arraformel direkt im VBA-Code, ohne Excelzelle, verwenden kann.

Mit der Methode Evaluate lassen sich Arrayformeln direkt in VBA berechnen und das sogar sehr schnell.

Beispiel:

Das Ergebnis einer SUMMEWENN-Berechnung mit zwei Bedingungen soll in VBA verwendet werden. Die entsprechende Arrayformel lautet

{=SUMME((A1:A5000=1)*(B1:B5000=2)*(C1:C5000))}

Auf herkömmliche Weise würde man diese Berechnung in VBA wie folgt darstellen:

Sub SummeMitSchleife()
Dim t
Dim c As Range
Dim Summe As Double
t = Timer
For Each c In Range("a1:A5000")
If c = 1 Then
If c.Offset(0, 1) = 2 Then
Summe = Summe + c.Offset(0, 2)
End If
End If
Next
MsgBox Summe & " Timer:" & Timer - t
End Sub

Mit Evaluate lässt sich die Arrayformel direkt nach VBA übertragen:

Sub SummeMitEvaluate()
Dim t
t = Timer
MsgBox Evaluate("=SUM((A1:A5000=1)*(B1:B5000=2)*C1:C5000)") & " Timer:" & Timer - t
End Sub

Diese Methode ist sogar wesentlich schneller als der herkömmliche Weg.

Der Formelausdruck ist ein String. Wenn einzelne Parameter des Strings dynamisiert werden sollen, muß er in Teilstrings zerlegt werden.

mit
K1 = 1
K2 = 2
MsgBox Evaluate("=SUM((A1:A5000=" & K1 & ")*(B1:B5000=" & K2 & ")*C1:C5000)")
können z.B. die Suchkriterien variabel verändert werden.


Übergabe an Datenfeld:

Arrayformeln, die als Ergebnis ein Array liefern, können dieses mit Evaluate direkt an ein Datenfeld übergeben, z.B.

Sub Übergabe AnDatenfeld()
Dim feld
feld = Evaluate("transpose(A1:A10&left(b1:B10,2))")
Tabelle1.ListBox1.List = feld 'Weitergabe an ein Listenfeld
MsgBox feld(3)'Aufruf eines Elementes vom Datenfeld
End Sub

Man beachte, daß ein vertikales Array zur Weitergabe an eine Listbox transponiert werden muß.