VBA-Perfomance und StandBy

Forumthread: VBA-Perfomance und StandBy

12.02.2020 14:23:27

AndyO

Hallo,
ich habe größere Liste mit jeweils ~10.000 Datensätzen zu korrelieren. Soweit mir bekannt ist VBA nicht multiprozessortauglich. D.h. Performancesteigerungen durch Hardware bringen nur bedingt etwas. Im Makro selbst sollte ich Grafikoperationen wie sheet.select vermeiden. Das umgehe ich mit worksheet.cells. Zwischendurch kommt mir auch der StandBy in die Quere. Mein Arbeitsrechner hängt an einer Softwareverteilplattform und ich kann keine zusätzliche SW installieren. Ich hab das Gefühl, dass die Abarbeitung im StandBy zum Erliegen kommt. Kann ich den StandBy-Zustand VBA-seitig verhindern? Habt ihr sonstige Vorschläge?
MfG
AndyO

3
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Arrays

12.02.2020 14:31:21

Fennek
AW: VBA-Perfomance und StandBy

12.02.2020 17:44:32

Nepumuk
AW: VBA-Perfomance und StandBy

12.02.2020 23:08:21

Daniel

AW: Arrays

12.02.2020 14:31:21

Fennek

Hallo,
einzelne Zugriffe auf Zellen sind in VBA eher langsam, schneller wird es, wenn man zuerst alle relevanten Zellen auf einmal in ein Array lädt und dann alle Berechnungen im Array durchführt. Die Beschleunigung kann auch ein Faktor 100 sein.
Dann sollte auch Standby kein Problem sein. (oder über Windows abschalten/verlängern)
mfg

AW: VBA-Perfomance und StandBy

12.02.2020 17:44:32

Nepumuk

Hallo Andy,
ein Beispiel:

Option Explicit

Private Declare Sub SetThreadExecutionState Lib "kernel32.dll" ( _
    ByRef esFlags As EXECUTION_STATE)
Private Declare Sub Sleep Lib "kernel32.dll" ( _
    ByVal dwMilliseconds As Long)

Private Enum EXECUTION_STATE
    ES_SYSTEM_REQUIRED = &H1
    ES_DISPLAY_REQUIRED = &H2
    ES_USER_PRESENT = &H4
    ES_AWAYMODE_REQUIRED = &H40&
    ES_CONTINUOUS = &H80000000
End Enum

Public Sub Beispiel()

    Dim lngRow As Long

    'Energiesparmodus und Bildschirmschoner unterdruecken
    Call SetThreadExecutionState(EXECUTION_STATE.ES_SYSTEM_REQUIRED Or _
        EXECUTION_STATE.ES_DISPLAY_REQUIRED Or EXECUTION_STATE.ES_CONTINUOUS)

    'nur damit Excel beschaeftigt ist
    For lngRow = 1 To Rows.Count
        Cells(lngRow, 1).Value = lngRow
        Call Sleep(100) 'damit es nicht ganz so schnell geht ;-)
    Next

    'Normalzustand des Rechners wiederherstellen
    Call SetThreadExecutionState(EXECUTION_STATE.ES_CONTINUOUS)

End Sub

Gruß
Nepumuk

AW: VBA-Perfomance und StandBy

12.02.2020 23:08:21

Daniel

Hi
Ich habe meinen Rechner in den Energiesparoptionen so eingestellt, dass er im Netzbetrieb immer angeschaltet bleibt und auch kein Energiesparmodus aktiviert wird, lediglich die Bildschirm Abschaltung bleibt aktiv.
Dann kann man den Rechner auch mal über Nacht rödeln lassen.
Zum korrelieren von Datensätzen ist es oft hilfreich, diese nach dem ID-Begriff aufsteigend zu sortieren.
Dann kann man zum Suchen der Position einer ID die Funktion VERGLEICH (in VBA APPLICATION.MATCH) mit 3.Parameter =1 verwenden und die ist um Welten schneller als die normale Suche in unsortierten Listen.
Auch kann es beim Umgang mit solchen Listen nicht schaden, sich mal mit dem Dictionary-Obejekt vertraut zu machen, das erlaubt auch schnelles Arbeiten in großen Datenmengen.
Gruß Daniel

Infobox / Tutorial

Verbesserung der VBA-Performance und Vermeidung von Standby-Problemen

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Um den Standby-Zustand Deines Rechners während der Ausführung von VBA-Makros zu verhindern, kannst Du die Windows-API SetThreadExecutionState verwenden. Hier ist eine Schritt-für-Schritt-Anleitung:

VBA-Editor öffnen: Drücke ALT + F11, um den VBA-Editor zu öffnen.
Modul einfügen: Klicke mit der rechten Maustaste auf „VBAProject (DeinWorkbookName)“ und wähle „Einfügen“ > „Modul“.

Code einfügen: Kopiere den folgenden Code in das Modul:

Option Explicit

Private Declare Sub SetThreadExecutionState Lib "kernel32.dll" (ByRef esFlags As EXECUTION_STATE)

Private Enum EXECUTION_STATE
    ES_SYSTEM_REQUIRED = &H1
    ES_DISPLAY_REQUIRED = &H2
    ES_CONTINUOUS = &H80000000
End Enum

Public Sub Beispiel()
    ' Energiesparmodus und Bildschirmschoner unterdrücken
    Call SetThreadExecutionState(EXECUTION_STATE.ES_SYSTEM_REQUIRED Or _
                                  EXECUTION_STATE.ES_DISPLAY_REQUIRED Or _
                                  EXECUTION_STATE.ES_CONTINUOUS)

    ' Beispielhafte Schleife
    Dim lngRow As Long
    For lngRow = 1 To Rows.Count
        Cells(lngRow, 1).Value = lngRow
        Call Sleep(100) ' Damit es nicht ganz so schnell geht ;-)
    Next lngRow

    ' Normalzustand des Rechners wiederherstellen
    Call SetThreadExecutionState(EXECUTION_STATE.ES_CONTINUOUS)
End Sub

Makro ausführen: Führe das Makro aus, um die oben genannte Funktionalität zu testen. Der Rechner sollte während der Ausführung nicht in den Standby-Modus wechseln.

Häufige Fehler und Lösungen

Fehler: „Sub oder Funktion nicht definiert“
Lösung: Stelle sicher, dass Du die Sleep-Funktion ebenfalls deklariert hast, falls Du sie verwendest. Füge diese Zeile hinzu:
```
Private Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)
```
Problem: Der Rechner geht trotzdem in den Standby-Modus
Lösung: Überprüfe die Energiesparoptionen Deines Rechners. Stelle sicher, dass alle Einstellungen so konfiguriert sind, dass der Rechner nicht in den Energiesparmodus wechselt, während Du das Makro ausführst.

Alternative Methoden

Eine Alternative zur Verwendung von SetThreadExecutionState ist das Anpassen der Energiesparoptionen Deines Rechners:

Gehe zu den Energieoptionen in der Systemsteuerung.
Wähle Energiesparplan ändern und passe die Einstellungen an, um den Standby-Zustand zu deaktivieren.
Du kannst auch den Bildschirm nach einer längeren Zeitspanne abschalten lassen, während der Rechner aktiv bleibt.

Praktische Beispiele

Hier ist ein Beispiel zur Optimierung der Datenbearbeitung:

Lade alle relevanten Zellen in ein Array, bevor Du mit Berechnungen beginnst. Dies kann die Verarbeitungsgeschwindigkeit erheblich erhöhen.
```
Dim dataArray As Variant
dataArray = Range("A1:A10000").Value ' Beispiel für 10.000 Datensätze
```
Führe Berechnungen im Array durch und schreibe die Ergebnisse zurück, um Zeit zu sparen.

Tipps für Profis

Vermeide direkte Zellzugriffe: Lade Daten in ein Array, bevor Du Berechnungen durchführst. Das kann die Ausführungsgeschwindigkeit von VBA-Makros erheblich steigern.
Nutze das Dictionary-Objekt: Es ermöglicht schnelles Arbeiten mit großen Datenmengen, insbesondere beim Suchen und Sortieren von Datensätzen.
Reduziere Bildschirmaktualisierungen: Verwende Application.ScreenUpdating = False am Anfang Deines Makros, um die Performance weiter zu verbessern.

FAQ: Häufige Fragen

1. Wie kann ich den Standby-Zustand während der Ausführung von VBA-Makros verhindern?
Du kannst die API-Funktion SetThreadExecutionState nutzen, um den Standby-Zustand zu deaktivieren.

2. Welche Excel-Version benötige ich für diese Funktionen?
Die beschriebenen Funktionen sind in den meisten modernen Excel-Versionen (Excel 2010 und später) verfügbar.

3. Was ist der Vorteil der Verwendung von Arrays in VBA?
Die Verarbeitung von Daten in Arrays ist in der Regel schneller als der direkte Zugriff auf Zellen, besonders bei großen Datenmengen.

Beliebteste Forumthreads (12 Monate)