Performance von Excel mit Makros
28.01.2015 10:24:16
Excel
ich wollte fragen, wieso excel so ungeheuer lange braucht beim Rechnen mit Makros. Meine Excel Datei hat zwar ca 600 000.
ich zeig euch mal mein Makro:
Sub matheHinzufuegen()
Dim h As Long
'Calculating Server Respond Time
For h = 2 To ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Sheet1").Cells(h, 15).Value = Worksheets("Sheet1").Cells(h, 8) - Worksheets("Sheet1" _
_
_
_
).Cells(h, 5).Value
Next h
'Calculating Passed Time from 1st to 2nd Checkpoint'
For h = 2 To ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Sheet1").Cells(h, 16).Value = Worksheets("Sheet1").Cells(h, 6) - Worksheets("Sheet1" _
_
_
_
).Cells(h, 5).Value
Next h
'Calculating Passed Time from 2nd to 3rd Checkpoint'
For h = 2 To ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Sheet1").Cells(h, 18).Value = Worksheets("Sheet1").Cells(h, 7) - Worksheets("Sheet1" _
_
_
_
).Cells(h, 6).Value
Next h
'Calculating Passed Time from 3rd to 4th Checkpoint'
For h = 2 To ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Sheet1").Cells(h, 20).Value = Worksheets("Sheet1").Cells(h, 8) - Worksheets("Sheet1" _
_
_
_
).Cells(h, 7).Value
Next h
'Calculating Passed Time In Percent'
For h = 2 To ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Sheet1").Cells(h, 17).Value = Round((100 / Worksheets("Sheet1").Cells(h, 15)) * _
Worksheets("Sheet1").Cells(h, 16).Value, 2)
Next h
'Calculating Passed Time In Percent'
For h = 2 To ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Sheet1").Cells(h, 19).Value = Round((100 / Worksheets("Sheet1").Cells(h, 15)) * _
Worksheets("Sheet1").Cells(h, 18).Value, 2)
Next h
'Calculating Passed Time In Percent'
For h = 2 To ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Sheet1").Cells(h, 21).Value = Round((100 / Worksheets("Sheet1").Cells(h, 15)) * _
Worksheets("Sheet1").Cells(h, 20).Value, 2)
Next h
'Using the right Format'
Range("O:O").Select
Selection.NumberFormat = "hh:mm:ss.000"
Range("P:P").Select
Selection.NumberFormat = "hh:mm:ss.000"
Range("R:R").Select
Selection.NumberFormat = "hh:mm:ss.000"
Range("T:T").Select
Selection.NumberFormat = "hh:mm:ss.000"
Range("Q:Q").Select
Selection.NumberFormat = "0.00%"
Range("S:S").Select
Selection.NumberFormat = "0.00%"
Range("U:U").Select
Selection.NumberFormat = "0.00%"
'Add Captions'
Worksheets("Sheet1").Cells(1, 15).Value = "Sever Respond Time"
Worksheets("Sheet1").Cells(1, 16).Value = "Time Passed from 1st to 2nd Checkpoint"
Worksheets("Sheet1").Cells(1, 17).Value = "1st Time Pass in Percent"
Worksheets("Sheet1").Cells(1, 18).Value = "Time Passed from 1st to 2nd Checkpoint"
Worksheets("Sheet1").Cells(1, 19).Value = "2nd Time Pass in Percent"
Worksheets("Sheet1").Cells(1, 20).Value = "Time Passed from 1st to 2nd Checkpoint"
Worksheets("Sheet1").Cells(1, 21).Value = "3rd Time Pass in Percent"
End Sub
Ich brauch dringend hilfe, es dauert wirklich sehr sehr lange.