Herbers Excel-Forum - das Archiv

Worksheet_Change-Ereignis

Bild

Betrifft: Worksheet_Change-Ereignis
von: Kersten

Geschrieben am: 18.12.2006 10:37:08
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

Bild

Betrifft: Dicker Fehler !!!!!!!!!!!!!!
von: Kersten
Geschrieben am: 18.12.2006 11:17:26
Hallo
ich sehe gerade das mir ein dicker Fehler unterlaufen ist.
Das Ereignis wodurch die Berechnungsroutiene ausgelöst wird ist das:
Worksheet_SelectionChange(ByVal Target As Range)
Sorry. Aber jetzt macht es wieder Sinn. ;-)
Gruß Kersten
Bild

Betrifft: AW: Dicker Fehler !!!!!!!!!!!!!!
von: fcs

Geschrieben am: 18.12.2006 11:48:31
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
Bild

Betrifft: Ich glaube so geht es auch
von: Kersten

Geschrieben am: 18.12.2006 13:47:43
Hi
Ich glaube ich habe eine Lösung gefunden
So könnte es auch gehen.
So wird offenbar das Ereignis nur einmal aufgerufen bezogen auf die Inhaltsänderung.
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Call berechnen1
Application.EnableEvents = True
End Sub

 Bild