Office: (Office 2013) Makro wird immer langsamer

Hallo,

ich habe ein Makro, welches gefilterte Daten (Autofilter) von Liste 1 nach Liste 2 schreibt.
Dabei werden rund 1900 Zeilen und 35 Spalten durchlaufen.
Die ersten 200 Zeilen werden 5-7 Zeilen pro Sekunde geschrieben.
Gegen Zeile 1400 sind es 0,5 Zeilen pro Sekunde, also eine Zeile pro 2 Sekunden und es wird mit zunehmender Zeile nicht besser.
Der Übergang ist fließend. Es wird also nicht plötzlich langsamer sondern kontinuierlich...

Wie kann das sein?

Code:

Hatte den Code vorher aufgeteilt auf zwei Schleifen, eine die durch die Zeilen geht und innen durch die Spalten.
Dachte zuerst es könnte der Grund sein und so habe ich die innere Schleife rausgenommen.

Leider war es das nicht..

Grüße

Mathieu

:)

 

Hi Mathieu,

ist ein bisserl schwierig auf Fehlersuche zu gehen, wenn man nicht weiß, was das Makro eigentlich macht. Eine Besipielmappe ist immer Hilfreich.

Was machst zum Beispiel dieser Code Schnipsel? Ich kann nicht erkennen, was bzw. wieviel kopiert wird.
Code:

Kann es sein, dass Du Dir damit den Zwischenspeicher bei jeder weiteren Ausführung dicht machst? Dass würde Deine Verlangsamung erklären.

Viele Grüße
Hauke

 

Hallo Hees,

danke für die Rückmeldung.

Code:

Der Zwischenspeicher wird nach dieser Aktion immer geleert.
Es ist ein 1x35 Range, welcher nur Formatierung enthält.

Habe den Code auch bereits ohne diesen PasteSpecial ausgeführt und das ändert leider nichts...

Eine Beispielmappe wird schwer, da sensible Informationen in den 2700 Zeilen stehen.

Ich schaue mal was ich da machen kann.

Grüße

Mathieu

EDIT: Die Beispieldatei ist zu groß zum Hochladen. Werde es von zu Hause aus irgendwo hochladen und den Link freigeben.
Habe auf der Arbeit keinen Zugriff auf Share/Cloud Dienste.

 

Hallo Mathieu,

einzelne Zellenzugriffe (lesend/schreibend) sind Gift für Geschwindigkeit.
Die Berechnung (Calculation) sollte zumindest temporär auf manuell (=aus) gestellt werden, wenn Berechnungen ausgeführt werden.

Allein der Block mit den zig Zeilen der Art
If Not .Columns(3).Width = 0 Then
wird pro Schleife immer wieder abgearbeitet - mit stets der gleichen Erkenntnis. Einmal prüfen und einlesen in eine Variable / Datenfeld und die Erkenntnis jeweils wieder nutzen.

Es kann schneller sein, die Daten in ein Array einzulesen und anstelle des Filters die Daten nach Deinen Kriterien auszusortieren.

Ohne Mustermappe bin ich aber sicher nicht dabei.

...diesen wenig hilfreichen Satz habe ich schon gefühlt 38.000 Mal gehört und immer wieder geantwortet: Dann mach die Informationen eben unsensibel! Und reduziere sie auf eine sinnvolle Anzahl, die noch ausreichend ist, um das geplante Funktionieren zu demonstrieren.

Grüße
EarlFred

 

Hallo EarlFred,

die Datei ist leider 1mb groß, als zip 490kb, kann ich hier nicht hochladen. Werde es heute abend per Dropbox oder ähnlichem freigeben.

Interessanterweise läuft das Makro ist der neuen Mappe mit 2800 Zeilen binnen weniger Sekunden durch.

Ist mir also schleierhaft, wieso es in der anderen Mappe nicht funktioniert. Gleiches Format, xlsm inkl. Custom UIs etc..

Grüße und bis heute Abend.

Mathieu

EDIT:

Also mit Arrays ist das echt gut, werde mal weiter dran forschen.

Code:

Leider funktioniert das rote nicht.
Habe ich in einem anderen Code einst mal genutzt, aber nur bei einem Eindim. Array. Sollte für 2 Dimensionen doch genauso funktionieren, oder?

Oder wäre hier ein anderer Ansatz sinnvoller? zB. einfach die Zeilen aus dem MyArray löschen, die nicht betroffen sind?

Grüße

Mathieu

 

Hallo Mathieu,

eine Musterdatei ist niemals 1MB groß. Mach sie kleiner. Das geht. Sicher. Nur Mut. Reduziere die Zahl der Musterdaten auf das notwendige Maß. Schrieb ich schonmal, kommt mir bekannt vor.

Eine Beschreibung, wie sich das bemerkbar macht, gehört dazu!

Hier ist es allerdings klar: Wie man in der OH nachlesen kann, darf bei Verwendung von Redim PRESERVE nur die LETZTE Dimension geändert werden. Du willst die erste ändern.

Zähle Topic = "Blabla" einfach VOR der Dimensionierung - z. B. mittels Application.CountIf() und "dimme" dann genau einmal. Das ganze "redimme" kostet auch nur Zeit - die Du ja nicht hast.

Grüße
EarlFred

 

Hallo EarlFred,

ich lösche mal die ganzen bed. Format. raus, die brauchen meist enorm viel Platz.

Bzgl. Fehler im Code: "Index außerhalb des gültigen Bereichs".

Anbei das Beispiel vom Anfang. Dort kann im Worksheet Entwicklungsliste auch das eben gezeigte Makro eingefügt werden.

Grüße

Mathieu

EDIT: Okay danke für den Tipp. Werde es ausprobieren.

 

Hallo Mathieu,

noch Fragen? *wink.gif*

Grüße
EarlFred

 

Ja, wieso?xD
Ist unlogisch warum in der einen Mappe das Makro schnell und in der anderen langsam läuft, da keinerlei OnAction Ereignisse definiert sind, heißt, der Code kann ungestört laufen.

Selbst die Variante mit dem Array dauert ewig im ursprünglichen Workbook. Sobald es darum geht die Daten in eine Tabelle zu schreiben, geht es sehr langsam von Statten.

Gibt es ggf. in der XML Komponente von Excel die Antwort?

Grüße

Mathieu

 

Hallo Mathieu,

nein, ist es nicht.

In der neuen Mappe - so unterstelle ich - hast Du keine BF eingerichtet. Dort läuft der Code schneller.

BF ist volatil. D. h. die hinterlegte Berechnung wird nicht nur bei Änderung des betreffenden Zellinhalts ausgeführt, sondern auch bei vielen anderen Ereignissen. Folge dem Link.

Schau Dir die BF an, vor allem auch, wie rechenintensiv sie ist. Ganze Spalten in hinterlegten Formeln sind z. B. ungünstig.

Grüße
EarlFred

 

Das ist ja wirklich pervers...
Leider sind wir darauf angewiesen.

Alternativ wären Makros, die diese Formatierung übernehmen.
Also zB. Worksheet_Change Ereignisse, die die Zellen einfärben?

Die könnte man dann auch tatsächlich abschalten, sofern gewünscht.

Grüße

Mathieu

 

Hallo Mathieu,

Solange die Werte "hart" eingegeben werden (von Hand, per Code), also nicht über Formeln errechnet, ist das einfach.
Wenn die Werte berechnet werden und Du weißt, dass die berechneten Werte in z. B. Spalte X immer über Werte aus Spalte Y errechnet werden, kannst Du Spalte Y überwachen - auch noch einfach.
Sobald Du aber auf das Calculate-Ereignis angewiesen bist, wird es wieder uneleganter.

Andere Idee: Kopiere die Werte der Zieltabelle in ein neues Blatt - ohne Formatierungen ("temporäres Ziel"). Stelle die Spaltenbreite auf 0, wie im ursprünglichen Zielblatt - oder setze einen anderen geeigneten Marker, den Du im Code verwenden kannst.
Beziehe Dein Makro nun auf das temporäre Zielblatt. Am Ende kopierst Du nur die Werte in das ursprüngliche Zielblatt.

Grüße
EarlFred

 

Hallo EarlFred,

tempo. Blätter sind leider nicht möglich, da die Mappe freigegeben wird und in diesem Modus ist das Löschen von Tabellenblättern nicht möglich, Excel halt.

Ja, freigeg. Mappen sind böse und schlecht und da wäre ein SQL Server / Access besser, aber dies steht leider nicht zur Verfügung, wäre aber auch mein pers. Wunsch.

Habe gerade nochmal mit der alten Mappe ohne BF ausprobiert und es geht leider immer noch langsam.
Ggf. werde ich noch Datenüberprüfungen etc. entfernen.

Was leider auch nicht mit einer Freig. Mappe funktioniert, ist das Löschen und Neusetzen von BFs vor und nach einer Prozedur...

Die BFs sind aber simpel. Rot wenn ="offen" oder grün wenn ="erledigt" etc., keine komplexen Formeln aber ggf. macht es die Anzahl einfach..

Grüße

Mathieu

 

Hallo Mathieu,

das temporär angelegte Blatt muss nicht in der gleichen Mappe sein...

und Quelle von 86,3% allen Übels auf dieser Welt - wirklich! *wink.gif*

Grüße
EarlFred

 

Hallo,

meine BFs lassen sich in den paar Zeilen zusammenfassen:

Code:

Ist also nicht umfangreich. Verstehe also weniger, warum das mit den BFs nicht funktioniert...

Grüße

Mathieu