Frohe Ostern allerseits
Ich hab hier ein Excel-Makro, das mir diverse Bauteile für meine
Altiumbibliothek erstellen soll. Es funktioniert soweit, nur dauert mir
die Ausführung entschieden zu lange (gefühlt eine Ewigkeit, gemessen ne
knappe Dreiviertelstunde).
Ich hab bereits einige Versuche unternommen und festgestellt, daß die
Berechnung der Werte selber nicht allzu lange dauert. Was lange zu
dauern scheint ist das Eintragen der Werte in das Excelblatt.
Die Werte trag ich in einer Do-While-Schleife wie dieser ein:
1
Dim i As Integer
2
3
i = 1
4
5
Do While i <= ComponentValues.Count 'Collection mit einem Parametersatz
6
With ThisWorkbook.Worksheets(1)
7
.Range("A" & i).value = ComponentValues(i)
8
End With
9
i = i + 1
10
loop
Hat jemand eine Idee, wie ich da mehr Gas geben kann? Automatische
Berechnung, Screenupdating, usw. hab ich schon alles abgestellt. Das
Tabellenblatt enthält auch nur Daten, keinerlei Formeln oder ähnliches.
Aktuell trage ich die Werte zeilenweise bei jedem Schleifendurchlauf
ein. Ich hatte schon versucht, alles Statische (Formatierung, Eintragung
einiger Konstanten, usw.) von generierten Daten (sowas wie Bauteilwerte,
Toleranzen, Herstellernummer, usw.) zu separieren und möglichst viel
nicht mehr über die Schleife, sondern über ausgewählte Bereiche
einzutragen:
Hallo Wühlhase,
ich sehe nur, dass Dein erstes Codebeispiel die A-Spalte befüllt, nicht
aber, dass er zeilenweise arbeitet.
Wie groß ist "ComponentValues.Count"?
Wird die Schleife nach oben hin immer langsamer? :)
Setz mal ein Debug.print i alle hundert i's ab.
Was passiert? :)
Den fehlerhaften Code hast Du vermutlich nicht gezeigt.
Immer diese Salamitaktik. :(
Wenn Du sowieso nicht mehrere Operanden für WITH hast, kann Du das WITH
schön ersetzen und die Zellen direkt mit der CELLS-Methode ansprechen.
Wie Deine zweite Frage mit der ersten zusammenhängt, weiß ich nicht.
Mein Lösungshinweis für Dich, da Du ja nicht alles Relevante zeigst:
Was passiert, wenn Du versuchst, jede Minute einer Kassettenseite kurz
anzuspielen und danach das Band wieder an Position Null fährst?
Der Code oben war ein Bespiel, insgesamt hab ich 19 Spalten zu füllen,
mit 115.xxx Zeilen. Ist einfacher zu überblicken als die paar hundert
Codezeilen, und den Code krieg ich hier auch nicht einfach so formatiert
daß die Forensoftware das noch einigermaßen lesbar darstellt.
Ich hab den dafür mal als .txt angehangen.
Ich werd mal alle hundert Durchläufe eine Zeitmessung machen-mal sehen
was passiert, ich würde aber einen einigermaßen linearen Verlauf
erwarten.
Hallo Wühlhase,
bitte nichts mehr für das Forum weiter vorbereiten.
Der "Fehler" ist offensichtlich.
Du solltest mit Indizes in den Collection arbeiten, weil ansonsten VBA
die ganzen Elemente iteriert, um zu dem von Dir gesuchten Element zu
gelangen.
Unten wird beim Aufbau der Collection ein Index verwendet.
Das Auslesen geschieht dann nicht über die numerische Elementnummer,
sondern über den Index, der vom Typ String sein muss.
In meinem Beispiel wird einfach die Elementnummer als Suchschlüssel
verwendet.
Unten findest Du eine leicht veränderte Version der
Schnarchcode-Prozedur.
Die Änderungen sind mit Sternchen gekennzeichnet.
1
Sub schnellcode()
2
Dim coll As Collection
3
Set coll = New Collection
4
Dim i As Long
5
Dim dummy As Long
6
Dim zeit As Double
7
8
zeit = Timer
9
For i = 1 To 100000
10
coll.Add i, Str(i) '******************************************
Via Excel auf Excel Files zuzugreifen ist leider allgemein langsam. Viel
schneller geht es z.B. mit Java und der Apache POI Bibliothek.
Unterstützt dann natürlich nicht 100% der Excel Features... Aber für
Java gibt's Profiling Tools :)
Wühlhase schrieb:> Ich werd mal alle hundert Durchläufe eine Zeitmessung machen-mal sehen> was passiert, ich würde aber einen einigermaßen linearen Verlauf> erwarten.
Genau das passiert nicht, wenn Du über ComponentValues über die
Elementnummer iterierst. Der Aufwand wächst quadratisch.
Wenn Du ComponentValues mit Indizes versiehst, und für den Zugriff
Indizes verwendest, sollte alles viel schneller gehen.
Hallo Dr.Sommer,
Dr. Sommer schrieb:> Via Excel auf Excel Files zuzugreifen ist leider allgemein langsam. Viel
Konnte ich bisher nicht nachvollziehen.
> schneller geht es z.B. mit Java und der Apache POI Bibliothek.> Unterstützt dann natürlich nicht 100% der Excel Features... Aber für> Java gibt's Profiling Tools :)
"Profiling Tools" sind hier gar nicht erforderlich.
Man muss nur verhindern, dass die Laufzeit O(n^2) beträgt, wenn man auch
O(nlog(n)) haben kann.
Fremdzugriff von außen auf Exceldateien mit Drittanbietersoftware bergen
Risiken wegen der Formatskomplexität von Excel.
Dieses Jahr durfte ich das beobachten, dass eine Erweiterung? für
Python, um auf Excel zuzugreifen zu können, bei einfachen Dingen
versagte.
Der Programmierer musste dann heraus aus seinen vertrauten
Python-Gewässern und herein in den VBA-Teich.
Wühlhase schrieb:> Die Iteration erfolgt nur bei "normalen" Collections?
Ob "nur" kann ich Dir nicht sagen.
Bei Collections in VBA ohne Index hast Du immer ein Laufzeitproblem bei
wahlfreien Zugriff.
> Nicht z.B. bei> Range-Objekten?
Das weiß ich nicht.
Über eine Rückmeldung, ob Du das Laufzeitproblem beheben konntest, würde
ich mich freuen!
Peter M. schrieb:> Konnte ich bisher nicht nachvollziehen.
Hm. Meine Erfahrung dazu ist etwas älter (ca 2009). Damals wurde per VBA
und danach per OLE Automatisierung auf Excel zugegriffen. Das war sehr
lahm und der Umstieg auf POI hat die Sache um Größenordnungen
beschleunigt. Wollte es auch nur als mögliche Alternative in den Raum
stellen.
Peter M. schrieb:> Fremdzugriff von außen auf Exceldateien mit Drittanbietersoftware bergen> Risiken wegen der Formatskomplexität von Excel.
Ja, das ist das Problem. Hatte bei mir damals aber gut funktioniert...
Peter M. schrieb:> Der Programmierer musste dann heraus aus seinen vertrauten> Python-Gewässern und herein in den VBA-Teich.
Der Arme. Aber das ist doch gar nicht nötig, von C# aus müsste das doch
genau so gehen...
Wühlhase schrieb:> Excel
Fehler 1.
Wühlhase schrieb:> VBA
Fehler 2.
Größere Daten(wie hier 115.xxx Zeilen) gehören in eine (welch Wunder)
DATENBANK und nicht in eine Tabellenkalkulation.
Peter M. schrieb:> Fremdzugriff von außen auf Exceldateien mit Drittanbietersoftware bergen> Risiken wegen der Formatskomplexität von Excel.
Das stimmt. Nicht mal MS hat seine Formate bei einem Versionswechsel
unter Kontrolle.
So...mein Makro ist nach 00:43:01 endlich durchgelaufen. Alle
Collection-Zugriffe fanden nun über den Index statt.
Gebracht hat es leider nichts. Ich habe mir die Zeiten von zwei
vorherigen Durchläufen mal notiert, das waren:
00:41:56 für den ersten gemessenen Durchlauf
00:39:30 nach meinen ersten Optimierungsversuchen (da hatte ich
zwischendurch auch mal 01:41:xx gebraucht).
Die Zeitdifferenzen würde ich mal auf wechsselnde Auslastung schieben,
da ich zwischendurch noch andere Dinge mit meinem Rechner mache.
Anscheinend lag es wohl nicht an den Collection-Zugriffen. :(
Hallo Wühlhase,
das ist schade!
Hast Du denn meine beiden Beispielsprozeduren ausprobiert um den
Unterschied zu sehen?
Den von Dir gezeigten Code würde ich anders schreiben:
1
Dim i As Integer
2
3
i = 1
4
5
Do While i <= ComponentValues.Count 'Collection mit einem Parametersatz
6
With ThisWorkbook.Worksheets(1)
7
.Range("A" & i).value = ComponentValues(i)
8
End With
9
i = i + 1
10
loop
Etwa so:
1
Dim i As Integer
2
Dim CompValCount as long
3
Dim here as Object
4
5
set here = ThisWorkbook.worksheets(1)
6
7
i = 1
8
CompValCount= ComponentValues.Count
9
10
Do While i <= CompValCount 'Collection mit einem Parametersatz
11
here.cells(i,1).Value=ComponentValues(str(i))
12
i = i + 1
13
loop
Eigentlich operierst Du ja auf Zellen und nicht auf Bereichen, deswegen
würde ich die Cells-Eigenschaft nutzen.
Der With-Befehl erscheint mir sinnlos, wenn Dann gerade mal ein
"."-Befehl folgt.
Ansonsten kannst Du mit dem eingestreuten Timer-Abruf schon mal ein paar
Infos abrufen und den Zeitfresser eingrenzen.
Der Aufruf von Excel-Funktionen in VBA wie z.B. Application.Roundup()
könnte teuer sein.
In Deinem längeren Code-Beispiel hast Du so eine Art Wiederholgruppe:
1
With ThisWorkbook.Worksheets("01 Keramik")
2
With .Range("A" & lineCnt)
3
.HorizontalAlignment = xlLeft
4
.VerticalAlignment = xlCenter
5
.NumberFormat = "@"
6
.Value = SymbolRef
Die Möglichkeit einzelnen Zellen abweichend zu formatieren, nutzt Du
kaum.
Probier mal die Formatierungsbefehle mit nur einem Befehl auf die
gesamte Range anzuwenden, also z.B.
with .Range("A1:" & "Z"&lineCnt)
....
Das könnte funktionieren und wäre dann vermutlich schneller als
zellweises immer gleichartiges Formatieren.
Der Schleifeninhalt würde sich dann auf Setzen von Werten reduzieren.
Horst schrieb:> Größere Daten(wie hier 115.xxx Zeilen) gehören in eine (welch Wunder)> DATENBANK und nicht in eine Tabellenkalkulation.
100.000 Zeilen sind nichts, wir sind hier nicht mehr in DOS-Zeiten. Wenn
das so lahmt, dann muß an irgendeiner Stelle unter der Haube ein
n^2-Algorithmus (oder Schlimmeres) vorliegen, und das wäre auch in C
noch lahm.
Luther B. schrieb:> Ranges direkt setzten ist sehr langsam, das ist wahr
Ja.
> und da kann man> nicht so viel machen.
Doch, natürlich: Keine Ranges benutzen, sondern über die
"Cells"-Eigenschaft des Sheets mittels der zweidimensionalen Indizierung
auf die einzelnen Zellen zugreifen. Ganz so wie Gott es gewollt hat und
wie es jeder verständige Programmierer von Hause aus ohne jede weitere
Überlegung machen würde...
Peter M. schrieb:> Hallo Wühlhase,>> das ist schade!> Hast Du denn meine beiden Beispielsprozeduren ausprobiert um den> Unterschied zu sehen?
Hallo Peter
Ja, habe ich. Ich habe gestern noch einen Test geschrieben um direkt
zwischen Collection-Behandlungen mit und ohne Index zu unterscheiden,
ich hab die Datei mal in den Anhang gesteckt falls jemanden der direkte
Vergleich interessiert.
Das Makro macht nichts anderes als eine Collection 150.000 mal mit dem
String "Test" zu füllen und dann in einer Schleife in die Spalten A und
B zu schreiben, jeweils einmal mit und einmal ohne Indexierung.
Das Füllen der Collection geht quasi sofort, das Schreiben dauert aber
in der Tat fünf Minuten bei mir.
Mit Indexierung dauert es nur noch 13 Sekunden-dafür dauert das Füllen
der Collection erstaunlicherweise etwa genauso lange.
Danke für den Tip mit dem Cells-Objekt. Ich hatte völlig vergessen das
es das auch noch gibt, das probiere ich mal aus. Und berichte dann
wieder.
Zum Stand der Dinge: Die Ausführungszeit über Cells-Zugriff dauert nun
immerhin nur noch rund 27 Minuten.
Irgendwie kommt mir das aber immer noch sehr lang vor...
Wühlhase schrieb:> Zum Stand der Dinge: Die Ausführungszeit über Cells-Zugriff dauert nun> immerhin nur noch rund 27 Minuten.>> Irgendwie kommt mir das aber immer noch sehr lang vor...
Wieso holst du dir 150.000 mal das Sheet1?
Ändert sich das jedesmal (abgesehen von seinem Inhalt), ist es immer
eine andere Instanz?
Der Tipp mit dem "With" weiter oben war so gemeint, daß die "Common
subexpression" einmalig vor der Schleife ermittelt werden soll.
Ja, das braucht man bei zeitgemäßen Compilern heutzutage nicht mehr
machen, aber VBA hat sicher keinen Optimizer, der sowas kann.
?
Ich hab mich mit dem internen Aufbau von VBA nur sehr wenig beschäftigt
(ich weiß aber daß da nicht viel Leidenschaft von den Entwicklern
reingesteckt wurde), hab das Schlüsselwort 'With' bisher aber als rein
textuelle Maßnahme verstanden, denn letztendlich wird doch eh auf
dasselbe Objekt zugegriffen, unabhängig davon ob 'With' verwendet wird
oder wo es steht. Oder nicht?
> ?>> Ich hab mich mit dem internen Aufbau von VBA nur sehr wenig beschäftigt> (ich weiß aber daß da nicht viel Leidenschaft von den Entwicklern> reingesteckt wurde), hab das Schlüsselwort 'With' bisher aber als rein> textuelle Maßnahme verstanden, denn letztendlich wird doch eh auf> dasselbe Objekt zugegriffen, unabhängig davon ob 'With' verwendet wird> oder wo es steht. Oder nicht?
Ja, das ist auch eine vereinfachte Schreibweise für
1
Dim sheet as Excel.Sheet
2
sheet = ThusWorkbook...
und danach Ergänzung von "." zu "sheet."
Wichtiger is aber, daß das sheet außerhalb der Schleife einmal
ermittelt wird, ob mit oder ohne Hilfe von "With".
Andernfalls wir 150.000 mal Workbook geholt, dessen Collection Sheets
geholt, der erste Eintrag der Sheets-Collection geholt. Das geht über
COM-Interfaces, für jeden Zugriff wird womöglich erst mal QueryInterface
gerufen, um aus einem IUnknown-Pointer z.B. ein Excel.Sheet-Pointer zu
machen. Alle diese Zugriffe laufen über Virtuelle Methoden, deren Code
unoptimierbar in DLLs versteckt ist. Das Dauert alles nicht lange,
passiert bei dir nur zu oft.
27min/150.000 sind übrigens nur 10ms je Durchlauf.
Naja...10ms um noch nichtmal 20 Werte in eine Zeile zu schreiben würde
ich nicht gerade als schnell bezeichnen.
Ich habe mal alle Formatierungen und Konstanten aus der Schleife
geschmissen und füge diese am Ende über den gesamten Bereich bzw. über
die jeweilige Spalte als zusammenhängenden Bereich ein.
So wie ich es am Anfang beschrieben habe (und was dann eine volle Stunde
länger gedauert hat).
Und siehe da-jetzt dauert es nur noch anderthalb Minuten. Damit kann ich
nun leben. :)
Wühlhase schrieb:> Naja...10ms um noch nichtmal 20 Werte in eine Zeile zu schreiben würde> ich nicht gerade als schnell bezeichnen.>> Ich habe mal alle Formatierungen und Konstanten aus der Schleife> geschmissen und füge diese am Ende über den gesamten Bereich bzw. über> die jeweilige Spalte als zusammenhängenden Bereich ein.
Probier mal eine Spalte Zellenweise auf zentriert zu setzen, statt
"komplette Spalte". Nichts anderes mußte dein Script machen. Noch
Fragen?
> So wie ich es am Anfang beschrieben habe (und was dann eine volle Stunde> länger gedauert hat).>> Und siehe da-jetzt dauert es nur noch anderthalb Minuten. Damit kann ich> nun leben. :)
Das ist das Ergebnis von "Common subexpression elemination", was zu den
Optimierungs-Basics eines ordentlichen Compilers gehört. VBA kann das
wohl nicht, ist aber eben auch eher zu den Script-Sprachen zu zählen.
Auch weil es ständig mit Objekten hantiert, deren Typ erst zur Laufzeit
bekannt ist.
Harald schrieb:> Mein Tipp: Wirf den VBA Müll weg und mach das mit einer richtigen> Programmiersprache für Erwachsene, dazu zählen z.B. Python oder C++.
Python ist da nicht besser, nur anders,
Und C++ bedeutet all die COM-Internas von Hand zu machen. Das ändert
nichts an der Laufzeit in Excel selbst, nur merkt man das je nach
Kenntnisstand erst Tage/Wochen später. Oder auch niemals, mangels
funktionierendem C++-Programm.
Der Hauptfehler dürfte eh darin liegen, Excel als Datenbank nutzen zu
wollen. Immerhin läuft es nun ja grob 100 mal schneller.
Carl D. schrieb:> Der Hauptfehler dürfte eh darin liegen, Excel als Datenbank nutzen zu> wollen.
Verwaltung von Listen ist in Excel aber eine der populärsten
Verwendungen überhaupt, noch vor Tabellenkalkulation im eigentlichen
Sinne. Das war es übrigens schon immer:
https://www.joelonsoftware.com/2000/05/09/the-process-of-designing-a-product/
"When we were designing Excel 5.0, the first major release to use
serious activity-based planning, we only had to watch about five
customers using the product before we realized that an enormous number
of people just use Excel to keep lists. They are not entering any
formulas or doing any calculation at all! We hadn’t even considered this
before. Keeping lists turned out to be far more popular than any other
activity with Excel. And this led us to invent a whole slew of features
that make it easier to keep lists: easier sorting, automatic data entry,
the AutoFilter feature which helps you see a slice of your list, and
multi-user features which let several people work on the same list at
the same time while Excel automatically reconciles everything."
Nop schrieb:> Carl D. schrieb:>>> Der Hauptfehler dürfte eh darin liegen, Excel als Datenbank nutzen zu>> wollen.>> Verwaltung von Listen ist in Excel aber eine der populärsten> Verwendungen überhaupt, noch vor Tabellenkalkulation im eigentlichen> Sinne. Das war es übrigens schon immer:>> https://www.joelonsoftware.com/2000/05/09/the-process-of-designing-a-product/>> "When we were designing Excel 5.0, the first major release to use> serious activity-based planning, we only had to watch about five> customers using the product before we realized that an enormous number> of people just use Excel to keep lists. They are not entering any> formulas or doing any calculation at all! We hadn’t even considered this> before. Keeping lists turned out to be far more popular than any other> activity with Excel. And this led us to invent a whole slew of features> that make it easier to keep lists: easier sorting, automatic data entry,> the AutoFilter feature which helps you see a slice of your list, and> multi-user features which let several people work on the same list at> the same time while Excel automatically reconciles everything."
Die schreiben von Verbesserung in der Bedienung und nicht von
Datenstrukturen, die 150.000 Datensätze locker wegstecken.
Letzter Stand war 100.000 "Inserts/Minute" oder 1666/s, das ist nicht
das, was ein typischer "Projekt-Task-Listen-Pfleger" mit 10-Fingersystem
hinbekommt.
Ich frage mich auch, wenn ich doch zu blöd sein soll Excel richtig zu
benutzen, warum haben dann nicht andere den Faktor 100 rausgeholt.
Carl D. schrieb:> Die schreiben von Verbesserung in der Bedienung und nicht von> Datenstrukturen, die 150.000 Datensätze locker wegstecken.
Es geht dabei um Excel 5.0, und das kam 1993 raus. Man darf wohl
annehmen, daß in den 25 Jahren danach noch einiges mehr dazukam,
insbesondere was Scripte und Macros angeht.
Nop schrieb:> Carl D. schrieb:>>> Die schreiben von Verbesserung in der Bedienung und nicht von>> Datenstrukturen, die 150.000 Datensätze locker wegstecken.>> Es geht dabei um Excel 5.0, und das kam 1993 raus. Man darf wohl> annehmen, daß in den 25 Jahren danach noch einiges mehr dazukam,> insbesondere was Scripte und Macros angeht.
Und du glaubst das ist heute grundsätzlich schneller? Vermutlich durch
noch mehr Attribute/Styles/etc.
BTW, mit VBA hatte ich 1997 oder 1998 erstes mal Kontakt, ist also auch
nicht mehr ganz frisch.
Carl D. schrieb:> Und du glaubst das ist heute grundsätzlich schneller?
Wenn man bedenkt, daß denen erst mit Excel 5.0 klar wurde, wie gerne
Leute Listen in Excel verwalten, da würde ich schon erwarten, daß das
auch scriptseitig noch aufgebohrt wurde. Zumal 100.000 Elemente
heutzutage ja gar nichts sind. Das sind doch weniger Daten als eine
normale Webseite alleine an Werbe-Overhead zieht, ohne daß der Rechner
da eine geschlagene Stunde bräuchte.
Nop schrieb:> Carl D. schrieb:>>> Und du glaubst das ist heute grundsätzlich schneller?>> Wenn man bedenkt, daß denen erst mit Excel 5.0 klar wurde, wie gerne> Leute Listen in Excel verwalten, da würde ich schon erwarten, daß das> auch scriptseitig noch aufgebohrt wurde. Zumal 100.000 Elemente> heutzutage ja gar nichts sind. Das sind doch weniger Daten als eine> normale Webseite alleine an Werbe-Overhead zieht, ohne daß der Rechner> da eine geschlagene Stunde bräuchte.
Wie schon mal früher geschrieben:
NOP ist eigentlich nur zur Zeitverschwendung da.
Harald schrieb:> Mein Tipp: Wirf den VBA Müll weg und mach das mit einer richtigen> Programmiersprache für Erwachsene, dazu zählen z.B. Python oder C++.
Als Programmiersprache schätze ich VBA nicht sonderlich. Lieber
programmiere ich z.B. in Java.
Bevor ich mich jetzt allerdings in Python eingearbeitet und mir die
ganze Toolchain dazu installiert habe, hab ich meinen Kram in VBA
mehrmals erledigt. Das Makro, was ich hier geschrieben habe, soll
eigentlich nur einmal durchrattern und danach wird es nie wieder
angefaßt. Ich werds wohl mal recyclen um andere Bauteile zu erstellen,
grundsätzlich hat das Makro aber seine Schuldikeit getan. Mein Interesse
an der Beschleunigung des Makros war, das mich das eben gewundert hat.
Gestört auch weil doch noch irgendwas nicht ganz passt,
kriegsentscheidend ist das aber nicht unbedingt. Und genau für solchen
Kram sind Scriptsprachen (und Python gehört da auch in die gleiche
Schublade) doch gemacht, nach meinem Verständnis.
Und ja, mir ist auch durchaus bewußt das Excel kein Datenbankprogramm
ist und ich hätte hier durchaus auch Access zur Verfügung. Die Tabellen
werden zwar durchaus etwas größer, das ist es dann aber auch schon. Es
greifen nicht mehrere Nutzer parallel auf die Daten zu, ich brauche
keine sequenzierten Zugriffe, der oben erwähnte Begriff Liste ist da
durchaus passend. Als Datenbank würde ich das nicht unbedingt
bezeichnen.
Ach ja: Auf Access hab ich nur Zugriff, weil das in der Ms
Office-365-Uni-Version drin ist, in der normalen Version nicht. Wer weiß
ob ich nach meinem Studium noch an eine Access-Lizenz komme.
Und bevor ich jetzt hier anfange mich in SQL einzuarbeiten (falls Altium
das überhaupt schluckt) und ein großes Datenbankkonzept
ausarbeite...wenn man eine Datenbank vernünftig nutzt zieht man die auch
nicht als einfache Liste auf.
Carl D. schrieb:> Wühlhase schrieb:>> Naja...10ms um noch nichtmal 20 Werte in eine Zeile zu schreiben würde>> ich nicht gerade als schnell bezeichnen.>>>> Ich habe mal alle Formatierungen und Konstanten aus der Schleife>> geschmissen und füge diese am Ende über den gesamten Bereich bzw. über>> die jeweilige Spalte als zusammenhängenden Bereich ein.> Probier mal eine Spalte Zellenweise auf zentriert zu setzen, statt> "komplette Spalte". Nichts anderes mußte dein Script machen. Noch> Fragen?
Nö...mir ist das durchaus logisch, hab mich aber schon gewundert wieso
das bei meinem ersten Versuch so viel länger gedauert hat.
Wie auch immer-danke für deinen Rat. :)
Wühlhase schrieb:> ich hätte hier durchaus auch Access zur Verfügung
Weil das gleich viel besser ist... :D schneller vermutlich, aber die
SQL Unterstützung ist (war?) doch sehr dünn. Da lieber MariaDB oder so
Wühlhase schrieb:> Harald schrieb:>> Mein Tipp: Wirf den VBA Müll weg und mach das mit einer richtigen>> Programmiersprache für Erwachsene, dazu zählen z.B. Python oder C++.> Als Programmiersprache schätze ich VBA nicht sonderlich. Lieber> programmiere ich z.B. in Java.
Ich hatte letztes Jahr das Vergnügen OO-Calc und Writer mit Java
fremdzusteuern.
Das hat ein Java-Interface, das auf Corba basiert. Jede Teilfunktion
eines Objekts ist in einem eigenen Interface abgebildet, das jedesmal
per "QueryInterface" erfragt werden muß. Da gibt es dann ein IF um eine
Collection positionsweise durchzugehen und eins um Elemente per Namen zu
finden und alle geben "IUnknown" Referenzen zurück, und man darf wieder
nach X oder Y fragen. Echt kein Spaß. 80% der Zeit gingen in einen
Layer, der daraus was Java-kompatibles machte.
Der Grund das zu tun war: 130 Jahresrechnungen für einen Verein mit
jeweils 50..60 mal VLookup auf die Rohdaten waren zu langsam, unflexibel
und fehleranfällig. Nach dem Kampf mit Corba waren alle drei Punkte
erledigt.
Hast du auch "Application.EnableEvents = False" probiert bevor Durchlauf
beginnt? Damit nicht jeder frischer Wert ein Ereignis auslöst? Das
funktioniert nicht wenn man außerhalb der Tabellen hin und her schaltet.
Was die anderen schon gesagt haben soll es immer ein Durchlauf über
150.000 Werte sein? Ist das möglich z. B. die Daten in Access zu
importieren und dann nur mit SQL die benötigten filtern? Und dann
durchlaufen in Excel?
Carl D. schrieb:> Harald schrieb:>> Mein Tipp: Wirf den VBA Müll weg und mach das mit einer richtigen>> Programmiersprache für Erwachsene, dazu zählen z.B. Python oder C++.>> Python ist da nicht besser, nur anders,> Und C++ bedeutet all die COM-Internas von Hand zu machen. Das ändert> nichts an der Laufzeit in Excel selbst, nur merkt man das je nach> Kenntnisstand erst Tage/Wochen später. Oder auch niemals, mangels> funktionierendem C++-Programm.> Der Hauptfehler dürfte eh darin liegen, Excel als Datenbank nutzen zu> wollen. Immerhin läuft es nun ja grob 100 mal schneller.
Mein Vorschlag war es eher ganz auf Excel zu verzichten.
Datensätze speichert man besser in einer Datenbank (z.B. postgresql),
will man es ganz primitiv dann meinetwegen CSV oder XML, aber eine
Datenbank liefert einem derart viel Komfort, gerade wenn man wie der TS
Datensätze verwalten möchte.
Gerade das Parsen einer PDF ist meiner Meinung nach sehr komplex, da
würde ich mir den Mist mit Excel usw. nicht einfangen wollen. Man sollte
sich generell überlegen ob es nicht einfacher ist z.B. die Specs beim
Distributor zu pullen, die bekommt man dann relativ sauber
standardisiert formatiert. Teils bieten die Hersteller dafür auch
Schnittstellen an.
Aber irgendwas mit Excel ist immer Müll aus Erfahrung. Hab schon
mitbekommen wie Leute an der Uni eine Leihverwaltung von einer SQL
Datenbank auf Excel haben migrieren lassen (mit exakt den Anforderungen
wofür SQL entwickelt wurde) nur weil die Sekretärin sich weigerte mit
etwas anderem als Excel zu arbeiten und sie sich selbst davon bessere
Wartbarkeit erhofft haben.
Nop schrieb:> Carl D. schrieb:>>> Die schreiben von Verbesserung in der Bedienung und nicht von>> Datenstrukturen, die 150.000 Datensätze locker wegstecken.>> Es geht dabei um Excel 5.0, und das kam 1993 raus. Man darf wohl> annehmen, daß in den 25 Jahren danach noch einiges mehr dazukam,> insbesondere was Scripte und Macros angeht.
Naja, bis Excel 2007 hätte unser TO mit seinem Vorhaben ohnehin Pech
gehabt, wenn ich mich recht erinnere. Denn bis dahin gab es doch IIRC
ein Limit von maximal 65.536 Zeilen pro Sheet... ;-)
Es muß ja keiner nachmachen, aber ich ganz persönlich würde sogar schon
bei solch kleinen Datensätzen auf Elasticsearch zurückgreifen. Das ist
nämlich nicht nur eine extrem performante Volltext-Suchmaschine, sondern
dank des Webfrontend Kibana auch ganz hervorragend für die interaktive
Analyse und Visualisierung von Daten geeignet. Die durchaus aufwändige
Einarbeitung in diese Werkzeuge lohnt sich allerdings wohl nur, wenn man
sowas häufiger machen will, darf oder muß.
Carl D. schrieb:> Harald schrieb:>> Mein Tipp: Wirf den VBA Müll weg und mach das mit einer richtigen>> Programmiersprache für Erwachsene, dazu zählen z.B. Python oder C++.>> Python ist da nicht besser, nur anders,
Klar: wenn man das Excel mit IronPython über COM fernsteuert, kostet das
sicher dasselbe wie wenn man das mit VB(A), C++ oder C# macht.
Jedoch fand ich den oben gegebenen Tipp mit der CSV-Datei ganz
interessant.
Deswegen habe ich jetzt spaßeshalber mal die Probe aufs Exempel gemacht
und mit Python eine CSV-Datei mit 120.000 Zeilen zu je 19 Zufallszahlen
erzeugt. Kostete auf einem alten Q9650 ca. 3,6, auf einem i7-3720QM ca.
2,7 Sekunden; LibreOffice Calc hat die Datei auf dem Q9650 in ca. 16,
der i7 in knapp 10 Sekunden geladen, inklusive manuellem Abnicken des
Importformats.
Danach habe ich die generierte CSV-Datei mit Pandas gelesen und über den
XlsxWriter direkt aus Python heraus ein Excel-Cheet erzeugt. Das hat auf
meinem alten Maschinchen 1:22 Minuten, und auf dem i7 knapp 44 Sekunden
gebraucht. Die beiden Kindergartenskripte habe ich mal angehängt, falls
jemand Interesse an eigenen Tests hat.
Nunja, bekanntlich führen viele Wege nach Rom.
Wühlhase schrieb:> Und genau für solchen> Kram sind Scriptsprachen (und Python gehört da auch in die gleiche> Schublade) doch gemacht, nach meinem Verständnis.
Einerseits hast Du da natürlich nicht ganz Unrecht, aber andererseits
auch nicht ganz Recht. Da sich Python sehr leicht mit nativem Code in C
und C++ erweitern läßt und obendrein in der Analyse und -Verarbeitung
sehr großer Datenmengen verwendet wird, gibt es sehr viele Python-Module
und -Packages, die in kompiliertem Code geschrieben und dadurch äußerst
performant sind.
Meiner ganz persönlichen Ansicht nach lohnt es sich aber für die meisten
Anwender, eine moderne Skriptsprache zu beherrschen, und Python ist
dafür heute sicher eine der besten Möglichkeiten.
Hallo Wühlhase,
Wühlhase schrieb:> Nö...mir ist das durchaus logisch, hab mich aber schon gewundert wieso> das bei meinem ersten Versuch so viel länger gedauert hat.> Wie auch immer-danke für deinen Rat. :)
vielen Dank, dass Du Dich bei bei anderen für meinen Rat bedankst.
Ich bin eigentlich schon davon ausgegangen, dass Du meine Hinweise
sorgfältig liest.
So etwas demotiviert mich und mindert meine Hilfsbereitschaft, zumal ich
Dir auch bei Deinem Vorproblem (Ermittlung der Bauteilwerte für
Normreihen) geholfen habe.
Auch wenn mein Problem gelöst ist (es wurde noch gestern Abend durch ein
Neues ersetzt...), ich bin für andere Lösungsansätze durchaus offen.
Excel war für das Problem bis gestern Abend durchaus noch Mittel der
Wahl, allerdings stoße ich grad auf das 32/64-Bit-Problem. Die ODBC-API
und externe Anwendungen haben anscheinend immer noch größere Probleme
miteinander.
Wäre zu beheben wenn ich ein 32-Bit-Office installieren würde, aktuell
läuft eine 64Bit-Version (und dass Upgrade würde mich zumindest jetzt
gerade mal fünf Euro kosten), ich sehe es aber nicht ein.
Ich überlege ernsthaft, mir was Eigenes zu schnitzen. Eine vernünftige
Bauteilverwaltung wäre schon angemessen. Ich muß mal sehen wie Altium
mit anderen Datenbankformaten klarkommt.
@Peter M.:
Entschuldige bitte...aber es war doch schon etwas spät als ich das
schrieb. Danke auch an dich.
Danke auch an die, die die Diskussion weitergeführt haben, das ist recht
inspirierend. Mal sehen wie ich das ohne MS hinkriege...
Vielen Dank für den Thread. Ist zwar schon älter, aber hat mir geholfen
von > 30 min auf 3 sek zu kommen!
(Verwende Excel-Version 2013)
Ich hatte viele Fenster- und Sheet-Wechsel drin, da ich mit mehreren
Dateien arbeiten muss.
Entscheidend war der Tipp von Carl D. alles (wirklich alles) mit vorher
außerhalb der Schleifen definierten Sheet-Objekten zu machen:
'Dateien öffnen
Workbooks.Open Filename:=FilePath_Q + FileName_Q2
Workbooks.Open Filename:=FilePath_Q + FileName_Q3
'Sheet-Objekte festlegen (für schnellere Makro-Ausführung)
Dim Sheet_Q2 As Excel.Worksheet
Dim Sheet_Q3 As Excel.Worksheet
Set Sheet_Q2 = Workbooks(FileName_Q2).Sheets(1)
Set Sheet_Q3 = Workbooks(FileName_Q3).Sheets(1)
For ZZ_akt = ZZ_Start To ZZ_Ende
'Preise auslesen
P_1 = Sheet_Q2.Cells(ZZ_akt, 1).Value
P_25 = Sheet_Q2.Cells(ZZ_akt, 2).Value
P_50 = Sheet_Q2.Cells(ZZ_akt, 3).Value
'Preise schreiben
Sheet_Q3.Cells(ZZ_akt, 1).Value = P_1
Sheet_Q3.Cells(ZZ_akt, 2).Value = P_25
Sheet_Q3.Cells(ZZ_akt, 3).Value = P_50
Next ZZ_akt
Die Sheet-Objekte kann man auch für das Kopieren ganzer Spalten oder
auch die Find-Methode nutzen:
Set RangeFound =
Sheet_Preisgruppen.Columns(SZ_AdrNr).Find(What:=AdrNr,
After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole,
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False,
SearchFormat:=False)
If RangeFound Is Nothing Then
Antwort = MsgBox("Adressnummer " + Str(AdrNr) + " wurde in
der Kundentabelle nicht gefunden!", vbCritical, "Fehler")
'GoTo ENDE_Sub
Else
Sheet_Preisgruppen.Cells(RangeFound.Row, SZ_Preisgr) =
"90001"
End If
Was übrigens auch einiges ausmacht (ca. 30%) ist, ob der VBA-Editor
offen ist oder nicht.
Tja, Hendrik,
nicht nur meine Wenigkeit hat das in einem Beispiel demonstriert, dass
man die Objekte außerhalb der Schleife belegt und nur die sich ändernden
Teile am Objekt ergänzt, sondern auch c-hater hat das noch einmal
explizit in Worten für das Zellobjekt formuliert.
Carl D. wird sich über das Lob bestimmt freuen.
Hendrik schrieb:> Vielen Dank für den Thread. Ist zwar schon älter, aber hat mir geholfen> von > 30 min auf 3 sek zu kommen!> (Verwende Excel-Version 2013)>> Ich hatte viele Fenster- und Sheet-Wechsel drin, da ich mit mehreren> Dateien arbeiten muss.>> Entscheidend war der Tipp von Carl D. alles (wirklich alles) mit vorher> außerhalb der Schleifen definierten Sheet-Objekten zu machen:>> 'Dateien öffnen> Workbooks.Open Filename:=FilePath_Q + FileName_Q2> Workbooks.Open Filename:=FilePath_Q + FileName_Q3>> 'Sheet-Objekte festlegen (für schnellere Makro-Ausführung)> Dim Sheet_Q2 As Excel.Worksheet> Dim Sheet_Q3 As Excel.Worksheet> Set Sheet_Q2 = Workbooks(FileName_Q2).Sheets(1)> Set Sheet_Q3 = Workbooks(FileName_Q3).Sheets(1)>> For ZZ_akt = ZZ_Start To ZZ_Ende> 'Preise auslesen> P_1 = Sheet_Q2.Cells(ZZ_akt, 1).Value> P_25 = Sheet_Q2.Cells(ZZ_akt, 2).Value> P_50 = Sheet_Q2.Cells(ZZ_akt, 3).Value> 'Preise schreiben> Sheet_Q3.Cells(ZZ_akt, 1).Value = P_1> Sheet_Q3.Cells(ZZ_akt, 2).Value = P_25> Sheet_Q3.Cells(ZZ_akt, 3).Value = P_50> Next ZZ_akt>
Alles langsam, wenn du auf unter 1 sec kommen willst, arbeite mit
Arrays, also kopiere die benötigten Sheet-Inhalte in ein Array
z.B.
Dim arrSource as variant
Dim arrTarget as variant
arrSource=Sheet_Q2.range(Sheet_Q2.cells(1,1),Sheet_Q2.cells(ZZEnde,3))
redim arrTarget(1 to ZZEnde,1 to 3)
Dann gehts das Array genauso durch, Zugriff allerdings mit:
For ZZ_akt = ZZ_Start To ZZ_Ende
arrTarget(ZZ_akt,1)=arrSource(ZZ_akt,1)
Die Hilfsvariable P_1, P_25, P_50 sind unnötig
und am Ende
Sheet_Q3.range(Sheet_Q3.cells(1,1),sheet_Q3.cells(ZZEnde,3))=arrTarget
Arrays bringen nochmals ca. 1000-fache Geschwindigkeit, 1 Mio
Operationen/sec sind dann kein Problem, weil Excel RAM und
Prozessorleistung fast ohne Ende von Windows bekommt..
Thread beobachten
Seitenaufteilung abschalten