Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1064to1068
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Makro sehr langsam > Formeln kopieren

Makro sehr langsam > Formeln kopieren
02.04.2009 19:00:18
Stefan
Hallo Herber-Freunde
ich kopiere per Makro Formeln aus der 3. Zeile in eine weiter unten liegenden Zeile.
Das Makro löst aus, wenn ich auf eine Zelle in der Spalte A gehe
Mittlerweile ist die 3.000-ste Zeile befüllt und das Makro wird "sau-langsam"
Es dauert ca 1/2 Sekunden um nur EINE Formel zu kopieren.
Was kann ich machen, dass die Ausführung beschleunigt wird ?
Würd mich sehr über einen Tip freuen:)
Besten Gruss Stefan
hier das Makro:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'formeln einfügen bei neuen werten in spalte a
Dim formel(1 To 8)
formel(1) = "i3"
formel(2) = "j3"
formel(3) = "k3"
formel(4) = "l3"
formel(5) = "h3"
formel(6) = "e3"
formel(7) = "n3"
formel(8) = "o3"
If Not Application.Intersect(Target, Range("a4:a50000")) Is Nothing Then
Application.EnableEvents = False
For x = 1 To 8
Set dest = Cells(Target.Row, Range(formel(x)).Column)
'MsgBox dest.Address
Range(formel(x)).Copy Destination:=dest
dest.Value = dest.Value
Next
Application.EnableEvents = True
End If
End Sub


15
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Tipp
02.04.2009 19:08:26
chris
TIpp:
Versuchs mal so
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
...Dein code
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
end sub
AW: Tipp
02.04.2009 19:08:26
chris
TIpp:
Versuchs mal so
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
...Dein code
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
end sub
AW: Tipp
02.04.2009 19:19:14
Stefan
Hey chris
Application.ScreenUpdating
hat geholfen :)
Application.Calculation
kann ich leider nicht nutzen - da in den Formeln auf "vorhergehende Zellen" eine Berechnung durchgeführt werden.
Danke und Gruss Stefan
Anzeige
Auweia ... is mein Browser langsam (oder ich) ...
02.04.2009 19:20:04
Matthias
Hallo
Sorry. hatte nicht aktualisiert
Gruß Matthias
Calculate auf Automatik ? stell auf manuell um
02.04.2009 19:15:27
Matthias
Hallo
Bin nicht sicher, aber vieleicht hlft es ja die automatische Berechnung auszuschalten.
Übrigens:
Das ist das nur halbherzig deklariert.
Warum ist "X" und "dest"ohne Dim-Anweisung ?
oder stehen die Variablen als Public deklariert in einem Modul ?
Gruß Matthias
AW: Calculate auf Automatik ? stell auf manuell um
02.04.2009 19:23:32
Stefan
Hey Matthias
automatische Berechnung muss ich eingeschaltet lassen, da die Formeln auf "vorhergehende Zellen" zugreifen.
Die anderen Hinweise verstehe ich leider nicht - mangels vba-kenntnisse
Wenn es da etwas zu optimieren gibt : herzlich gerne einen Tip :)
Das Makro steht genau so in
VBAProjekt # Microsoft Excel Objekt # Tabelle2 (...)
Gruss Stefan
Anzeige
Dim Anweisungen sind Fehlerunanfälliger
02.04.2009 19:37:56
Matthias
Hallo Stefan
Du hast ja z.B. formel deklariert
mit Dim formel(1 To 8).
Generell sollte man aber alle Variablen deklarieren.
1. um mind. soviel Speicher zu reservieren wie benötigt wird
2. um Fehler schneller aufzuspühren.
wenn Du im VBA Projekt Extras-Optionen
das Häkchen setzt, wird der Debugger immer meckern, wenn Du eine Varible nicht deklariert hast.
BeispielBild aus XL2000 sollte bei Dir ähnlich sein.
Userbild
Gruß Matthias
Anzeige
AW: Dim Anweisungen sind Fehlerunanfälliger
02.04.2009 20:52:47
Stefan
Hallo Matthias
werd ich mir zu Herzen nehmen
ab jetzt nur noch mit "Häckchen" *grins*
Besten Dank
Stefan
AW: Dim Anweisungen sind Fehlerunanfälliger
02.04.2009 21:33:54
Nepumuk
Hallo Stefan,
und wenn du schon in dem Dialog drin bist, dann nimm das Häkchen bei "Automatische Syntaxüberprüfung" raus. Das nervt nur, wenn du aus der Zeile nicht mehr raus kommst weil dauernd eine Msgbox hochkommt. Bei einem Syntaxfehler wird die Zeile rot, das reicht als Hinweis. Ich kopiere mir manchmal komplexere Zeilen zusammen und da will ich auch aus einer halbfertigen Zeile rauskommen, um aus einer anderen den Rest zu kopieren und einzufügen.
Ach ja, und dann wähle mal auf der Registerseite "Allgemein" unter "Unterbrechen bei Fehlern" die Option "In Klassenmodul". Das bewirkt, dass bei einem Fehler z.B. in einem Userform der Debugger tatsächlich in der fehlerhaften Zeile stehen bleibt, und nicht wie sonst in der Zeile mit der Show-Methode.
Gruß
Nepumuk
Anzeige
AW: Dim Anweisungen sind Fehlerunanfälliger
02.04.2009 22:05:05
Stefan
Hey Nepumuk
besten Dank für Deinen Tip !
klingt einleuchtend - werd ich so machen !!! :))
na, dass ist ja heute ein Tag wie Weihnachten und Ostern zusammen ;)))
Besten Dank Stefan
AW: Makro sehr langsam > Formeln kopieren
03.04.2009 08:32:53
ChrisL
Hallo Stefan
Ich denke "Evaluate" könnte noch helfen.
dest.Value = Evaluate(Range(formel(x)).Formula1)
Also gleiches Spiel, aber ohne Copy/Paste.
Warum das ganze über SelectionChange gelöst ist, ist mir nicht ganz klar. Bei einer Änderung der Selektion ändert i.d.R. das Resultat nicht, warum also neu berechnen. Manchmal wende ich ein Sheet-Change Ereignis an. Berechnung dauert dann einmalig etwas länger, dafür kann man nachher ohne Verzögerungen rumklicken. Bedingt natürlich dass Daten und Resultat auf separaten Tabellen zu finden sind.
cu
Chris
Anzeige
AW: Makro sehr langsam > Formeln kopieren
03.04.2009 22:07:39
Stefan
Hallo ChrisL
warum auch immer - ich steh gerad auf dem Schlauch :O
wie müsste der Code aussehen - um Deine Idee "ohne Paste und Copy" umzusetzen
hier der aktuelle Code

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'formeln einfügen bei neuen werten (umsätzen) ... spalte a
Dim x As Integer
Dim dest As Range
Dim formel(1 To 8)
formel(1) = "i3"
formel(2) = "j3"
formel(3) = "k3"
formel(4) = "l3"
formel(5) = "h3"
formel(6) = "e3"
formel(7) = "n3"
formel(8) = "o3"
Application.ScreenUpdating = False
If Not Application.Intersect(Target, Range("a4:a50000")) Is Nothing Then '
Application.EnableEvents = False
For x = 1 To 8
'MsgBox Range(formel(x)).Address
Set dest = Cells(Target.Row, Range(formel(x)).Column)
'MsgBox dest.Address
Range(formel(x)).Copy Destination:=dest
dest.Value = dest.Value
'alternativ für die oberen 4 zeilen       noch testen
'Cells(Target.Row, Range(formel(x)).Column).Value=Range(formel(x)).Value
Next
Application.EnableEvents = True
End If
Application.ScreenUpdating = True
End Sub


Anzeige
AW: Makro sehr langsam > Formeln kopieren
04.04.2009 08:36:09
ChrisL
Hallo Stefan
Eigentlich dachte ich so...
For x = 1 To 8
Set dest = Cells(Target.Row, Range(formel(x)).Column)
dest.Value = Evaluate(Range(formel(x)).Formula1)
Next x
Nur wird mir jetzt grad klar, dass die Version u.U. nicht funktioniert, abhängig von den Formeln in Zeile 3. Wenn da ein Zeilenbezug in der Formel drin ist, dann stimmt der u.U. nicht, wenn direkt die Formel aus Zeile 3 gelöst wird.
Naja, um eine weitere Millisekunde Speed raus zu holen, könnte man auch anders auf Copy/Paste verzichten (die Berechnung einfach direkt in VBA durchführen - siehe VBA Befehl WorksheetFunctions), aber bringt vermutlich nicht viel.
cu
Chris
Anzeige
AW: Makro sehr langsam > Formeln kopieren
04.04.2009 11:16:56
Daniel
Hi
hier noch ein paar Optimierungen:
das mit dem deklarieren der Variablen hat man dir ja schon erklärt.
was auch noch unsinnig zeit verbraten könnte:
1. nimm die Zuweisung der Variablen doch auch in die IF-Klammer mit rein. So werden die Variablen nicht jedesmal zugewiesen, sondern nur dann wenn sie benötigt werden (bringt nicht viel, aber so aus prinzip)
2. deine Variablen sind Strings, die du mal in Spalten-Nummern und mal in Ranges umwandelst. auch das kostest Zeit, verwende für Fomel(x) doch gleich Spalten-Nummern.
3. Objekte, die mehrfach verwendet werden, kann man mit der WITH-Klammer definieren. Innerhalb der WITH-Klammer schreibt man für das definierte Objekt dann nur noch den Punkt "." .
das spart Tipparbeit und ist vom Programmablauf auch noch ein wenig schneller:
als gesamtes Makro siehts dann so aus:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim formel(1 To 8) As Long
Dim x As Long
If Not Application.Intersect(Target, Range("a4:a50000")) Is Nothing Then
formel(1) = 9
formel(2) = 10
formel(3) = 11
formel(4) = 12
formel(5) = 8
formel(6) = 5
formel(7) = 14
formel(8) = 15
Application.EnableEvents = False
For x = 1 To 8
With Cells(Target.Row, formel(x))
Cells(3, formel(x)).Copy .Cells
.Formula = .Value
End With
Next
Application.EnableEvents = True
End If
End Sub


Gruß, Daniel

Anzeige
AW: Makro sehr langsam > Formeln kopieren
06.04.2009 22:14:39
Stefan
Hallo Daniel
sorry, bin erst jetzt dazu gekommen, Deinen Tip zu probieren.
Funktioniert bestens !
ist viel kürzer !
Klasse !!!
Hab nur noch das ScreenUpdating abgeschaltet ;)
Und ganz nebenbei habe ich noch einiges dazu gelernt ! :)
Dank Dir sehr für Deine klasse und schnelle und perfekte Unterstützung
Besten Gruß
Stefan

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige