array formel in vba

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)

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

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

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

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)

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

Beliebteste Forumthreads (12 Monate)

17
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

320 Forumthreads zu ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

array formel in vba

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

320 Forumthreads zu ähnlichen Themen

VBA Beispiele zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

17
Beiträge zum Forumthread

Beiträge zu diesem Forumthread