[Excel] VBA Code sehr langsam (Viele Daten)

[Maxx2332]

Moin,


ich hab folgendes Problem:

Ich habe eine ziemlich große Excel Tabelle und in dieser stehen jede Menge Formeln, die ihr Ergebnis nicht weiter verändern, aber immer abgefragt werden und die Tabelle ist ziemlich träge geworden. Nun hatte ich versucht mit folgendem Code, die Formeln durch die Werte zu ersetzen. Das funktioniert auch, allerdings sehr langsam und selbst auf den kleinen Bereich angewedendet dauert es ziemlich lange. Nun umfasst der eigentliche Bereich aber etwa 80000 Zellen. Jemand ne Idee, wie man den Code schneller bekommt oder wie ich das ganze effektiver lösen könnte?

Sub Formelersetzen()
    For Each c In Worksheets("Data").Range("C30:JZ32").Cells
        If c.HasFormula Then _
            If IsNumeric(c.Text) Then _
            c.Value = c.Value
    Next
End Sub

MfG

 

[miac]

Hast Du die automatische Formelberechnung abgeschaltet?

 

[Maxx2332]

Die muss an sein

 

[Physikbuddha]

Probier doch mal ein

Application.ScreenUpdating = False
[...]
Application.ScreenUpdating = True

vor und nach dem Code.

 

[miac]

Ich meine für den Update. Da sollte man das temporär ausschalten können.

Screenupdate sollte natürlich auch helfen.

 

[Physikbuddha]

Da hat miac Recht. Könntest du also beides kombinieren.

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
[...]
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

 

[cc_aero]

Warum nicht einfach ueber "kopieren" und anschließend "Inhalte einfügen" loessen? Geht auch mit vba und dürfte bei weitem schneller sein.

 

[Maxx2332]

Also ich hatte nun die Verbessurgen in den Code eingebaut und ja er war erheblich schneller, allerdings immernoch unfassbar langsam.


Ich benutze VBA, da ich dachte, dass es schneller gehen würde.

Deine Methode hat aber einwandfrei funktioniert und keine 10 Sekunden gedauert.

 

[miac]

Damit hast Du dann aber alles überschrieben. Dein Code erhielt noch eine Einschränkung bezüglich numerische Daten.

 

[Maxx2332]

Hmm, da hast du recht. Ich hab jetzt das Script einfach mal laufen lassen in seiner jetzigen Form und es brauch schon länger als 30 Minuten, dass ist schon etwas happig.

Hat noch jemand ne Idee, wie man es schneller machen könnte, aber eben mit der Einschränkung?

 

[Physikbuddha]

Ich weiß nicht wie du auf 30 Minuten kommst, aber mit ScreenUpdating = False und Calculation = xlCalculationManual kriege ich mit deiner Foreach-Schleife 80.000 Zellen in 3,611 Sekunden bearbeitet.

 

[Maxx2332]

Sub Formelersetzen()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False

    For Each c In Worksheets("Data").Range("C30:JZ280").Cells
        If c.HasFormula Then _
            If IsNumeric(c.Text) Then _
            c.Value = c.Value
    Next
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
End Sub

Implementierung so falsch?

 

[Physikbuddha]

Sieht an sich gut aus. Ich würde beide If-Abfragen noch zusammenfassen:

If C.HasFormula And IsNumeric(C.Text) Then _
    C.Value = C.Value

Erstell doch mal ein neues Arbeitsblatt und lass den Algorithmus doch mal über 80.000 =ZUFALLSZAHL()-Felder laufen. Ist er dann immer noch so langsam?
So habe ich das getestet. Deine Formeln werden sicher komplexer sein, aber die automatische Berechnung ist ja ausgeschaltet.

 

[Maxx2332]

Das geht praktisch instantan, auch bei deutlich mehr Zellen.

 

[cumulonimbus8]

Könnte mir jemand erklären was
c.value = c.value
überhaupt für einen Nutzen haben soll? Ich könnte eine Formel durch eine festen Wert ersetzten. Ich könnte bewusst eine Zelle mit einer Formel versehen. Aber ihren Inhalt zurückzuschreiben?

Liegt auf dem Bereich noch Anderes wie Bedingte Formatierung? Oder sind die Formel da komplex, mit Bezügen usw.? Denn wenn AutoCalc griffe (sollte ja vom Makro lahmgelegt sein) müsste das als Eingabe gewertet werden wegen der bei kreuz-und-quer-Bezügen all das jedes Mal ›druchgerührt‹ würde.

Was soll also diese Übung überhaupt?

CN8

 

[Physikbuddha]

Wenn du Value mit VBA beschreibst, wird der Inhalt der Zelle automatisch durch den festen Wert ersetzt. Prinzip von Getter und Setter.

@Max Kann mir auch nur noch vorstellen, dass es an Sachen wie Formatierungen liegt, oder dass die Datei mittlerweile einfach nen Schuss weg hat. Hab ich selber schon erlebt. Ohne eine Beispieldatei von dir kann ich nur im Dunklen stochern.

 

[Maxx2332]

Es sind ziemlich viele Bezüge und die Formel ist auch nicht wirklich eine Glanzleistung. Wollte mich mal daran setzen mir etwas besseren zu überlegen, aber hatte dazu weder Zeit und Lust, da es ja funktioniert.

Jede Zelle bildet über jede Zelle über sich die Summe.
Hatte auch mal in den Optionen auf manuell gestellt, aber wirklich was gebracht hat dies auch nicht.


Ich lad nachher mal ne Datei hoch.

 

[miac]

Ich habe dein Beispiel jetzt mal in einer VM mit 2 Prozessoren ausprobiert (Laptop) und das Blatt komplett mit Formeln mit numerischen Daten gefüllt..

Wenn die Formelberechnung abgeschaltet ist, läuft dein Makro 1-2 Sekunden und hat alle Zellen geändert, wenn nicht, dauert es tatsächlich etwa 30 Minuten.

 

[Maxx2332]

Hab jetzt ne Beispiel Datei als zip angehängt. Code ist im Spoiler, dachte ich lad mal keine Excel Datei mit Makro hoch.

Meine Excel Datei hat tatsächlich nen Schuss weg, diese Datei hat diesen Fehler soweit ich das sehen kann nicht. Bei dieser Datei hatte ich jetzt keine Zeitmessung durchgeführt, aber ein paar Minuten müsste es auch laufen.

Spoiler

Sub Formelersetzen()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
 
    For Each c In Worksheets("Data").Range("C6:JZ220").Cells
        If c.HasFormula And IsNumeric(c.Text) Then _
            c.Value = c.Value
    Next
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
End Sub

 

[miac]

Also ich kann die Datei nicht entpacken