Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1816to1820
1816to1820
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

array formel in vba

Forumthread: array formel in vba

array formel in vba
09.03.2021 15:39:05
Fred
Hallo Excel Profis,
Ich habe da ein "Problem" mit einer Matrixformel. Lasse ich gewisse Makros laufen, dauert die Abarbeitung unglaublich lang an. Das ist erst so, seit ich eine Matrixformel (von Lupo1) nutzen möchte.
Mein Gedanke ging nun da hin, dass ich mit einem Makro diese gewisse Formel
1.) eintrage
2.) entsprechend runterkopiere
3.) und dann die Formeln in entsprechende Werte umwandele.
dachte, das geht mit folgenden Makro: 
  With ActiveSheet
ENDE1 = Sheets("Basis").Cells(Rows.Count, 1).End(xlUp).Row - 0
Worksheets("Basis").Range("R2").FormulaArray = "=SUMME(WENNFEHLER(--(TEIL(WECHSELN(GLÄTTEN(""- _
11 ""&WECHSELN(N2;""+"";"" +""));"" "";WIEDERHOLEN("" "";199));SPALTE(B:AF)*199-198;199)-""TEIL(WECHSELN(GLÄTTEN(""-11 ""&WECHSELN(N2;""+"";"" +""));"" "";WIEDERHOLEN("" "";199));SPALTE(A:AE)*199-198;199)

"FormulaArray" ist doch in diesen Fall richtig!?
Kann mir bitte jemand helfen.
Gruss
Fred

Anzeige

17
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: array formel in vba
09.03.2021 16:05:40
onur
1) Formula.Array versteht nur ENGLISCHE Formeln.
2) Microsoft meint dazu:
Range.FormulaArray-Eigenschaft (Excel)
...
Gibt eine Arrayformel für einen Bereich zurück oder legt sie an. Gibt eine einzelne Formel oder einen Visual Basic-Array zurück (oder kann als solche(r) festgelegt werden). Wenn der angegebene Bereich keine Arrayformel enthält, gibt diese Eigenschaft nullzurück. Variant mit Lese-/Schreibzugriff.
Syntax
Ausdruck. FormulaArray
Ausdruck Eine Variable, die ein Range-Objekt darstellt.
Bemerkungen
Bei der FormulaArray-Eigenschaft sind maximal 255 Zeichen erlaubt.

Anzeige
AW: array formel in vba
09.03.2021 16:36:47
Fred
Hallo Onur,
ich muß also die Matrixformel (die ich nicht verstehe) in R1C1 umschreiben ?
Gruss
Fred

AW: array formel in vba
09.03.2021 16:42:01
onur
Englisch oder Deutsch ist etwas anderes wie A1 oder Z1S1.
Scgreibe mal in A1 eine Arrayformel und im Vba_editor-Direktfenster diese hier:
? Range("A1").FormulaArray.
Dann siehst du erstens, wie die Formel aussehen muss und zweitens, dass alle Befehle auf englisch sind.

Anzeige
AW: array formel in vba
09.03.2021 16:05:59
Daniel
HI
ist prinzipell schon richtig und machbar.
allerdings macht die länge der Formel an mehreren stellen probleme.
1. sie ist wahrscheinlich für den Recorder zu lang.
das kannst du damit beheben, dass du die Formel nicht direkt als ganzes angibst, sondern erst mal in eine Variable schreibst und das in mehreren Schritten:
also nicht 
.FormulaArray = "=ganz lange Formel"

sondern 
FO = "=erster Teil"
FO = FO & "zweiter Teil"
FO = FO & "dritter Teil"
.FormulaArray = FO
also die Formel so in abschnitte geteilt, dass es für dich übersichtlich ist und der Editor es verarbeiten kann.
2. Das größere Problem dürfte sein, dass die Formellänge, die .FormulaArray verarbeiten kann, stark limitiert ist, also wesentlich kürzer als du Formeln selber in Excel eingeben kannst. (ich meine 256 Zeichen)
Für normale Formlen gilt das auch, allerdings mit mehr Zeichen und hier gibt es einen Workaround.
Bei ArrayFormeln funktioniert der nicht und ich kenne keinen anderen Workaround.
wenn du sowieso mit Makros arbeitest, dann könntest du auch ggf die ganze Rechung in VBA machen. bei so text-Sachen hast du da auch mehr möglichkeiten das zu programmieren als mit Formeln.
vielleicht reicht es ja aus um die Sache zu beschleunigen, wenn du in der Formel bei allen Spalte(...)-Funktionen nicht die ganze Spalte als Zellbereich angibst (Spalte(B:AF)) sondern nur eine Zeile: Spalte(B$1:AF$1)
eine Ursache für die lange Rechenzeit könnte sein, die Formeln unnötig oft berechnet werden.
Eine Formel wird immer dann neu berechnet, wenn sich in dem Zellbereich, den sie verwenden, ein Wert ändert. Durch so angaben wie B:AF wird dieser Bereich allerdings sehr groß, was aber nicht notwendig ist, da die Inhalte aus diesem Bereich keinen Einfluss auf das Ergebnis haben.
Daher sollte man für die Spalte- und Zeile-Funktion die Zellbereiche so klein wie möglich wählen und möglichst auf Bereiche legen, die sich nicht ändern (überschriftenzeile).
Bei Abarbeitung von Makros kann es aber auch oft ausreichen, die automatische Neuberechnung von Formeln zu deaktivieren (Application.Calculation = xlcalculationmanual)
dann werden die Formeln nicht neu berechnet und das Makro läuft schneller.
hinter im Makro das einschalten nicht vergessen, denn diese Einstellung bleibt bei Makroende erhalten.
Gruß Daniel

Anzeige
AW: array formel in vba
09.03.2021 16:34:34
Yal
Hallo Fred,
es geht un dein Tor-Problem in
https://www.herber.de/forum/archiv/1816to1820/t1818527.htm#1818527
Ich wurde einfach die Eingabe mit einem UDF (User Defined Function) splitten:

Public Function InSpalten_aufteilen(Target) As String()
Dim A
Dim i
A = Split(Target.Text, " ")
For i = 0 To UBound(A)
A(i) = CStr(Application.Evaluate(A(i)))
Next
InSpalten_aufteilen = A
End Function
dann diese Function in der Blatt als Matrix-Formel verwenden:
Parameter aus der Spalte A,
Ergebnisse in die Spalte B bis ...
Spricht (ab Zeile 2):
Bereich B2:M2 (12 Spalten) markieren,
=InSpalten_aufteilen(A2) als Formel eingeben
mit Shift+Strg+Enter abschliessen
Der Trick: aus 45+2 wird durch "Evaluate" zu 47
Ab der Spalte O2 und 12 Spalten nach rechts
~f~=WENNFEHLER(((B2-C2) ~f~
Am Ende die Summe darauf.
VG
Yal

Anzeige
AW: array formel in vba
09.03.2021 16:45:04
Yal
Hallo Fred,
Mein Vorschlag war eigentlich doof: es geht besser, wenn man es direkt berechnet.
(dieses Code muss in einem Modul liegen, nicht im Code-Pane eines Worksheets) 
Public Function AnzahlTor(Target, Abstand) As Long
Dim A
Dim i
Dim Mem
Dim S
A = Split(Target.Text, " ")
For i = 0 To UBound(A)
A(i) = Application.Evaluate(A(i))
If Mem  0 Then S = S - ((A(i) - Mem)
Dann fügst Du in B2
=AnzahlTor(A5;10)
Wenn es nicht funktioniert, gehe über den Funktion-Assistent im Bereich "Benutzerdefiniert".
VG
Yal

Anzeige
AW: array formel in vba
09.03.2021 16:46:14
Fred
Hallo Yal, Onur und Daniel,
ich weis schon gar nicht mehr wo links und rechts ist ... einfach zuviel Programmiersprache ...
Vieleicht sollte ich wieder zum Ursprung gehen;
Die Suche nach einer Formel;
https://www.herber.de/bbs/user/144584.xlsx
Vieleicht gibt es doch eine "freundlichere Formel" als die Matrixformel von Lupo?
Gruss
Fred

Anzeige
AW: array formel in vba
09.03.2021 16:51:48
onur
Mehrere Werte in eine EINZIGE Zelle zu quetschen, ist ein "Liebestöter" für jede Formel, es verkompliziert alles ungemein.
Wieso nicht nebeneinander ?

AW: array formel in vba
09.03.2021 17:12:31
Fred
Hallo Onur,
du hast mir vor ca. 2 Jahren ein Makro geschrieben, welches die Trefferzeiten auseinander legt.
Hier sind die Zeiten in einzelnen Tellen abgelegt. Ich bräuchte allerdings die Lösung (wie oft innerhalb von 10min. getroffen wird) als "normale Formel",- damit ich diese auf Erweiterungen der Tabelle selbst anpassen kann.
Hier mal die Datei
https://www.herber.de/bbs/user/144602.xlsb
Gruss
Fred

Anzeige
AW: array formel in vba
09.03.2021 17:35:42
Fred
Hallo Yal und Onur,
den ganzen Tag mache ich an dieser "einen Sache" rum und gehe eher 2 Schritte zurück als nur einen vorwärts. Studiere was "wechseln, wiederholen ..." im Zusammenhang bedeutet undundund ...
Und dann die zwei Funktonen auf einen Schlag
von Yal 
Public Function AnzahlTor(Target, HZ, Optional Abstand = 10) As Long
Dim A
Dim i
Dim Mem As Long
Dim S As Long
Dim Z As Long
'On Error Resume Next
A = Split(Target.Text, " ")
For i = 0 To UBound(A)
If A(i) = "" Then A(i) = 0
Z = CLng(Split(A(i), "+")(0))
A(i) = Application.Evaluate(A(i))
If (Z  45 And HZ = 2) Then
If Mem  0 Then S = S - ((A(i) - Mem)
von Onur 
Public Function Tore(tor As String, hz As Integer, dt As Integer) As Integer
Application.Volatile
Dim mi, mx, arr, i, t1, t2
mi = 0: mx = 45
If hz = 2 Then
mi = 46: mx = 90
End If
arr = Split(tor, " ")
For i = 0 To UBound(arr) - 1
If InStr(arr(i), "+") Then arr(i) = 999
t1 = Val(arr(i)): t2 = Val(arr(i + 1))
If t1 >= mi And t1 = mi And t2
Vielen VIELEN DANK für eure kompetente Arbeit!!
Gruss
Fred

Anzeige
Vielen Dank für die Rückmeldung.
10.03.2021 11:17:19
Yal
Hallo Fred,
jetzt noch die Frage, die dein VBA-Kompetenz erhöhen wird:
Wo sind die Utnerschied zwischen die beiden Versionen? (sind ja sehr ähnlich)
Bei dem
If InStr(arr(i), "+") Then arr(i) = 999
von Onur könnte es dazu führen manche Tore in der Verlängerung ignoriert werden.
Das muss Du in ausführlich testen.
VG
Yal

Anzeige
UDF mit "kleinen Fehler"
13.03.2021 13:31:11
Fred
Hallo Excel,- VBA Profis,
ich habe vor geraumer Zeit ein UDF (Nutzer definierte Funktion) von Onur bekommen.Im Ergebnis soll die Funktion ermitteln, wie oft ein "Folgetreffer" innerhalb von einer angegebenen Zeit und angegebener Halbzeit, gefallen ist.
Hier die Funktion: 
Public Function Tore(tor As String, hz As Integer, dt As Integer) As Integer
Application.Volatile
Dim mi, mx, arr, i, t1, t2
mi = 0: mx = 45
If hz = 2 Then
mi = 46: mx = 90
End If
arr = Split(tor, " ")
For i = 0 To UBound(arr) - 1
If InStr(arr(i), "+") Then arr(i) = 999
t1 = Val(arr(i)): t2 = Val(arr(i + 1))
If t1 >= mi And t1 = mi And t2
Ich mußte nun feststellen, dass die ermittelte Anzahl "nur" für die 2. Hz korrekt sind.
Die zu ermittelnde Anzahl in der 1. Halbzeit ist teilweise nicht korrekt.
Die "Trefferzeiten" stehen alle in EINER Zelle und zu Beginn steht ein Leerzeichen. Ich habe festgestellt, dass wenn ich dieses Leerzeichen lösche, die Anzahl von der Funktion richtig wiedergegeben wird.
Meine Frage;
Kann mal bitte jemand auf die Funktion schauen und evt. dahin ergänzen, dass dieses "erste Leezeichen" bei der Berechnung entsprechend berücksichtigt wird?
Vieleicht alles ein bischen wirre geschrieben, daher eine Beispielmappe mit dieser Funktion;
https://www.herber.de/bbs/user/144730.xlsb
Gruss
Fred

Anzeige
AW: UDF mit "kleinen Fehler"
13.03.2021 15:31:22
onur
Mach aus 
arr = Split(tor, " ")

das 
arr = Split(LTrim(tor), " ")


Onur: Perfekt!
13.03.2021 15:35:07
Fred
vielen Dank Onur!
Gruss
Fred

UDF mit "kleinen Fehler"
13.03.2021 13:31:31
Fred
Hallo Excel,- VBA Profis,
ich habe vor geraumer Zeit ein UDF (Nutzer definierte Funktion) von Onur bekommen.Im Ergebnis soll die Funktion ermitteln, wie oft ein "Folgetreffer" innerhalb von einer angegebenen Zeit und angegebener Halbzeit, gefallen ist.
Hier die Funktion: 
Public Function Tore(tor As String, hz As Integer, dt As Integer) As Integer
Application.Volatile
Dim mi, mx, arr, i, t1, t2
mi = 0: mx = 45
If hz = 2 Then
mi = 46: mx = 90
End If
arr = Split(tor, " ")
For i = 0 To UBound(arr) - 1
If InStr(arr(i), "+") Then arr(i) = 999
t1 = Val(arr(i)): t2 = Val(arr(i + 1))
If t1 >= mi And t1 = mi And t2
Ich mußte nun feststellen, dass die ermittelte Anzahl "nur" für die 2. Hz korrekt sind.
Die zu ermittelnde Anzahl in der 1. Halbzeit ist teilweise nicht korrekt.
Die "Trefferzeiten" stehen alle in EINER Zelle und zu Beginn steht ein Leerzeichen. Ich habe festgestellt, dass wenn ich dieses Leerzeichen lösche, die Anzahl von der Funktion richtig wiedergegeben wird.
Meine Frage;
Kann mal bitte jemand auf die Funktion schauen und evt. dahin ergänzen, dass dieses "erste Leezeichen" bei der Berechnung entsprechend berücksichtigt wird?
Vieleicht alles ein bischen wirre geschrieben, daher eine Beispielmappe mit dieser Funktion;
https://www.herber.de/bbs/user/144730.xlsb
Gruss
Fred
Anzeige
;
Anzeige
Anzeige

Infobox / Tutorial

Excel-Matrixformeln in VBA effektiv nutzen

Schritt-für-Schritt-Anleitung

  1. Matrixformel in VBA eingeben: Um eine Array-Formel in VBA zu verwenden, musst du die FormulaArray-Eigenschaft nutzen. Beispiel:

    Worksheets("Basis").Range("R2").FormulaArray = "=SUMME(WENNFEHLER(...))"

    Achte darauf, dass die Formel in englischer Sprache geschrieben ist, da FormulaArray nur englische Formeln akzeptiert.

  2. Formel in Variable speichern: Wenn die Formel zu lang ist, speichere sie in einer Variable und füge die Teile zusammen:

    Dim FO As String
FO = "=erster Teil"
FO = FO & " zweiter Teil"
FO = FO & " dritter Teil"
Worksheets("Basis").Range("R2").FormulaArray = FO

  3. Automatische Neuberechnung deaktivieren: Um die Performance zu verbessern, kannst du die automatische Neuberechnung deaktivieren:

    Application.Calculation = xlCalculationManual

    Vergiss nicht, die Neuberechnung am Ende wieder zu aktivieren:

    Application.Calculation = xlCalculationAutomatic

Häufige Fehler und Lösungen

  • Fehler: Formel zu lang: Wenn du mehr als 255 Zeichen verwendest, wird die Formel nicht akzeptiert. Teile die Formel in mehrere Abschnitte auf, wie im vorherigen Schritt beschrieben.

  • Fehler: Falsche Sprache: Stelle sicher, dass alle Funktionen in der Formel auf Englisch sind. Zum Beispiel SUM statt SUMME oder IFERROR statt WENNFEHLER.

  • Leere Zellen: Wenn deine Eingabewerte leere Zellen enthalten, kann dies zu Fehlern führen. Verwende LTrim oder Trim, um führende Leerzeichen zu entfernen:

    arr = Split(LTrim(Target.Text), " ")

Alternative Methoden

  • User Defined Functions (UDF): Wenn du komplexe Berechnungen hast, die sich nicht leicht in einer Array-Formel darstellen lassen, kannst du eine UDF schreiben. Beispiel:

    Public Function AnzahlTor(Target As String) As Long
   ' Deine Berechnungslogik hier
End Function

  • Direkte Berechnung in VBA: Du kannst auch die gesamte Berechnung in VBA durchführen, ohne eine Array-Formel zu verwenden. Das gibt dir mehr Flexibilität.

Praktische Beispiele

  1. Eingabe einer Array-Formel:

    Worksheets("Daten").Range("B2:B10").FormulaArray = "=SUM(IF(A2:A10=1, C2:C10))"

  2. Berechnung mit einer UDF:

    Dim result As Long
result = AnzahlTor("1 2 3 4", 1)

  3. Verwendung von Evaluate:

    Dim value As Variant
value = Application.Evaluate("SUM(A1:A10)")

Tipps für Profis

  • Nutze die Evaluate-Funktion, um dynamische Formeln direkt in VBA auszuwerten.
  • Halte deine Formeln so einfach wie möglich, um die Wartbarkeit zu erhöhen.
  • Teste deine Formeln regelmäßig, um sicherzustellen, dass sie wie erwartet funktionieren.

FAQ: Häufige Fragen

1. Wie kann ich eine Array-Formel in einer bestimmten Zelle eingeben? Du kannst die FormulaArray-Eigenschaft verwenden, um eine Array-Formel in einer bestimmten Zelle einzugeben, wie im Schritt-für-Schritt-Anleitung beschrieben.

2. Warum funktioniert meine Formel nicht? Stelle sicher, dass deine Formel in englischer Sprache geschrieben ist und die maximale Zeichenanzahl von 255 nicht überschreitet.

3. Was ist der Unterschied zwischen Formula und FormulaArray? Formula wird für normale Formeln verwendet, während FormulaArray speziell für Array-Formeln gedacht ist, die mehrere Werte gleichzeitig verarbeiten können.

4. Kann ich eine Array-Formel in VBA bearbeiten? Ja, du kannst eine Array-Formel in VBA bearbeiten, indem du die FormulaArray-Eigenschaft benutzt und die Formel entsprechend anpasst.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige