Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1480to1484
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Namen (die Bereiche enthalten) nach sortieren

Namen (die Bereiche enthalten) nach sortieren
15.03.2016 16:29:33
Martin
Hallo und guten Tag.
Ich habe ein Problem, zu welchem ich bis jetzt keine Lösung gefunden habe.
Zusammenfassung:
Meine Datei ist ein Dienstplanungs-Tool
Die Datei hat 4 Blätter. Eines davon ist das Hauptblatt.
Das Hauptblatt hat etwa 150 Zeilen und rund 7000 Spalten.
Diverse Auswertungen (z.B. ein einzelner Monat aus dem ganzen Jahr) nehme ich vor, in dem ich den Bereich (der einen Namen hat) per VBA auf den entsprechenden Monat lege.
Alles funktioniert einwandfrei bis...
Problem:
Ich damit beginne, die Zeilen zu sortieren. z.B. die Zeilen 20-110 (gemäss den Namen der Mitarbeitenden).
Die aller erste Sortierung funktioniert gut und schnell. Die Namen für die Bereiche funktionieren immer noch.
Nach Abschluss der ersten Sortierung wird die Tabelle EXTREM langsam - wenn ich den Monat wechsle (also per VBA die Bereiche der Namen ändere) dauert das ewig - bis hin zum Absturz.
Ich habe schon diverses probiert. Immer das selbe Resultat.
Hat da jemand Erfahrung mit so einem Problem?
Besten Dank und schöne Grüsse
Martin

7
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Namen (die Bereiche enthalten) nach sortieren
15.03.2016 16:47:23
Steve
Hallo Martin,
kann es sein, dass die Datei sich speicherplatzmäßig immer weiter aufbläht? Wenn ja, dann habe ich in der Vergangenheit den selben Fehler bereits gemacht und kann dir hiermit helfen.
Das Problem bei meiner Mappe bestand darin, dass ich den Bereich geändert habe indem ich einfach nur einen neuen Hinzugefügt habe (Workbook.Names.Add) und damit (vermeintlich!!) überschrieben. Hat in meinen Diagrammen auch alles wertmäßig gepasst. Jedoch steckt da offensichtlich mehr dahinter, denn die Datei wurde größer und größer bis sie schließlich defekt war und sich reparieren musste beim nächsten Öffnen. Dann war sie plötzlich wieder klein und funktionierte, Bereiche waren aber weg... . Deshalb ändere ich nur noch die Formel wenn es denn sein muss, arbeite aber vorzugsweise mit Bereich.Verschieben. Wenn du eine Beispielmappe hochlädts, kann ich dir auch dabei helfen.
lg Steve

Anzeige
AW: Namen (die Bereiche enthalten) nach sortieren
15.03.2016 18:24:42
Martin
Hallo Steve
Vielen Dank! Das tönt nach Erfahrung mit dem selben Problem...
Ich werde das so bald wie möglich ausprobieren.
Du hörst von mir (werde in den kommenden Tagen aber noch unterwegs sein).
Beste Grüsse
Martin

AW: Namen (die Bereiche enthalten) nach sortieren
16.03.2016 08:11:00
Martin
Guten Morgen Steve und alle Anderen die mir hier helfen.
Ich habe die "Symptome" der Störung weiter abgeklärt und eingegrenzt: Es scheint genau das Problem zu sein, welches du auch hattest.
Es funktioniert problemlos bis zu dem Punkt an dem diese Zeile (nach dem ersten "Sortieren") abgearbeitet wird:
ActiveWorkbook.Names.Add Name:="Master_AktuellMonat", RefersToR1C1:= _
ActiveWorkbook.Worksheets("Dienste").Range(Cells(1, KuSeI(272)), Cells(KuSeI(66), KuSeI(273)))
Hinweis: KuSeI(xx) ist eine Private Function die Spaltenwerte aus der Settings-Tabelle ausliest.
Bemerkung: Mit Names.Add wir der bestehende Name (Master_AktuellMonat) ersetzt oder überschrieben.
Das Problem tritt erst nach dem ersten Sortieren auf. Vorher alles problemlos. Auch nach dem Sortieren ist noch alles TipTop. Ein erneutes Sortieren geht auch.
Sogar Names.Add funktioniert, sofern "der gleiche Bereich" dem bestehenden Namen zugeordnet wird (also keine Änderung an "Master_AktuellMonat")...
Erst mit der effektiven Änderung von "Master_AktuellMonat" NACH dem Sortieren beginnen die Probleme...
Zu deinem Vorschlag: Bereich.Verschieben ist eine volatile Funktion und ich kann sie somit in meinem Projekt nicht verwenden /zu langsam)
Bereits getesteter Lösungsansatz:
Vor dem erneuten Names.Add werden alle Name gelöscht mit
Dim KUM_Name As Name
For Each KUM_Name In ActiveWorkbook.Names
On Error Resume Next
KUM_Name.Delete
On Error GoTo 0
Next KUM_Name
Bereits das löschen der Namen dauert nach dem ersten Sortieren eine Ewigkeit...
Was könnte da noch ausprobiert werden? Wie lässt sich das Lösen? Welche Alternativen zu Name.Delete und Names.Add gibt es (nicht volatile Funktion!)?
Besten Dank und Grüsse
Martin

Anzeige
AW: Namen (die Bereiche enthalten) nach sortieren
16.03.2016 09:14:41
Steve
Guten Morgen Martin,
da würde es mich interessieren wie deine Mappe aufgebaut ist, wenn das solang bei dir braucht. Ich habe Mappen mit um die zehn Reitern, je 30 Bereiche (Spalten) sowie je 100.000+ Datensätze und sowohl Bereich.Verschieben als auch den Bereich per VBA ändern fallen zeitlich überhaupt nicht auf. Wie erfolgt denn dein Sortieren bei der ganzen Geschichte?
Löschen aller Bereiche mache ich nicht gern, denn von anderen Benutzer eingerichtete Bereiche, im einfachsten Fall nur ein Druckbereich, sind dann auch jedes Mal weg. Unschön, weil mir die entsprechenden Kollegen dann (zurecht) auf die Ketten gehen. Meist habe ich einen ausgeblendeten Reiter "Parameter" indem die Informationen zu den verschiedenen Bereichen der Typen hinterlegt sind. Damit kann das Makro dann die Formel generieren, meist im Zusammenhang mit einer Dropdown um den veränderlichen Effekt zu ermöglichen, und über die RefersTo-Eigenschaft den Bezug anpassen. Damit ändere ich gezielt meine Bereiche und nicht die von anderen.
For x = 2 To wksPar.Cells(3,2)
ThisWorkbook.Names().RefersToR1C1 = "=Formel"
Next x
Anerkung, verschiedene Prüfungen sind hier weggelassen, z.B. wenn die Zelle für die Spaltennummer leer ist weil der Bereich für diesen Typen nicht gebraucht wird usw.. Du kannst ebenso wie bei der Formula-Eigenschaft RefersTo, RefersToLocal, RefersToR1C1 und RefersToR1C1Local wählen.
In einer Mappe habe ich (relativ!) identische Typen die über Diagramme ausgewertet werden, welche sich auf die Bereiche beziehen. Dort habe ich Ressourcen gespart und nur für ein Blatt Bereiche angelegt, das Makro verschiebt diese dann nur in den auszuwertenden Reiter des Types. Eventuell kannst du sowas auch für dich nutzen. Man muss ja nicht alles immer definiert haben, nur wenn es gebraucht wird.
Als letzer Tipp fällt mir nur noch eins ein: Während der Ausführung des Makros die Formelberechnung ausstellen, wie du so schön sagtest, Bereich.Verschieben ist volatil, aber auch so spart das unheimlich Ressourcen bei der Verarbeitung von Daten. Dafür spare ich mir mit Bereich.Verschieben wiederum den Bereich beim Import per Makro zu verändern, berechnet werden muss er eh neu, das kann dann auch gern die Formel tun.
lg Steve

Anzeige
AW: Namen (die Bereiche enthalten) nach sortieren
17.03.2016 08:33:36
Martin
Guten Tag Steve
Nochmals habe ich versucht die "Names.Add" zu retten... Es geht nicht. Immer das selbe Problem.
Wie weiter? Deine Ideen und Hinweise gemixt mit schon vorhandenen Systemen führt mich zu folgendem Ansatz:
1. Keine "Names.Add" verwenden (also gar keine Namen definieren und verwenden).
2. Das VBA-Script definiert nun nicht mehr die Bereiche (Names.Add) sondern ersetzt direkt die Formel in der Zelle mit FormulaR1C1 Da bei mir nur rund 10 x 100 Zellen betroffen sind, geht das sehr schnell.
3. Konsequentes ausschalten der Neuberechnung und des Bildschirmaufbaus während den VBA-Scripts.
Die Vorteile sind für mich (momentan! - vielleicht tritt dann ein neues Problem auf)
1. Keine Probleme nach dem Sortieren. Die Bereiche (Bezüge) sind immer fix in die Formel geschrieben. Die "Dynamik" entsteht durch das VBA-Script und FormulaR1C1 (aktuell benötigte Formel wird gesetzt)
2. Gute Performance im normalen Betrieb (der Monatswechsel = VBA-Script mit FormulaR1C1 ist verhältnismässig selten)
Mal schauen, was dabei herauskommt...
Grüsse und Danke
Martin

Anzeige
AW: Namen (die Bereiche enthalten) nach sortieren
17.03.2016 09:13:05
Steve
Guten Morgen Martin,
freut mich wenn ich helfen konnte.
lg Steve
PS.: Ja die Names.Add-Methode ist nicht zu retten und sollte daher wenn möglich nur einmalig für einen Bereich genutzt werden.

AW: Namen (die Bereiche enthalten) nach sortieren
17.03.2016 09:16:05
Martin
Hallo Steve
PS.: Ja die Names.Add-Methode ist nicht zu retten und sollte daher wenn möglich nur einmalig für einen Bereich genutzt werden.
Mit dieser einfachen Aussage hast du mir (und sicher auch anderen) sehr viel geholfen.
Grüsse
Martin

224 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige