Autor: Peter Haserodt --- Aus Excel VBA - Gruppe:
TutorialsPerformance (2) - Select Activate Bremsen und ein Beispiel
Autor: Peter Haserodt - Erstellt: -- - Letzte Revision: --Gruppenthema: 4 Folgen 1 2 3 4 Sie sind in Folge:2
Select, Activate und ... auch nur Bremsen und zu 99,99 % unsinnig.
Wenn ich mit dem Makrorekorder arbeite, produziert er einen Code auf Grund meiner Aktivitäten.
Dadurch werden Activates, Selects etc... im Code geschrieben.
Dies muss ich bereinigen, man braucht dies einfach nicht.
In guter Literatur würden Sie folgenden Beispielcode nicht finden:
(Wieder vom im ersten Teil nicht genannten Buchautoren, der tatsächlich in etwa dazu folgendes schreibt:
[... um mehrere Spalten zu löschen benutzen Sie das folgende Makro [...] (Nicht wortgetreu nur sinngemäß)
Dann gibt er dem Leser folgenden Code (um im Tabellenblatt Tabelle1 die Spalten B bis D zu löschen):
Sub SpaltenDeleteHorror()
Sheets("Tabelle1").Activate
Columns("B:D").Select
Selection.Delete Shift:=xlToLeft
End Sub
Wie kann man nur soetwas veröffentlichen, frage ich?
Genau so soll man es nicht machen.
Das aktivieren, selektieren benötigt Zeit, ganz davon abgesehen, dass ich auf einmal das Blatt 1 aktiv habe ohne dies wirklich zu wollen und und und ...
Sub SpaltenDeleteAnders()
Worksheets("Tabelle1").Columns("B:D").Delete Shift:=xlToLeft
End Sub
Es ist ein sich durch die Excelgeschichte fortführender Wahn, dass man Objekte erst selektieren muss, um mit ihnen etwas zu machen.
Verursacht wird dieser Wahn durch den Makrorekorder und Autoren, die den dort produzierten Müll einfach abschreiben ohne Sinn und Verstand.
In fast allen Fällen kann ich darauf verzichten. Dafür habe ich ja Objekte und wiederum Objekte der Objekte
Ein Tabellenblatt kann ich über das Sheetsobjekt (ok, eigentlich besser das Worksheets... aber soll hier mal egal sein) ansprechen.
Dieses Objekt hat wieder das Columnsobjekt zur Verfügung und dieses wiederum stellt mir die Deletemethode bereit.
Das ganze blödsinnge selektieren macht nur eines, nämlich dass es das Selectionobject benutzt und dies darauf ansetzt.
Kommen wir aber nun zu einem Beispiel, anhand dessen wir eine Verfeinerung vornehmen.
(Hier habe ich den Grausamcode selbst erstellt - ich kann dies, wenn ich will, auch
)
Aufgabe soll es sein, per VBA im ersten Tabellenblatt (Tabelle1) in Spalte A von Zeile 1 bis 10000 Zufallszahlen zwischen 1 und 500 einzutragen.
(Die Zeilenzahl 10000 ist gewählt, damit Sie wirklich den Zeitunterschied spüren können)
Beginnen wir mit dem Schrecken, wie wir ihn unter Zuhilfenahme des Makrorekorders eventuell produzieren könnten:
Sub ZufallGanzSchlimm()
Dim i As Long ' wenn wir noch mehr Zeilen wollen
' Int(500 * Rnd + 1) ermittelt eine Zufallszahl zwischen 1 und 500
Worksheets("Tabelle1").Activate
Range("a1").Select
Randomize Timer
Selection.Value = Int(500 * Rnd + 1)
For i = 1 To 9999
Selection.Offset(1, 0).Select
Selection.Value = Int(500 * Rnd + 1)
Next i
End Sub
Als schon gestählte Nichtaktivierer und Selektierer sehen wir schon das Grausen.
Wir fassen jede einzelne Zelle an und weisen ihr die Zufallszahl dann zu.
Man kann richtig schön sehen, wie Excel hier arbeitet.
(Man könnte mit dem Ausschalten der Bildschirmaktualisierung noch etwas schneller werden, aber ...)
Kommen wir nun zu einem Code, der sich doch schon viel besser liest und unserem NoSelect und NoAktivate Gedanken entspricht.
Vergleichen Sie einfach gefühlt die Geschwindigkeit der beiden Codes.
(Beachten Sie die Punkte im Code!)
Sub ZufallSchonGanzGutOder()
Dim i As Long
Randomize Timer
With Worksheets("Tabelle1")
For i = 1 To 10000
.Cells(i, 1).Value = Int(500 * Rnd + 1)
Next i
End With
End Sub
Der normale VBA Anwender wird nun zufrieden sein.
Der Code ist erheblich schneller und tut seine Pflicht.
Aber jemand der sich mit Performance beschäftigt, wird noch nicht zufrieden sein und grübeln, wie dies noch zu beschleunigen wäre.
Tatsächlich muss man dazu einige Tests fahren aber auch von ein paar Möglichkeiten in VBA Kenntnis haben.
Eine ganz entscheidende Rolle spielt dabei, dass man wissen muss, dass in VBA Felder aufgebaut werden können, die ich direkt nach Excel übertragen kann, sofern ich diese richtig dimensioniere und den richtigen Bereich anspreche.
Vor allem muss einem aber klar sein, dass auch das Einzelfüllen von Zellen erheblich Zeit kostet.
Einen sehr guten Vergleich bekommt man, wenn man die gefüllte Spalte 1 kopiert und z.B. in Spalte 2 einfügt.
Verglichen mit der Zeit, die unser Code braucht, geht das RatzFatz. (Und das Ermitteln der Zufallszahl fällt wirklich nicht ins Gewicht)
Wenn man sich dies überlegt hat, kann man auf folgenden Code kommen:
(Beachten Sie wieder die Punkte im Code)
Sub ZufallDirekt()
Dim i As Long, vx(1 To 10000, 1 To 1) As Variant
Randomize Timer
For i = 1 To 10000
vx(i, 1) = Int(500 * Rnd + 1)
Next i
With Worksheets("Tabelle1")
.Range(.Cells(1, 1), .Cells(10000, 1)) = vx
End With
End Sub
Wow - oder?
Die Geschwindigkeit ist enorm.
(Vielleicht gibt es ja noch Möglichkeiten, dies zu beschleunigen, ich würde niemals nie sagen)
Dieses Beispiel sollte zeigen, dass man mit ein wenig Arbeit erheblich an der Geschwindigkeitsschraube drehen kann.
Weitere Artikel der Gruppe: Tutorials Aus Excel VBA
Nach oben