VBA Code sehr langsam - Optimierungen?
06.03.2015 09:34:55
hennerich
in meiner Excel Tabelle werden Soll Stunden und Ist Stunden verglichen. Dazu gibt es verschiedene Projekte mit jeweils einzelnen Vorgängen.
Ein Beispiel:
Projekt: Garten
Vorgang: Pflanzen, Design, Planung
Projekt: Haus
Vorgang: Planung, Aufbau, Qualitätskontrolle
Die Soll Werte befinden sich in einem separaten Tabellenblatt Soll in einer formatierten Tabelle tblSoll.
Die Ist Werte befinden sich in einem anderen Tabellenblatt Ist in einer formatierten Tabelle tblIst.
Pro Monat werden dort die erfassten Ist Werte eingetragen, um sie dann mittels einer Pivot-Tabelle grafisch aufzubereiten.
Nun gibt es ein Problem, welches ich lösen möchte. Mangels der Möglichkeit zwei Pivot-Tabellen als Basis für ein Diagramm zu nutzen habe ich in meiner Ist-Tabelle bereits Spalten für die Berechnung der Differenzen Soll-Ist eingefügt. So schaffe ich eine einzige Datenbasis. Das funktioniert bisher ganz gut.
Bis auf die Tatsache, dass es passieren kann, dass in einem Monat keine Ist Werte für einen bestimmten Vorgang erfasst werden. Daraus folgt, dass in der Privot-Tabelle aufgrund des Vergleiches Soll-Ist auch kein Wert für diesen Vorgang auftaucht und damit mein Diagramm verfälscht wird (ich habe Stunden geplant, diese werden aber nicht angezeigt).
Um dieses Problem zu lösen, wollte ich über einen Workaround (was Besseres ist mir nicht eingefallen) Dummy-Stunden als Ist Werte per VBA einfügen. Das kann schon mal viel werden, denn pro Projekt (ca. 5-20 an der Zahl) und pro Vorgang im Projekt (ca. 4) muss ich für jeden Monat eine Dummy-Zeile einfügen. Damit komme ich schnell auf bis zu 1.000 Zeilen.
Das Makro ist auch soweit fertig und funktioniert, jedoch extrem langsam.
Habt ihr eine Idee, wie ich es schneller bekommen kann?
Sub InsertStandard()
Dim iAnzahl As Integer, iZeile As Integer, iEnde As Integer, iMonat As Integer
Application.ScreenUpdating = False
'** bestimmen der Anzahl der benutzten Zeilen in der Soll-Tabelle
iZeile = ThisWorkbook.Sheets("Soll").Range("A65536").End(xlUp).Rows.Row - 1
With ThisWorkbook.Sheets("Ist")
'** bestimmen der Anzahl der benutzen Zeilen in der Ist-Tabelle
iEnde = .UsedRange.SpecialCells(xlCellTypeLastCell).Row - 1
For iAnzahl = 1 To iZeile
For iMonat = 1 To 12
'** neue Zeile einfügen
.Cells(iEnde + iMonat, 1).EntireRow.Insert
'** Projektname einfügen
.Cells(iEnde + iMonat, 6).Value = Sheets("Soll").Cells(iAnzahl + 1, 1).Value
'** Datum einsetzen
.Cells(iEnde + iMonat, 11).Value = DateSerial(Sheets("Dashboard").Range("Dat").Value, _
iMonat, 1)
'** Dummy Wert für Stunden einsetzen
.Cells(iEnde + iMonat, 12).Value = "0,01"
'** Stundenwert in Zahlenformat umwandeln
.Cells(iEnde + iMonat, 12).NumberFormat = "General"
'** Vorgang einfügen
.Cells(iEnde + iMonat, 18).Value = Sheets("Soll").Cells(iAnzahl + 1, 2).Value
Next iMonat
iEnde = .UsedRange.SpecialCells(xlCellTypeLastCell).Row - 1
Next iAnzahl
End With
Application.ScreenUpdating = True
End Sub
Henri