Worksheet_Change-Ereignis

Hallo zusammen
Ich suche eine Alternative zu folgendem Problem:
Ich habe eine Tabelle,als Arbeitsvorlage, die (zeilenmäßig) sehr umfangreich werden kann.
In jeder Zeile werden Berechnungen durchgeführt. Einfache mathematische.
Diese kann, und habe ich bisher ganz normal mit entsprechenden Formeln in den einzelnen Zellen berechenen lassen. Das heißt die Tabelle hat Leben in sich. Dieses birgt so maches Problem wenn mehrer Leute damit Arbeiten. Außerdem wird die Datei sehr groß, da ich für einen sehr großen Tabellenbereich diese Formeln vorhalten muss.
Jetzt habe ich das Problem mittels VBA und entsprechende Berechnungsroutienen (Schleifen für Einlesen, Berechnung und Ausgabe) (Zeile für Zeile) versucht zu lösen. Klappt auch soweit ganz gut.
Diese Vorgänge (Einlesen, berechnen Ausgabe) lasse ich durch das Worksheet_Change(ByVal Target As Excel.Range)-Ereignis auslösen.
Mein Problem:
Bei jedem Zellenwechsel (Curserwechsel) wird dieses Ereignis ausgelöst. Bei einem Umfang von vielleicht 50 Zeilen ist das auch noch akzeptabel. Aber bei Zeilenmengen von über 100 oder 200 dauert ein Curserwechesel eine halbe Ewigkeit.
Das Calulations-Ereignis ist dafür auch völlig ungeeignet, weil sich das Programm dann totläuft.
Gibt es eine Möglichkeit solche Berechnungsschleifen zeitlich aktzeptabel zu lösen? Und welches Ereignis ist vielleicht besser geeignet.
Ich habe nachfolgend mal einen Auszug aus dieser Berechnungsroutiene (Prototyp) angehängt: Ausgelöst wie oben beschrieben.
Danke schon mal
Gruß Kersten


Sub berechnen1()
Application.ScreenUpdating = False
Dim rng1 As Range
Dim rowende As Integer
Dim MaterialEK As Variant
Dim Materialzuschlag As Variant
Dim Stundensatz As Integer
Dim wks As Worksheet
Set wks = ActiveSheet
'Berechnungsgrenze Zeilengrenze letzte Eintragung festlegen
rowende = wks.UsedRange.Rows.Count
'zuvor berechnete Bereiche und Eintragungen löschen
Range("J68:AF" & rowende).ClearContents
'Schleife für Dateneinlesen berechnen und Ausgabe
For intcounter = 68 To rowende - 1
Stundensatz = 0
Eigenfertigung = 0
Selbstkosten = 0
'Einlsen
MaterialEK = Range("D" & intcounter).Value * Range("F" & intcounter).Value
Materialzuschlag = (Range("I4").Value) * MaterialEK + (Range("I5").Value) * MaterialEK
'Ausgabe
Range("O" & intcounter).Value = MaterialEK
Range("P" & intcounter).Value = Materialzuschlag
Range("Q" & intcounter).Value = MaterialEK + Materialzuschlag
If Not Range("G" & intcounter).Value = Leer Then
'sverweis für Datenzuordnung
Stundensatz = Application.WorksheetFunction.VLookup(Sheets("Tabelle1").Range("G" & intcounter).Value, Sheets("Tabelle1").Range("C3:E17").Value, 3, False)
Eigenfertigung = Stundensatz * Range("H" & intcounter)
'Ausgabe
Range("R" & intcounter).Value = Eigenfertigung
Range("J" & intcounter).Value = Application.WorksheetFunction.VLookup(Sheets("Tabelle1").Range("G" & intcounter).Value, Sheets("Tabelle1").Range("C3:E17").Value, 2, False)
End If
'Summenberechnung uns Ausgabe
Selbstkosten = MaterialEK + Materialzuschlag + Eigenfertigung
Range("K" & intcounter).Value = Selbstkosten
Next intcounter
Application.ScreenUpdating = True
End Sub

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Hallo Kerstin,
auch das Change-Ereignis wird dich auf die Dauer nicht weiterbringen. So wie ich das lese, wird bei jeder Eingabe immer fast die komplette Tabelle ab Zeile 68 neu berechnet, d.h. bei jeder Dateneingabe wird eine Wartezeit fällig, die mit der Größe der Tabelle wächst.
Generell sind Formeln mit Excelstandard-Funktionen in Tabellen immer schneller als entsprechender VBA-Code.
Du könntest jetzt zwei Wege zur Beschleunigung gehen:
1. Gestalte die Prozeduren beim Change-Ereignis so um, dass immer nur die Zeilen neu berechnet werden, die auch auch betroffen sind. Dies sind im allgemeinen nur die Zeile(n) in der(denen) gerade ein Wert geändert wurde und ggf. Zellen in denen Summenwerte berechnet werden.
2. Berechne wie früher die Tabellenwerte per Formel, allerdings schreibst du nicht alle Formeln für die maximal zu erwartende Zahl an Eingabezeilen in die Tabelle sondern nur für die 1. Eingabezeile (oder evtl auch 10 oder 20).
Das Change-Ereignis gestaltes du du so, dass dann, wenn Eingaben in eine Zeile ohne Berechnungs-Formeln erfolgen, die Berechnungsformeln in der Zeile per VBA generiert werden.
Zusätzlich solltest du Zellenformat und Blattschutz so einstellen, dass Eingaben nur in den dafür vorgesehenen Zellen/Bereichen möglich sind.
Gruß
Franz

Beliebteste Forumthreads (12 Monate)

3 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

3
Beiträge zum Forumthread

Beiträge zu diesem Forumthread