Excel Makro beschleunigen

[Jannes S. (jannesschmunkamp)]

Hallo,

ich suche nach einer Möglichkeit meinen Code zu kürzen um das Makro zu 
beschleunigen. Ich habe nämlich einige Durchläufe für den folgenden loop 
und in jedem step berechnet excel etwa 8000 neue werte (einfache 
summen). die Etwa 300 steps dauern etwa zwei Minuten. Manual calculation 
kann ich leider nicht nutzen, da die neue Berechnung in jedem step 
existentiell für die Funktion ist :D Ich glaube es gibt noch 
Einkürzungspotential im Umgang mit Zellen wie (i, intSpalte). Aber 
leider habe ich keine Lösung gefunden.

Vielen Dank schonmal für die Hilfe :)

Dim i As Long
Dim intSpalte As Integer
If ActiveCell <> "" Then
i = ActiveCell.Row
intSpalte = ActiveCell.Column
Do
    Application.ScreenUpdating = False
'Select and copy active cell value
    Cells(i, intSpalte).Select
    Selection.Copy
'Select and paste value in capacity cell
    Application.Goto Reference:="R7C2"
    ActiveSheet.Paste
'Select and copy wanted variable
    Range("B43").Copy
'Select and paste the value in neighbored column
    Application.Goto Cells(i, intSpalte + 1)
    Selection.PasteSpecial Paste:=xlPasteValues
'Go one cell lower, loop
    i = i + 1
Loop While Cells(i, intSpalte) <> ""
End If
    Application.ScreenUpdating = True
End Sub

[Torsten C. (torsten_c)]

Siehe "Steering clear of copy and paste" unter:
https://www.dummies.com/software/microsoft-office/excel/10-ways-to-speed-up-your-macros/

Erst mit Loop While Cells(i, intSpalte) <> "" die letzte Zeile bestimmen 
und dann alles auf einmal kopieren, mit:

Range(…).Value = Range(…).Value

PS: Vielleicht kannst Du statt mit "Loop While" die letzte Zeile sogar 
bestimmen mit ActiveSheet.UsedRange.Rows.Count?

[Irgendwer (Gast)]

Das "Application.ScreenUpdating = False" lang ein mal am Anfang und muss 
nicht zigmal in der Schleife gemacht werden.

Denn Sinn davon dauernd den Umweg über die Zwischenablage zu gegen kann 
ich hier nicht wirklich erkennen. Ich würde die Werte einfach direkt 
zuweisen.

[ge-nka (Gast)]

Hier etwas optimierter, ob es Sinn macht oder nicht macht aber genau das 
gleiche wie mit dem oberen Code:

Dim i As Long
Dim intSpalte As Integer
If ActiveCell <> "" Then
    i = ActiveCell.Row
    intSpalte = ActiveCell.Column
    Application.ScreenUpdating = False
    
    'copy wanted variable
    Dim valRangeB43
    valRangeB43 = Range("B43").Value
    
    Do
    'copy active cell value
    Cells(7, 2) = Cells(i, intSpalte)
    'paste the value in neighbored column
    Cells(i, intSpalte + 1) = valRangeB43
    'Go one cell lower, loop
        i = i + 1
    Loop While Cells(i, intSpalte) <> ""
End If
    Application.ScreenUpdating = True
End Sub

[Sebastian R. (lange_leitung)]

Ist Dir das schon schnell genug?
Was soll denn eigentlich genau gemacht werden? Was wird summiert?

Gruß
Sebastian

[Pascal (Gast)]

Hallo,

du kannst die application.calculate Funktion nutzen, indem du eine 
Abfrage auf den Status nachschaltest.

with Application
    .calculate
    if not .CalculationState = XLDone then
    DoEvents
    end if
end with

erst wenn die Berechnung abgeschlossen ist geht VBA in den nächsten 
Step.
Ich benutze das um eine Liste von Protokollen abzuarbeiten.

Bei Listenberechnungen finde ich persönlich Zählschleifen besser, da 
hier i nicht separat mit i = i + 1 neu berechnet werden muss. I 
erweitert sich automatisch, nur musst du die letzte verwendete Zelle 
kennen. Ist diese statisch ist es am einfachsten. Ansonsten kann man die 
Letzte verwendete Zelle auch ermitteln lassen.

for i = DeineStartZahl to Range("Wunschspalte").End(xlUp).Row
dein Code
next

Auch muss valRangeB43 nicht gespeichert werden -->
Cells(i, intSpalte + 1) = Range("B43").Value

das sollte nochmal ein wenig bringen.

Dim i as Integer ist Speichereffizienter als Dim i as Long. Da du i nur 
als Zähleinheit nimmst und nur Ganzzahlen vorkommen können ist Long 
überflüssig.
Long benötigt 4 Byte
Integer benötigt 2 Byte


Grüße
Pascal

[Sebastian R. (lange_leitung)]

Wenn er aber sehr weit zählen will, braucht man schon long.

[Sebastian R. (lange_leitung)]

Was bei Excel auch ewig dauert, ist das Schreiben in die Zelle.
Kopier Dir lieber die relevanten Daten in ein Array, mach alle 
Berechnungen, etc. und schreib dann das gesamte Array zurück.

Ich hab das mal für eine Kalkulation gemacht. Das bringt Welten an 
Geschwindigkeit!! Hab leider die Daten grad nicht da, sonst könnte ich 
das noch mit Laufzeiten belegen.

Gruß
Sebastian

[Pascal (Gast)]

Da im ersten Post von ca. 8k Werten die rede war, kam der vorschlag mit 
Integer. Als Array ist es tatsächlich schon deutlich schneller.

Man könnte die Berechnung auch in VBA selber durchführen indem man die 
Formel in den Code implementiert die Ergebnisse containert und 
anschließend in einem einfügt. Die Berechnung findet dann ja 
ausschließlich im Back End statt.

Grüße
Pascal

[Torsten C. (torsten_c)]

Bevor man über Integer oder Long debattiert, sollte man sich erst mal 
auf die effektiveren Verbesserungen konzentrieren:

Sebastian R. schrieb:
> Kopier Dir lieber die relevanten Daten in ein Array, mach alle
> Berechnungen, etc. und schreib dann das gesamte Array zurück.

+ 1

Pascal schrieb:
> … und anschließend in einem einfügt.

+ 1

[Jannes S. (jannesschmunkamp)]

Vielen vielen Dank, für all eure Vorschläge! Da ich VBA hier das erste 
mal benutze und sonst auch eher fachfremd bin habe ich einige für mich 
einfache Verbesserungen eingefügt. Vor allem die Lösung von gen-ka hat 
mir geholfen.Allerdings musste ich den copy Befehl für B43 noch in den 
Loop integrieren, damit es funktioniert. Um mit dem Projekt fertig zu 
werden nehme ich jetzt vorerst diese Lösung. Ich konnte etwa 20% Zeit 
einsparen!

[Torsten C. (torsten_c)]

Jannes S. schrieb:
> die Etwa 300 steps dauern etwa zwei Minuten

Jannes S. schrieb:
> Ich konnte etwa 20% Zeit einsparen

Mit den Vorschlägen von Sebastian und Pascal würde das in wenigen 
Sekunden flutschen, also über 90% Zeit einsparen.