Microsoft Excel

Herbers Excel/VBA-Archiv

Laufzeit verringern

Betrifft: Laufzeit verringern von: dirk
Geschrieben am: 23.06.2014 19:23:20

Hallo,

normalerweise googel ich mir jedes Excel-Problem weg, aber jetzt stehe ich nach Jahren mal so richtig auf dem Schlauch.
Ich möchte spaltenweise (nicht das komplette Tabellenblatt auf ein Mal) diejenigen Zellen leeren, die scheinbar bereits leer sind - heißt in meinem Fall: Zellen, die mit Formeln gefüllt sind, aber aktuell kein Ergebnis ausgeben.

Dazu habe ich mir vor langer Zeit schon folgenden Code zusammengestellt:

Dim vSpalte As Range
Dim lngLast As Long
Dim lngZ As Long
Dim actClmn As Integer
Dim lngC As Long

For Each vSpalte In Range("E11", "BJ11").Columns
vSpalte.Select
If vSpalte.Offset(-1, 0).Value = "c" Then
actClmn = ActiveCell.Column
lngLast = Cells(Rows.Count, actClmn).End(xlUp).row
For lngZ = lngLast To ActiveCell.row + 29 Step -1
If Cells(lngZ, actClmn).Value = "" Then
Cells(lngZ, actClmn).Value = ""
lngC = lngC + 1
End If
Next
End If
Next

Das Makro läuft zunächst über die Zeile 11 von E nach BJ und bearbeitet ausschließlich diejenigen Spalten, die ich jeweils eine Zeile darüber, in Zeile 10, mit "c" gekennzeichnet habe - und zwar End(xlUp) von der letzten gefüllten Zelle der "c"-Spalte bis Zeile 40.

Funktioniert alles prima, aber ich möchte diesen Code nun auf ein neues, deutlich größeres Tabellblatt übertragen - und hier dauert die Berechnung geschlagene 12 Minuten. Die letzte gefüllte Zeile ist die Nummer 1996, was ja eigentlich so groß gar nicht ist.

Spaßeshalber habe ich --Cells(lngZ, actClmn).Value = ""-- mal ersetzt durch
Cells(lngZ, actClmn).select
With Selection.Interior
.ColorIndex = 40
.Pattern = xlSolid
End With

Statt den Content zu clearen färbt ein Durchlauf nun die betreffenden Zellen einfach ein - und das in einem Affentempo! WIESO?? Eine Zelle zu leeren kann doch nicht so viel mehr Aufwand sein als dieselbe Zelle bloß einzufärben. Ich kriegs nicht gebacken.

Wie auch immer, vielleicht hat jemand hier eine Idee, wie ich von den 12 Minuten auf 2 komme oder so, 2 Minuten wären im Rahmen, 12 nicht.

Grüße,
Dirk.

  

Betrifft: AW: Laufzeit verringern von: Daniel
Geschrieben am: 23.06.2014 19:48:09

Hi
sollen die Formeln, die ein anderes Ergebnis als "" liefern stehen bleiben?
wenn ja, dann könntest du die Formeln so abändern, dass sie statt "" einen WahrheitsWert als Ergebnis liefern.
Wenn in der Tabelle sonst keine Wahrheitswerte vorkommen, könntest du dann alle Zellen mit Wahrheitswert löschen über:

Cells.SpecialCells(xlcelltypeFormulas, true).ClearContents
eine Zeile, dauert keine 2 Sekunden.

jetzt zu deiner Frage:
Wenn du einen Zellwert änderst, dann starten damit in Excel eine ganze Reihe von Aktionen.
Beispielsweise musst Excel prüfen, ob nicht in einer der geöffneten Exceldateien eine Formel existiert, die sich auf die Zelle bezieht und neu berechnet werden muss, ob nicht vielleicht eine Bedingte Formatierung neu gesetzt werden muss und ob nicht vielleicht ein Change-Event-Makro vorhanden ist, welches dann gestartet werden müsste.
Selbst wenn nichts davon vorhanden ist, geprüft werden muss es und zwar bei jeder Zelländerung erneut.
Beim Färben von Zellen passiert das alles nicht, den eine Umformatierung bewirkt keine Neuberechnung oder Start eines Eventmakros, daher ist das Färben von Zellen wesentlich schneller als das ändern eines Wertes.

Daher versucht man wenn möglich Zellwertänderungen für alle betroffenen Zellen gleichzeitig ausszuführen, so wie es mein Codebeispiel macht.
Dann müssen die genannten Aktionen nicht für jede Zelle einzeln ausgeführt werden, sondern sie können einmalig für alle Zellen gleichzeitig ablaufen.

Gruß Daniel


  

Betrifft: AW: Laufzeit verringern von: dirk
Geschrieben am: 23.06.2014 20:04:53

An Daniel

Hi!
Schöne Erklärung, vielen Dank!

Deine Idee für den Code klappt leider nicht, weil das Blatt tatsächlich voller Formeln ist, die ein positives Ergebnis liefern - dein Code löscht mir das komplette Blatt. Trotzdem, dass du mit so einer schlagenden Idee kommst, die nur aus Zufall nicht funktioniert, finde ich super. Nochmal, vielen Dank!

Gruß, Dirk


  

Betrifft: AW: Laufzeit verringern von: Daniel
Geschrieben am: 23.06.2014 20:11:07

Hi
sorry, war zu schnell
muss lauten .specialclells(xlcelltypeformulas, 4), um auf Formeln mit Wahrheitswert als Ergebnis zuzugreifen.

das Entspricht der Excelfunktion START - BEARBEITEN - SUCHEN UND AUSWÄHLEN - INHALTE - FORMLEN - WAHREITSWERTE, falls du selber und ohne Makro mal ein bisschen experimentieren willst.

Gruß Daniel


  

Betrifft: AW: Laufzeit verringern von: dirk
Geschrieben am: 23.06.2014 20:55:06

Daniel!

Offenbar nur Künstler unterwegs im Herber-Forum. Der letzte Code von dir funktioniert. Auf den Punkt dieselbe Laufzeit wie Raphaels Vorschlag! Sehr interessant! Beide Vorschläge werde ich genauer angucken, Stichwort specialcells etwa, vielleicht kann ichs nachvollziehen, und dann kann ich was lernen.

Vielen Dank nochmal an beide!

Schöne Grüße, Dirk


  

Betrifft: AW: Laufzeit verringern von: Raphael H
Geschrieben am: 23.06.2014 19:49:37

Hallo Dirk,

färben geht schneller weil das Blatt nicht nach jeder Aktion neu berechnet wird.

Ohne genauer auf deinen Code einzugehen wirst du mit diesen Zeilen sicher schon einiges an Zeit gutmachen.

Sub XX()
Dim vSpalte As Range
Dim lngLast As Long
Dim lngZ As Long
Dim actClmn As Integer
Dim lngC As Long

With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
End With

    For Each vSpalte In Range("E11", "BJ11").Columns
        vSpalte.Select
        If vSpalte.Offset(-1, 0).Value = "c" Then
            actClmn = ActiveCell.Column
            lngLast = Cells(Rows.Count, actClmn).End(xlUp).Row
            For lngZ = lngLast To ActiveCell.Row + 29 Step -1
                If Cells(lngZ, actClmn).Value = "" Then
                    Cells(lngZ, actClmn).Value = ""
                    lngC = lngC + 1
                End If
            Next
        End If
    Next

With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationAutomatic
End With
End Sub



  

Betrifft: AW: Laufzeit verringern von: Dirk
Geschrieben am: 23.06.2014 20:26:36

Hallo, Raphael,

"einiges an Zeit gutmachen" ist ein guter Witz - von 12 Minuten auf 10 Sekunden. Sagenhaft. Wenn ich mehr Ahnung hätte von VBA, hättest du mich mit deiner Problemlösung zum Amateur gemacht.
Hast mir sehr! geholfen. Vielen Dank.

Ich muß mir deinen Code mal richtig angucken, weil ich das gern verstehen würde, was sich da jetzt so krass geändert hat. Interessanterweise hast du die With-Anweisung benutzt - auch wenn das bloße Einfärben der Zellen offenbar völlig außer Konkurrenz läuft, geht das Einfärben ja auch mit dem With, End With (wobei das lumpige Einfärben sogar noch etwas länger gedauert hat, als deine Codelösung).

Gruß, Dirk


  

Betrifft: danke für die Blumen, aber ... von: Raphael_H
Geschrieben am: 23.06.2014 21:27:41

Daniels Lösung ist meiner Meinung nach die Bessere.

Meine Anpassung (alles was im With Block steht und ein bisschen Kosmetik) verhindern lediglich, dass die ganzen Aktualisierungen im Hintergrund laufen. Dein Blatt wird also während der Ausführung des Codes nicht dauernd neu berechnet.

Daniels Vorschlag ist besser weil er die Ursache angeht, meine Version kümmert sich lediglich um die Symptome.

Gruess
Raphael


  

Betrifft: AW: danke für die Blumen, aber ... von: Dirk
Geschrieben am: 23.06.2014 21:50:22

Raphael, "ein bißchen von hier und ein bißchen von da" versteh ich nicht, von 12 Min auf 10 Sekunden löst das Problem, und das ist für mich sensationell.

Deine Lösung hat aber einen entscheidenden Vorteil, zumindest dachte ich erst, dass er entscheidend wäre, nämlich deine Lösung bezieht sich enger auf mein Anliegen, denn ich hatte ja nach "spaltenweise" gefragt, und genau das tut dein Makro: es arbeitet Spalte für Spalte ab. Allerdings habe ich mit Daniels Lösung nun gesehen, dass die Tabellenblatt-weise Bearbeitung völlig in Ordnung ist, die Auf-einen-Schlag-Leerung der Zellen hat mir nix in den anderen Spalten zerhauen, was ich vorher angenommen hatte (auch wenn sichs blöd anhört). Deshalb ist Daniels Lösung für mich perfekt. Archivieren werde ich aber beide, logisch.

Gruß, Dirk


  

Betrifft: AW: danke für die Blumen, aber ... von: Dirk
Geschrieben am: 27.06.2014 10:22:23

Hallo, Raphael,

nur nochmal kurz ein Feedback.
Auch wenn beide Problemlösungen gleich gut waren, hat sich dein "bisschen Kosmetik" für weitere Anwendungen bei mir als sehr sehr hilfreich herausgestellt.
xlCalculationAutomatic, bzw. Manual und EnableEvents habe ich zwar gegoogelt, aber ich bin da doch noch nicht hintergestiegen - der Punkt ist aber, dass deine Kosmetikaktion sämtliche andere Codes in zig anderen Makros bei mir ebenfalls extrem beschleunigt.

Ich habe mich immer gescheut, mal direkt in einem Forum nach einer Problemlösung zu fragen, aber das hier war der Bringer. Nochmal besten Dank!

Gruss, Dirk


  

Betrifft: AW: xlCalculationAutomatic, EnableEvents, Screenup von: Daniel
Geschrieben am: 27.06.2014 14:22:45

Hi

das grundprinzip ist folgendes:
wenn du in einer Zelle einen Wert veränderst, muss Excel eine ganze Reihe von Aktionen ausführen:
- in allen geöffneten Exceldateien schauen, ob Formeln und Bedingte Formate von dieser Änderung betroffen sein könnten und wenn ja, diese neu berechnen.
- prüfen, ob es ein Change-Event Makro gibt und wenn ja, dieses Ausführen.
- Bildschirmanzeige ändern.

all diese Aktionen kosten Zeit.
Insbesondere dann, wenn man in einer Schleife jede Zelle einzeln bearbeitet.
Daher hat MS vorgesehen, dass man diese Aktionen deaktivieren kann, damit das Makro schneller abläuft.


allerdings kann man die Bearbeitung auch auf eine andere Weise beschleunigen, nämlich indem man darauf achtet, dass man möglichst nicht Zellen einzeln in Schleifen bearbeitet, sondern wenn möglich alle Zellen gleichzeitig ändert.
Dann werden diese Aktionen nämlich nicht für jede Zelle seperat, sondern einmalig für alle Zellen gleichzeig ausgeführt und fallen somit zeitlich nicht ins gewicht und man muss sie somit auch nicht aus- und wieder einschalten.
(ist im Prinzip so ähnlich wie wenn du den Maler kommen lässt, um deine Wohnung neu tapezieren zu lassen, dann lässt du ihn ja auch nicht für jedes Zimmer herfahren und wieder zurück)

Dh anders ausgedrückt, wenn man richtig programmiert, ist das Auschalten der automatischen Neuberechnung, der Events und der Bildschirmaktualisierung in 90% der Fälle unnötig.
Man sollte es dann auch nicht tun, denn wenn das Programm bspw mit einem Fehler abbricht, muss man alle Einstellung von Hand wieder rückgängig machen.

Gruß Daniel


  

Betrifft: AW: xlCalculationAutomatic, EnableEvents, Screenup von: Dirk
Geschrieben am: 27.06.2014 19:32:40

Hi Daniel, sehr interessant! Du hast Sportsgeist, offensichtlich - und das ist ebenfalls ein interessanter Punkt, zumindest was meinen eigenen Sportsgeist angeht. Wenn man viel von einer Sache versteht, dann ist man immer hinter der eleganten, sprich der unbedingt einfachsten Lösung her, aus ästhetischen und praktischen Gründen gleichermaßen - ich kann da zwar nicht groß mitreden, aber das isses wohl, was man tut, wenn man "richtig programmiert".

Problem dabei ist für mich selbst regelmäßig, dass ich mit der Zeit Mittel und Zweck nicht mehr richtig trennen kann: ich bin seit letzten August an dieser Arbeitsmappe dran und fast fertig damit, aber prompt dass ich merke, wieviel Power in VBA steckt, verschiebt sich mein Interesse von meiner Arbeitsmappe zum Programmieren, eigentlich will ich das gar nicht.

Wenn also ein Makro zum Debuggen unterbricht, dann bin ich bis jetzt eigentlich gut damit klar gekommen, die Bildschirmaktualisierung abzuschalten und den Fehler zu suchen, ich dachte eher, das gehört dazu. Aber stimmt schon, was dazu gehört ist der richtige Code, nicht die Sucherei. Vorerst werde ich jetzt trotzdem mal Raphaels Event- u. Calculation-Abschaltung (jetzt wird mir auch klar, was er mit Kosmetik meint) als Zauberformel für die Codebeschleunigung verwenden. Ich freu mir einen Ast, dass ich damit neuerdings so einen schönen Trick in der Kiste habe. Dagegen spricht natürlich die Arbeit an den Ursachen, nicht die an den Symptomen, wie Raphael meinte. Mal sehen, wohin es führt.

Gruß, Dirk


  

Betrifft: AW: xlCalculationAutomatic, EnableEvents, Screenup von: Raphael H
Geschrieben am: 27.06.2014 20:39:19

Hallo Dirk,

es freut mich sehr das du die Anspielung mit der Kosmetik verstanden hast.
Daniel hat es soweit auf den Punkt gebracht was die Programmierung angeht. Ich möchte bei der Fehlermeldung nochmals kurz einhängen. Damit man Bildschirmaktualisierung, etc. nicht jedes Mal manuell einschalten muss kannst du eine Fehlerbehandlung einbauen.

Sub XX()
dim x as String
...
...
...

On Error goto Failure
...
...
Dein Code
...
...

Failure:
Msgbox Err.Number & ": " & Err.Desciption
With Application
    .ScreenUpdate = True
    .EnableEvents = True
    ...
End With
Damit stellt er dir wieder alles ein wenn ein Fehler kommt.

Grundsätzlich hat Daniel recht fast jeder Code lässt sich so schreiben, dass das abschalten der Events und Aktualisierungen unnötig ist. Allerdings bin auch ich ein Hobbybastler und weiss aus Erfahrung, dass wenn man mit VBA beginnt selten die optimale Lösung findet und einem diese Tricks wenigstens etwas liefern was den erstellten Code brauchbar macht.
Aber die Erfahrung und das aktive mitlesen und mithelfen im Forum helfen da schnell weiter. Ich schmunzle heute über viele VBA Codes die ich letztes Jahr geschrieben habe, da sie teilweise einfachste Dinge sehr kompliziert machen... und nächstes Jahr werde ich über meine Codes dieses Jahr schmunzeln. Das schöne als Hobby VBA-ler ist das man immer wieder was lernt.

Gruess
Raphael


  

Betrifft: AW: xlCalculationAutomatic, EnableEvents, Screenup von: Daniel
Geschrieben am: 27.06.2014 21:42:06

noch ein Tipp:
als Hobbybastler, der seinen Code in dem meisten Fällen selbst verwendet, sollte man auf das On Error Got Failure verzichten und die Einstellungen lieber wieder von Hand rückgäng machen, wenn ein Fehler auftritt.
Dies gilt auch, wenn man Code für andere schreibt, aber man sich noch in er Entwicklungs- oder Testphase befindet
Das On Error Goto Failure baut man erst zum Schluss ein, wenn der Code nach besten Wissen und Gewissen getestet ist und man den Code an andere Weitergibt.

Warum???

ganz einfach.
Wenn ein Fehler auftritt, sollte man natürlichich schnellstmöglich die Fehlerursache finden und beseitigen.
Dazu sind aber neben der Fehlernummer einige weitere Informationen wichtig:
- in welcher Programmzeile passiert der Fehler
- welche Werte haben die Variablen und Zellen, wenn der Fehler passiert.
Diese Informationen vernichtest du aber mit dem On Error Goto Failure und musst dann erst das On Error goto Failure ausbauen und den fehlerverursachenden Vorgang wiederholen.
das kostet halt viel Zeit, während man im anderen Fall den Fehler oft direkt beheben und das Programm fortsetzen kann (wenn man Glück hat).

Gruß Daniel


  

Betrifft: Recht hast du... von: Raphael H
Geschrieben am: 27.06.2014 22:14:31

und trotzdem lohnt es sich eine Fehlerbehandlung einzubauen. Meine Erfahrung (wenn auch nicht gigantisch gross) zeigt das der DAU auch nach dem 500 Testtag noch etwas zustande bringt womit niemand gerechnet hat.



  

Betrifft: AW: Recht hast du... von: Daniel
Geschrieben am: 27.06.2014 22:42:53

naja, sag ich doch.
einbauen, wenn man den Code an andere weiter gibt.
weglassen, wenn man selber damit arbeitet.
Gruß Daniel


  

Betrifft: AW: Recht hast du... von: Dirk
Geschrieben am: 27.06.2014 22:53:04

öh, wat heißt denn DAU?


  

Betrifft: AW: Recht hast du... von: Daniel
Geschrieben am: 27.06.2014 23:16:43

dümmster anzunehmender User.
es gibt natürlich auch den DAP.


  

Betrifft: Abschliessend noch.. von: Raphael_H
Geschrieben am: 27.06.2014 23:57:39

http://de.wikipedia.org/wiki/D%C3%BCmmster_anzunehmender_User

Damit hätten wir die gängigsten Abkürzungen im Zusammenhang Benutzer/Software geklärt. Die Software Ersteller sind ein anderes Thema...

Gruess
Raphael


  

Betrifft: AW: Recht hast du... von: Dirk
Geschrieben am: 28.06.2014 11:48:59

Hab ich jetzt erst gesehen die Antworten. Hehe. Klarer Fall von Eingeweihtensprech. Und ab sofort versteh ich diie Codes!


 

Beiträge aus den Excel-Beispielen zum Thema "Laufzeit verringern"