Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1848to1852
1848to1852
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

VBA - For Schleife extrem langsam

Forumthread: VBA - For Schleife extrem langsam

VBA - For Schleife extrem langsam
23.09.2021 19:17:18
Manuel
Hallo zusammen,
ich durchsuche jetzt seit längerer Zeit Google und diverse Foren, bin aber noch nicht fündig geworden. Mit folgendem Code will ich meine Zeilen in der Tabelle mit Farben versehen. Das funktioniert soweit auch gut, bremst das Makro aber tierisch aus. Kleinere Tabellen sind damit kein Problem, bei Tabellen mit >2.000 Zeilen kann es aber schonmal dauern.
Habe jetzt überlegt die bedingte Formatierung zu nutzen, aber ich hätte an sich gerne fixe Farben, da sich die Werte nicht mehr ändern. Jetzt hoffe ich ihr könnt helfen.
Hier der Code in Kurzversion (es gibt 13 Farben):

For aktuelleZeileNLSim = ErsteZeileNLSimAL To letzteZeileNLSim
If wsNLSim.Cells(aktuelleZeileNLSim, SpalteNachlassgruppe).Value = "B1" Then
wsNLSim.Range(Cells(aktuelleZeileNLSim, ErsteSpalteNLSimPrAL), Cells(aktuelleZeileNLSim, LetzteSpalteNLSimPrAL)).Interior.Color = wsNLSim.Cells(40, 4).Interior.Color
wsNLSim.Range(Cells(aktuelleZeileNLSim, ErsteSpalteNLSimConAL), Cells(aktuelleZeileNLSim, LetzteSpalteNLSimConAL)).Interior.Color = wsNLSim.Cells(40, 4).Interior.Color
ElseIf wsNLSim.Cells(aktuelleZeileNLSim, SpalteNachlassgruppe).Value = "B2" Then
wsNLSim.Range(Cells(aktuelleZeileNLSim, ErsteSpalteNLSimPrAL), Cells(aktuelleZeileNLSim, LetzteSpalteNLSimPrAL)).Interior.Color = wsNLSim.Cells(41, 4).Interior.Color
wsNLSim.Range(Cells(aktuelleZeileNLSim, ErsteSpalteNLSimConAL), Cells(aktuelleZeileNLSim, LetzteSpalteNLSimConAL)).Interior.Color = wsNLSim.Cells(41, 4).Interior.Color
Else
wsNLSim.Range(Cells(aktuelleZeileNLSim, ErsteSpalteNLSimPrAL), Cells(aktuelleZeileNLSim, LetzteSpalteNLSimPrAL)).Interior.Color = wsNLSim.Cells(52, 4).Interior.Color
wsNLSim.Range(Cells(aktuelleZeileNLSim, ErsteSpalteNLSimConAL), Cells(aktuelleZeileNLSim, LetzteSpalteNLSimConAL)).Interior.Color = wsNLSim.Cells(52, 4).Interior.Color
End If
Next aktuelleZeileNLSim
Falls ihr mehr Infos oder Code braucht, dann gerne Bescheid sagen.
Vielen Dank für Eure Hilfe vorab.
Liebe Grüße,
Manuel
Anzeige

6
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: VBA - For Schleife extrem langsam
23.09.2021 19:28:43
PawelPopolski
Da es nur einen Ausschnitt aus dem Code gibt hier ein Schuss ins Blaue:
Setze Folgendes and den Anfang deines Codes:
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Und ans Ende:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Das sollte ein paar Sekunden rausholen.
Anzeige
AW: VBA - For Schleife extrem langsam
23.09.2021 19:33:36
ChrisL
Hi Manuel
Ich glaube nicht, dass eine wesentliche Beschleunigung möglich ist. Darfst die Frage aber gerne wieder als offen markieren.
Ein klein wenig helfen könnte folgende Zeile gleich am Anfang vom Code:

Application.ScreenUpdating = False
Bei mehr als 2000 Zeilen würde ich eine Form der Datenauswertung/Analyse/Filter suchen. Farben bringen ja nur etwas wenn man es sich "durchliest", was bei der Zeilenzahl wenig praktisch ist. Und wenn doch, was spricht denn gegen die bedingte Formatierung. Aber egal, worauf ich eigentlich hinweisen möchte ist, dass du deine Referenzierung sauber durchziehen musst:

wsNLSim.Range(wsNLSim.Cells(aktuelleZeileNLSim, ErsteSpalteNLSimPrAL), wsNLSim.Cells(aktuelleZeileNLSim, LetzteSpalteNLSimPrAL)).Interior.Color = wsNLSim.Cells(40, 4).Interior.Color
cu
Chris
Anzeige
AW: VBA - For Schleife extrem langsam
23.09.2021 19:51:19
Peter
Noch ein paar Ansätze:

Dim col40 As Long, col41 As Long, col52 As Long
With wsNLSim
col40 = .Cells(40, 4).Interior.Color
col41 = .Cells(41, 4).Interior.Color
col52 = .Cells(52, 4).Interior.Color
For aktuelleZeileNLSim = ErsteZeileNLSimAL To letzteZeileNLSim
Select Case .Cells(aktuelleZeileNLSim, SpalteNachlassgruppe)
Case "B1"
.Range(.Cells(aktuelleZeileNLSim, ErsteSpalteNLSimPrAL), .Cells(aktuelleZeileNLSim, LetzteSpalteNLSimPrAL)).Interior.Color = col40
.Range(.Cells(aktuelleZeileNLSim, ErsteSpalteNLSimConAL), .Cells(aktuelleZeileNLSim, LetzteSpalteNLSimConAL)).Interior.Color = col40
Case "B2"
.Range(.Cells(aktuelleZeileNLSim, ErsteSpalteNLSimPrAL), .Cells(aktuelleZeileNLSim, LetzteSpalteNLSimPrAL)).Interior.Color = col41
.Range(.Cells(aktuelleZeileNLSim, ErsteSpalteNLSimConAL), .Cells(aktuelleZeileNLSim, LetzteSpalteNLSimConAL)).Interior.Color = col40
Case Else
.Range(.Cells(aktuelleZeileNLSim, ErsteSpalteNLSimPrAL), .Cells(aktuelleZeileNLSim, LetzteSpalteNLSimPrAL)).Interior.Color = col52
.Range(.Cells(aktuelleZeileNLSim, ErsteSpalteNLSimConAL), .Cells(aktuelleZeileNLSim, LetzteSpalteNLSimConAL)).Interior.Color = col52
End If
Next aktuelleZeileNLSim
End With
Insbesondere die Farben muss man ja nicht 2000 mal auslesen - einmal reicht!
Anzeige
AW: VBA - For Schleife extrem langsam
23.09.2021 19:52:46
Peter
Sorry, unten muss es natürlich "End Select" statt "End If" heißen...
AW: VBA - For Schleife extrem langsam
23.09.2021 20:04:34
Daniel
Hi
1. Grundsatzfehler, den wohl jeder mal machen muss:
Bei Range(Cells(), Cells()) muss das Tabellenblatt vor den beiden Cells stehen, nicht vor der Range!!!!!!!!!!!!!
2. es ist in Excel langsam, jede Zelle einzeln zu färben. du könntest den Autofilter nutzen, um bspw nach "B1" zu filtern um dann diese Zellen alle gemeinsam zu färben:
3. die Grundfarbe, die die meisten Zellen bekommen, weist man zunächst allen Zellen zu und überschreibt sie dann:

dim Spalten as Range
with wsNLSim.UsedRange
Set Spalten = Union(Range(.Columns(ErsteSpalteNLSimPrAL), .Columns(LetzteSpalteNLSimPrAL)), Range(.Columns(ErsteSpalteNLSimConAL), .Columns(LetzteSpalteNLSimConAL)))
Spalten.Interior.Color = wsNLSim.Cells(52, 4).Interior.Color
.Autofilter field:=SpalteNachlassgruppe, criteria1:="B1"
Spalten.SpecialCells(xlcelltypevisible).Interior.Color = wsNLSim.Cells(40, 4).Interior.Color
.Autofilter Field:=SpalteNachlassgruppe, criteria1:="B2"
Spalten.SpecialCells(xlcelltypevisible).Interior.Color = wsNLSim.Cells(41, 4).Interior.Color
... usw
.Autofilter
end with
Code bitte nicht kopiern, den hab ich nur so hingeschrieben ohne Test und Kontrolle.
Bitte Lesen, verstehen und dann mit Hilfe der Intellisense selber schreiben. Danke.
Gruß Daniel
Anzeige
AW: VBA - For Schleife extrem langsam
23.09.2021 21:07:30
Manuel
Hi Daniel,
mega Tipp! Vielen Dank, der hat das Ganze richtig beschleunigt! :-) Habe die Range entsprechend angepasst und jetzt läufts sehr gut!
Vielen Dank und liebe Grüße,
Manuel
;

Forumthreads zu verwandten Themen

VBA_For-Next Schleife ist fehlerhaft
Dirk

Hallo allerseits,
habe da noch ein Problem mit einer Schleife.
Nachstehender CODE soll im TB Eingaben Zeile 1 nach einem Wert aus TB Ausgabe A1 suchen.
Macht er auch, nur zeigt er mir immer erst die msgBox an (nichts gefunden) und wenn ich die dann x-mal bestätige, kommt dann auch i...
06.05.2010 17:41:15
Thread-ID: 1155887
5 Beiträge
VBA|UserForm For-Each-Schleife zählt nicht korrekt
Mario

Hallo zusammen,
bei folgendem Code zählt meine For-Each Schleife fehlerhaft.
Solange die Tabelle entsprechenden Suchwert beinhaltet, wird auch der richtige Wert angegeben.
Selbst bei einem Eintrag wird auch nur 1 angezeigt.
Wenn die Tabelle aber keinen Eintrag mit diesem Suchwe...
10.07.2013 01:38:15
Thread-ID: 1321752
7 Beiträge
"Schleifenfunktion" nur mit Formel, kein VBA
reiner

hallo Leute,
bei Klicken in die CheckBox in Zelle "S17" soll ein Datum aus Spalte "P" (gleiche Zeile) kopiert und eine Zeile tiefer in Zelle "F18" kopiert werden, aber nur wenn gleichzeitig in "H17" mindestens eine "1" oder eine höhere Ziffer steht. Ist diese Zelle leer oder enthält diese l...
20.11.2013 11:02:21
Thread-ID: 1339350
5 Beiträge
For Next Schleife mit integrierter If Schleife
Jano

Hallo liebes Forum,
ich schreibe zurzeit meine Diplomarbeit und muss für diese ein Programm zum Führen eines Nachweises schreiben. Zur Programmierung habe ich mich für VBA entschieden. Bis jetzt habe ich auch alles, mit meinem wenigen VBA-Wissen, so weit lösen können, doch nun komme ich ein...
10.02.2015 11:53:47
Thread-ID: 1408360
4 Beiträge
VBA Formel aus Blatt auslesen und in For-Schleife
Alex

Hallo,
ich möchte für ein größeres Marko verschiedene Formeln (zB. y=2*a) aus einem Arbeitsblatt auslesen und dann über eine For Schleife nutzen. Das Ergebnis soll dann in die Tabelle geschrieben werden. Da die Formel geänderbar sein soll, kann sie leider nicht dirket ins Makro schreiben.<...
13.01.2017 17:11:22
Thread-ID: 1534931
6 Beiträge
S-Verweis Schleife bis Bedingung erfüllt
Lizz

Guten Morgen!
In diesem Forum scheinen viele schlaue Köpfe unterwegs zu sein und ich hoffe auch diesmal, dass mir vielleicht jemand helfen kann.
In einer variierenden Anzahl von Spalten möchte ich einen S-Verweis laufen lassen, bis zur ersten freien Zeile.
Die Funktion soll so lange...
27.07.2017 08:16:02
Thread-ID: 1571000
2 Beiträge
VBA For-Schleife?
Roman

Hallo Leute, wer kann mir weiter helfen?
Und zwar habe ich einen Code geschrieben, der auch wunderbar funktioniert, so wie ich es auch brauche. Private Sub Worksheet_Change(ByVal Target As Range) If [c9] "" Then Exit Sub Else If [j7] "" Then Range("c9").Value = Range("b9").Value - Range(...
24.04.2018 23:30:01
Thread-ID: 1621354
4 Beiträge
VBA - For Next Schleife funktioniert nur im Debug
Arnd-Olav

Hall Freunde,
ich möchte von einem Sheet alle Zeilen auf ein anders Sheet untereinander kopieren, wenn in einer bestimmten Zelle der Zeile "Send RFQ" steht.
Er macht dieses aber immer nur für die ersten beiden Zeieln auf dem Sheet, die er mit dieser Bedingung findet.
Ich find den Fe...
04.06.2019 12:23:23
Thread-ID: 1695976
10 Beiträge
Anzeige
VBA: in For-Schleife das aktuelle Sheet ausweisen
Peter

Hallo Excelforum, in einer For-Schleife werden die Sheets durchlaufen und unterliegen jeweils einer Prüfung. Wenn nun ein Fehler auftritt, möchte ich in einer MsgBox auch das aktuelle Sheet ausweisen (s. unten fett markiert).
Bitte um Ergänzung des Code-Schnipsel.
Danke
Peter
Fo...
29.08.2019 14:19:41
Thread-ID: 1710243
4 Beiträge
Anzeige
VBA Anfänger Problem (Fortlaufende Schleife)
pascal_h

Liebe ForumsbesucherInnen,
Ich möchte eine anscheinend simple Berechnung mit VBA durchführen, komme aber nicht weiter.
Ausgangslage: Habe in Spalte B eine fortlaufende Zeitreihe (Format z.B.: 12.02.2000 15:12:03). In A1 steht die Zeit 00:10:00 als Interval.
Ziel: Nun möchte ich, da...
11.07.2005 10:51:42
Thread-ID: 635483
5 Beiträge
VBA For-next Schleife: brauche kleinen Denkanstoss
Klaus

Hallo Leute,
Ich hab mir mit Codefetzen aus der Recherche einen VBA Code zur Ermittlung der letzten benutzten Zeile gebastelt.
(Erklärung: Die Liste fängt in Zeile 11 an, darum k2=11. In Spalte 9 und Spalte 16 stehen bis ans Listenende kopierte Formeln, darum sollen die bei der Ermittlu...
16.09.2005 09:40:19
Thread-ID: 668117
4 Beiträge
VBA, For/Next mit IF/Then
H.

Hi Leute,
folgendes Problem habe ich:
Mein geposteter Ausschnitt eines Makros soll ca. 3000 Zeilen nach einem bestimmten Datum durchsuchen. Sobald das Datum gefunden wird, soll eine Sub aufgerufen werden. Leider scheint es so zu sein, dass sobald er einmal ein Datum gefunden hat, nicht...
18.04.2007 10:21:00
Thread-ID: 863199
5 Beiträge
Anzeige
Anzeige

Infobox / Tutorial

VBA-Optimierung: Schnelleres Färben von Zellen in Excel

Schritt-für-Schritt-Anleitung

Um die Geschwindigkeit deiner For-Schleife beim Färben von Zellen in Excel zu optimieren, kannst du die folgenden Schritte befolgen:

  1. Verwende Application.ScreenUpdating und Application.Calculation: Füge am Anfang deines Codes folgende Zeilen ein, um die Berechnung und die Bildschirmaktualisierung zu deaktivieren:

    Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

  2. Optimiere die Zellenreferenzierung: Stelle sicher, dass du die Referenzierung sauber durchziehst, indem du das Tabellenblatt direkt angibst:

    wsNLSim.Range(wsNLSim.Cells(...), wsNLSim.Cells(...))

  3. Vermeide redundante Farbzuweisungen: Definiere die Farben vor der Schleife:

    Dim col40 As Long, col41 As Long, col52 As Long
With wsNLSim
   col40 = .Cells(40, 4).Interior.Color
   col41 = .Cells(41, 4).Interior.Color
   col52 = .Cells(52, 4).Interior.Color
End With

  4. Nutze Select Case statt If...ElseIf: Dies verbessert die Lesbarkeit und kann die Performance erhöhen:

    Select Case .Cells(aktuelleZeileNLSim, SpalteNachlassgruppe)
   Case "B1"
       ' Farbzuweisungen
   Case "B2"
       ' Farbzuweisungen
   Case Else
       ' Farbzuweisungen
End Select

  5. Aktiviere die Bildschirmaktualisierung und Berechnung wieder: Am Ende deiner Schleife solltest du die vorher deaktivierten Einstellungen wieder aktivieren:

    Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

Häufige Fehler und Lösungen

  • Fehler bei der Referenzierung: Achte darauf, dass du wsNLSim vor beiden Cells()-Aufrufen angibst, nicht nur vor Range().

  • Langsame Ausführung: Wenn das Makro auch nach Optimierungen zu langsam ist, könnte es hilfreich sein, die Verwendung von bedingter Formatierung in Erwägung zu ziehen, insbesondere bei großen Datenmengen.

  • Falsche Farbzuweisungen: Überprüfe, ob die richtigen Farben den richtigen Bedingungen zugewiesen werden. Ein Select Case kann hier Klarheit schaffen.

Alternative Methoden

  • Bedingte Formatierung: Wenn du fixe Farben verwendest, könnte die bedingte Formatierung eine schnellere Alternative sein, vor allem bei großen Tabellen.

  • Autofilter verwenden: Du kannst den Autofilter verwenden, um alle Zellen, die einer bestimmten Bedingung entsprechen, gleichzeitig zu färben. Das reduziert die Anzahl der Zellen, die einzeln bearbeitet werden müssen.

Praktische Beispiele

Hier ist ein einfaches Beispiel zur Verwendung des Autofilters:

Dim Spalten As Range
With wsNLSim.UsedRange
    Set Spalten = Union(Range(.Columns(ErsteSpalteNLSimPrAL), .Columns(LetzteSpalteNLSimPrAL)), Range(.Columns(ErsteSpalteNLSimConAL), .Columns(LetzteSpalteNLSimConAL)))
    Spalten.Interior.Color = wsNLSim.Cells(52, 4).Interior.Color ' Grundfarbe setzen
    .AutoFilter field:=SpalteNachlassgruppe, criteria1:="B1"
    Spalten.SpecialCells(xlCellTypeVisible).Interior.Color = wsNLSim.Cells(40, 4).Interior.Color
    .AutoFilter Field:=SpalteNachlassgruppe, criteria1:="B2"
    Spalten.SpecialCells(xlCellTypeVisible).Interior.Color = wsNLSim.Cells(41, 4).Interior.Color
    .AutoFilter ' Autofilter zurücksetzen
End With

Tipps für Profis

  • Vermeide das Färben jeder Zelle einzeln: Wenn du viele Zellen gleichzeitig färben musst, setze zuerst eine Grundfarbe und überschreibe dann nur die Zellen, die eine andere Farbe benötigen.

  • Testen und Anpassen: Nutze die IntelliSense-Funktion in VBA, um deinen Code während des Schreibens zu überprüfen und sicherzustellen, dass er fehlerfrei ist.

FAQ: Häufige Fragen

1. Wie kann ich die Ausführungsgeschwindigkeit meines VBA-Codes verbessern? Durch das Deaktivieren der Bildschirmaktualisierung und der automatischen Berechnung während der Ausführung kannst du die Geschwindigkeit erheblich steigern.

2. Was ist der Unterschied zwischen Cells und Range? Cells bezieht sich auf eine bestimmte Zelle, während Range einen Bereich von Zellen beschreibt. Achte darauf, sie korrekt in deinem Code zu referenzieren.

3. Wie kann ich Farben in Excel einfacher verwalten? Definiere deine Farben in Variablen am Anfang deines Codes, um die Lesbarkeit zu verbessern und die Ausführung zu beschleunigen.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige