Microsoft Excel

Herbers Excel/VBA-Archiv

Makro wird immer langsamer bis absturz

Betrifft: Makro wird immer langsamer bis absturz von: Mike
Geschrieben am: 10.09.2020 08:31:49

Hallo zusammen,

irgendwie kann ich leider auf meinen letzten Beitrag keine Antworten mehr schreiben daher muss ich das Thema nochmal neu erstellen.

Das nachfolgende Makro soll nach und nach Dateien öffnen (pro KW gibt es eine Datei) und aus den Dateien jeweils von TAB 4 bis 20 aus Zelle D6 den Wert in wsZiel kopieren.
Im Grunde funktioniert das auch (Makro im Einzelschritt läuft Problemlos) aber ich muss fesstellen, dass der Ablauf nach jedem durchlaufenen Workbook langsamer wird bis Excel sich schließlich verabschiedet. Ich habe erst gedacht es könnte mit der Zwischenablage zusammenhängen aber auch das explizite leeren dieser hat nichts gebracht.

Vielleicht kann ja jemand mal drüber schauen ob ihr irgendwo einen mittelgroßen Fehler finden könnt der mir bislang nicht aufgefallen ist.

Ich habe zwischenzeitlich den Tipp von Hanssueli bekommen nach getmorespeed zu schauen aber musste feststellen, dass ich das quasi bereits über call EventsOff/On umgesetzt habe.

Nach wie vor bin ich mir nicht wirklich sicher ob ich vielleicht einen grundlegenden Fehler im eigentlich Ablauf meines Programms habe der letztlich dafür sorgt. Aber soweit ich das sehe sollte der Code als solches doch eigentlich in Ordnung sein oder?

Option Explicit

Sub Stueckzahlen1()

Dim wsZiel As Worksheet
Dim pathQuelle As String
Dim wbQuelle As Workbook
Dim wsQuelle As Worksheet
Dim i As Byte
Dim Kwaktuell As String
Dim strKw As String
Dim z As Byte
Dim t As Byte
Dim strMaschine As String

Call EventsOff

strMaschine = "Smart1"
Set wsZiel = ThisWorkbook.Sheets("Smart1 Mengen")


Kwaktuell = Format(Date, "ww")

    For i = 2 To Kwaktuell - 1
        
        If wsZiel.Cells(i, 2).Value = "" Then
            strKw = wsZiel.Cells(i, 1).Value
            pathQuelle = "\\test.com\Zeiten\" & strMaschine & "\2020\" & strKw & " Zeiten _
                                  " & strMaschine & ".xlsm"
            Set wbQuelle = Workbooks.Open(Filename:=pathQuelle, ReadOnly:=True)
                z = 2
                For t = 4 To 20
                    wbQuelle.Sheets(t).Cells(6, 4).Copy
                    wsZiel.Cells(i, z).PasteSpecial Paste:=xlValues
'                    Application.CutCopyMode = False
                    z = z + 1
                Next t
            wbQuelle.Close SaveChanges:=False
            
        End If


    Next i


Call EventsOn


End Sub
Im call Events steht nur

With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
End With
Das entspricht ja nach meiner Auffasung dem Ansatz von getmorespeed da zu Beginn die Events ausgeschaltet und zum Ende des Makros wieder gestartet werden.

Gruß
Mike

Betrifft: AW: Makro wird immer langsamer bis absturz
von: Nepumuk
Geschrieben am: 10.09.2020 08:50:03

Hallo Mike,

teste mal:

Public Sub Stueckzahlen1()
    
    Dim wsZiel As Worksheet
    Dim pathQuelle As String
    Dim wbQuelle As Workbook
    Dim i As Long
    Dim Kwaktuell As String
    Dim strKw As String
    Dim z As Long
    Dim t As Long
    Dim strMaschine As String
    
    Call EventsOff
    
    strMaschine = "Smart1"
    Set wsZiel = ThisWorkbook.Sheets("Smart1 Mengen")
    
    Kwaktuell = Format$(Date, "ww")
    
    For i = 2 To Kwaktuell - 1
        
        If IsEmpty(wsZiel.Cells(i, 2).Value) Then
            strKw = wsZiel.Cells(i, 1).Text
            pathQuelle = "\\test.com\Zeiten\" & strMaschine & "\2020\" & strKw & " Zeiten " & strMaschine & ".xlsm"
            Set wbQuelle = Workbooks.Open(Filename:=pathQuelle, ReadOnly:=True)
            z = 2
            For t = 4 To 20
                wsZiel.Cells(i, z).Value = wbQuelle.Sheets(t).Cells(6, 4).Value
                z = z + 1
            Next t
            wbQuelle.Close SaveChanges:=False
            Set wbQuelle = Nothing
        End If
    Next i
    
    Set wsZiel = Nothing
    
    Call EventsOn
    
End Sub

Gruß
Nepumuk

Betrifft: AW: Makro wird immer langsamer bis absturz
von: Mike
Geschrieben am: 10.09.2020 10:41:03

Hallo Nepumuk,

mal wieder hast du eine Lösung vorgeschlagen die perfekt funktioniert. Vielen Dank

Aber jetzt würde ich das gerne verstehen damit ich meine Makros zukünftig besser schreiben kann.

Byte nach Long -> hatte anfangs auch Long und zum Testen auf Byte gesetzt aber keinen Unterschied festgestellt.

Format$ -> hat offensichtlich nicht direkt Einfluss auf das Problem aber scheint ja einen Sinn zu haben. Kannst du mir das kurz erklären wozu der $ Zusatz da ist? Ich finde dazu im Netz keine Erklärung.


Der Knackpunkt scheint hier zu liegen
wbQuelle.Sheets(t).Cells(6, 4).Copy
wsZiel.Cells(i, z).PasteSpecial Paste:=xlValues
Was genau macht meine Variante im Gegensatz zu deiner
wsZiel.Cells(i, z).Value = wbQuelle.Sheets(t).Cells(6, 4).Value
so viel schneller?

Bei meinem Code sehe ich im VBA Projekt Fenster auch jeden Sprung von Worksheet zu Worksheet.
Bei deinem Code ist das nicht der Fall. Da sehe ich nur das er die Workbooks öffnet und nach kurzer Zeit wieder schließt. Ist also vom Ablauf überhaupt nicht vergleichbar.


Gruß
Mike

Betrifft: AW: Makro wird immer langsamer bis absturz
von: Daniel
Geschrieben am: 10.09.2020 11:08:46

Hi

beim .Value = .Value werden nur die Werte übertragen.
beim .Copy. PasteSpecial kopiert Excel zunächst mal alles.
außerdem wird beim .PasteSpecial der Zielbereich selektiert.
dh beim .PasteSpecial führt Excel noch ein paar zusätzliche Tätigkeiten aus.

bei ausgeschalteter Bildschirmaktualisierung sollte der Sprung aber nicht zu sehen sein.
alternativ kannst du die Datei auch mit GetObject öffnen.
Dann wird sie im Hintergrund geöffnet und bleibt ausgeblendet.
Nur sollte man sie so nicht speichern, da sie sonst auch beim nächsten Öffnen durch einen anderen Anwender ausgeblendet bleibt, aber da du ja nicht speicherst, kannst du auch GetObject verwenden.

Gruß Daniel

Betrifft: AW: Makro wird immer langsamer bis absturz
von: Nepumuk
Geschrieben am: 10.09.2020 11:28:18

Hallo Mike,

der Unterschied von Format und Format$ ist folgender. Format gibt einen Variant-Untertype String zurück, Format$ einen String. Das ist schneller und benötigt weniger Speicher. Es gibt einige dieser Funktionen. Schau dir mal im Objektkatalog die Klasse Strings an.

Dein Programm ist wahrscheinlich wegen der Zwischenablage in die Knie gegangen. Das umgehe ich indem ich den Wert direkt von Zelle zu Zelle übergebe. Die PasteSpecial-Methode aktiviert die Tabelle.

Gruß
Nepumuk

Betrifft: AW: Makro wird immer langsamer bis absturz
von: Mike
Geschrieben am: 10.09.2020 12:46:56

Danke für die Hilfe und die Erklärung. Auch an Daniel.

PS das mit der Mailadresse war sicher nicht so gewollt oder

Gruß
Mike