Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1580to1584
1580to1584
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

VBA Code Optimieren Beschleunigen

Forumthread: VBA Code Optimieren Beschleunigen

VBA Code Optimieren Beschleunigen
19.09.2017 11:33:30
Manuela
Hallo zusammen,
Ich habe da einen Code geschrieben der zwar Funktioniert aber meinem Geschmack nach viel zu lange dauert.
Kann einer den Code so Optimieren das dass ganze etwas beschleunigt ?
Wäre super und sehr lieb von euch.
Gruß
Manuela
Es sind 4 Schaltflächen mit so einer Odernerliste 1-4
Sub Ordnerliste_1()
Dim fs, f, f1, fc, s, i
Application.DisplayAlerts = False
'Spalte Löschen
Range("A2:B1000").Select
Selection.ClearContents
Call Netzlaufwerke_verbinden
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder("\\192.168.2.2\")
Set fc = f.SubFolders
Cells(2, 1) = f
i = 3
For Each f1 In fc
Cells(i, 1) = f1.Name
i = i + 1
Next
i = 3
For Each f1 In fc
Cells(i, 2) = f1.Name
i = i + 1
Next
'Alles nach dem _ wird entfernt
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="_", FieldInfo:=Array(Array(1, 1), Array(2, 9), Array(3, 9)), _
TrailingMinusNumbers:=True
Range("A1").Select
Application.DisplayAlerts = True
Call Netzlaufwerke_trennen
End Sub


Sub Netzlaufwerke_verbinden()
Dim objFSO As Object, objNetzwerk As Object
On Error Resume Next
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objNetzwerk = CreateObject("WScript.Network")
objNetzwerk.MapNetworkDrive "W:", "\\192.168.2.2\", False, "XX", "XXX"
objNetzwerk.MapNetworkDrive "X:", "\\192.168.2.19\", False, "XX", "XXX"
objNetzwerk.MapNetworkDrive "Y:", "\\192.168.2.1\", False, "XX", "XXX"
objNetzwerk.MapNetworkDrive "Z:", "\\192.168.2.107\", False, "XX", "XXX"
On Error GoTo 0
DoEvents
If objFSO.DriveExists("W:") Then
End If
If objFSO.DriveExists("X:") Then
End If
If objFSO.DriveExists("Y:") Then
End If
If objFSO.DriveExists("Z:") Then
End If
Set objNetzwerk = Nothing
Set objFSO = Nothing
End Sub


Sub Netzlaufwerke_trennen()
Dim objNetzwerk As Object
Set objNetzwerk = CreateObject("WScript.Network")
On Error Resume Next
objNetzwerk.RemoveNetworkDrive "W:"
objNetzwerk.RemoveNetworkDrive "X:"
objNetzwerk.RemoveNetworkDrive "Y:"
objNetzwerk.RemoveNetworkDrive "Z:"
Set objNetzwerk = Nothing
End Sub

Anzeige

4
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: VBA Code Optimieren Beschleunigen
19.09.2017 11:48:16
UweD
Hallo
ungetestet mal einige Kleinigkeiten
Application.DisplayAlerts = False
Application.ScreenUpdating = False 'hinzufügen: verhindert das dauernde Bildschirmzucken


Range("A2:B1000").Select
Selection.ClearContents
wird zu 

Range("A2:B1000").ClearContents
und analog 

Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
ggf bringt das noch was.
    If objFSO.DriveExists("W:") Or objFSO.DriveExists("X:") Or _
objFSO.DriveExists("Y:") Or objFSO.DriveExists("Z:") Then End If

Anzeige
AW: VBA Code Optimieren Beschleunigen
19.09.2017 13:23:00
Manuela
Vielen Danke UweD,
hat nur minimal was gebracht.
Und durch das "Application.ScreenUpdating = False" ist nicht mehr so schön ersichtlich, ob es fertig ist.
Wenn ich die vier Makros über ein Makro auslöse, dauert das schon so 15sec.
Kann man die Makros gleichzeitig statt hintereinander starten ?
Evtl. würde dies dann das ganze beschleunigen. 
Sub Zusammenfassung()
ActiveSheet.Range("$A$1:$C$800").AutoFilter Field:=1
ActiveSheet.Range("$A$1:$C$800").AutoFilter Field:=2
ActiveSheet.Range("$A$1:$C$800").AutoFilter Field:=3
Selection.AutoFilter
Sheets("Ordnerliste").Select
Call Ordnerliste_1
Call Ordnerliste_2
Call Ordnerliste_3
Call Ordnerliste_4
Sheets("Zusammenfassung").Select
End Sub

Anzeige
AW: VBA Code Optimieren Beschleunigen
19.09.2017 13:45:17
Daniel
Hi
ob man das Netzlaufwerke verbinden beschleunigen kann, weiß ich nicht.
dein erster Code hat noch folgendes Optimierungpotential:
1. kein Select (wurde schon erwähnt)
2. größere Datenmengen zuerst in ein Array schreiben und dann das ganze Array in einem Schritt in die Zellen (geht schneller als jede Zelle einzeln zu beschreiben)
3. die erste Spalte braucht nicht dupliziert werden. Wenn diese beim TextToColumns erhalten bleiben soll, dann gib einfach die nächsete Spalte als Ziel ein:
Sub Ordnerliste_1()
Dim fs, f, f1, fc, s, i
Dim arr
Application.DisplayAlerts = False
'Spalte Löschen
Range("A2:B1000").ClearContents
Call Netzlaufwerke_verbinden
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder("\\192.168.2.2\")
Set fc = f.SubFolders
Cells(2, 1) = f
ReDim arr(1 To fc.Count, 1 To 1)
i = 0
For Each f1 In fc
i = i + 1
arr(i, 1) = f1.Name
Next
Cells(3, 1).Resize(UBound(arr, 1), 1) = arr
'Alles nach dem _ wird entfernt
Columns("A:A").TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="_", FieldInfo:=Array(Array(1, 1), Array(2, 9), Array(3, 9)), _
TrailingMinusNumbers:=True
Application.DisplayAlerts = True
Call Netzlaufwerke_trennen
End Sub
Gruß Daniel
Anzeige
AW: VBA Code Optimieren Beschleunigen
19.09.2017 14:23:19
Manuela
Vielen Danke Daniel,
bin somit immerhin 5 Sekunden Schneller.
Denke der Netzwerkaufbau braucht viel Zeit
und das die Makros nacheinander laufen, anstatt gleichzeitig.
Gruß
Manuela
;
Anzeige

Infobox / Tutorial

VBA Code Optimieren und Beschleunigen

Schritt-für-Schritt-Anleitung

Um Deinen VBA Code zu optimieren und die Ausführungsgeschwindigkeit erheblich zu steigern, kannst Du folgende Schritte befolgen:

  1. Bildschirmaktualisierung deaktivieren: Füge Application.ScreenUpdating = False hinzu, um das ständige Bildschirmflackern zu verhindern.
  2. Vermeide die Verwendung von Select und Selection: Dies kann die Ausführungsgeschwindigkeit verlangsamen. Verwende stattdessen direkte Zellreferenzen.
  3. Nutze Arrays zur Datenverarbeitung: Schreibe größere Datenmengen zuerst in ein Array und übertrage das Array dann in einem Schritt in die Zellen. Das ist deutlich schneller als jede Zelle einzeln zu beschreiben.
  4. Vermeide unnötige Berechnungen: Setze Application.Calculation = xlCalculationManual am Anfang und Application.Calculation = xlCalculationAutomatic am Ende Deines Codes, um unnötige Berechnungen während der Ausführung zu vermeiden.

Hier ist ein optimierter Beispielcode:

Sub Optimierter_Code()
    Dim fs, f, f1, fc, arr, i
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Range("A2:B1000").ClearContents
    Call Netzlaufwerke_verbinden

    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder("\\192.168.2.2\")
    Set fc = f.SubFolders
    ReDim arr(1 To fc.Count, 1 To 1)

    i = 0
    For Each f1 In fc
        i = i + 1
        arr(i, 1) = f1.Name
    Next

    Cells(2, 1).Resize(UBound(arr, 1), 1) = arr
    Columns("A:A").TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
        Other:=True, OtherChar:="_"

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

    Call Netzlaufwerke_trennen
End Sub

Häufige Fehler und Lösungen

  1. Fehler beim Netzwerkzugriff:

    • Lösung: Stelle sicher, dass die Netzwerkpfade korrekt sind und dass Du die richtigen Berechtigungen hast, um auf die Ordner zuzugreifen.

  2. Langsame Ausführung:

    • Lösung: Überprüfe, ob Du Application.ScreenUpdating, Application.DisplayAlerts und Application.Calculation richtig verwendest, um die Performance zu optimieren.

  3. Fehlerhafte Datenübertragung:

    • Lösung: Vergewissere Dich, dass die Dimensionierung des Arrays korrekt ist und dass Du die Zellen in einem Schritt beschreibst.

Alternative Methoden

  • Parallele Ausführung von Makros: Statt die Makros nacheinander auszuführen, kannst Du sie in separaten Threads laufen lassen. Allerdings ist dies in VBA nicht direkt möglich. Du könntest die VBA-Programmierung so anpassen, dass weniger blockierende Operationen stattfinden.
  • Verwendung von Timer: Nutze Timer, um die Ausführungsdauer Deiner Makros zu messen und so gezielt Optimierungen vorzunehmen, die den größten Effekt haben.

Praktische Beispiele

  1. Ordnerliste generieren: Der oben angegebene Code generiert eine Liste von Unterordnern in einem bestimmten Verzeichnis und beschreibt diese effizient in Excel.

  2. Netzlaufwerke verbinden: Mit den MapNetworkDrive und RemoveNetworkDrive Methoden kannst Du Netzlaufwerke effizient verbinden und trennen.

Hier ist ein Beispiel für das Verbinden von Netzlaufwerken:

Sub Netzlaufwerke_verbinden()
    Dim objNetzwerk As Object
    Set objNetzwerk = CreateObject("WScript.Network")
    On Error Resume Next
    objNetzwerk.MapNetworkDrive "W:", "\\192.168.2.2\"
    ' Weitere Laufwerke hier verbinden
    On Error GoTo 0
End Sub

Tipps für Profis

  • Verwende Application.Calculation und Application.ScreenUpdating strategisch, um die Performance während der Ausführung zu maximieren.
  • Reduziere die Anzahl der For Each-Schleifen, indem Du die Daten in einem Schritt verarbeitest.
  • Nutze DoEvents sparsam, um die Reaktionsfähigkeit der Anwendung nicht zu beeinträchtigen.

FAQ: Häufige Fragen

1. Wie kann ich die Ausführungsgeschwindigkeit meiner Makros messen? Du kannst die Zeitmessung in VBA mit der Timer-Funktion durchführen, um die Laufzeit Deines Codes zu ermitteln.

2. Was ist der Vorteil von Arrays in VBA? Arrays ermöglichen es Dir, mehrere Werte gleichzeitig zu verarbeiten, was die Geschwindigkeit Deiner Makros erheblich steigern kann.

3. Wie kann ich sicherstellen, dass meine Netzlaufwerke immer verbunden sind? Du kannst Deine Netzlaufwerk-Verbindung in einem Startup-Makro einfügen, das beim Öffnen der Arbeitsmappe automatisch ausgeführt wird.

4. Was kann ich tun, wenn mein VBA Code immer noch langsam ist? Überprüfe, ob Du alle oben genannten Optimierungen angewendet hast und analysiere, ob es externe Faktoren gibt, die die Geschwindigkeit beeinträchtigen könnten, wie z.B. Netzwerkgeschwindigkeiten.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige