VBA Makro optimieren
25.07.2022 18:02:34
ExcelVBA
Ich bin recht neu in der Excel-VBA Welt und möchte nun ein bereits bestehendes VBA Makro optimieren.
Genauer gesagt geht es um eine Verkürzung der Rechendauer, da das Makro derzeit ca. eine halbe Stunde in Anspruch nimmt.
Hat einer von euch Tipps für mehr Geschwindigkeit?
Hier der Code:
Private Sub btnOutputTable_Click()
Dim calcState As Integer
Dim lngI As Long
' Freeze screen updating and calculation
Application.StatusBar = "Calculating results..."
Application.ScreenUpdating = False
calcState = Application.Calculation
Application.Calculation = xlCalculationManual
' Delete output sheet
If ActiveSheet.Range("rngPasteDelete").Value = "Ja" Then
Sheets(ActiveSheet.Range("rngPasteSheet").Value).Cells.Delete
End If
' Copy headers to output sheet
Range("rngCopyHeaders").Copy
Sheets(ActiveSheet.Range("rngPasteSheet").Value).Cells(1, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
' Run through loans
For lngI = ActiveSheet.Range("rngFrom").Value To ActiveSheet.Range("rngTo").Value
' Set ID to current loan
Application.StatusBar = "Calculating results: " & lngI & " / " & ActiveSheet.Range("rngTo").Value
ActiveSheet.Range("rngID").Value = lngI
' Calculate
Application.Calculate
' Copy results to output sheet
Sheets(ActiveSheet.Range("rngPasteSheet").Value).Cells(lngI + 1, 1).Resize(1, Range("rngCopyResults").Rows.Count) = Application.WorksheetFunction.Transpose(Range("rngCopyResults").Value2)
Next lngI
' Format outputs
Range("rngCopyResults").Copy
Sheets(ActiveSheet.Range("rngPasteSheet").Value).Cells(1, 1).Resize(ActiveSheet.Range("rngTo").Value + 1, Range("rngCopyResults").Rows.Count).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Sheets(ActiveSheet.Range("rngPasteSheet").Value).Cells(1, 1).Resize(ActiveSheet.Range("rngTo").Value + 1, Range("rngCopyResults").Rows.Count).Font.ColorIndex = xlAutomatic
Sheets(ActiveSheet.Range("rngPasteSheet").Value).Cells(1, 1).Resize(ActiveSheet.Range("rngTo").Value + 1, Range("rngCopyResults").Rows.Count).Borders.LineStyle = xlLineStyleNone
Sheets(ActiveSheet.Range("rngPasteSheet").Value).Cells(1, 1).Resize(1, Range("rngCopyResults").Rows.Count).HorizontalAlignment = xlLeft
' Reset screen updating and calculation
Application.StatusBar = "Resetting view..."
Range("A1").Select
ActiveSheet.Range("rngID").Value = ActiveSheet.Range("rngFrom").Value
Application.Calculate
Application.Calculation = calcState
Application.ScreenUpdating = True
Application.StatusBar = False
Würde es etwas bringen "Application.Enable = False" oder "Application.CutCopyMode = False" einzubauen?Über diese Ausdrücke bin ich während meiner Forums- und Internetrecherche gestolpert.
Ich bin über jegliche Tipps dankbar!
Viele Grüße
Felix