Office: (Office 2010) extrem hohe CPU-Last mit kleinem VBA

Hallo zusammen,

ich habe eine Excel Datei in der ich diverse Werte aus einer täglichen Excel Datei per VBA kopiere. Seit kurzem geht die CPU-Last während des kopierens extrem nach oben und der Code benötigt rund 4 Minuten. An was kann das liegen???

Zieldatei aktuell 202 KB
Quelldateien knapp 250 KB

Kopiert wird mit diesem Code
Code:

So sieht die CPU-Last aus
Nach Systemstart
PC nach Neustart.PNG

Nach Start des VBA-Codes
PC nach VBA Start.PNG

Nach Ende des VBA-Codes
PC nach VBA Ende.PNG

Kann es denn wirklich sein, dass dieser "lächerliche" VBA-Code einen PC mit i5-Prozessor so beansprucht???

:)

 

Hallo Name?,

ohne eine Musterdatei ist die Sinnhaftigkeit des Codes schwer zu bewerten, aber bei solchen Konstrukten jagen mir kalte Schauer über den Rücken:
Code:

Code:

Dazu noch das Übertragen von Daten mittels Einzelzellen (und nicht zusammenhängender Bereiche / Arrays)...
Echte Performancekiller!

Grüße
EarlFred

 

Hallo EarlFred,

also mein Name ist Andi (so wie es am Ende des Beitrags in der Signatur steht).

Eine Datei kann ich leider nicht zur Verfügung stellen, da die Datein in der Quelldatei vertraulich sind.

Wenn ich am Code etwas zu verbessern gibt würde ich das gerne machen. Aber nur kritisieren ohne einen Ratschlag zu geben finde ich in einem Forum nicht gerade nett.

Wenn Do/Loop Dir den kalten Schauerüber den Rücken jagt, wie soll ich sonst bestimmt Positionen im Arbeitsblatt suchen, wenn die Dateien dynamisch sind?
Wie kann man das Übertragen efektiver gestalten? Die Daten in ein Array einlesen und am Ende mit einer Schleife in die Zieldatei schreiben?

Ich wäre Dir echt dankbar, wenn Du mir etwas weiter helfen könntest. Deswegen stell ich meine Frage ja auch hier im Forum.

Vielen Dank,
Andi *wink.gif*

 

Ich denke, konkrete Vorschläge orientieren sich an konkreten Bedingungen, die Du aber nicht liefern kannst/willst. Und man sollte die Erwartung niedrig halten, dass jemand den Sinn einer Anwendung aus (wie auch immer) schlechten Code ermittelt.

Daher ein Allgemeinplatz, den auch EarlFred schon anspricht:
Massendatenverarbeitung schlägt serielle Einzeldatenverarbeitung.
Oder auch: Bagger statt Sandkornpinzette.
In einer Datenbank verwendet man im Sinne von Performance vorzugsweise Abfragen (SQL-Anweisungen) statt Einzelzugriffe auf Einzelfelder.
Excel: Schleifen die keiner braucht
Auch hier wird man vorzugsweise maximale Bereiche auf einmal verarbeiten statt Zelle für Zelle.

 

Hallo Andi,

der (indirekte) Ratschlag wurde ja abgewiesen: Eine Musterdatei (bzw. 2 für Quelle und Ziel) zur Verfügung zu stellen.

Im Gegenzug finde ich es nicht gerade nett, wenn sich jemand nichtmal die Mühe macht, seinen Namen von Hand in eine Grußformel zu schreiben, sondern dies durch den Automaten erledigen lässt - aber das mag nicht die Hauptsache sein, sondern nur ein Hinweis.

Eine Musterdatei enthält selbstverständlich keine sensiblen Daten, sondern dient der Veranschaulichung anhand von erfundenen / anonymisierten Daten vergleichbarer Art und Struktur (Dieser Ratschlag hat sich in den vergangenen 9 Jahren nicht geändert *wink.gif*). An dieser Datei könnte man die von Dir angewendeten Methoden analysieren und möglicherweise (sehr wahrscheinlich) optimieren.

z. B. mittels Find oder mittels Match wie in meiner Antwort auf Deine letzte Frage hier (im übrigen vollkommen unreflektiert Deinerseits und daher übrigens auch nicht nett *wink.gif*).
Die erste freie Zelle einer Spalte findet man schnell über Konstrukte mit Cells(Rows.Count, Spalte).End(Xlup).Offset(1,0).

Das geht auch ohne Schleife - und genau hier wird es schnell! Wenn Daten vorher zu anaylsieren / zu ändern sind, dann innerhalb des Arrays, da hier die Zugriffsgeschwindigkeit um Welten schneller ist als bei Zugriffen auf Tabellenblätter.
Sollen Daten ohne Bearbeitung kopiert werden, hat sich Copy / Paste / PasteSpecial als schneller erwiesen (finde den Artikel grad nicht) - aber auch hier gilt: Bereiche anstelle Einzelzellen.

Noch ein Hinweis: Baue mal Kontrollpunkte mit Timer in Deinen Code ein und prüfe, welche Schleife wie lange braucht. Das hilft zumindest schonmal bei der Lokalisierung der ärgsten Engstellen.

Trifft zwar thematisch nicht Deine Aufgabe, aber vielleicht reicht's aus, um mal einen Eindruck zu vermitteln, welche Unterschiede zwischen Schleifen und geeeigneten Methoden bestehen:
35.000 Zeilen durchlaufen. Zu langsame Routine.

Grüße
EarlFred

 

Hallo zusammen,

vielen Dank für die Links und Hinweise! Mit Eurer Hilfe habe ich den Code so ändern können, dass er innerhalb weniger Sekunden durch ist und die CPU nicht minutenlang blockiert ist.

Das Ende einer Liste wird jetzt so gesucht
Code:

Dort wo ich nach einem festen Begriff suchen kann verwende ich jetzt
Code:

Aber der absolute Performance-Bringer war, die Werte in ein Array zu schreiben und am Ende zu übergeben
Code:

Da würde ich sagen habe ich heute wieder etwas gelert. Das .Select Performance kostet wusste ich bereits, aber dass das Suchen hier und Schreiben da auch so starkt bremst war mir neu.
Erlaubt mir bitte noch zwei Fragen:

1. Wie dimensioniere ich das Array am Besten? Mein Problem ist, dass ich am Anfang weiß wie viele Datensätze es sind. Wenn ich also zwischendrin ein ReDim mache sind die bisherigen Daten futsch. Deswegen habe ich jetzt die Variable zu Beginn großzügig dimensioniert
2. Ich kann die Daten des Arrays nur ausgeben wenn ich das Sheet vorher aktiviere. wsZ.Range(....) = arrPos funktioniert nicht (Fehler 1004: Anwendungs- oder objektdefinierter Fehler)

@EarlFred:
Ich habe doch in dem anderen Thema geantwortet

Okay, ich habe mich vielleicht mehr auf die anderen Antworten bezogen, was aber daran lag, dass ich etwas unter Zeitdruck stand und Deine Lösung auch nicht verstanden habe *redface

Deswegen nochmals: VIELEN DANK FÜR DIE HILFE

Gruß,
Andi

 

Hallo Andi,

da fehlen 2 Punkte, denn Cells bezöge sich sonst auf das aktive Tabellenblatt und nicht wsZ, so dass die Range aus Bereichen verschiedener Blätter bestünde, was nicht sein kann.
Code:

Den Rest muss ich mir mal in Ruhe ansehen, aber nicht mehr heute.

Grüße
EarlFred

 

ah, verstanden. Vielen Dank!