Excel VBA - Kopierte Daten einfügen dauert sehr lange

[willi0513]

Hallo,
ich habe mir vor ca. 25 Jahren eine Vokabellerntabelle mit Excel erstellt. Damals mit Office 97 und später mit Office 2003.
Jetzt habe ich sie wiedergefunden und möchte sie wieder nutzen, mit Office 2010.

Dabei ist mir aufgefallen, dass einige Aufrufe über verschiedene Userforms sehr lange dauern.
Beim debuggen bin ich auf folgende Procedur gestoßen:

Sub AusgewaehlteDatenKopieren(Tabelle)
' Kopiert die Daten die auf Tabelle "Vokabeltabelle" sichtbar sind. Die Daten, die mithilfe vom Autofilter
' ausgeblendet wurden, werden nicht kopiert. Es wird entweder auf "Filtertabelle" kopiert oder auf "Karteitabelle"

Sheets("Vokabeltabelle").Activate
Range("C2").Select
ActiveCell.CurrentRegion.SpecialCells(xlVisible).Copy
Sheets(Tabelle).Activate
Range("A1").Select
ActiveSheet.Paste
ActiveCell.EntireRow.Delete

End Sub

Bei 6 zu kopierenden Vokabeln dauert Active.Sheet.Paste ca. 20 Sekunden. Bei 100 sind es ca. 30 Sekunden.
Wenn ich zur Zeile ActiveCell.EntireRow.Delete komme, bekomme ich immer die Meldung "keine Rückmeldung", aber nach ca. 25 Sekunden geht es wieder weiter.

Hat jemand eine Erklärung, warum das so ist?
Ich kann mich nicht daran erinnern, dass das vor 20 Jahren auch so war. Wahrscheinlich aber nicht, sonst hätte ich das Projekt schon lange aufgegeben.

Gibt es eine andere Möglichkeit die selektierten Vokabeln auf einer anderen Tabelle einzufügen als mit ActiveSheet.Paste?
Kann man irgendwo nachlesen, welche Eigenschaften die verschiedenen Objekte habe?

 

[Krik]

Hat jemand eine Erklärung, warum das so ist?

Ohne die Datei zu kennen, nenne ich mal den häufigsten Grund für Hänger:

Nach jeder Zellinhaltsänderung wird eine Neuberechnung des Arbeitsblattes angestoßen. Wenn er also 100 Zellen ändert, rechnet er 100 mal alles neu durch.
Das kann man vermeiden, wenn man am Anfang des Codes Application.Calculation = xlManual und am Ende Application.Calculation = xlAutomatic schreibt. Die automatische Neuberechnung ist damit für die Laufzeit des Makros ausgeschaltet. Das sorgt häufig für einen enormen Geschwindigkeitsschub. Je mehr Formeln, desto mehr Schub bekommt man. Bei Reaktivierung rechnet er natürlich nochmal alles durch, aber das ist dann ja i. O.

Ein wenig kann man mit Application.ScreenUpdating = False auch noch herausholen, da damit die Aktualisierung der Ansicht ausgeschaltet wird. Am Ende der Berechnung nicht vergessen wieder einzuschalten.

 

[Makkaroni]

In VBA kannst du den Code Debuggen. D.h. die Schritte einzeln ausführen u. die Ausführungszeit der Schritte selbst beobachten. Setze im VBA Modus (Alt+F11) einen Haltepunkt zu Beginn deines Codes. Dann steppst du mit F8 durch deinen Code. Damit solltest du zumindest die zeitraubende Codezeile identifizieren können ...

 

[willi0513]

@Makkaroni
Sorry, dass ich mich nicht verständlich genug ausgedrückt habe.
Ich dachte, wenn ich schreibe: "beim debuggen ist mir aufgefallen, dass ein Befehl soundso lange dauert",
ist es klar wie ich das gemacht habe.

@Krik
Bei mir stehen ja in jeder Zelle nur Vokabel, also keine Formeln.
Da gibt es doch eigentlich nicht viel zu berechnen.

Ich habe aber mal alle Optionen ausprobiert. Mit 8 Vokabeln, ist ja nicht sehr viel.
Im Original dauert der Durchlauf der o.a. Prozedur ca. 48 Sek.
Mit Application.Calculation = xlManual ca.44 Sek.
Mit Application.ScreenUpdating = False ca. 19 Sek.
Und mit beiden auch 19 Sek.
Mit allen 2893 Vokabeln dauert es ca. 24 Sek. und im Original sind es ca. 60 Sek.
Also ist die Anzahl der Vokabeln nicht das Entscheidende.

Ich arbeite noch mit dem .xls Format von Office 97 bis 2003.
Wenn ich die Datei als .xlsm speichere und dann teste, dauert es ca. 50% länger, ist also kein Vorteil.

Ich habe gerade 10 Vokabeln von Hand markiert, mit Ctrl-C kopiert, dann eine leere Tabelle angeklickt und habe dann die Meldung "Keine Rückmeldung" bekommen. Nach ca. 10 Sek. gings weiter und ich konnte die Daten mit Ctrl-V einfügen, ohne Zeitverlust.
Hierbei ist mir aufgefallen,, dass die Datei im [Kompatibilitätsmodus] geöffnet ist.
Hat das evtl. einen Einfluß?

Kann es an einer Excel oder VBA-Einstellung liegen, oder wonach müsste ich evtl. suchen?

 

[Krik]

Der Kompatibilitätsmodus wird angezeigt, weil das eine uralte .xls ist.

Was noch möglich ist, dass es zu viele "benutzte" Zellen in der Arbeitsmappe gibt. Eine Zelle fängt dann an zu existieren, wenn sie mal Daten enthalten. Selbst wenn man die Zelle leert, bleiben die Zellen "übrig". Markiere mal alle Spalten und Zeilen, die leer sein müssten und dürfen, und dann Rechtsklick auf die Spalte/Zeile und Löschen.
Schau dann noch mal, ob die Datei nun schneller ist.

Bringt das auch nicht, dann bleibt eigentlich nur noch übrig, mit einer neuen Datei (dieses Mal aber als .xlsm) anzufangen und den VBA-Code rüber zu kopieren. Das sollte jetzt aber wirklich einen Geschwindigkeitsschub geben.

Wenn es dann immer noch hakt, dann ist wohl was mit Excel oder dem VBA-Code kaputt.

 

[willi0513]

Gute Idee, aber leider kein Erfolg gehabt.

An das kopieren in eine neue Datei habe ich auch schon gedacht.
Den Code kopieren ist ja noch relativ schnell gemach, aber das erstellen von 15 Userforms und die ganzen Commandbuttons hält mich dann doch davon ab.

Vielleicht fange ich mal klein an und kopiere erstelle nur das was ich im Moment brauche und lasse die Karteikästen und die ganzen Statistiken weg.

Vielen Dank euch.

 

[S.a.M.]

die Userforms mit den Buttons kannst Du doch exportieren und in der neuen Datei dann importieren, die Makros ebenso.

dazu in den Entwidkertools rechtsklick auf Module und dan dann "Datei exportieren"

 

[cumulonimbus8]

und die ganzen Commandbuttons

Hmtja - das wären also Buttons außerhalb der Userforms? Das ist dann nur sehr umständlich zu machen, außer man kopiert ganze Blätter, was ggf. ja das Problem mitschleppt.

CN8

 

[willi0513]

Ich wusste nicht, dass man die Userforms auch exportieren kann, werde ich mal ausprobieren.
Aber die Gefahr ist wirklich, dass man die Fehler mit kopiert.

 

[cumulonimbus8]

Aber die Gefahr ist wirklich, dass man die Fehler mit kopiert.

Mein Bauch will hier nicht an Fehler glauben sondern eher an das vorhandene VBA, also Office. Eher könntest du da ein Schräubchen finden als im Code und den Objekten selbst.
CN8

 

[willi0513]

Ich habe jetzt die Tabelle mit den Vokabeln, eine Userform, ein Commandbutton (der die Userform direkt aufruft) und nur die nötigen Daten aus den Modulen in eine neue Datei kopiert und als .xlsm gespeichert.

Bei 10 bis 300 Vokabeln habe ich keinen Zeitverlust mehr, und kann praktisch sofort anfangen zu lernen.
Bei allen 2900 Vokabeln dauert es ca. 4-5 Sek.

Das ist ja schon mal ein Riesen Erfolg.

Hat jemand eine Erklärung dafür, warum es einwandfrei funktioniert?

Die Frage ist ja auch, bleibt das so, wenn ich nach und nach die anderen Daten in die neue Datei integriere.

 

[Krik]

Meiner Erfahrung nach kann es passieren, dass sich im Laufe der Zeit irgendwelche Datenreste ansammeln, die nicht sichtbar sind, aber im Hintergrund Ressourcen fressen. Wie genau das zustande kommt, habe ich auch nie herausgefunden, aber der Kram wird in der Datei gespeichert.
Andere Performance-Fresser sind Matrixformeln und bedingte Formatierungen.

Denk dran, Excel ist nicht dafür vorgesehen, als Datenbank herzuhalten. Es ist eine Software für Tabellenkalkulation. Dein Vokabeltrainer ist bei Access und vergleichbarem besser aufgehoben. Alternativ kann man auch fertige Lösungen setzen, wie z. B. Anki.

 

[willi0513]

Natürlich kann man fertige Lösungen nutzen, aber selbst erstellte sind in der Regel ja auf die eigenen Bedürfnisse am besten angepasst.

An Access habe ich auch schon mal gedacht. Aber dann fange ich wieder ganz von vorne an, da habe ich auch keine Lust zu.
Damals hatte ich von Access noch keine Ahnung und habe es halt mit Excel gelöst.

Ich werde jetzt nach und nach die alten Daten in die neue Datei kopieren.
Mal sehen, wie lange es gut geht.

Vielen Dank Euch