Code schneller machen? Was kann man bei mir hier verbessern?

  • Excel

Es gibt 23 Antworten in diesem Thema. Der letzte Beitrag () ist von petaod.

    Code schneller machen? Was kann man bei mir hier verbessern?

    Hi Leute,

    ich hab ein kleines Makro geschrieben, bei dem ich um 2 for-Schleifen nicht rum komm. Nun ist es so, dass ab 100000 Datensätze das Programm rund 2h laufen würde. Momentan hab ich erst rund 800 Datensätze, aber die 100000er-Marke wird mit Sicherheit durchbrochen werden! Nun meine Frage an euch: Wie kann ich den Code schneller machen?!?!? Hier mal mein Code, allerdings nur der Ausschnitt, der für diese Laufzeit verantwortlich ist!:

    Visual Basic-Quellcode

    1. lineCounter = 2
    2. ProjectID_alt = "" 'ProjectID_alt mit Startwert beschreiben
    3. For i = 2 To lastRow
    4. Forms!frmprogress!progressBar0.value = i * lastRow 'Start des Fortschrittbalkens
    5. 'Wenn Bewertung gelöscht wurde, ist Feld mit Angabe über Größe des Projekts leer
    6. If xwsin.Cells(i, 19) = "" Then
    7. ProjectID = xwsin.Cells(i, 4) 'ProjektID (= DG-Nummer) von Excel-Sheet in Variable speichern
    8. ProjectManager = xwsin.Cells(i, 2)
    9. For j = 2 To lastRow
    10. 'Jede ProjectID mit jeder ProjectID vergleichen
    11. If ProjectID = xwsin.Cells(j, 4) Then
    12. 'Wenn aktuelle ProjectID und ProjectID_alt unterschiedlich sind,
    13. 'dann aktuelle ProjectID und ProjectManager in neue Zeile schreiben
    14. If ProjectID <> ProjectID_alt Then
    15. ProjectID_alt = ProjectID
    16. xwsout.Cells(lineCounter, 1) = ProjectID 'ProjectID in Excel-Sheet schreiben
    17. xwsout.Cells(lineCounter, 2) = ProjectManager 'ProjectManager in Excel-Sheet schreiben
    18. lineCounter = lineCounter + 1 'Zeile von PMCompared_filled inkrementieren
    19. Else
    20. xwsout.Cells(lineCounter - 1, 4) = ProjectID_alt
    21. xwsout.Cells(lineCounter - 1, 5) = xwsin.Cells(j, 2)
    22. End If
    23. End If
    24. Next j
    25. End If
    26. Next i

    bandchef schrieb:

    Wie kann ich den Code schneller machen?!?!?

    Kein VBA verwenden ...

    Visual Basic-Quellcode

    1. For i = 2 To lastRow
    2. ...
    3. For j = 2 To lastRow

    Dh du hast eine Komplexizität von O(n^2) ... sowas was ist immer Sch... ;)
    Ich habe zwar Probleme, den "Sinn" zu verstehen (vlt magst du erläutern, WAS du eigentlich genau tun willst und wie die Daten aussehen?), aber ich frage mich, warum j bei 2 starten muss? Warum kann j nicht bei i+1 starten?

    Das Teil vergleicht ein Excel-Sheet mit dem Inhalt einer Datenbank. Kriterium dafür ist eine bestimmte Zelle im Excel-Sheet in der nichts stehen darf. Die äußerste Schleife sucht also jede Zeile im Excel-Sheet durch ob eine von dieser gewissen Zelle dabei ist, die leer ist. Wenn ja, kopiert er mir ProjectManager und ProjectID in zwei Variablen. Und nun kommt die innerste Schleife zum Einsatz. Diese vergleicht nun die gespeicherte ProjectID mit der Project ID aus der DB. Stimmt dann ProjectID und Name aus dem Excel-File nicht mit der ProjectID und ProjectManager mit dem Inhalt der Datenbank überein wird die neue und alt ProjectID bzw. der neue und alte ProjectManager in ein neues Excel-File gegenübergestellt.

    Hab ich das jetzt verständlich geschafft???

    Warum ich mich an zwei Schleifen gemacht habe, ist der Grund, dass die Excel-Liste gegenüber der DB unterschiedlich groß ist. Ich kann quasi nicht von gleichen "Ausmaßen" ausgehen...

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „bandchef“ ()

    naja - so lala. @Topic - wie picoflop sagte: kein VBA verwenden. Naja, jdfs. nur möglichst wenig.
    Also kopiere alle Werte als erstes aus Excel raus in Dotnet-Auflistungsklassen
    Vlt. befüllste sogar gleich ein Dictionary damit, dann brauchst du auch keine Suchschleifen mehr zu coden.

    Man kann Excel-Sheets wie eine Datenbank befragen - das geht über einen bestimmten ODBC-Provider, den Microsoft bereitstellt. Die Abfragen, die du oben beschrieben hast, lassen sich recht einfach mit LINQ formulieren, das geht aber nur mit .NET. Auf dieser Datengrundlage kannst du dann dein neues Excel-Sheet erstellen.

    Um das umzusetzen, müsstest du wie gesagt in .NET entwickeln. Besteht diese Möglichkeit? VBA-Code kann man nämlich nicht einfach "schneller" machen. Unter bestimmten Voraussetzungen kannst du deinen Suchalgorithmus optimieren, aber mit VBA wird die Leistungssteigerung eher gering ausfallen.
    Gruß
    hal2000

    naja. Normalerweise ist VBA durchaus grauenhaft schnell.
    In diesem Fall nur ist der SuchAlgorithmus grauenhaft mies, wegen der Komplexität (s. Post#2), und was auch ausbremst sind die ständigen Grabschereien in die Worksheets.
    Auch in VBA kann das Super-Schnell ablaufen, wenn man die Sheets einmalig ausliest, in geeignete Datenklassen packt, und dann verarbeitet.

    Nur hat .Net halt so fabelhafte Sachen wies Dictionary, das müssteman sich in VBA erst selbst zurechtproggen. Aber auch in VBA kann man den Speed vertausendfachen, wenn man zb. statt des Dictionaries eine BinärSuche verwendet - das wäre noch vergleichsweise einfach selbst zu schreiben (bei Dictionary müsste ich passen).
    Und eben nicht jeden Wert jedesmal wieder neu aussm Worksheet grabbeln.

    Dictionary gibt's "fast" im Standard

    Visual Basic-Quellcode

    1. Set myDic = CreateObject("Scripting.Dictionary")


    Noch ein paar Tipps für Excel-Code-Optimierung:
    Ein MUSS:

    Visual Basic-Quellcode

    1. Application.ScreenUpdating = False
    Nach Berechnung wieder zurücksetzen auf True
    Ein Kann:

    Visual Basic-Quellcode

    1. Application.Calculation = xlCalculationManual
    Am Schluss wieder auf xlCalculationAutomatic
    --
    If Not Program.isWorking Then Code.Debug Else Code.DoNotTouch
    --

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „petaod“ ()

    petaod schrieb:


    Noch ein paar Tipps für Excel-Code-Optimierung:
    Ein MUSS:

    Visual Basic-Quellcode

    1. Application.ScreenUpdating = False
    Nach Berechnung wieder zurücksetzen auf True


    Wo muss ich diese Konstrukte hinschreiben? Jeweils ganz am Anfang, gleich nach den Dimensionierungen und jeweils ganz kurz vor der Sub? Wenn ich mich für das "Kann" auch noch entscheide, wie wäre dann die Reihenfolge? Erst das "Muss" und dann das "Kann" oder umgekehrt?

    Danke!

    am anfang deiner Bearbeitung..
    Reihenfolge ist egal.
    ScreenUpdating schaltet das Update des Monitors aus.
    Das andere die automatische BErechnung von Formeln in der Arbeitsmappe
    mit nem Dictionary(Anzahl Datensätze: 1.048.576):
    Spoiler anzeigen
    Füllen ausm Worksheet 2x85 sec, vergleichen 50 sec mittels folgendem geschribsel auf nem UraltLäppi (Intel DualCore T6600 mit nem Leistungsindex von 5,7)

    Visual Basic-Quellcode

    1. Option Explicit
    3. Sub FillDic(ByRef dic As Object, ByVal ws As Worksheet)
    4. Dim rng As Range
    5. Set dic = CreateObject("Scripting.Dictionary")
    7. For Each rng In ws.Range("A:A")
    8. If Not (rng.Value = "" And rng.Offset(0, 1).Value = "") Then
    9. If Not rng.Value = "" Then
    10. dic.Add rng.Value, rng.Offset(0, 1).Value
    11. End If
    12. Else
    13. Exit For
    14. End If
    15. Next rng
    16. End Sub
    18. Sub testdic()
    19. Dim Start As Single
    20. Dim Ende As Single
    21. Dim Dauer As Single
    22. Dim test As Object
    23. Dim vergleich As Object
    24. Dim nichtDrin As Object
    25. Set nichtDrin = CreateObject("Scripting.Dictionary")
    27. Start = Timer
    28. Call FillDic(test, Worksheets("Tabelle1")) 'Erstes Dictionary füllen
    29. Ende = Timer
    30. Dauer = Ende - Start
    31. MsgBox ("Dictionary gefüllt in " & CStr(Dauer) & " Sekunden.")
    33. Start = Timer
    34. Call FillDic(vergleich, Worksheets("Tabelle2")) 'Zweites Dictionary füllen
    35. Ende = Timer
    36. Dauer = Ende - Start
    37. MsgBox ("Dictionary gefüllt in " & CStr(Dauer) & " Sekunden.")
    39. Start = Timer
    40. Dim k
    41. For Each k In test
    42. If Not vergleich.exists(k) Then nichtDrin.Add k, test(k) 'guggen welche elemente in dem zweiten dic fehlen un in ein neues schreiben was fehlt
    43. Next k
    44. Ende = Timer
    45. Dauer = Ende - Start
    46. MsgBox ("Dictionary verglichen in " & CStr(Dauer) & " Sekunden.")
    48. End Sub

    Dieser Beitrag wurde bereits 4 mal editiert, zuletzt von „FloFuchs“ ()

    Ich hab nun dieses ScreenUpdating ausrobiert. Aber leider kompiliert er mir's nicht! Er bricht auch mit diesem Fehler ab! Das intelliSense schlägt übrigens auch gar kein ScreenUpdating vor! Es gibt nur Screen. Ist es das?

    Der Code sieht so aus:

    Visual Basic-Quellcode

    1. Sub duplicateProjects()
    3. Application.ScreenUpdating = False
    4. Dim rsPM As DAO.Recordset
    5. Dim xwbin As Excel.workbook
    6. Dim xwsin As Excel.Worksheet
    7. Dim xwbout As Excel.workbook
    8. Dim xwsout As Excel.Worksheet
    9. Dim ProjectID$, ProjectID_alt$, ProjectManager$, ProjectManager_alt$, fname$
    10. Dim i As Long
    11. Dim j As Long
    12. Dim lastRow As Long
    13. Dim lineCounter As Long
    15. 'code
    17. End Sub


    Ich hab doch das Statement richtig eingefügt, oder?

    Ich arbeite in Access 2007 mit VBA. Ich drücke den Button im Formuler der mir dieses Makro aufruft und bekomme dann diese Fehlermeldung. Fehler beim kompilieren: Methode oder Datenformat nicht gefunden. Die Zeile ist die, in der das Statemenst steht! Die Fehlermeldung selbst ist ein Popup-Fenster mit gelbem Ausrufezeichen drauf!

    Wie gesagt, das intelliSense schlägt mir ScreenUpdating auch gar nicht vor!