PDA

Vollständige Version anzeigen : Laufzeit von VBA-Programm


frontloop
21.07.2014, 14:03
Hi,

ich hab ein VBA-Programm, das ich hier leider nicht veröffentlichen darf.
Daher muss die Frage allgemein bleiben:

Und zwar läuft in dem Programm eine Schleife, solange bis alle Zeilen der dazugehörigen Excel-Tabelle durch sind.

Dabei bin ich auf ein interessantes Phänomen gestoßen:
Hat die Excel-Datei 100 Zeilen ist das Programm signifikant schneller, als wenn die Excel-Datei 1000 Zeilen hat.
Und zwar nicht dahingehend, dass natürlich 100 Zeilen schneller erledigt sind, als 1000 Zeilen, sondern bereits innerhalb der Schleife, bei der nur jeweils 8 Zeilen gleichzeitig betrachtet werden.

Also Schleifendurchlauf 1:
Zeilen 1 - 8 werden bearbeitet
Schleifendurchlauf 2:
Zeilen 2 - 9 werden bearbeitet
usw.

Warum ist das so?
Eigentlich müsste die Schleife immer gleich schnell ablaufen, egal wie viele Zeilen die Excel-Datei hat. Weil sich ja der Umfang bzw. die Komplexität der Schleife nicht ändert.

Hat da jemand ne Idee?

MWOnline
21.07.2014, 14:07
Hallöchen,

ohne die Daten und den Quelltext kann man keine korrekte Analyse durchführen. Aber wenn Du 1000 Zeilen verarbeitest und diese beinhaltet komplexere Formeln dann ist es normal, da ja 1000 Zeilen neukalkuliert werden anstatt nur 10 oder 100. Aber wie gesagt... ohne Code und Datengrundlage kann man keine Aussage treffen... denn es gibt viele Möglichkeiten Makros zu beschleunigen oder auch zu verlangsamen (z.B. Verschachtelte Schleifen) ;)

Beste Grüße
Marc

frontloop
21.07.2014, 14:19
wie gesagt: Code veröffentlichen geht leider nicht. Datenschutz/Firmengeheimnis.


Was mir halt nicht einleuchtet ist die reine Durchlaufdauer einer Schleife.
Die müsste ja immer gleich sein, da er da immer die gleichen Daten/Schritte behandelt.

Dass das Programm natürlich ingesamt 10x länger läuft, wenn die Schleife 10x häufiger durchlaufen wird, ist klar.

Aber auch folgendes z.B.:
Ich weiß, dass eine Excel-Datei mit 500 Zeilen 5 Minuten dauert um komplett durchzulaufen.
Wenn ich jetzt die Datei mit 5000 Zeilen laufen lasse und nach 5 Minuten das Skript abbreche, ist er erst bei Zeile 200, obwohl er genausoviele Schritte hätte machen sollen wie bei der kurzen Datei.
Er müsste eigentlich auch bei Zeile 500 sein.

aloys78
21.07.2014, 14:32
Hallo,
Er müsste eigentlich auch bei Zeile 500 sein.
Ja, wenn er immer das Gleiche macht.
Wenn aber zB Daten sammelt und zwischendurch sortiert, dann wird ja der Umfang immer größer. Und wenn es noch suboptimal programmiert ist, kann schon mal was zusammenkommen.

Also ohne Kenntnis des relevanten Codes bleibt das Spekulation.

Du kannst aber mal Messpunkte setzen, zB so
Dim sZeit as Single

sZeit = Timer 'Start Messung

... Code

MsgBox Timer - sZeit

Vielleicht kannst du damit den Verursacher lokalisieren.


Gruß
Aloys

frontloop
21.07.2014, 14:57
Hm. Es geht eigentlich schon gleich am Anfang los.

die ersten Zeilen des Codes nach der Variablendeklaration:
'1. Schritt: 'Kopiere die 8 ersten Zeilen aus Tabellenblatt 1 ins Tabellenblatt 2

zeile = 9
spalte = 1
schon_geschaltet = 0
falsch_geschaltet = 0

Do While zeile < 17
Do While spalte < 9
If spalte = 8 Then
hilfswert = Sheets("Tabelle1").Cells(zeile, spalte).Value
Sheets("Tabelle2").Cells(zeile - 7, spalte) = hilfswert + Sheets("Tabelle1").Cells(zeile, 1).Value / 10000000
Else
hilfswert = Sheets("Tabelle1").Cells(zeile, spalte).Value
Sheets("Tabelle2").Cells(zeile - 7, spalte) = hilfswert
End If
spalte = spalte + 1
Loop
hilfswert = Sheets("Tabelle1").Cells(zeile, 12).Value
Sheets("Tabelle2").Cells(zeile - 7, 9) = hilfswert
spalte = 1
zeile = zeile + 1

Loop


Erst danach beginnt die eigentliche Schleife, die dann den wesentlichen Teil des Programms darstellt.

In dem Code kopiert er nur die ersten 8 Zeilen, die er im 1. Schritt der Schleife dann verwenden soll, vom Tabellenblatt 1 ins Tabellenblatt 2.
Hat das Tabellenblatt 1 insgesamt rund 600 Zeilen geht das "ratzfatz".
Hat das Tebellenblatt 1 insgesamt rund 13.000 Zeilen, kann man ihm schön beim kopieren zusehen.


Setze ich einfach um den Code-Block von oben den Timer wie Du beschrieben hast, gibt er mir bei den 13.000 Zeilen den Wert "57.240" zurück.
Bei 600 Zeilen den Wert: 57.399

Also kanns daran nicht wirklich liegen. Woran aber dann?
Einfach daran, dass es einen Unterscheid macht ob eine 800 kb - Datei oder eine 4,5 MB-Datei offen ist?


Edit:
Setze ich mich mit der Stoppuhr danaben, braucht er für die Zeilen oben bei kleiner Datei kaum 0,4 Sekunden (schneller bin ich nicht ;-) ) und bei großer Datei 3,5 Sekunden (!!!!)

Mc Santa
21.07.2014, 15:12
Hallo,

du kannst versuchen die Berechnung der Tabelle auszuschalten. Vielleicht wird durch die geänderten Zellen dauernd eine Berechnung angestoßen, die alle Zellen einschließt. Das würde bei 13.000 Zellen natürlich länger dauern, als bei 600.

Hoffe es hilft dir.

VG

frontloop
21.07.2014, 15:17
ja. das macht er.

muss er aber auch, da jede VBA-bearbeitete Zeile auswirkungen auf alle anderen Zeilen hat.

Aber beim 1. Schritt - dem reinen Kopieren - sollte da doch noch nichts passieren.

Mc Santa
21.07.2014, 15:19
Doch, auch dann wird eine neue Berechnung angestoßen, wenn sich Formeln auf diese Zellen beziehen.

frontloop
21.07.2014, 17:32
Also auch wenn ich z.B. habe

Zelle A1 = 2
Zelle A2 = 4
Zelle A3 = A1*A2 (=8).

Wenn ich dann per VBA programmiere:
Kopiere Zelle A1 in den Wert "test"
Kopiere den Wert "test" in die Zelle B5

Kann man das irgendwie optimieren? Also nur an bestimmten stellen (z.B. dem Ende der Schleife) neu berechnen?

Mc Santa
21.07.2014, 17:48
Hallo,

in deinem Beispiel nicht.
Ich habe in zb in A1 stehen: Summe(A2:A13000) Wenn sich nun irgendeine Zelle aus A2:A13000 ändert, egal wie, wird die Formel neu berechnet.
Du kannst die automatische Berechnung am Anfang des Makros deaktivieren und am Ende wieder aktivieren. Zusätzlich kannst du innerhalb des Makros eine manuelle Berechnung anstoßen, wenn dies erforderlich ist.

VG

frontloop
22.07.2014, 10:30
also insgesamt läuft es so, dass er im 1. schritt (siehe code oben)
die 8 Zeilen ins Tabellenblatt 2 kopiert.
Im tabellenblatt 2 finden dann einfache Berechnungen (excel-Formeln) mit nur diesen paar Werten statt

die ergebniswerte werden dann wieder ins Tabellenblatt 1 zurückkopiert, wo dann weitere (umfangreiche) Berechnungen per Excel-Formeln erfolgen.

Zum zeitpunkt des 1. kopierens muss er also so gut wie nichts berechnen, braucht aber trotzdem so lang..

RPP63neu
22.07.2014, 10:40
Hallo!
Das siehst Du falsch!
Bei Excel gibt's nur Hopp oder Topp, will heißen, alle Formeln aller Sheets werden gerechnet!
Abhilfe:
Du kannst die Berechnung ausschalten und nur einen Bereich kalkulieren lassen:

Sub TeilBerechnung()
Application.Calculation = xlCalculationManual
Tabelle1.Range("F5:F6").Calculate
End Sub

Gruß, Ralf

EarlFred
22.07.2014, 10:52
Hallo,

es sind immer wieder die gleichen Dinge, die Ursache für langsamen Code sind: Unnütze und vielzählige Zugriffe auf Blattbereiche / Einzelzellen.

Der Code, den Du in Beitrag 5 gepostet hast, beinhaltet schon massenweise davon. Bei mir wären das genau 2 Blattzugriffe:
- Einmal den Bereich vom Blatt 1 KOMPLETT in ein Array transferieren
- Berechnungen innerhalb des Arrays durchführen
- Array KOMPLETT in Blatt 2 eintragen.

Ich weiß, dass eine Excel-Datei mit 500 Zeilen 5 Minuten dauert um komplett durchzulaufen.
Bereits hier schrillen die Alarmglocken: Offenbar passiert "enorm viel" in Deiner Mappe (was auch immer). Wie weit man das optimieren kann, erkennt selbst meine Glaskugel nicht.

Grüße
EarlFred

EarlFred
22.07.2014, 11:04
..Ergänzung zum Vorpost:
Ersatz für Deinen Code aus Beitrag 5:
Dim fVarWerte() As Variant
Dim Zeile As Long, Spalte As Long

fVarWerte = Worksheets("Tabelle1").Cells(9, 1).Resize(17 - 9, 9 - 1).Value

For Zeile = LBound(fVarWerte, 2) To UBound(fVarWerte, 2)
fVarWerte(Zeile, 8) = fVarWerte(Zeile, 8) + fVarWerte(Zeile, 1) / 10000000
Next Zeile

Worksheets("Tabelle2").Cells(2, 1).Resize(17 - 9, 9 - 1).Value = fVarWerte


Achte bei Deinem Code auch mal auf die Formatierung / Einrückungen - macht die Sache einfacher les- und begreifbar.

Grüße
EarlFred

EarlFred
22.07.2014, 12:22
...noch eine Ergänzung:
Wenn Du von vornherein weißt, welchen Bereich Du mit einer Schliefe abarbeiten willst, solltest Du For-Next nehmen, da schneller (und besser lesbar, wie ich finde): Die Schleife weiß zu Beginn, was sie zu tun hat. Bei dem von Dir verwendeten Do-Loop muss die Abbruchbedingung bei jedem Durchlauf geprüft werden.

Option Explicit

Sub test()
Dim i As Long
Dim t1 As Single, t2 As Single

t1 = Timer
For i = 1 To 100000000
Next i
t1 = Timer - t1

i = 0

t2 = Timer
Do While i < 100000000
i = i + 1
Loop
t2 = Timer - t2

MsgBox "For-Next-Schleife: " & t1 & vbLf _
& "Do-Loop-Schleife: " & t2
End Sub

Das mag bei 10 Durchläufen nicht ins Gewicht fallen, aber den Vergleich zum Kleinvieh kennst Du ja.

Grüße
EarlFred