Makrozeit verkürzen, Optimierung von VBA-Code

[schauan]

Hallöchen,

Da widerspreche ich mal. Dachte ich auch dran. Hatte aber nichts gebracht Bei Dir war dann gleich alles in Ordnung?

[kliffi01]

Hallo liebe Community,

nach Rücksprache und Update von Office 2016 von Version 1803 auf 1808 (neuste hier) existiert das Problem nach wie vor, dass, egal welchen Code ich verwende (also den von euch und meinen eigenen), die Berechnungszeit > 188 ms ist. Auch, wenn ich alle Anwendungen geschlossen habe, dauert die Berechnung dämliche lange.
Habe meine Datei an den Rechnern der Kollegen getestet und aufgefallen ist: Win7 + Office 1808 = keine Probleme, Berechnungen <80 ms OBWOHL die Rechner mindestens 2 Jahre älter sind als meiner. Am Rechner des Kollegen mit Win10 und 1808 tritt das selbe Problem auf wie bei mir. Ich fruste.

Dafür habe ich die ursprüngliche Datei maximal gekürzt. D.h. alle Formeln gelöscht, alle Zellen mehrmals gelöscht, alle Formatierungen gelöscht und trotzdem dauert die Berechnung bei mir ~300 ms.

Kann ich euch bitten die Datei zu prüfen und zu schauen ob der Code bei euch das gleiche auslöst? UND ob der gleiche VBA Code in einer neuen Mappe eben so lange dauert?
Die zu bearbeitende Zelle beginnt bei D24 bis D117.

1000 Dank.

Grüße
Martin

[snb]

Code:

Private Declare Function GetTickCount Lib "kernel32.dll" () As Long

Private Sub Worksheet_Change(ByVal target As Range)
    
  If Not Intersect(target, Range("D24:D117")) Is Nothing And target <> "" Then
    lTime = GetTickCount
    If Len(target) > 400 Then
      MsgBox "Zeichenlimit von 400 erreicht."
    Else
      target.Font.Size = Array(11, 10, 9, 8, 7, 6)(Application.Match(Len(target), Array(0, 120, 151, 201, 241, 351), 1))
    End If
  End If
    
  Application.StatusBar = "Makrolaufzeit " & GetTickCount - lTime & " ms"
End Sub

Immer 0 ms.

Win XP, Excel 2010

[Zwenn]

Hallo Martin,

das Zeitgap kann Dich eigentlich nur stören, wenn Du viele Inhalte auf einmal in die Zellen kopierst. Denn tippen kann man so schnell nicht. Aber mal ganz allgemein gesprochen, ist VBA ganz sicher keine Sprache für "Echtzeitanzwendungen". Es ist eine interpretierte Sprache mit sehr vielen von Haus aus "eingebauten Bremsen". Das ganze läuft in einer Anwendung, nämlich Excel, die ihrerseits Recourcen braucht.

Bei mir läuft Deine Mappe mit Zeiten zwischen 47ms und 63ms. Wenn ich nur die Ansicht von "Seitenlayout" auf "Normal" umschalte, sinkt der Zeitbedarf in ms auf 0 bei jedem Durchgang. Das Verhlten ist beim hin- und herschalten reproduzierbar. Das ist jetzt nur eine einzige Einstellung und eine der genannten eingebauten Bremsen. Excel braucht halt für die Verwaltung der Zelleninhalte in unterschiedlichen Ansichten offenbar unterschiedlich lange. Warum es auf Deinem Rechner bis zu 300ms dauert wird Dir hier kein Mensch beantworten können, weil niemand weiß, was Excel auf Deinem Rechner noch alles im Hintergrund veranstaltet. Durch AddIns oder weiß der Geier was.

Mehr kann ich dazu nicht sagen.

Viele Grüße,

Zwenn

[schauan]

Hallöchen,

ich bestätige dann mal die unterschiedliche Dauer je nach Ansicht. Bei mir sind es im Seitenlayout ca. 140 s und normal auch 0. W10 / O2016Pro / HP Zbook 15 G2
Als xls gespeichert und unter W2000 / O2000 / Compaq Evo / Pentium 3 / 850 MHz / 512 MB RAM sind es nach dem Öffnen ca. 140 s und dann 0-10 s

[kliffi01]

Hallo,

wirklich vielen Dank für eure Rückmeldungen, die allesamt nachvollziehbar sind.
Ich werde in diesem Fall herausfinden müssen, welche Einstellung es hier so schleppend macht. 300 ms sind mir persönlich einfach zu lange

Nochmals Danke und Grüße
Martin

[Zwenn]

Hallo Martin,

wenn man einen persönlichen Kampf draus macht, kann einen sowas echt fuchsen. Das kenne ich auch :19:

Mir ist noch etwas eingefallen. Deine "echte" Arbeitsmappe wird ja nicht aus einer leeren Tabelle und nur dieser Event-Funktion bestehen. Solltest Du volatile Formeln in der Tabelle haben, werden die bei jeder Änderung neu berechnet. Egal ob die Zelle in die Du schreibst etwas mit so einer Formel zu tun hat oder nicht. Direkt abhängige Formeln werden natürlich auch berechnet. Weiterhin kann es auch sein, dass Du noch andere Event-Funktionen in VBA nutzt. Z.B. Change noch in anderen Tabellen oder sonstwas. VBA wird sequentiell ausgeführt, weil es nicht multithreading fähig ist.

Ich kenne die Reihenfolge nicht, in der Excel etwas abarbeitet. Aber wenn Formeln zuerst berechnet werden und dann erst die Events, musst Du ggf. die Formeln ändern. Bei mehreren Events in VBA weiß ich auch nicht, in welcher Reihenfolge die abgearbeitet werden. Wenn dieses entscheidende Change-Event aber erst "spät" dran ist, müsstest Du auf die anderen verzichten oder es schaffen die Reihenfolge der Abarbeitung zu ändern. Wie und ob das möglich ist weiß ich allerdings nicht.

Da die Zeitmessung innerhalb Deiner Funktion ausgelöst und abgeschlossen wird, sind diese Gedankengänge zwar eher theoretischer Natur, aber z.B. Formeln werden sehr wohl im Multithreading-Betrieb ausgefürt. Ob dann z.B. 3 Kerne mit Formeln beschäftigt sind und 1 Kern mit VBA weiß ich nicht. Sollte theoretisch auch nichts ausmachen, aber es gibt dabei natürlich einen gewissen Verwaltungs-Overhead für die Zuweisung der Operationen an die jeweiligen Kerne.

Vielleicht bringen Dir diese Gedanken etwas für einen Lösungsansatz. Vielleicht auch vorausschauend, weil Du später noch entsprechende Erweiterungen in Deinem Projekt vornehmen willst.

Viele Grüße,

Zwenn

[kliffi01]

Hallo liebe Community,

ich traue es mich fast nicht zu schreiben.....
Nachdem ich die Ansicht von Seitenlayout auf Normal umgestellt habe dauerten die Berechnungen nur noch ~15 ms.
Au weia ist mir das peinlich euch mit diesem Problem belästigt zu haben. In diesem Fall hilft mir nur noch mich zu entschuldigen und eine sehr wichtige Erkenntnis gemacht zu haben. 

Danke vielmals.

Grüße
Martin