Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1076to1080
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

VB-Optimierung

VB-Optimierung
27.05.2009 10:28:26
CRossmeissl
Hi,
ich habe folgendes Problem: Ich habe im Excel ein Makro geschrieben, welches auch zumindest läuft. Leider läuft es zu lange, um Anwender darauf loslassen zu können.
Hintergrund ist folgender:
Die Exceltabelle wird Feld für Feld javaunterstützt aufgebaut. Hier besteht leider keine Eingriffsmöglichkeit. Mein Ziel ist es, bei Änderungen von Feldern in der Spalte "U" eine Prüfung auf den Zellinhalt des geänderten Feldes in der Spalte "U" durchzuführen und dann das Feld ggf. zu formatieren. Da das Feld mit einer Formel bestückt ist, greift zwar beim bestücken des Feldes mit der Formel das Worksheet_Change-Ereignis, dies greift aber nicht, wenn die zur Berechnung benötigten Felder geändert werden (und die Formel der Felder in der "U"-Spalte bleibt).
Deswegen habe ich mich für das Calculation-Event entschieden. Leider nur wird das beim Aufbau des Blattes sehr oft ausgeführt, was dann zu langen Ladezeiten führt.
Meine Fragen deshalb:
- Wie kann man den unten stehenden Code schneller machen?
- Oder gibt es eine einfachere Möglichkeit?
Aufgrund des Aufbaus der Anwendung gibt es leider keine Möglichkeiten, Events wie Open abuzfragen. Die Anzahl der Zeilen ist nicht fix.
Wenn jemand Hinweise oder Ideen dazu hätte wäre ich sehr dankbar.
>>

Private Sub Worksheet_Calculate()
Dim Inhalt As String
Dim my_coloumn As String
Dim my_coloumn_vgl As String
Dim my_range As String
Dim my_error_tmp As Boolean
Dim my_fehler_in_eingabe As Boolean
Dim setze_blattschutz_wieder As Boolean
setze_blattschutz_wieder = False
my_error_tmp = False
my_fehler_in_eingabe = False
my_coloumn = "U"
my_coloumn_vgl = "E"
If Worksheets(1).ProtectContents = True Then
Worksheets(1).Unprotect Password:="TEST"
setze_blattschutz_wieder = True
End If
For i = 13 To 999
my_range = my_coloumn & i
my_range_vgl = my_coloumn_vgl & i
If Range(my_range).Value > (Range(my_range_vgl).Value * 1.5) Or _
Range(my_range).Value 


Viele Grüße
Christian

10
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: VB-Optimierung
27.05.2009 12:52:18
David
Hallo Christian,
ich hab mir deinen Code jetzt nicht intensiv angeschaut, aber meist hilft es in Sachen Geschwindigkeit, die Bildschirmaktualisierung und automatische Berechnung temporär abzuschalten.
Endweder direkt im Code:
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
diese musst du dann am Ende wieder auf "True" bzw. auf "...Automatic" setzen.
Oder du lagerst das in ein Untermakro aus

Sub GetMoreSpeed(bYesNo As Boolean)
Application.ScreenUpdating = Not (bYesNo)
Application.EnableEvents = Not (bYesNo)
Application.Calculation = IIf(bYesNo, xlCalculationManual, xlCalculationAutomatic)
If Not bYesNo Then Calculate
End Sub


In deinem Code kannst du dann mit "Getmorespeed True" den Turbo anschalten und mit "... False" wieder ausschalten.
Versuch mal, ob das für deine Zwecke schon reicht.
Gruß
David

Anzeige
AW: VB-Optimierung
27.05.2009 14:42:29
CRossmeissl
Hi,
danke für die schnelle Antwort. Es nützt insofern, als das es nun etwas schneller läuft, aber immer noch so 5 Minuten rum. Leider kann ich die Eigenschaften wie Screenupdating nur innerhalb des Makros aus- und am Ende des Worksheet_Calculate-Events wieder einschalten (analog zum Blattschutz setzen im obigen Beispiel).
Ich muß mir wohl etwas anderes einfallen lassen.
Trotzdem vielen Dank!
Christian
AW: VB-Optimierung
27.05.2009 16:11:11
David
Wenn ich mir deinen Code mal etwas genauer anschaue, halte ich 5 Minuten dafür doch ein wenig übertrieben. Soweit ich sehen kann, wird doch "nur" in rund 1000 Zellen ein Wert geprüft, ob dieser innerhalb einer Bandbreite liegt, und wenn nein, die entsprechende Zelle eingefärbt und eine Meldung ausgegeben.
Das sollte bei deaktiviertem Screenupdating und manueller Berechnung eine Sache von Sekunden !!! sein.
Kannst du vielleicht mal eine solche Mappe zu Testzwecken hochladen?
Gruß
David
Anzeige
AW: VB-Optimierung
27.05.2009 16:53:04
CRossmeissl
Hi,
5 Minuten ist leider eher gewaltig untertrieben :).
Ich habe jetzt mal eine Protokolldatei mitlaufen lassen, wie oft das Calculate-Ereigniss ausgeführt wird. Das Problem ist denke ich, daß dieses Ereignis scheinbar pausenlos beim Aufbau der Datei aufgerufen wird. Ich würde das gerne heute abend noch etwas selber genauer ansehen, wie oft was eintritt.
Gerne kann ich Dir dann morgen die Datei privat schicken, vielleicht kannst Du mir Deine Email per Email schicken, meine ist meine ich sichtbar. Nur ungern stelle ich diese Datei hier ins Forum.
Vermutlich ist in der Datei jedoch wenig zu sehen, sie besteht nur aus ein paar ganz wenigen Zeilen. Beim Laden der Datei wird diese dann zeilenweise erweitert.
Ich kann derzeit leider noch nicht nachvollziehen, warum das Calculate-Ereignis so oft aufgerufen wird. Gerne schreibe ich Dir auch, wie genau das Laden der Daten in die Datei abläuft, wenn es was nützen sollte.
Ich probiere heute mal aus was passiert, wenn man in der "Vorlage-Datei" (die Datei, in die dann Zeilen eingefügt und Daten geladen werden) in den Optionen die Berechnung auf "manuell" setzt. Dann müsste ich wahrscheinlich nur noch die richtige Stelle finden, um erst ganz am Ende, wenn alle Daten geladen sind eine Neuberechnung durchzuführen. Leider kenne ich diese Stelle nicht.
Vielen Dank schonmal für Deine Bemühungen, ich würde mich dann morgen wieder melden.
Viele Grüße Christian
Anzeige
AW: VB-Optimierung
28.05.2009 08:41:06
David
Hallo Christian,
das Calculate-Ereignis wird meines Wissens bei JEDER Zelländerung ausgelöst (auch durch VBA), sofern in den Optionen automatische Berechnung an ist, deswegen ja auch die (temporäre) Umschaltung zu manuell.
Du redest nun auf einmal von mehreren Dateien, das sind wichtige Infos, die du uns nicht vorenthalten solltest.
Ich bin auch kein großer VBA-Experte (Level: bescheiden), aber soweit ich erkennen kann, wird in deinem Code keine andere Datei aufgerufen oder ausgelesen. Wenn du natürlich eine ANDERE Datei nebenbei mit offen hast, in der aufwändige Berechnungen oder ähnliches gemacht werden, kann das natürlich auch die Performance in der besprochenen Datei nach unten ziehen.
Man muss nur eine Datei im Hintergrund haben, in der einige Tausend Matrixformeln oder Summenprodukt-Formeln drin sind, dann wird man das verstehen.
Also, vielleicht solltest du dir erst mal über den Grundaufbau deiner Datei(en) im Klaren werden und alle anderen Störfaktoren, soweit möglich, eliminieren.
Gruß
David
Anzeige
AW: VB-Optimierung
28.05.2009 11:57:15
CRossmeissl
Hi,
vielen Dank für Deine Informationen.
Der Grundaufbau ist mir hier durchaus klar. Ich habe wohl mit der "Vorlage-Datei" Verwirrung gestiftet.
Es gibt nur eine Datei. Diese wird aber bei Aufruf der Anwendung lokal auf den Rechner des Anwenders kopiert und dort erweitert (Zeilen und Formeln werden eingefügt/generiert und mit Daten bestückt). Da diese Datenversorgung für mich nicht änderbar ist, habe ich keine Möglichkeiten, hier etwas zu ändern.
Es werden keine Verknüpfungen zu anderen Dateien erstellt.
Der Anwender könnte mehrere Excel-Dateien zeitgleich geöffnet haben, dann jedoch zwangsweise in anderen Excel-Instanzen.
Bei meinen Tests waren keine weiteren Excel-Dateien geöffnet.
Sicherlich sinnvoll ist, die automatische Berechnung temporär innerhalb des Calculate-Ereignis zu aktivieren / deaktivieren. Leider wird das Calculate-Ereignis aber dermaßen oft ausgeführt, daß diese Performance-Steigerung nicht ausreicht.
Vielleicht muß ich eine Notlösung bauen, was besseres fällt mir nicht ein. Vermutlich wäre es möglich, im Calculation-Ereigniss eine IF-Bedingung einzubauen. Dann könnte man vielleicht zumindest vermeiden, daß dort pausenlos der Code abgearbeitet wird.
Mir fehlt leider die passende Stelle, um die automatische Berechnung genau dann zu aktivieren, wenn der Anwender die vollständig aufgebaute Datei sieht. Bei Workbook_open könnte ich sie zumindest deaktivieren.
Ich werde hier selber recherchieren, ob es möglichweise abfragbare Felder diesbezüglich gibt (Application.visible - in der Art).
Viele Grüße
Christian
Anzeige
AW: VB-Optimierung
28.05.2009 12:44:55
David
Christian,
wenn du dein Makro so aufbaust:

Private Sub Worksheet_Calculate()
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
dein Code
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub


sollte das "rasend" schnell ablaufen.
Wenn danach immer noch Performance-Probleme sind, werden diese DEFINITIV nicht durch eine Veränderung DIESES Codes beeinflußt.
Zitat: Diese wird aber bei Aufruf der Anwendung lokal auf den Rechner des Anwenders kopiert und dort erweitert (Zeilen und Formeln werden eingefügt/generiert und mit Daten bestückt).
DAS scheint mir der eigentliche "Killer" zu sein, leider hast du dazu keine Details geschrieben. Aber wenn du dort eh nicht eingreifen kannst, sehe ich keinerlei Optimierungsmöglichkeiten.
Gruß
David

Anzeige
AW: VB-Optimierung
28.05.2009 08:51:17
Werner
Hallo Christian,
hast Du schon folgende VBA-Syntax probiert?

 Sub Workbook_Open()    Application.Calculation = xlCalculationManual End Sub Private Sub  _ Workbook_BeforeClose(Cancel As Boolean)    Application.Calculation = xlCalculationAutomatic End Sub


Gruß
Werner

Anzeige
AW: VB-Optimierung
28.05.2009 12:13:27
CRossmeissl
Hi,
danke für die Bemühungen.
Aus meiner Sicht ist das Setzen von Application.Calculation = xlCalculationAutomatic in Workbook_BeforeClose jedoch zu spät. Die Ergebnisse der Formeln sollten bei Anwender-Eingaben ja neu berechnet werden. Habe ich die Berechnung ausgeschaltet, so sieht der Anwender dann nicht mehr die aktuellen Ergebnisse. Hast Du dazu noch eine andere Idee?
Viele Grüße
Christian
AW: VB-Optimierung
28.05.2009 12:30:53
Werner
Hallo Christian,
ja, ich hatte genau das gleiche Problem mit einer Jahresarbeitsplanung, die extrem viele Matrixformeln und Verweise hat.
Gelöst habe ich es mit der Worksheet-Change Methode.
Hier muss dann der Bereich eingetragen werden, wo Eingaben erfolgen, die die Neuberechnung auslösen sollen:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If (Target.Address = "$A$1") Or _
(Target.Address = "$A$5") Or _
(Target.Address = "$B$5:$D$5") _
Then
Calculate
MsgBox "irgendeine Aktion"
End If
End Sub


nachdem ich vorher die automatische Berechnung ausgeschaltet habe, wird diese so einmalig angeschubst.
Gruß
Werner

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige