Live-Forum - Die aktuellen Beiträge
Datum
Titel
17.10.2025 17:57:35
Zellen-Name aus A1 nach H1 übertragen mit Prä- + Suffix
17.10.2025 11:36:58
verschachteltes Dropdown-Menü
17.10.2025 10:28:49
Call Anweisung
16.10.2025 18:38:17
Zusammenfassung nach Personalnummer
Keine passende Antwort im Forumarchiv gefunden?
Stelle deine Frage im Live-Forum
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1560to1564
1560to1564
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Performance verbessern

Forumthread: Performance verbessern

Performance verbessern
06.06.2017 15:40:17
Jann
Hallo,
Ich benutze seit Donnerstag Excel 2016 (Vorher 2003). Durch die Umstellung (zumindest ist das vermutlich der Grund) ist mein kleines Programm erheblich langsamer geworden. Ich habe hier mal beispielhaft eine Funktion aus dem Programm gepostet.
Auf dem Tabellenblatt "Formular" ist eine Art Eingabemaske. In einem weiteren Tabellenblatt "Fragen" ist eine Liste von 400 Zeilen mit Parametern, mit denen das Tabellenblatt "Formular" befüllt wird, bzw. welche durch Daten aus der Eingabemaske selbst befüllt wird. "Fragen" dient also quasi als Datenbank.
Die Funktion "vor()" sucht in der Tabelle "Fragen" nach den nächsten Daten zum Befüllen der Eingabemaske, abhängig von den aktuellen Daten, dem "Bearbeiter" und der "Sparte" (Globale Variablen).
Außerdem werden abhängig von den Daten Hyperlinks zu bestimmten Dateien eingefügt.
Da ich kein VBA-Profi bin, wollte ich mal grundsätzlich fragen, ob mein Code totaler Mist ist oder zumindest an der ein oder anderen Stelle verbessert werden kann um die Geschwindigkeit des Programms zu erhöhen.
Außerdem habe ich das Gefühl, dass das "Application.ScreenUpdating = False" kaum Auswirkungen hat. Mache ich da einen Fehler?
Habt ihr andere Tipps um die Performance zu verbessern?
Vielen Dank schon im Voraus. Ich hoffe ihr könnt mir helfen.

Sub vor()
Dim i As Integer
Dim j As Integer
Application.ScreenUpdating = False
Call Speichern
Call BlattschutzAus
Call Datei_Anzeige_löschen
'Zeile suchen:
For i = 2 To 424
If Worksheets("Fragen").Cells(i, 1) = Worksheets("Formular").Cells(4, 4) And Worksheets( _
_
"Fragen").Cells(i, 4) = Worksheets("Formular").Cells(4, 2) Then
Exit For
End If
Next i
j = i + 1
If j = 425 Then j = 2
Do
If Worksheets("Fragen").Cells(j, 6) = Bearbeiter Or Bearbeiter = "" Then
If Worksheets("Fragen").Cells(j, 4) = sparte Or sparte = "" Then
With Worksheets("Formular")
.Cells(4, 6).Interior.Color = Worksheets("Fragen").Cells(j, 6).Interior.Color
.Cells(4, 2) = Worksheets("Fragen").Cells(j, 4)
.Cells(4, 6) = Worksheets("Fragen").Cells(j, 6)
.Cells(4, 4) = Worksheets("Fragen").Cells(j, 1)
.TextBox3.Value = Worksheets("Fragen").Cells(j, 7)
.TextBox2.Value = Worksheets("Fragen").Cells(j, 5)
.Cells(7, 2) = Worksheets("Fragen").Cells(j, 2)
End With
Application.ScreenUpdating = False
Call Dateisuche(Worksheets("Fragen").Cells(j, 3), Worksheets("Fragen").Cells(j, 4))
Call Dateisuche2(Worksheets("Fragen").Cells(j, 1), Worksheets("Fragen").Cells(j, 4)) _
_
Application.ScreenUpdating = True
Exit Do
End If
End If
j = j + 1
If j > 424 Then
j = 2
End If
Loop
Call Blattschutz
Application.ScreenUpdating = True
End Sub
Sub Speichern()
Call BlattschutzAus
Dim i As Integer
For i = 1 To 424
If Worksheets("Fragen").Cells(i, 1) = Worksheets("Formular").Cells(4, 4) And  _
Worksheets("Fragen").Cells(i, 4) = Worksheets("Formular").Cells(4, 2) Then
Worksheets("Fragen").Cells(i, 7) = Worksheets("Formular").TextBox3.Value
Exit For
End If
Next i
Call Blattschutz
End Sub
Sub Datei_Anzeige_löschen()
Application.ScreenUpdating = False
Dim i As Integer
For i = 10 To 24
Worksheets("Formular").Cells(i, 10) = ""
Next i
For i = 32 To 46
Worksheets("Formular").Cells(i, 10) = ""
Next i
Application.ScreenUpdating = True
End Sub

Anzeige

8
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Performance verbessern
06.06.2017 15:50:44
Philipp
Hallo Jann,
ich habe den Code nur kurz überflogen, aber ich vermute stark, dass das Öffnen des Blattschutzes die Performance runterzieht. Ich habe auf der Arbeit auch jede Menge Dateien, bei denen unbedarft mit Blattschutz auf/zu programmiert wurde und die heute dadurch extrem langsam sind. Wahrscheinlich hat es Änderungen in der Verschlüsselungsmethodik gegeben, die es in den alten Versionen noch nicht gab und es deshalb damals flotter ging.
Brauchst du für den Blattschutz zwingend ein Passwort? Ohne Passwort geht es wesentlich schneller. Alternativ kannst du den Code durchgehen und schauen, dass der Blattschutz nicht redundant geöffnet wird. Im Makro "Speichern" wird er bspw. zunächst geöffnet, dann wieder gesetzt und in der nächsten Zeile wird der Blattschutz dann wieder aufgehoben.
Das kann schon mal einige Sekunden bringen, wenn sich das Makro "BlattschutzAus" auf viele Tabellenblätter bezieht.
VG Philipp
Anzeige
AW: Performance verbessern
06.06.2017 16:18:20
Jann
Volltreffer, Vielen Dank.
Ich habe mal das ganze Thema Blatt- und Mappenschutz auskommentiert und die Performance ist erheblich besser geworden, auch wenn sich der Blattschutz nur auf zwei Tabellenblätter bezieht...
Ich werde versuchen bei dem Thema etwas sparsamer zu sein. Mal gucken ob das klappt. Irgendwie muss ich aber einen Weg finden meine "User" davon abzuhalten in der Excel etwas zu verändern was nicht verändert werden soll.
Anzeige
AW: Performance verbessern
06.06.2017 16:13:16
Max2
Hallo,
Die suche am Anfang würde ich eher über ein Range.Find machen.
Das könnte dann so in etwa aussehen: 
Private Function found_Value_in_Row() As Integer
Dim i As Integer
Dim rng As Range
Set rng = Range(Cells(2, 1), .Cells(424, 1))
i = rng.Find(Worksheets("Formular").Cells(4, 4).Value )
If i > 0 Then
Set rng = Range(Cells(2, 4), Cells(424, 4))
i = rng.Find(Worksheets("Formular").Cells(4, 2).Value)
End if
found_Value_in_Row = i
End Function
Und am besten eben in einer Funktion... sonst hat dein Code irgendwann eine Länge und
Größe wo sich keiner mehr zu recht findet.
Anzeige
AW: Performance verbessern
06.06.2017 16:22:56
Jann
Danke für den Vorschlag, das werde ich morgen ausprobieren.
AW: Protect UserInterFaceOnly
07.06.2017 15:19:10
Jann
Danke für den Link. Ausschlaggebend war definitiv der Arbeitsblattschutz. Entsprechende Änderungen haben die Performance jetzt deutlich erhöht.
AW: Performance verbessern
06.06.2017 21:10:06
Daniel
Hi
das Marko "Anzeige löschen" enthält überflüssige Schleifen.
das ganze Makro kannst du mit dieser Befehlszeile ersetzen: 
Worksheets("Formular").Range("J10:J24,J32:J46").ClearContents
desweiteren würde ich nochmal überprüfen, ob du das Marko "Speichern" nicht auch irgendwie ins Hauptmakro integrieren kannst, damit die Schleife For i = 2 To 424 nur 1x laufen muss und nicht 2x.
Was machen die Makros "Dateisuche" und "Dateisuche2"?
vielleicht steckt da ja der große Zeitfresser.
kleiner Tip noch zur Schleife For i = 2 To 424:
Besser als 
IF Bedingung1 AND Bedingung2 THEN

ist 
IF Bedingung1 THEN
If Bedingung2 THEN

das liegt daran, dass im ersten Fall die Bedingung2 immer geprüft wird und im 2. Fall nur dann, wenn schon die Bedingung1 WAHR ist (also wahrscheinlich deutlich weniger oft)
Gruß Daniel
Anzeige
AW: Performance verbessern
07.06.2017 15:18:05
Jann
Danke für die Tipps. Ich habe das auch angewendet.
Ausschlaggebend war definitiv der Arbeitsblattschutz. Entsprechende Änderungen haben die Performance jetzt deutlich erhöht.
;
Anzeige

Infobox / Tutorial

Tipps zur Verbesserung der Excel-Performance

Schritt-für-Schritt-Anleitung

Um die Excel-Performance zu verbessern, gehe wie folgt vor:

  1. Blattschutz optimieren:

    • Überprüfe, ob der Blattschutz notwendig ist. Wenn nicht, entferne ihn oder schränke seine Verwendung ein.
    • Reduziere die Anzahl der Blattschutz-Öffnungen und -Schließungen in Deinem VBA-Code.

  2. Schleifen optimieren:

    • Verwende Range.Find statt Schleifen, um nach Werten zu suchen. Beispiel: 
      Set rng = Worksheets("Fragen").Range("A2:A424")
Set foundCell = rng.Find(What:=Worksheets("Formular").Cells(4, 4).Value)
If Not foundCell Is Nothing Then
' Weiterverarbeitung
End If

  3. Screen Updating:

    • Setze Application.ScreenUpdating = False vor langwierigen Operationen und Application.ScreenUpdating = True danach.

  4. Integriere Makros:

    • Wenn möglich, integriere ähnliche Funktionen in ein Hauptmakro, um die Anzahl der Schleifendurchläufe zu reduzieren.

  5. Vermeide überflüssige Schleifen:

    • Nutze Range.ClearContents anstelle von Schleifen, um Zellen zu leeren: 
      Worksheets("Formular").Range("J10:J24,J32:J46").ClearContents

Häufige Fehler und Lösungen

  • Problem: Langsame Ausführung durch häufige Blattschutzänderungen.

    • Lösung: Reduziere die Anzahl der Aufrufe der Makros BlattschutzAus und Blattschutz. Mache dies nur einmal zu Beginn und einmal am Ende des Codes.

  • Problem: Veraltete Suchmethoden in VBA.

    • Lösung: Ersetze die Schleife zur Datensuche durch Range.Find, um die Suche effizienter zu gestalten.

Alternative Methoden

  • VBA Performance verbessern:

    • Nutze die Application.Calculation-Eigenschaft, um die Berechnung nur dann durchzuführen, wenn alle Daten bearbeitet sind: 
      Application.Calculation = xlCalculationManual
' Operationen
Application.Calculation = xlCalculationAutomatic

  • Datenbankfunktionen:

    • Wenn Deine Datenbankstruktur komplex ist, ziehe in Betracht, Excel-Datenbankfunktionen wie DSUM oder DGET zu verwenden, um die Performance zu steigern.

Praktische Beispiele

Hier sind einige praktische Anwendungen zur Performance-Verbesserung:

  1. Optimierung des Speicherns:

    Sub SpeichernOptimiert()
   Dim i As Integer
   Dim wsFragen As Worksheet
   Set wsFragen = Worksheets("Fragen")
   For i = 1 To 424
       If wsFragen.Cells(i, 1) = Worksheets("Formular").Cells(4, 4) Then
           wsFragen.Cells(i, 7) = Worksheets("Formular").TextBox3.Value
           Exit For
       End If
   Next i
End Sub

  2. Effiziente Datenlöschung:

    Sub DatenLoeschen()
   Worksheets("Formular").Range("J10:J24,J32:J46").ClearContents
End Sub

Tipps für Profis

  • Verwende Array-Operationen für große Datenmengen, um die Performance zu steigern.
  • Setze Markierungen für große Datenmengen, damit Du nur die relevanten Teile bearbeitest.
  • Halte Deine Makros modular, um die Wartbarkeit und Lesbarkeit zu erhöhen.

FAQ: Häufige Fragen

1. Wie kann ich die Performance in Excel generell verbessern?
Verwende effiziente Formeln, reduziere die Anzahl der Zellen, die auf Berechnungen angewiesen sind, und optimiere VBA-Code nach den oben genannten Tipps.

2. Welche Rolle spielt der Blattschutz für die Performance?
Der Blattschutz kann die Performance erheblich beeinträchtigen, insbesondere wenn er häufig aktiviert und deaktiviert wird. Überlege, ob er in Deiner Anwendung wirklich notwendig ist.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige