Forum: PC-Programmierung VBA - lahmarschiges Makro beschleunigen, aber wie?


von Wühlhase (Gast)


Lesenswert?

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:
1
Const DatasheetLink = "C:\Bibliothek\Datenblatt.pdf"
2
With ThisWorkbook.Worksheets(1)
3
  .Range("A2:A115000").value = DatasheetLink
4
End With

Dieses Makro hat dann locker eine Stunde länger gebraucht.
Hat jemand eine Idee wie es fixer geht?

von Arc N. (arc)


Lesenswert?

Updates der Anzeige während der ganzen Änderungen ausschalten?
https://msdn.microsoft.com/de-de/vba/excel-vba/articles/application-screenupdating-property-excel

: Bearbeitet durch User
von Wühlhase (Gast)


Lesenswert?

Aber selbstverständlich.

Application.SreenUpdate, Application.EnableEvents, 
Application.Calculation-das hab ich schon ausprobiert.

von Peter M. (r2d3)


Lesenswert?

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?

: Bearbeitet durch User
von Peter M. (r2d3)


Lesenswert?

Wühlhase,

hier ein Beispiel dafür, wie man NICHT mit großen Collections 
arbeitet.
Guck' mal auf das Debug-Fenster!
1
Sub schnarchcode()
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
11
         If (i Mod 1000) = 0 Then
12
            Debug.Print i
13
        End If
14
    Next i
15
    
16
    Debug.Print "Rein "; Timer - zeit
17
    zeit = Timer
18
    
19
    For i = 1 To 100000
20
        
21
        dummy = coll(i)
22
        If (i Mod 1000) = 0 Then
23
            Debug.Print i
24
        End If
25
    Next i
26
    
27
    Debug.Print "Raus"; Timer - zeit
28
End Sub

Mit Collections zu arbeiten, war schon richtig, aber gerade in Deinem 
Fall musst Du Deinen Code noch ein bisschen modifizieren.

: Bearbeitet durch User
von Wühlhase (Gast)


Angehängte Dateien:

Lesenswert?

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.

von Peter M. (r2d3)


Lesenswert?

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) '******************************************
11
         If (i Mod 1000) = 0 Then
12
            Debug.Print i
13
        End If
14
    Next i
15
    
16
    Debug.Print "Rein "; Timer - zeit
17
    zeit = Timer
18
    
19
    For i = 1 To 100000
20
        
21
        dummy = coll(Str(i)) '******************************************
22
        If (i Mod 1000) = 0 Then
23
            Debug.Print i
24
        End If
25
    Next i
26
    
27
    Debug.Print "Raus"; Timer - zeit
28
End Sub

von Dr. Sommer (Gast)


Lesenswert?

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 :)

von Peter M. (r2d3)


Lesenswert?

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.

von Peter M. (r2d3)


Lesenswert?

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.

von Wühlhase (Gast)


Lesenswert?

Die Iteration erfolgt nur bei "normalen" Collections? Nicht z.B. bei 
Range-Objekten?

Vielen Dank, ich probiere es gleich mal aus. :)

von Peter M. (r2d3)


Lesenswert?

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!

: Bearbeitet durch User
von Dr. Sommer (Gast)


Lesenswert?

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...

von Horst (Gast)


Lesenswert?

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.

von Wühlhase (Gast)


Lesenswert?

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. :(

von Peter M. (r2d3)


Lesenswert?

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.

: Bearbeitet durch User
von Luther B. (luther-blissett)


Lesenswert?

Ranges direkt setzten ist sehr langsam, das ist wahr und da kann man 
nicht so viel machen.

Was du probieren könntest: Die Daten mit VBA in eine (temporäte) CSV 
Textdatei zu schreiben und diese dann per VBA zu importieren. 
QueryTables.Add kann das:

https://msdn.microsoft.com/en-us/vba/excel-vba/articles/querytables-add-method-excel

Da ich kein Office hier habe, Beispielcode aus dem Internet:

https://www.extendoffice.com/documents/excel/3165-how-to-import-csv-file-into-worksheet.html#a2

: Bearbeitet durch User
von Nop (Gast)


Lesenswert?

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.

von c-hater (Gast)


Lesenswert?

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...

von Wühlhase (Gast)


Lesenswert?

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.

von Wühlhase (Gast)


Angehängte Dateien:

Lesenswert?

Und hier noch das versprochene Makro.

von Wühlhase (Gast)


Lesenswert?

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...

von Carl D. (jcw2)


Lesenswert?

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.

von Wühlhase (Gast)


Lesenswert?

Meinst du, anstatt
1
Do While ...
2
  With ThisWorkbook....
3
    ...
4
  End With
5
loop

sowas
1
With ThisWorkbook...
2
  Do While ...
3
    ...
4
  Loop
5
End With
?

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?

von Carl D. (jcw2)


Lesenswert?

Wühlhase schrieb:
> Meinst du, anstatt
>
1
> Do While ...
2
>   With ThisWorkbook....
3
>     ...
4
>   End With
5
> loop
6
>
>
> sowas
>
1
> With ThisWorkbook...
2
>   Do While ...
3
>     ...
4
>   Loop
5
> End With
6
>
> ?
>
> 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.

: Bearbeitet durch User
von Wühlhase (Gast)


Lesenswert?

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. :)

von Harald (Gast)


Lesenswert?

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++.

von Carl D. (jcw2)


Lesenswert?

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.

von Carl D. (jcw2)


Lesenswert?

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.

von Nop (Gast)


Lesenswert?

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."

von Carl D. (jcw2)


Lesenswert?

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.

von Nop (Gast)


Lesenswert?

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.

von Carl D. (jcw2)


Lesenswert?

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.

von Nop (Gast)


Lesenswert?

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.

von Carl D. (jcw2)


Lesenswert?

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.

von Wühlhase (Gast)


Lesenswert?

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. :)

von Dr. Sommer (Gast)


Lesenswert?

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

von Carl D. (jcw2)


Lesenswert?

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.

von ge-nka (Gast)


Lesenswert?

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?

von Harald (Gast)


Lesenswert?

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.

von Nop (Gast)


Lesenswert?

Harald schrieb:

> will man es ganz primitiv dann meinetwegen CSV oder XML

XML wird erst recht lahm zu parsen sein.

von Sheeva P. (sheevaplug)


Lesenswert?

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ß.

von Sheeva P. (sheevaplug)


Angehängte Dateien:

Lesenswert?

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.

von Sheeva P. (sheevaplug)


Lesenswert?

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.

von Peter M. (r2d3)


Lesenswert?

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.

: Bearbeitet durch User
von Wühlhase (Gast)


Lesenswert?

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.

von Wühlhase (Gast)


Lesenswert?

@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...

von Hendrik (Gast)


Lesenswert?

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.

von Peter M. (r2d3)


Lesenswert?

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.

: Bearbeitet durch User
von Manfred S. (Firma: Manfred) (xfred343)


Lesenswert?

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..

: Bearbeitet durch User
Bitte melde dich an um einen Beitrag zu schreiben. Anmeldung ist kostenlos und dauert nur eine Minute.
Bestehender Account
Schon ein Account bei Google/GoogleMail? Keine Anmeldung erforderlich!
Mit Google-Account einloggen
Noch kein Account? Hier anmelden.