Code Geschwindigkeit abhängig von Filegröße?
17.01.2017 09:53:52
Filegröße?
ich habe mir eine VBA funktion in einem stand - alone file geschrieben. Dieses Makro läuft in ungefähr 10 Sekunden durch und tut was es soll.
Jetzt will ich dieses Funktion in eine anderes, größeres File, in dem auch andere funktionen vorhanden sind, verschieben und dort durchlaufen lassen. Das funktioniert immer noch, aber ist extrem langsam. In 10 Sekunden werden nur sehr wenige Werte berechnet (100 statt 9000). Weiß jemand woran das liegen könnte?
Sub myFunction()
'Get current state of various Excel settings; put this at the beginning of your code
Dim screenUpdateState As Variant, statusBarState As Variant, eventsState As Variant, _
DisplayPageBreaks As Variant
screenUpdateState = Application.ScreenUpdating
statusBarState = Application.DisplayStatusBar
eventsState = Application.EnableEvents
DisplayPageBreaks = ActiveSheet.DisplayPageBreaks 'note this is a sheet-level setting
'turn off some Excel functionality so your code runs faster
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False 'note this is a sheet-level setting
Dim countI As Integer
With Sheets("Tabelle1")
Range(Cells(4, "Y"), Cells(9000, "AB")) = 0
.Cells(1, 1).Clear
For countI = 4 To 9000
If (Evaluate("=MAX(" & Cells(countI, 20).Address(0, 0) & ":" & Cells(countI, 22). _
Address(0, 0) & ")") > 0) Then
If Cells(countI, 20) > 0 Then
If Cells(countI, 21).Value = 0 And Cells(countI, 22) = 0 Then
Cells(5, "C") = Cells(countI, 20)
Cells(6, "C") = Cells(countI, 23)
Cells(7, "C") = 0
Cells(countI, "Y") = Cells(17, "C")
Cells(countI, "Z") = Cells(23, "C")
Cells(countI, "AA") = Cells(31, "C")
Cells(countI, "AB") = Cells(32, "C")
ElseIf Cells(countI, 21).Value > 0 Then
Cells(5, "C") = Cells(countI, 20)
Cells(6, "C") = Cells(countI, 23)
Cells(7, "C") = Cells(countI, 21)
Cells(countI, "Y") = Cells(17, "C")
Cells(countI, "Z") = Cells(23, "C")
Cells(countI, "AA") = Cells(31, "C")
Cells(countI, "AB") = Cells(32, "C")
End If
If Cells(countI, 22).Value > 0 Then
Cells(5, "K") = Cells(countI, 20)
Cells(6, "K") = Cells(countI, "W")
Cells(7, "K") = Cells(countI, 22)
Cells(countI, "Y") = Cells(17, "K")
Cells(countI, "Z") = Cells(23, "K")
Cells(countI, "AA") = Cells(31, "K")
Cells(countI, "AB") = Cells(32, "K")
If Cells(countI, "Z") 0 Then
Cells(6, "G") = Cells(countI, 22)
Cells(countI, "Y") = Cells(13, "G")
Cells(countI, "Z") = 0
Cells(countI, "AA") = Cells(31, "G")
Cells(countI, "AB") = 0
End If
End If
End If
Next countI
'after your code runs, restore state; put this at the end of your code
Application.ScreenUpdating = screenUpdateState
Application.DisplayStatusBar = statusBarState
Application.EnableEvents = eventsState
ActiveSheet.DisplayPageBreaks = DisplayPageBreaks 'note this is a sheet-level setting
Cells(1, 1).FormulaLocal = "=SUMME(AA:AB)*10^-6"
End With
End Sub