Forum: PC-Programmierung Excel Makro beschleunigen


Announcement: there is an English version of this forum on EmbDev.net. Posts you create there will be displayed on Mikrocontroller.net and EmbDev.net.
von Jannes S. (jannesschmunkamp)


Bewertung
0 lesenswert
nicht lesenswert
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

von Torsten C. (torsten_c) Benutzerseite


Bewertung
0 lesenswert
nicht lesenswert
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?

: Bearbeitet durch User
von Irgendwer (Gast)


Bewertung
0 lesenswert
nicht lesenswert
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.

von ge-nka (Gast)


Bewertung
0 lesenswert
nicht lesenswert
Hier etwas optimierter, ob es Sinn macht oder nicht macht aber genau das 
gleiche wie mit dem oberen Code:
1
Dim i As Long
2
Dim intSpalte As Integer
3
If ActiveCell <> "" Then
4
    i = ActiveCell.Row
5
    intSpalte = ActiveCell.Column
6
    Application.ScreenUpdating = False
7
    
8
    'copy wanted variable
9
    Dim valRangeB43
10
    valRangeB43 = Range("B43").Value
11
    
12
    Do
13
    'copy active cell value
14
    Cells(7, 2) = Cells(i, intSpalte)
15
    'paste the value in neighbored column
16
    Cells(i, intSpalte + 1) = valRangeB43
17
    'Go one cell lower, loop
18
        i = i + 1
19
    Loop While Cells(i, intSpalte) <> ""
20
End If
21
    Application.ScreenUpdating = True
22
End Sub

von Sebastian R. (lange_leitung)


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

Gruß
Sebastian

von Pascal (Gast)


Bewertung
0 lesenswert
nicht lesenswert
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

von Sebastian R. (lange_leitung)


Bewertung
0 lesenswert
nicht lesenswert
Wenn er aber sehr weit zählen will, braucht man schon long.

von Sebastian R. (lange_leitung)


Bewertung
1 lesenswert
nicht lesenswert
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

von Pascal (Gast)


Bewertung
1 lesenswert
nicht lesenswert
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

von Torsten C. (torsten_c) Benutzerseite


Bewertung
0 lesenswert
nicht lesenswert
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

Beitrag #5691895 wurde vom Autor gelöscht.
von Jannes S. (jannesschmunkamp)


Bewertung
0 lesenswert
nicht lesenswert
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!

von Torsten C. (torsten_c) Benutzerseite


Bewertung
0 lesenswert
nicht lesenswert
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.

Antwort schreiben

Die Angabe einer E-Mail-Adresse ist freiwillig. Wenn Sie automatisch per E-Mail über Antworten auf Ihren Beitrag informiert werden möchten, melden Sie sich bitte an.

Wichtige Regeln - erst lesen, dann posten!

  • Groß- und Kleinschreibung verwenden
  • Längeren Sourcecode nicht im Text einfügen, sondern als Dateianhang

Formatierung (mehr Informationen...)

  • [c]C-Code[/c]
  • [code]Code in anderen Sprachen, ASCII-Zeichnungen[/code]
  • [math]Formel in LaTeX-Syntax[/math]
  • [[Titel]] - Link zu Artikel
  • Verweis auf anderen Beitrag einfügen: Rechtsklick auf Beitragstitel,
    "Adresse kopieren", und in den Text einfügen




Bild automatisch verkleinern, falls nötig
Bitte das JPG-Format nur für Fotos und Scans verwenden!
Zeichnungen und Screenshots im PNG- oder
GIF-Format hochladen. Siehe Bildformate.
Hinweis: der ursprüngliche Beitrag ist mehr als 6 Monate alt.
Bitte hier nur auf die ursprüngliche Frage antworten,
für neue Fragen einen neuen Beitrag erstellen.

Mit dem Abschicken bestätigst du, die Nutzungsbedingungen anzuerkennen.